Mark W. Breneman wrote: > > So my question is where do I get started rewriting this query.
If that is an option, start with optimizing the data model. > First off I can see that the yes, No and NA should be converted to a number. To a BOOLEAN. > The DISTRICT also needs to be converted to a number. In general, you need to normalize the data. Obviously the DISTRICT should be a foreign key to a district table, but for instance, I am wondering if STUDENT_AGE_AT_IEP isn't redundant with for instance some table with records from students which has a birthday. > Then the whole thing needs to be converted into a stored procedure. Why? For performance? A stored procedure has a precompiled execution plan so it saves you the time to parse and plan the query. From the looks of it you are running multiple indexscans and possibly even seqscans on the table so that won't help you. It saves you 99.99% of the 1 second it takes to compile, and doesn't help with the 119 seconds it takes to run. Profile the query. What is the execution plan? Is your system I/O bound or CPU bound? What DBMS are you using? 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:185260 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
