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. Declare @myBit int Set @myBit = 1 SELECT @intCourseCompletions = COUNT(m.intMemberStageID) FROM dbo.tblMemberStages m WHERE m.bitCompleted = @myBit; On 2/20/13 11:30 AM, "Torrent Girl" <moniqueb...@gmail.com> wrote: > >> 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? >> >> >Thank you. > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:354597 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
