sp recompilation, how it can be avoided?

Question

sp recompilation, how it can be avoided?

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

newbie_dev SSCommitted Points: 1916 More actions · Answer 1

Yes , I used your suggestions , and the sp I just posted is recompiled 40 000 times a day. And I think this is a problem. I know that the option modifies the threshold of the temp tables making it like for normal tables.And analyzing the profiler, I saw that in all the recompiled statements I had temporary tables, so I'm trying as I can to remove them. I posted the sp because I'm sure that the dynamic sql is the big problem, but I don't think I can remove it. At least I hope to reduce the number of statements that recompile, so any suggestion about rewriting the sp is accepted gladly :-D.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

shnex (5/26/2009)
If you have any questions about the logic...I can help you this time 🙂

I wasn't exactly planning on rewriting it for you. I was more suggesting that you look at it with a view to removing the temp tables, if possible.

I'll pull it into management studio later and have a look, but for now, what does this do?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

newbie_dev SSCommitted Points: 1916 More actions · Answer 3

Of course...I wait for your suggestions....

The sp lists some 'activities' from Wf_Activity table and receives from the UI a whereClause that filters by an organization, workdate, etc.After this the records are filtered again.

This listing is made by pages, the number of rows on a page and the page number is send as a parameter.

If the @work_Date parameter is 1 the ordering of the records is made listing first the ones that have work_Date null or empty...

Thanks again for the help...

Paul White SSC Guru Points: 150468 More actions · Answer 4

Having looked at the procedure in detail, I would very much encourage you to read:

http://www.sommarskog.se/dyn-search-2005.html

That should give you some ideas.

Cheers,

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 5

Also, for ideas on efficient paging when using a custom ORDER BY clause, take a look at the following SSC article:

http://www.sqlservercentral.com/articles/Advanced+Querying/3181/

There are many similar works on the interweb, as indexed by Google.

Hope that helps - longer term.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi