Silicon Investor (SI) -- The First Internet Community

STOCKTALK

We've detected that you're using an ad content blocking browser plug-in or feature. Ads provide a critical source of revenue to the continued operation of Silicon Investor. We ask that you disable ad blocking while on Silicon Investor in the best interests of our community. If you are not using an ad blocker but are still receiving this message, make sure your browser's tracking protection is set to the 'standard' level.

Pastimes : Silicon Investor, under the hood -- Ignore unavailable to you. Want to Upgrade?

To: SI Dave who wrote (26)	12/15/2004 9:27:25 AM
From: SI Bob	Respond to of 81

When I first asked about this, your stated preference was that everything be in an sproc so we could easily see dependencies. Actually, I was unaware of the ability to easily see dependencies until you pointed it out to me. I'd always counted on my ability to remember the dependencies on my own. As the system gets more complicated, that's not as easily done, granted. Your preference has migrated a bit, As the number of procs has ballooned. <g> My overall philosophy is to keep the number of tables, functions, procs, and views to the bare minimum need for the site to run at acceptable (very fast) speed. I'll do nearly anything to make an often-used routine run quickly. Not so when it comes to something that might get used just a few times per day. I can't imagine the execution planner can pre-process that, especially since there are variables used to build the statement. And it might be worse than that, but I haven't tested this theory. The QEP might pre-process a proc then stick with an execution plan that worked for the first variation of it that it'd processed, but works more slowly on subsequent calls because it's using an execution plan that isn't correct for those other variations. And I'm not aware of a way to force a proc to always run itself through the QEP before it executes. If that's possible, the overhead of always pre-processing the query could very well be a lot less than it using the wrong execution plan most of the time. sp_recompile wouldn't be the answer because I'm pretty sure that only works on tables; not SP's. And it wouldn't be good to have a proc do "exec sp_recompile message" every time it ran, because it'd make every proc that touches the message table go through QEP the next time it's run.