Jochem, Thank you for your reply. You have answered one of my questions I have never asked but, always wondered about. How a stored procedure improves performance.
Is there a better way to get the end result then using the inline queries? I am running a MSSQL 2000 database. The execution plan in SQL Query Analyzer for this query is as large or larger then the query itself. Now how do I tell if the query is I/O bound or CPU bound? Thanks for your help. Mark W. Breneman -Cold Fusion Developer -Network Administrator Vivid Media [EMAIL PROTECTED] www.vividmedia.com 608.270.9770 -----Original Message----- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 3:47 PM To: CF-Talk Subject: Re: Mambo SQL query help.... Please 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:185263 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
