sp recompilation, how it can be avoided?

Question

sp recompilation, how it can be avoided?

Viewing 4 posts - 16 through 20 (of 20 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

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 2

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 3

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 4

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