|
Home > Archive > microsoft.public.sqlserver.server > October 2002 > Stored Procedure compile > 10 seconds
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Stored Procedure compile > 10 seconds
|
|
|
| We have a stored procedure that executes a single query. It's a pretty big
query that joins 14 tables (that's another problem that I need to address).
When I execute this for the first time in a query window where I am showing
Statistics IO - it shows the parse and compile time > 10 seconds, sometimes
as much as 18 seconds.
I have a few questions that I was hoping some of the mor experienced folks
could answer:
1) Why does this take so long to parse and compile? We have several other
stored procs with queries containing 20-table joins and it may take 1-2
seconds to parse and compile. This other stored proc in question
consistently takes over 10 seconds.
2) Is there anything I can do to cut some time off the parse and compile
time?
3) If I can't cut down on the parse and compile time - Is there anything I
can do to prevent this plan from being flushed out of the cache?
Thanks in advance,
Tom
| |
| Russell Fields 2002-10-05, 8:51 pm |
| Thomas,
Hmmm. Interesting problem.
The optimizer is looking for an optimal plan as you know. This is not
strictly a question of how many joins are done, but how many options a
particular set of joins offers for processing.
For example, 20 joins may be simple FK relationships to 20 very small
tables. Not much to think about there. Fewer joins may be interrelated in
a more complex manner and that gives the optimizer more to think about.
(We've got some of those, too.)
The bottom line is: simplify the procedure if you can.
Russell Fields
"TJT" <Thomas.Todd@smed.com> wrote in message
news:uVXDeFRSCHA.1976@tkmsftngp11...
> We have a stored procedure that executes a single query. It's a pretty
big
> query that joins 14 tables (that's another problem that I need to
address).
> When I execute this for the first time in a query window where I am
showing
> Statistics IO - it shows the parse and compile time > 10 seconds,
sometimes
> as much as 18 seconds.
>
> I have a few questions that I was hoping some of the mor experienced folks
> could answer:
>
> 1) Why does this take so long to parse and compile? We have several other
> stored procs with queries containing 20-table joins and it may take 1-2
> seconds to parse and compile. This other stored proc in question
> consistently takes over 10 seconds.
>
> 2) Is there anything I can do to cut some time off the parse and compile
> time?
>
> 3) If I can't cut down on the parse and compile time - Is there anything I
> can do to prevent this plan from being flushed out of the cache?
>
> Thanks in advance,
> Tom
>
>
| |
| Gert-Jan Strik 2002-10-05, 8:52 pm |
| With queries like that, there are more possible query plans than the
query optimizer has time to estimate. Therefore, it will estimate the
simplest plans. SQL-Server is optimized to execute the entire statement
as fast as possible. This includes both compilation and execution time.
The query optimizer will keep searching more efficient query plans as
long as it thinks that the total time will decrease.
Indexing might help here. You might want to check out the Index Tuning
Wizard. If there are appropriate indexes, the query optimizer probably
does not have to search too long, because the indexes will guarantee
adequate performance.
I think there is a KEEP PLAN query hint which should keep the query plan
in cache for a longer period than 'regular' query plans.
Hope this helps,
Gert-Jan
TJT wrote:
>
> We have a stored procedure that executes a single query. It's a pretty big
> query that joins 14 tables (that's another problem that I need to address).
> When I execute this for the first time in a query window where I am showing
> Statistics IO - it shows the parse and compile time > 10 seconds, sometimes
> as much as 18 seconds.
>
> I have a few questions that I was hoping some of the mor experienced folks
> could answer:
>
> 1) Why does this take so long to parse and compile? We have several other
> stored procs with queries containing 20-table joins and it may take 1-2
> seconds to parse and compile. This other stored proc in question
> consistently takes over 10 seconds.
>
> 2) Is there anything I can do to cut some time off the parse and compile
> time?
>
> 3) If I can't cut down on the parse and compile time - Is there anything I
> can do to prevent this plan from being flushed out of the cache?
>
> Thanks in advance,
> Tom
| |
| BP Margolin 2002-10-05, 8:52 pm |
| Tom,
Basically as Russell said, you need to try to simplify the procedure. If you
are using SQL Server 2000 ... and please ALWAYS post which version of SQL
Server you are using ... you might consider creating indexed views. Using
indexed views **might** be able to significantly simplify the code. There
are con's to indexed views, so make sure that the con's don't overwhelm the
pro's :-)
Information about indexed views can be found in the SQL Server 2000 Books
Online.
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"TJT" <Thomas.Todd@smed.com> wrote in message
news:uVXDeFRSCHA.1976@tkmsftngp11...
> We have a stored procedure that executes a single query. It's a pretty
big
> query that joins 14 tables (that's another problem that I need to
address).
> When I execute this for the first time in a query window where I am
showing
> Statistics IO - it shows the parse and compile time > 10 seconds,
sometimes
> as much as 18 seconds.
>
> I have a few questions that I was hoping some of the mor experienced folks
> could answer:
>
> 1) Why does this take so long to parse and compile? We have several other
> stored procs with queries containing 20-table joins and it may take 1-2
> seconds to parse and compile. This other stored proc in question
> consistently takes over 10 seconds.
>
> 2) Is there anything I can do to cut some time off the parse and compile
> time?
>
> 3) If I can't cut down on the parse and compile time - Is there anything I
> can do to prevent this plan from being flushed out of the cache?
>
> Thanks in advance,
> Tom
>
>
|
|
|
|
|