Queries slower with SQL Server than Access

[cfoster]

Hoping for better security and performance, I'm moving data from an Access
database to SQL Server. However, our more query-intensive reports in CF are
taking significantly longer to run with SQL Server, not what I expected. For
example, the following query counts how many eligible surveys received a
response of '2' to each of the survey questions. Here's the SQL version:
select SourceID, count1_1 = sum (case when Q1_1=2 then 1 else 0 end), count1_2
= sum (case when Q1_2=2 then 1 else 0 end), count1_3 = sum (case when Q1_3=2
then 1 else 0 end), count1_4 = sum (case when Q1_4=2 then 1 else 0 end),
count1_5 = sum (case when Q1_5=2 then 1 else 0 end), count1_6 = sum (case when
Q1_6=2 then 1 else 0 end), count1_7 = sum (case when Q1_7=2 then 1 else 0 end),
count1_8 = sum (case when Q1_8=2 then 1 else 0 end), count1_9 = sum (case when
Q1_9=2 then 1 else 0 end), count2_1 = sum (case when Q2_1=2 then 1 else 0 end),
count2_2 = sum (case when Q2_2=2 then 1 else 0 end), count2_3 = sum (case when
Q2_3=2 then 1 else 0 end), count2_4 = sum (case when Q2_4=2 then 1 else 0 end),
count2_5 = sum (case when Q2_5=2 then 1 else 0 end), count2_6 = sum (case when
Q2_6=2 then 1 else 0 end), count3_1 = sum (case when Q3_1=2 then 1 else 0 end),
count3_2 = sum (case when Q3_2=2 then 1 else 0 end), count3_3 = sum (case when
Q3_3=2 then 1 else 0 end), count3_4 = sum (case when Q3_4=2 then 1 else 0 end),
count4_1 = sum (case when Q4_1=2 and StudentAge > 2 and StudentAge < 7 then 1
else 0 end), count4_2 = sum (case when Q4_2=2 and StudentAge > 4 and StudentAge
< 9 then 1 else 0 end), count4_3 = sum (case when Q4_3=2 and StudentAge > 13
then 1 else 0 end), count4_4 = sum (case when Q4_4=2 and StudentAge > 13 then 1
else 0 end), count4_5 = sum (case when Q4_5=2 and StudentAge > 13 then 1 else 0
end), count5_1 = sum (case when Q5_1=2 and StudentAge < 5 then 1 else 0 end),
count5_2 = sum (case when Q5_2=2 and StudentAge < 5 then 1 else 0 end),
count5_3 = sum (case when Q5_3=2 and StudentAge < 5 then 1 else 0 end),
count5_4 = sum (case when Q5_4=2 and StudentAge < 5 then 1 else 0 end),
count5_5 = sum (case when Q5_5=2 and StudentAge < 5 then 1 else 0 end),
count5_6 = sum (case when Q5_6=2 and StudentAge < 5 then 1 else 0 end),
count5_7 = sum (case when Q5_7=2 and StudentAge < 5 then 1 else 0 end),
count6_1 = sum (case when Q6_1=2 then 1 else 0 end), count6_2 = sum (case when
Q6_2=2 then 1 else 0 end), count6_3 = sum (case when Q6_3=2 then 1 else 0 end),
count6_4 = sum (case when Q6_4=2 then 1 else 0 end), count6_5 = sum (case when
Q6_5=2 then 1 else 0 end) from tblSurveys where districtID IN
(20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,3 6,38) group by SourceID order
by SourceID CF debugging times this query at 250ms, whereas the Access query
runs in 63ms. And, that's with nearly twice as many surveys recorded in the
Access database. The Access query is essentially the same, using IIF instead of
CASE (e.g. Sum(IIf(Q1_1=2,1,0)) as count1_1). When I run the SQL query in Query
Analyzer, the results come back instantaneously (zero seconds), so the problem
seems to be with ColdFusion's handling of the query. I tried using a stored
procedure instead of a query and ColdFusion took just as long. Any suggestions
for improving performance? Thanks!

[briguyCF151]

Maybe run it as either a view or stored procedure. Just taking a guess

[philh]

Do you have indices on the aggregated and WHERE clause fields? Run the query
in QA with Execution Plan tab turned on and see if there are table scans.
Definitely create an SP so SQL can cache the plan.