Dynamic SQL and Execution plan and very slow. A few questions
Hey. I've a problem and I think I know the answer also but still want to confirm. We are using SQL 2000 and SSRS 2000. The problem is, we have custom reports which a customer can build and run. I wonder how one can write sp's for that. The way it's written right now is a dynamic select clause then a dynamic, from, a dynamic where, dynamic groupby all appended tor\gether and run by execute command. I know it'd dynamic SQL and execution plans and stuff will hurt me but someof these reports take forever. Is there anything that can be done to fasten these reports? And if the select will be dynamic and the where will be dynamic, does it make sense to even use a sp? Is it ever going to use the same execution plan? When I run DBCC memorystatus, procedure cache takes up most of this memory. Does the use of dynamic SQL explain that?
Thank you for your time and effort in replying.
[908 byte] By [
Tej] at [2007-12-24]
The main problem might be the amount of ad-hoc SQL code being generated from the client. This introduces compilation overhead on the server and bloating of procedure cache. It is possible to rewrite the SELECT using SPs so that you can split a complex query into simpler ones and return results using temporary tables for example. But it is hard to say if it will work for everything. For example, you can design SPs that use optional search parameters but if you use dynamic SQL within the SP you are just switching the problem from one side to another. So it depends on your requirements for the custom reports.
Below are some questions:
1. What does the custom reports functionality do at a high-level?
2. Does the custom reports allow you to select columns for display? If so, you can use SPs to return results with fixed number of columns and filter the columns to display on the client-side
3. Does the custom reports allow you to search on multiple columns or combination of columns depending on user selection? If so, you can still use SPs to simplify lot of the logic instead embedding all the code in the client. Please take a look at
http://www.sommarskog.se/dyn-search.html for some suggestions
4. If SSRS allows you to embed SQL batches then you can split complex queries into simpler ones by creating temporary table for example. This gets ugly soon because debugging such embedded SQL code is going to be hard
You'd be hard-pressed to resolve every issue but you may be able to find some commonality among your reports. For example, are your reports based on date ranges? You might benefit from creating indexes on your dates if the indexes can are selective enough to make sure that less than 5% of the rows in your table are returned (<5% allows for bookmark lookup).
You might try the following:
- Get a list of the most common, worst-performing reports
- Generate each of the reports while capturing the results in Profiler
- Save the profiler trace and analyze it with Index Tuning Wizard (Database Tuning Advisor in SQL 2005)
Since ITW is not performing a full analysis on your workload, don't follow recommendations for dropping indexes but consider adding the indexes and statistics recommended by ITW.
Hey. I might be able to break the complex queries into simpler ones. The problem will still be there with the procedure cache taking up a lot of space. What would happen if I broke these queries and still a lot of them have to be recomplied cuz they are different?
1. What does the custom reports functionality do at a high-level?
When you create a report, you'll be passing on the select clause, from clause, where clause, group by clause, order clause to SQL and SQL would return you the results and then SSRS would process it and display it and sometimes pivot etc... Basically, certain columns from various tables are shown or not shown on the report. This is not it. For any given report, there could be 5 different where clauses created and these different where's would be on different tables.
2. Does the custom reports allow you to select columns for display? If so, you can use SPs to return results with fixed number of columns and filter the columns to display on the client-side
Yes, the custom reports allow us to select columns, so the select clause is never static. But the total number of max columns in the select clause could be about 55 from about 15 different tables. This dynamic SQL that gets the data actually joins about 15 different tables. Do you think it would be worth sending all 55 and SSRS displaying only about 8 to 10 columns?
3. Yes, reports allow the customers to search on multiple columns depending on their selection but this is not a huge selection(select 1or2 out of 8) assuming we are talking about a single report.
Thank you for all your time and effort.
Tej at 2007-10-8 >
