Improving Compile time performance | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Improving Compile time performance

Are there any resources available for improving compile time performance of a view? I am involved in report development and regularly have to work quite hard to denormalise a relational database structure into something that can be reported from. In this process, I have managed to create a view that requires a significant amount of time to parse and compile, and relatively little time to execute. Using the forceplan option, the compile time is reduced considerable, although the execution time suffers. Sample CPU times, as reported using statistics time, are: Parse and Compile: 148434 ms
Execute: 7ms The views are quite complex, but there must be a way of improving on these figures. Thanks in advance
If all of the objects in your views use the format of database_owner.object_name, then once an initial view has run, it will automatically reuse the same execution plan the next time. SQL Server 7.0 and 2000 have the ability to reuse execution plans if the database_owner names are used when referring to objects. Other than this, views don’t offer much in the way of optimizing their execution plan. Also keep in mind that if the parameters used in your views change a lot, then perhaps you don’t want to reuse the same execution plan because it may not always be appropriate for every set of different parameters you use. If you run the code from the view in QA, without the CREATE VIEW code, how long does it take to compile and execute? ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
Using a different view, (life’s too short to wait 150sec for 33 rows) For select * from view:
SQL Server parse and compile time:
CPU time = 63015 ms, elapsed time = 63230 ms.
SQL Server Execution Times:
CPU time = 7 ms, elapsed time = 7 ms. For view select statement:
SQL Server parse and compile time:
CPU time = 71094 ms, elapsed time = 71343 ms. SQL Server Execution Times:
CPU time = 8 ms, elapsed time = 8 ms. I have managed to get somewhere with this, since I found that adjusting the indexes on the base tables from which the view gets it’s data improved the performance of the query optimiser, and hence compile time.
It’s always a good idea to optimize the view code in query analyzer (using the execution plan) before using it, so that you can find problems like less than ideal indexing and syntax. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
index on the view? I believe you need Enterprise Edition to do this.
True, in SQL Server 2K EE, the query optimizer will automatically consider the indexed view. Satya SKJ
Hm, well just to clarify, its not really EE you need specifically, its the index tuning wizard which will recommend indexed views. This can be invoked from EE, but more commonly (for me anyway) from within Query Analyser also.
Chappy, thanks for keeping me honest. From technet: Note Indexed views can be created in any edition of SQL Server 2000. In SQL Server 2000 Enterprise Edition, the indexed view will be automatically considered by the query optimizer. To use an indexed view in all other editions, the NOEXPAND hint must be used.
Further clarification and amendment to my reply above:
Indexed views can be created in any edition of SQL Server 2000. Satya SKJ
> Chappy, thanks for keeping me honest. Hehe, thats ok. But I had misread what you said anyway, so apologies for that. I assumed you have said Enterprise Manager, not Enterprise Edition. As luck would have it my post still stands tho, because index tuning wizard comes with standard edition too
The Enterprise vs. Standard edition difference concerning indexed views does not refer to the index tuning wizard, but to the query optimizer. By default the EE version of the optimizer will consider the use of an indexed view in the creation of an execution plan. The Standard edition of the optimizer will not(a query hint is required before an indexed view will be used).
Very sorry. I was not aware of this!
]]>