How would you optimize this SQL?

2013-02-20 Thread Torrent Girl
Hello all I was told that the following sql is taking too long to run but the person doing load testing: SELECT @intCourseCompletions=(SELECT COUNT(intMemberStageID) as completions FROM tblMemberStages WHERE bitCompleted=1) It is a stored proc. Any suggestions on how I can optimize it for

RE: How would you optimize this SQL?

2013-02-20 Thread DURETTE, STEVEN J
[mailto:moniqueb...@gmail.com] Sent: Wednesday, February 20, 2013 11:07 AM To: cf-talk Subject: How would you optimize this SQL? Hello all I was told that the following sql is taking too long to run but the person doing load testing: SELECT @intCourseCompletions=(SELECT COUNT(intMemberStageID

Re: How would you optimize this SQL?

2013-02-20 Thread Torrent Girl
: Torrent Girl [mailto:moniqueb...@gmail.com] Sent: Wednesday, February 20, 2013 11:07 AM To: cf-talk Subject: How would you optimize this SQL? Thank you. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe

Re: How would you optimize this SQL?

2013-02-20 Thread Bobby
to see which works best. Steve -Original Message- From: Torrent Girl [mailto:moniqueb...@gmail.com] Sent: Wednesday, February 20, 2013 11:07 AM To: cf-talk Subject: How would you optimize this SQL? Thank you

Re: How would you optimize this SQL?

2013-02-20 Thread Dave Watts
You could also add schema prefixes to your tables and columns (or alias them) as well as make the 1 a parameter. That should help with execution plan caching. Actually, caching the execution plan with a parameter here is probably a bad idea - at least half the time! When you have a bit

Re: How would you optimize this SQL?

2013-02-20 Thread Bobby
Will it even cache the plan without the 1 being a parameter? In either case, I'd still advocate the use of schema prefixes/aliases. On 2/20/13 1:15 PM, Dave Watts dwa...@figleaf.com wrote: You could also add schema prefixes to your tables and columns (or alias them) as well as make the 1 a

Re: How would you optimize this SQL?

2013-02-20 Thread Dave Watts
Will it even cache the plan without the 1 being a parameter? Sure, by default. If you just run a query like SELECT * FROM mytable, the database server will cache that execution plan. Parameters usually let you more effectively reuse execution plans, that's all. For example, if you had two