SQL Server Performance

Improving Compile time performance

Discussion in 'T-SQL Performance Tuning for Developers' started by b00011011, Mar 6, 2003.

  1. b00011011 New Member

    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
  2. bradmcgehee New Member

    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
  3. b00011011 New Member

    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.
  4. bradmcgehee New Member

    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
  5. dtipton New Member

    index on the view? I believe you need Enterprise Edition to do this.
  6. satya Moderator

    True, in SQL Server 2K EE, the query optimizer will automatically consider the indexed view.

    Satya SKJ
  7. Chappy New Member

    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.
  8. dtipton New Member

    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.
  9. satya Moderator

    Further clarification and amendment to my reply above:
    Indexed views can be created in any edition of SQL Server 2000.

    Satya SKJ
  10. Chappy New Member

    > 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





  11. dtipton New Member

    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).
  12. Chappy New Member

    Very sorry. I was not aware of this!

Share This Page