Well, that code would be equivalent to: SELECT @intCourseCompletions = COUNT(intMemberStageID) FROM tblMemberStages WHERE bitCompleted = 1;
However I believe to get any performance increase you will need an index on bitCompleted. CREATE INDEX myindexname ON tblMemberStages (bitCompleted); You could also try: SELECT @intCourseCompletions = sum(cast(bitCompleted as int)) FROM tblMemberStages; The conversion to INT may or may not take longer. The index is probably your best bet but try both ways 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? 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 better performance? I just want to count the number of completions. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354594 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
