I'm using a couple of SQL data sources to populate parameters for a Telerik report. Some of these queries are loaded on very large databases and take upwards of of a minute to execute. I don't have a problem with that, but once values are selected by a user and the Preview button is clicked (in the Web Viewer) it takes even longer to generate the report. I couldn't understand why this was happening, as the resulting queries execute in a couple of seconds, so I started SQL profiler and discovered that the initial queries to populate the SQL data sources assigned to the parameters execute unnecessarily when you click that Preview button.
Is this expected behavior? Is there a way to prevent this from happening? I'd appreciate any help you could offer on the subject.
Thanks.
Is this expected behavior? Is there a way to prevent this from happening? I'd appreciate any help you could offer on the subject.
Thanks.
4 Answers, 1 is accepted
0
Hi Andrew,
This could happen whenever you have wired the Report Parameters to a DataSourceParameter, which means that the query is changed based on the user input and would trigger its execution again upon this change. This is a preferred approach when normally your data retrieval would take up to a few seconds, so you do not get all of your data unnecessary. However in your case, it seems that this has the opposite effect, so to avoid that, create a query that gets all the data, the SqlDataSource component would cache it, then you can filter the data on report level through the report Filterins.
For more info refer to How-To: Add filtering to Report or you can review the ProductLineSales demo report that filters data this way.
All the best,
Steve
the Telerik team
This could happen whenever you have wired the Report Parameters to a DataSourceParameter, which means that the query is changed based on the user input and would trigger its execution again upon this change. This is a preferred approach when normally your data retrieval would take up to a few seconds, so you do not get all of your data unnecessary. However in your case, it seems that this has the opposite effect, so to avoid that, create a query that gets all the data, the SqlDataSource component would cache it, then you can filter the data on report level through the report Filterins.
For more info refer to How-To: Add filtering to Report or you can review the ProductLineSales demo report that filters data this way.
All the best,
Steve
the Telerik team
NEW in Q1'12: Telerik Report Designer (Beta) for ad-hoc report creation. Download as part of Telerik Reporting Q1 2012. For questions and feedback, use the new Telerik Report Designer Forum.
0
Andrew
Top achievements
Rank 1
Rank 1
answered on 09 Mar 2012, 12:55 PM
Unless I misunderstand what you're saying, I am using a SqlDataSource that does not use parameters as seen at Using Parameters with the SqlDataSource component but instead a simple predefined SQL statement as described at Selecting Data with the SqlDataSource component . Is this not what you are suggesting?
Or are you saying that I need to bind a ReportParameter normally to the SqlDataSource but remove some values described in the attached screenshot?
I would appreciate a bit of clarification.
Thanks.
Or are you saying that I need to bind a ReportParameter normally to the SqlDataSource but remove some values described in the attached screenshot?
I would appreciate a bit of clarification.
Thanks.
0
Accepted
Hello Andrew,
I apologize, upon reading your reply and your original inquiry I believe I have misunderstood your question. Starting all over, first we need to know which is the report viewer you talk about, I would assume ASP.NET viewer or Silverlight viewer.
For the first case, you can get your data in another object i.e. DataTable and use that as a data source for your report parameter in the report constructor. As the report object would be alive in the ASP.NET viewer, the DataTable would be cached in memory (if InProc session) and would be reused, so no second query to the database is made.
As for the Silverlight viewer, we can only suggest caching your data on the Sql Server. For example create a stored procedure that would execute your query conditionally and store it in a temp table. If the temp table is empty (initial query), you execute it and fill the temp table, then upon subsequent request, you would check whether the temp table is empty and if not, directly make the select from it.
Greetings,
Steve
the Telerik team
I apologize, upon reading your reply and your original inquiry I believe I have misunderstood your question. Starting all over, first we need to know which is the report viewer you talk about, I would assume ASP.NET viewer or Silverlight viewer.
For the first case, you can get your data in another object i.e. DataTable and use that as a data source for your report parameter in the report constructor. As the report object would be alive in the ASP.NET viewer, the DataTable would be cached in memory (if InProc session) and would be reused, so no second query to the database is made.
As for the Silverlight viewer, we can only suggest caching your data on the Sql Server. For example create a stored procedure that would execute your query conditionally and store it in a temp table. If the temp table is empty (initial query), you execute it and fill the temp table, then upon subsequent request, you would check whether the temp table is empty and if not, directly make the select from it.
Greetings,
Steve
the Telerik team
NEW in Q1'12: Telerik Report Designer (Beta) for ad-hoc report creation. Download as part of Telerik Reporting Q1 2012. For questions and feedback, use the new Telerik Report Designer Forum.
0
Andrew
Top achievements
Rank 1
Rank 1
answered on 14 Mar 2012, 12:54 PM
That did it. Thanks.