Mark W. Breneman wrote: > > Is there a better way to get the end result then using the inline queries?
With a helicopter view of the problem I can think of various execution plans that could make sense in your situation. But it all depends on the schema, cardinality and distribution of the data. All of which would express themselves in the execution plan. Possible options I can think of on the SQL front are: - replace your COUNT queries by SUM + CASE SELECT SUM(CASE WHEN review_no = 1 THEN 1 ELSE 0 END) as tot_Q1R1, SUM(CASE WHEN review_no = 2 THEN 1 ELSE 0 END) as tot_Q1R2, SUM(CASE WHEN review_no = 1 AND ONE_INVITED = 'no' THEN 1 ELSE 0 END) as tot_Q1R1No, SUM(CASE WHEN review_no = 2 AND ONE_INVITED = 'no' THEN 1 ELSE 0 END) as tot_Q1R2No, etc. FROM CheckListData WHERE schoolyear = 2003 AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP < 15 AND DISTRICT = 'Black River Falls' (The idea behind this query is to force the database to run just one scan of the table and do the rest in RAM. This should help a lot if you are running many scans (one for each subquery) and are I/O bound.) Or: - create a temp table with all the data that matches the primary predicates: schoolyear = 2003 AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP < 15 AND DISTRICT = 'Black River Falls' - run your query on just that table - drop the temp table (Here we want to do away with possible seqscans due to low cardinality on the primary predicates.) But all of these are just (imperfect) means to an end: getting a good execution plan. I'll see if I can dig up some high level literature on database internals. Also, keep looking at the schema of your table. If you can get your fields in your schema to be BOOLEAN NOT NULL, you might be able to do away with half of the subqueries, because total = true + false. (In your case with MS SQL Server that would be BIT fields.) > The execution plan in SQL Query Analyzer for this query is as large or > larger then the query itself. Isn't it largely repetetive? (I would expect so.) Else put it up on a website. > Now how do I tell if the query is I/O bound or CPU bound? Task Manager: is your CPU at 100% ? If not, you need the Windows equivalent of IOstat to determine if the disks are running at their maximum capacity. If you are CPU bound, indexes often help. If you are I/O bound, normalize more so you store the data more efficiently. Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185265 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
