Increase the Rendering Timing of Reports

Hi,

We are using SQL Server 2005 Reporting Services for creating Reports. But report execution is taking bit time to give results.

Is there any way around to increase the rendering timing ?

Thx

First step would be to find where the delay is. There is a table called ExecutionLog in the reportserver database catalog. you can query this table and look at columns - TimeDataRetrival, Time processing, time rendering for this report to find out where the delay is. If the delay is in TimeDataRetrival, it means that your SQL query performance is the one to be blamed. You can optimize the query which is used in the report and get over it. NOTE: Always open the ExecutionLog table with no lock hint.

Incorrect syntax near ).

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near ')'.

Source Error:

Line 42: objAdapter.SelectCommand = New System.Data.SqlClient.SqlCommand(strSQL, objConn)
Line 43: ' Fill the dataset.
Line 44: objAdapter.Fill(objDataset)
Line 45: ' Create a new view.
Line 46: Dim oView As New DataView(objDataset.Tables(0))

 Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Dim AllorOne As String Dim company As String AllorOne = Request.QueryString("AllorOne") company = Request.QueryString("company")' Create a connection and open it. Dim objConn As New System.Data.SqlClient.SqlConnection("User ID=******;Password=******;Initial Catalog=customer_config;Data Source=rqa4-sql01;") objConn.Open() Dim strSQL As String Dim objDataset As New DataSet() Dim objAdapter As New System.Data.SqlClient.SqlDataAdapter() ' Get all the sql If AllorOne ="All" Then strSQL ="SELECT [Company_Name] as 'Company Name', [Entity_Code] as 'Entity Code', [YTD_Transactions] as 'YTD_Transactions', [Num_Reports_DB] as 'Num_Reports_DB', [Reports_Advanced_Workflow] as 'Reports_Advanced_Workflow', [Has_Customization] as 'Has_Customization',[TA_Trans_All] as 'TA Trans All', [TA_Trans_US] as 'TA Trans US', [Using_VAT] as 'Using VAT', [Total_Users] as 'Total Users',[Non_English_Langages] as 'Non English Langages',[Non_English_Users] as 'Non English Users', [Using_Offline] as 'Using Offline', [Using_Audit_Services] as 'Using Audit Services' from(v_customer_master_list) order by company_name" Else strSQL ="SELECT [Company_Name] as 'Company Name', [Entity_Code] as 'Entity Code', [YTD_Transactions] as 'YTD_Transactions', [Num_Reports_DB] as 'Num_Reports_DB', [Reports_Advanced_Workflow] as 'Reports_Advanced_Workflow', [Has_Customization] as 'Has_Customization',[TA_Trans_All] as 'TA Trans All', [TA_Trans_US] as 'TA Trans US', [Using_VAT] as 'Using VAT', [Total_Users] as 'Total Users',[Non_English_Langages] as 'Non English Langages',[Non_English_Users] as 'Non English Users', [Using_Offline] as 'Using Offline', [Using_Audit_Services] as 'Using Audit Services' from(v_customer_master_list) where lower(company_name) like '%" & company &"%' order by company_name" End If objAdapter.SelectCommand = New System.Data.SqlClient.SqlCommand(strSQL, objConn)' Fill the dataset. objAdapter.Fill(objDataset) ' Create anew view. Dim oView As New DataView(objDataset.Tables(0)) ' Set up the data grid and bind the data. SimpleDataGrid.DataSource = oView SimpleDataGrid.DataBind() End Sub

Can anyone tell me what causes this type of error, what they think is wrong, or what IS wrong if someone wants to take the time to go through the code. Anything helps. Eh, I feel like one of those guys on a street corner with a sign when I say that.

Since the exception that was thrown is of type: System.Data.SqlClient.SqlException, then the error is in the sql statement itself and not in the code that is calling it.

can you run your sql statement in query analyzer?

|||

Visual Web Developer "helped" me out by adding "(" and ")" around my "from" statement in the SQL. Thanks for pointing out it had to be in the SQL.

|||

I am having the same problem except I dont know where the problem is after reading your posts. Could someone please explain a bit more on where I can find/fix the error in SQL statement??

Inconsistent Linked Server Execution Plans

I am witnessing something odd with regard to execution plans and a
linked server.
I have a restore of a production database in two different
environments, one DEV the other QA. This database resides on the linked
server in our setup. The other database -- the local database -- has
been installed and populated by a script in both DEV and QA. The
version and edition of SQL Server are identical in both environments.
In the DEV environment, executing a query which uses the linked server
(in a four part name) results in a reasonable execution plan with
filtering ocurring on the remote database before the rows are returned.
However, in the QA environment the same query results in an execution
plan that has _all_ of the rows from the remote tables returned before
filtering. The tables in the database on the linked server are somewhat
large > 1M. The execution times differ by a vast amount.
I'm really at a loss. I know the query optimizer is a fickle mistress
but is this evidence of something predictable and knowable? What are
the types of things that I might take a look at in order to discover
why the same query in two very similar environments generates such
wildly different execution plans? And, more than that why on earth
would the query optimizer not just as a matter of course _always_
filter the rows remotely before returning them? Is there any way to
force the query optimizer to filter the rows remotely before returning
them? I've looked at the openquery function which does this but is not
practical in this circumstance given the query itself. Rearchitecture
is an option but I'm dying for some kind of explanation for this
behavior. My brain is broke.Hi
I am not familiar with your problem in particular, but you could try
updating indexes and statistics on the linked server to see if anything
changes. To avoid any possible filtering problem you could execute a remote
stored procedure or possibly use OPENQUERY.
John
"person" wrote:

> I am witnessing something odd with regard to execution plans and a
> linked server.
> I have a restore of a production database in two different
> environments, one DEV the other QA. This database resides on the linked
> server in our setup. The other database -- the local database -- has
> been installed and populated by a script in both DEV and QA. The
> version and edition of SQL Server are identical in both environments.
> In the DEV environment, executing a query which uses the linked server
> (in a four part name) results in a reasonable execution plan with
> filtering ocurring on the remote database before the rows are returned.
> However, in the QA environment the same query results in an execution
> plan that has _all_ of the rows from the remote tables returned before
> filtering. The tables in the database on the linked server are somewhat
> large > 1M. The execution times differ by a vast amount.
> I'm really at a loss. I know the query optimizer is a fickle mistress
> but is this evidence of something predictable and knowable? What are
> the types of things that I might take a look at in order to discover
> why the same query in two very similar environments generates such
> wildly different execution plans? And, more than that why on earth
> would the query optimizer not just as a matter of course _always_
> filter the rows remotely before returning them? Is there any way to
> force the query optimizer to filter the rows remotely before returning
> them? I've looked at the openquery function which does this but is not
> practical in this circumstance given the query itself. Rearchitecture
> is an option but I'm dying for some kind of explanation for this
> behavior. My brain is broke.
>|||I updated the statistics on all the databases involved to no avail. I'm
unable to use the OPENQEURY function since I have to join tables across
the databases.
Which execution plan seems to be the anomaly? Would it be the execution
plan where the data is filtered on the linked server prior to being
returned or the execution plan that returns all the rows before
filtering?|||I think I may have found some promising information:
http://www.sql-server-performance.com/linked_server.asp
/quote
When running distributed queries on a linked server, if the linked
server has the same character set and sort order (collation) as the
local SQL Server, then you can reduce overhead and boost performance if
you set the SP_SERVEROPTION "collation compatible" option to true. What
this setting does is tell SQL Server to assume that all columns and
character sets on the remote server are compatible with the local
server. This same option can also be turned on for a linked server
using Enterprise Manager
If this option is not selected, then the distributed query being
executed on the remote server must return the entire table to the local
server in order for the WHERE clause to be applied. As you can imagine,
this could potentially return a lot of unnecessary data over the
network, slowing it down.
If the option is selected, (which is always recommended if the
collations are the same on both servers), then the WHERE clause is
applied on the remote server. This, of course, means that much less
data is transmitted over the network, often greatly speeding up the
distributed query. [7.0, 2000] Updated 2-16-2004
/endquote|||Hi
Also check out the other information at the bottom of the article.
Some examples of locally performed operations include:
Data conversion operations
Queries that use the bit, timestamp, or uniqueidentifier data types
Queries that use the TOP clause
INSERTS, UPDATES, or DELETES
John
"person" wrote:

> I think I may have found some promising information:
> http://www.sql-server-performance.com/linked_server.asp
> /quote
> When running distributed queries on a linked server, if the linked
> server has the same character set and sort order (collation) as the
> local SQL Server, then you can reduce overhead and boost performance if
> you set the SP_SERVEROPTION "collation compatible" option to true. What
> this setting does is tell SQL Server to assume that all columns and
> character sets on the remote server are compatible with the local
> server. This same option can also be turned on for a linked server
> using Enterprise Manager
> If this option is not selected, then the distributed query being
> executed on the remote server must return the entire table to the local
> server in order for the WHERE clause to be applied. As you can imagine,
> this could potentially return a lot of unnecessary data over the
> network, slowing it down.
> If the option is selected, (which is always recommended if the
> collations are the same on both servers), then the WHERE clause is
> applied on the remote server. This, of course, means that much less
> data is transmitted over the network, often greatly speeding up the
> distributed query. [7.0, 2000] Updated 2-16-2004
> /endquote
>|||The collation compatibility was not the magic bullet I was hoping it
would be. The two database use the same collation and enabling the
collation compatibility option on the linked server had no effect.
However, digging in further it now appears that there is a correlation
between W2003 and the poor linked server performance.
In our initial setup in the dev environment the local server was a W2K
box while the remote server was a W2003 box. In the QA environment both
servers are W2003. I installed the local db on W2003 box in the Dev
environment. To my surprise, the problem is now reproducible. I can
reproduce the problem in the dev environment only when the local db is
on a W2003 box but not when it is on a W2K box.
So there is some correlation between W2003 and poor linked server
performance.|||Hi
A pure guess would be the check out DTC is working correctly and possibly
re-install it. [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306843[/ur
l]
John
"person" wrote:

> The collation compatibility was not the magic bullet I was hoping it
> would be. The two database use the same collation and enabling the
> collation compatibility option on the linked server had no effect.
> However, digging in further it now appears that there is a correlation
> between W2003 and the poor linked server performance.
> In our initial setup in the dev environment the local server was a W2K
> box while the remote server was a W2003 box. In the QA environment both
> servers are W2003. I installed the local db on W2003 box in the Dev
> environment. To my surprise, the problem is now reproducible. I can
> reproduce the problem in the dev environment only when the local db is
> on a W2003 box but not when it is on a W2K box.
> So there is some correlation between W2003 and poor linked server
> performance.
>|||I'm not sure that that article applies to my situation. We're not using
distributed transactions.
Does this seem like a situation where a support ticket to Microsoft
might be advisable? I can consistently demonstrate that the query
optimizer devises radically different execution plans for the same
stored procedure depending on which operating system the local db is on
(all other factors being equal -- or apparently so). I don't know if
anybody from Microsoft looks at this forum...but would starting a new
thread with a more specific subject line get this problem any better
exposure?|||Hi
It is not something I have come across. I could not find anything at
http://lab.msdn.microsoft.com/productfeedback so raising it with PSS may be
an idea.
John
"person" wrote:

> I'm not sure that that article applies to my situation. We're not using
> distributed transactions.
> Does this seem like a situation where a support ticket to Microsoft
> might be advisable? I can consistently demonstrate that the query
> optimizer devises radically different execution plans for the same
> stored procedure depending on which operating system the local db is on
> (all other factors being equal -- or apparently so). I don't know if
> anybody from Microsoft looks at this forum...but would starting a new
> thread with a more specific subject line get this problem any better
> exposure?
>|||Did you come up with an explanation for the differences in the way the
two OSs handled the queries?
I'm curious, because I migrated from a perfectly-good W2K installation
to a W2003 machine, and my OLE DB Sybase linked server threw ALL KINDS
of errors... things that look to me like collation differences.
Rick

Inconsistent Linked Server Execution Plans

I am witnessing something odd with regard to execution plans and a
linked server.
I have a restore of a production database in two different
environments, one DEV the other QA. This database resides on the linked
server in our setup. The other database -- the local database -- has
been installed and populated by a script in both DEV and QA. The
version and edition of SQL Server are identical in both environments.
In the DEV environment, executing a query which uses the linked server
(in a four part name) results in a reasonable execution plan with
filtering ocurring on the remote database before the rows are returned.
However, in the QA environment the same query results in an execution
plan that has _all_ of the rows from the remote tables returned before
filtering. The tables in the database on the linked server are somewhat
large > 1M. The execution times differ by a vast amount.
I'm really at a loss. I know the query optimizer is a fickle mistress
but is this evidence of something predictable and knowable? What are
the types of things that I might take a look at in order to discover
why the same query in two very similar environments generates such
wildly different execution plans? And, more than that why on earth
would the query optimizer not just as a matter of course _always_
filter the rows remotely before returning them? Is there any way to
force the query optimizer to filter the rows remotely before returning
them? I've looked at the openquery function which does this but is not
practical in this circumstance given the query itself. Rearchitecture
is an option but I'm dying for some kind of explanation for this
behavior. My brain is broke.Hi
I am not familiar with your problem in particular, but you could try
updating indexes and statistics on the linked server to see if anything
changes. To avoid any possible filtering problem you could execute a remote
stored procedure or possibly use OPENQUERY.
John
"person" wrote:
> I am witnessing something odd with regard to execution plans and a
> linked server.
> I have a restore of a production database in two different
> environments, one DEV the other QA. This database resides on the linked
> server in our setup. The other database -- the local database -- has
> been installed and populated by a script in both DEV and QA. The
> version and edition of SQL Server are identical in both environments.
> In the DEV environment, executing a query which uses the linked server
> (in a four part name) results in a reasonable execution plan with
> filtering ocurring on the remote database before the rows are returned.
> However, in the QA environment the same query results in an execution
> plan that has _all_ of the rows from the remote tables returned before
> filtering. The tables in the database on the linked server are somewhat
> large > 1M. The execution times differ by a vast amount.
> I'm really at a loss. I know the query optimizer is a fickle mistress
> but is this evidence of something predictable and knowable? What are
> the types of things that I might take a look at in order to discover
> why the same query in two very similar environments generates such
> wildly different execution plans? And, more than that why on earth
> would the query optimizer not just as a matter of course _always_
> filter the rows remotely before returning them? Is there any way to
> force the query optimizer to filter the rows remotely before returning
> them? I've looked at the openquery function which does this but is not
> practical in this circumstance given the query itself. Rearchitecture
> is an option but I'm dying for some kind of explanation for this
> behavior. My brain is broke.
>|||I updated the statistics on all the databases involved to no avail. I'm
unable to use the OPENQEURY function since I have to join tables across
the databases.
Which execution plan seems to be the anomaly? Would it be the execution
plan where the data is filtered on the linked server prior to being
returned or the execution plan that returns all the rows before
filtering?|||I think I may have found some promising information:
http://www.sql-server-performance.com/linked_server.asp
/quote
When running distributed queries on a linked server, if the linked
server has the same character set and sort order (collation) as the
local SQL Server, then you can reduce overhead and boost performance if
you set the SP_SERVEROPTION "collation compatible" option to true. What
this setting does is tell SQL Server to assume that all columns and
character sets on the remote server are compatible with the local
server. This same option can also be turned on for a linked server
using Enterprise Manager
If this option is not selected, then the distributed query being
executed on the remote server must return the entire table to the local
server in order for the WHERE clause to be applied. As you can imagine,
this could potentially return a lot of unnecessary data over the
network, slowing it down.
If the option is selected, (which is always recommended if the
collations are the same on both servers), then the WHERE clause is
applied on the remote server. This, of course, means that much less
data is transmitted over the network, often greatly speeding up the
distributed query. [7.0, 2000] Updated 2-16-2004
/endquote|||Hi
Also check out the other information at the bottom of the article.
Some examples of locally performed operations include:
Data conversion operations
Queries that use the bit, timestamp, or uniqueidentifier data types
Queries that use the TOP clause
INSERTS, UPDATES, or DELETES
John
"person" wrote:
> I think I may have found some promising information:
> http://www.sql-server-performance.com/linked_server.asp
> /quote
> When running distributed queries on a linked server, if the linked
> server has the same character set and sort order (collation) as the
> local SQL Server, then you can reduce overhead and boost performance if
> you set the SP_SERVEROPTION "collation compatible" option to true. What
> this setting does is tell SQL Server to assume that all columns and
> character sets on the remote server are compatible with the local
> server. This same option can also be turned on for a linked server
> using Enterprise Manager
> If this option is not selected, then the distributed query being
> executed on the remote server must return the entire table to the local
> server in order for the WHERE clause to be applied. As you can imagine,
> this could potentially return a lot of unnecessary data over the
> network, slowing it down.
> If the option is selected, (which is always recommended if the
> collations are the same on both servers), then the WHERE clause is
> applied on the remote server. This, of course, means that much less
> data is transmitted over the network, often greatly speeding up the
> distributed query. [7.0, 2000] Updated 2-16-2004
> /endquote
>|||The collation compatibility was not the magic bullet I was hoping it
would be. The two database use the same collation and enabling the
collation compatibility option on the linked server had no effect.
However, digging in further it now appears that there is a correlation
between W2003 and the poor linked server performance.
In our initial setup in the dev environment the local server was a W2K
box while the remote server was a W2003 box. In the QA environment both
servers are W2003. I installed the local db on W2003 box in the Dev
environment. To my surprise, the problem is now reproducible. I can
reproduce the problem in the dev environment only when the local db is
on a W2003 box but not when it is on a W2K box.
So there is some correlation between W2003 and poor linked server
performance.|||Hi
A pure guess would be the check out DTC is working correctly and possibly
re-install it. http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306843
John
"person" wrote:
> The collation compatibility was not the magic bullet I was hoping it
> would be. The two database use the same collation and enabling the
> collation compatibility option on the linked server had no effect.
> However, digging in further it now appears that there is a correlation
> between W2003 and the poor linked server performance.
> In our initial setup in the dev environment the local server was a W2K
> box while the remote server was a W2003 box. In the QA environment both
> servers are W2003. I installed the local db on W2003 box in the Dev
> environment. To my surprise, the problem is now reproducible. I can
> reproduce the problem in the dev environment only when the local db is
> on a W2003 box but not when it is on a W2K box.
> So there is some correlation between W2003 and poor linked server
> performance.
>|||I'm not sure that that article applies to my situation. We're not using
distributed transactions.
Does this seem like a situation where a support ticket to Microsoft
might be advisable? I can consistently demonstrate that the query
optimizer devises radically different execution plans for the same
stored procedure depending on which operating system the local db is on
(all other factors being equal -- or apparently so). I don't know if
anybody from Microsoft looks at this forum...but would starting a new
thread with a more specific subject line get this problem any better
exposure?|||Hi
It is not something I have come across. I could not find anything at
http://lab.msdn.microsoft.com/productfeedback so raising it with PSS may be
an idea.
John
"person" wrote:
> I'm not sure that that article applies to my situation. We're not using
> distributed transactions.
> Does this seem like a situation where a support ticket to Microsoft
> might be advisable? I can consistently demonstrate that the query
> optimizer devises radically different execution plans for the same
> stored procedure depending on which operating system the local db is on
> (all other factors being equal -- or apparently so). I don't know if
> anybody from Microsoft looks at this forum...but would starting a new
> thread with a more specific subject line get this problem any better
> exposure?
>|||Did you come up with an explanation for the differences in the way the
two OSs handled the queries?
I'm curious, because I migrated from a perfectly-good W2K installation
to a W2003 machine, and my OLE DB Sybase linked server threw ALL KINDS
of errors... things that look to me like collation differences.
Rick|||Rick,
No real explanation yet as to why the OSs handle the queries
differently.
We put in a support ticket with Microsoft. Their tech said to enable
the dynamic parameters option for the Sql Server provider. So in other
words, drop the linked server and recreate it with the dynamic
parameters option selected. I tried that and it had no effect. The
Microsoft tech then suggested we reboot the machine. I did and it
appears to have had a good effect. I am still in the process of testing
and I will post an update if anything interesting ocurrs. Here is the
knowledge base article the tech provided as documentation for his
suggestion:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q320208.
The article doesn't mention the reboot step. But it is a Microsoft
product and well...
Interestingly enough, the Sql Server provider option for the dynamic
parameters is _not_ selected on the w2k machine. The execution plan on
the w2003 machine is still not as efficient as on the w2k machine but
I'm closer than where I was last month.

Inconsistand execution of SISS Package

Hi

Can someone please help?

I have a small package that makes an OLDB connection to my local 2005 SQL server then does some processing and places the results in a text file as XML

I have created a stored proc that uses xp_cmdshell to call the package and pass a paramter to it.

It all works fine when I call it from the command line or execute the stored proc from SQL Management Studio or if I call it as part of a SQL Agent job, but when it is called from C# from an ASP page then it fails with a timeout error.

Even stanger is the fact that when there is no data there for it to process it runs OK so I dont think it is permissions but the size of the output file only has to be a couple of k for it to fail from the ASP page.

If we comment out some of the functions in the package then it will execute OK from ASP

Help Whats going on ?

Got a work around for it at the moment. I created an agent job which starts the package then used sp_start_job in my application to start the job and hence the package and it all works OK

Would like to know why I cant get it to work directly from the application though

Cheers

inconsistancy in execution time of SP. Need help.

Hi,
I have a stored procedure which creates a temporary table and insert into it
selected entries.
When executing the procedure, selecting few thousands entries, it takes few
MINUTES for it to finish.
When executing the same code in the query analyzer it takes a second to
finish.
I can't work as the accessing web page fails to get the data because of the
long time it takes.
Does anyone have an idea what's wrong with my procedure?
Thanks,
I.P.You're seeing sp recompiled. I suggest you take a look at this kb.
http://support.microsoft.com/kb/243586
-oj
"I.P." <lafafa@.yahoo.com> wrote in message
news:d7hce4$b86$1@.news.iucc.ac.il...
> Hi,
> I have a stored procedure which creates a temporary table and insert into
> it selected entries.
> When executing the procedure, selecting few thousands entries, it takes
> few MINUTES for it to finish.
> When executing the same code in the query analyzer it takes a second to
> finish.
> I can't work as the accessing web page fails to get the data because of
> the long time it takes.
> Does anyone have an idea what's wrong with my procedure?
> Thanks,
> I.P.
>|||Thanks.
"oj" <nospam_ojngo@.home.com> wrote in message
news:O7xa0gcZFHA.3780@.tk2msftngp13.phx.gbl...
> You're seeing sp recompiled. I suggest you take a look at this kb.
> http://support.microsoft.com/kb/243586
>
> --
> -oj
>
> "I.P." <lafafa@.yahoo.com> wrote in message
> news:d7hce4$b86$1@.news.iucc.ac.il...
>