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.
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.
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.
9 People are following this question.
