question

xnl28 avatar image
xnl28 asked

Long procedure recompile time

We have a stored procedure than takes around 2 minutes to recompile. Once recompiled, the stored procedure executes in about 10 seconds. Why would the recompile take so long? The procedure references local views which refer to views in another database on the same server. When I perform the checkpoint/DBCC dropcleanbuffers combination and with the procedure plan in the cache, the procedure still executes in about 10 seconds, so the recompile time is not due to pulling data off the disks. Does anyone have advice about how to further investigate what is causing the recompile time to be so long? Thanks in advance Here is some info produced when using SET STATISTICS TIME ON and executing the procedure WITH RECOMPILE: SQL Server parse and compile time: CPU time = 38250 ms, elapsed time = 107579 ms. SQL Server Execution Times: CPU time = 813 ms, elapsed time = 13729 ms.
sql-server-2008stored-procedures
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
We can only guess without seeing the procedure and the VIEWs. But seems like you are ending up with a pretty long query. On top of different databases, there are VIEWS (presumably involving JOINs on different tables which might not be needed actually):( This may not be an ideal situation. The optimizer would have to work hard to reach a good plan. I think better would be to use the base tables instead of the VIEWs. This could certainly help your recompile time.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
The size and complexity of the query determines compile time. We had a query with 86 tables which would take about 5 minutes to recompile. To reduce compile time, you have to reduce the size & complexity of the query. No other way around it.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.