RE: Speeding up select queries
General rules of thumb I use. Avoid Select * always specify your fields. Fastest is to select fields in the following datatype order, integers, numeric, strings according to field size. Memo or Long text fields are always last. The latter one is most important because all fields selected after a long text field will load as unbound fields and will slow the sql way down. -Original Message- From: Tracy Bost [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 02, 2002 11:22 AM To: CF-Talk Subject: Speeding up select queries What is the best approach to speed up select statements with a sql2000 database? The data is constantly being updated,deleted,inserted, through out the day with each user seeing data depending on his/her access level and assigned zip codes. I worry that using cachedwithin will not work in this senario, as using that will not allow to show data that has recently changed. ANY help, thoughts much appreciated. -- Visit The Most Powerful Tool on the Farm at http://www.ifarm.com Get the latest on Ag News, Market Reports, FREE email, and much more. __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Speeding up select queries
Other attributes of cfquery which may be of help in speeding up a query is to try using blockfactorwhick specifies the max number of rows to fetch at a time from the server or maxrows if you want to limit the number of rows returned. Regards, Declan -Original Message- From: Tracy Bost [mailto:[EMAIL PROTECTED]] Sent: 02 January 2002 16:22 To: CF-Talk Subject: Speeding up select queries What is the best approach to speed up select statements with a sql2000 database? The data is constantly being updated,deleted,inserted, through out the day with each user seeing data depending on his/her access level and assigned zip codes. I worry that using cachedwithin will not work in this senario, as using that will not allow to show data that has recently changed. ANY help, thoughts much appreciated. - - Visit The Most Powerful Tool on the Farm at http://www.ifarm.com Get the latest on Ag News, Market Reports, FREE email, and much more. __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Speeding up select queries
Maybe sorting the zip list might help. Although if it contains that many zip codes it might be worthwhile having a re-think. One more thing, the dateadd in your query, I'd convert that into SQL functions rather than CF, as follows. so.. Appt_Time #DateAdd(h, -48,#Now()#)# would become something like .. Appt_Time dateadd(Hour,-48,GetDate()) It's always better to make SQL server do the work imho. -Original Message- From: Tracy Bost [mailto:[EMAIL PROTECTED]] Sent: 02 January 2002 16:55 To: CF-Talk Subject: RE: Speeding up select queries This query takes about 47ms to execute, which I can live with that: cfquery name=getlateorders datasource=#AppDSN# SELECT FileNo, Order_ID, Appraiser_ID, HouseNo, Street, UnitNo, City, Cou nty, State_ID, Date_Ordered,time_ordered,Appt_Date,Appt_Time,AMorPM, Lender_ID , Zip, (SELECT State FROM T_States WHERE State_ID = O.State_ID) AS State, (SEL ECT Client_ID FROM T_Lenders WHERE Lender_ID = O.Lender_ID) AS Client_ID FROM T_Orders O WHERE StatusType_ID = 3 AND Appt_Time #DateAdd(h , - 48,#Now()#)# /cfquery BUT when I add this to the same query: AND Zip IN (#ziplist#) It takes anywhere from 45 to 50 seconds to execute. ziplist is a variable taken from a session.ziplist that contains the user's authorized zipcodes. It j ust started going slow, maybe there is a threshold after so many zipcodes are in the list it will slow things down tremendously ? Quoting Sandy Clark [EMAIL PROTECTED]: General rules of thumb I use. Avoid Select * always specify your fields. Fastest is to select fields in the following datatype order, integers, numeric, strings according to field size. Memo or Long text fields are always last. The latter one is most important because all fields selected after a long text field will load as unbound fields and will slow the sql way down. -Original Message- From: Tracy Bost [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 02, 2002 11:22 AM To: CF-Talk Subject: Speeding up select queries What is the best approach to speed up select statements with a sql2000 database? The data is constantly being updated,deleted,inserted, throug h out the day with each user seeing data depending on his/her access level an d assigned zip codes. I worry that using cachedwithin will not work in this senario, as using that will not allow to show data that has recently changed. ANY help, thoughts much appreciated. --- - -- Visit The Most Powerful Tool on the Farm at http://www.ifarm.com Get the latest on Ag News, Market Reports, FREE email, and much more. __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Speeding up select queries
hmmm how many zipcodes are we talking, and which database? and how many total records? -Original Message- From: Tracy Bost [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 02, 2002 11:55 To: CF-Talk Subject: RE: Speeding up select queries This query takes about 47ms to execute, which I can live with that: cfquery name=getlateorders datasource=#AppDSN# SELECT FileNo, Order_ID, Appraiser_ID, HouseNo, Street, UnitNo, City, Cou nty, State_ID, Date_Ordered,time_ordered,Appt_Date,Appt_Time,AMorPM, Lender_ID , Zip, (SELECT State FROM T_States WHERE State_ID = O.State_ID) AS State, (SEL ECT Client_ID FROM T_Lenders WHERE Lender_ID = O.Lender_ID) AS Client_ID FROM T_Orders O WHERE StatusType_ID = 3 AND Appt_Time #DateAdd(h , - 48,#Now()#)# /cfquery BUT when I add this to the same query: AND Zip IN (#ziplist#) It takes anywhere from 45 to 50 seconds to execute. ziplist is a variable taken from a session.ziplist that contains the user's authorized zipcodes. It j ust started going slow, maybe there is a threshold after so many zipcodes are in the list it will slow things down tremendously ? Quoting Sandy Clark [EMAIL PROTECTED]: General rules of thumb I use. Avoid Select * always specify your fields. Fastest is to select fields in the following datatype order, integers, numeric, strings according to field size. Memo or Long text fields are always last. The latter one is most important because all fields selected after a long text field will load as unbound fields and will slow the sql way down. -Original Message- From: Tracy Bost [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 02, 2002 11:22 AM To: CF-Talk Subject: Speeding up select queries What is the best approach to speed up select statements with a sql2000 database? The data is constantly being updated,deleted,inserted, throug h out the day with each user seeing data depending on his/her access level an d assigned zip codes. I worry that using cachedwithin will not work in this senario, as using that will not allow to show data that has recently changed. ANY help, thoughts much appreciated. --- - -- Visit The Most Powerful Tool on the Farm at http://www.ifarm.com Get the latest on Ag News, Market Reports, FREE email, and much more. __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Speeding up select queries
In addition to Sandys rules of thumb, I would convert any query you have into a stored procedure. One of the added benefits is that you can have multiple result sets when using stored procs. Also, index columns that are frequently accessed and/or columns you use in your where clause. Mark -Original Message- From: Sandy Clark [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 02, 2002 11:43 AM To: CF-Talk Subject: RE: Speeding up select queries General rules of thumb I use. Avoid Select * always specify your fields. Fastest is to select fields in the following datatype order, integers, numeric, strings according to field size. Memo or Long text fields are always last. The latter one is most important because all fields selected after a long text field will load as unbound fields and will slow the sql way down. -Original Message- From: Tracy Bost [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 02, 2002 11:22 AM To: CF-Talk Subject: Speeding up select queries What is the best approach to speed up select statements with a sql2000 database? The data is constantly being updated,deleted,inserted, through out the day with each user seeing data depending on his/her access level and assigned zip codes. I worry that using cachedwithin will not work in this senario, as using that will not allow to show data that has recently changed. ANY help, thoughts much appreciated. -- Visit The Most Powerful Tool on the Farm at http://www.ifarm.com Get the latest on Ag News, Market Reports, FREE email, and much more. __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Speeding up select queries
My suggestion would be to create a table in your SQL Server that contains the list of zip codes that a user can see. Each record would contain the user's ID and 1 zip code they can see. Then you can either join this table in, or user it in the in clause. So your select statement would be something like the following 2 selects: cfquery name=getlateorders datasource=#AppDSN# SELECT FileNo, Order_ID, Appraiser_ID, HouseNo, Street, UnitNo, City, County, State_ID, Date_Ordered, time_ordered, Appt_Date, Appt_Time, AMorPM, Lender_ID, Zip, (SELECT State FROM T_States WHERE State_ID = O.State_ID) AS State, (SELECT Client_ID FROM T_Lenders WHERE Lender_ID = O.Lender_ID) AS Client_ID FROM T_Orders O INNER JOIN user_zip Z ON O.zip = Z.zip WHERE StatusType_ID = 3 AND Appt_Time #DateAdd(h, -48,#Now()#)# AND Z.user_id = #my_user_id# /cfquery or option 2: cfquery name=getlateorders datasource=#AppDSN# SELECT FileNo, Order_ID, Appraiser_ID, HouseNo, Street, UnitNo, City, County, State_ID, Date_Ordered, time_ordered, Appt_Date, Appt_Time, AMorPM, Lender_ID, Zip, (SELECT State FROM T_States WHERE State_ID = O.State_ID) AS State, (SELECT Client_ID FROM T_Lenders WHERE Lender_ID = O.Lender_ID) AS Client_ID FROM T_Orders O WHERE StatusType_ID = 3 AND Appt_Time #DateAdd(h, -48,#Now()#)# AND O.zip IN (SELECT zip FROM user_zip WHERE Z.user_id = #my_user_id#) /cfquery __ Bill Grover Supervisor IS DepartmentPhone: 301.424.3300 x396 EU Services, Inc. FAX: 301.424.3561 649 North Horners Lane E-Mail: mailto:[EMAIL PROTECTED] Rockville, MD 20850-1299WWW: www.euservices.com __ -Original Message- From: Tracy Bost [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 02, 2002 11:55 AM To: CF-Talk Subject: RE: Speeding up select queries This query takes about 47ms to execute, which I can live with that: cfquery name=getlateorders datasource=#AppDSN# SELECT FileNo, Order_ID, Appraiser_ID, HouseNo, Street, UnitNo, City, Cou nty, State_ID, Date_Ordered,time_ordered,Appt_Date,Appt_Time,AMorPM, Lender_ID , Zip, (SELECT State FROM T_States WHERE State_ID = O.State_ID) AS State, (SEL ECT Client_ID FROM T_Lenders WHERE Lender_ID = O.Lender_ID) AS Client_ID FROM T_Orders O WHERE StatusType_ID = 3 AND Appt_Time #DateAdd(h , - 48,#Now()#)# /cfquery BUT when I add this to the same query: AND Zip IN (#ziplist#) It takes anywhere from 45 to 50 seconds to execute. ziplist is a variable taken from a session.ziplist that contains the user's authorized zipcodes. It j ust started going slow, maybe there is a threshold after so many zipcodes are in the list it will slow things down tremendously ? Quoting Sandy Clark [EMAIL PROTECTED]: General rules of thumb I use. Avoid Select * always specify your fields. Fastest is to select fields in the following datatype order, integers, numeric, strings according to field size. Memo or Long text fields are always last. The latter one is most important because all fields selected after a long text field will load as unbound fields and will slow the sql way down. -Original Message- From: Tracy Bost [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 02, 2002 11:22 AM To: CF-Talk Subject: Speeding up select queries What is the best approach to speed up select statements with a sql2000 database? The data is constantly being updated,deleted,inserted, throug h out the day with each user seeing data depending on his/her access level an d assigned zip codes. I worry that using cachedwithin will not work in this senario, as using that will not allow to show data that has recently changed. ANY help, thoughts much appreciated. -- - - -- Visit The Most Powerful Tool on the Farm at http://www.ifarm.com Get the latest on Ag News, Market Reports, FREE email, and much more. __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com
RE: Speeding up select queries
Other attributes of cfquery which may be of help in speeding up a query is to try using blockfactorwhick specifies the max number of rows to fetch at a time from the server or maxrows if you want to limit the number of rows returned. FYI - The MaxRows attribute of the CFQUERY tag is applied AFTER the dataset has been returned to CF. If you want to only retrieve the first 10 rows, you are better off using the TOP keyword ie: SELECT TOP 10 name, phone, etc FROM... -Cameron Cameron Childress elliptIQ Inc. p.770.460.1035.232 f.770.460.0963 -- http://www.neighborware.com America's Leading Community Network Software -Original Message- From: Declan Maher [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 02, 2002 11:59 AM To: CF-Talk Subject: RE: Speeding up select queries Other attributes of cfquery which may be of help in speeding up a query is to try using blockfactorwhick specifies the max number of rows to fetch at a time from the server or maxrows if you want to limit the number of rows returned. Regards, Declan -Original Message- From: Tracy Bost [mailto:[EMAIL PROTECTED]] Sent: 02 January 2002 16:22 To: CF-Talk Subject: Speeding up select queries What is the best approach to speed up select statements with a sql2000 database? The data is constantly being updated,deleted,inserted, through out the day with each user seeing data depending on his/her access level and assigned zip codes. I worry that using cachedwithin will not work in this senario, as using that will not allow to show data that has recently changed. ANY help, thoughts much appreciated. - - Visit The Most Powerful Tool on the Farm at http://www.ifarm.com Get the latest on Ag News, Market Reports, FREE email, and much more. __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Speeding up select queries
Tracy, When using 'IN' keep in mind that SELECT blah FROM table WHERE ID IN (1,2,3) is basically the same as: SELECT blah FROM table WHERE ID = 1 OR ID = 2 OR ID = 3 If the ID list is really long, you could imagine how many 'OR' statements you are sticking into your query, and how slow it will make it run. I suspect restructuring your query to avoid the huge 'IN' list will speed your query up substantially. -Cameron Cameron Childress elliptIQ Inc. p.770.460.1035.232 f.770.460.0963 -- http://www.neighborware.com America's Leading Community Network Software -Original Message- From: Tracy Bost [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 02, 2002 12:35 PM To: CF-Talk Subject: RE: Speeding up select queries I've converted the dateadd function to sql as suggested. It looks like it has taken about 17ms off the time by doing that. Thanks, as every ms c ounts. Also have used the query of query feature as Sandra suggested, and that r eally helped. I'll for sure be a query of query junkie now. Here's the initial query: getlateorders (Records=28, Time=43109ms) AND now the query using the query of query feature as suggested: getalllateorders (Records=32, Time=47ms) getlateorders (Records=31, Time=891ms) The new time for the query is just under a second. Now I'll just have to re- think the structing of using a long zip code list, which i suspect will b e no small project. Thanks All ! Quoting Craig Dudley [EMAIL PROTECTED]: Maybe sorting the zip list might help. Although if it contains that many zip codes it might be worthwhile having a re-think. One more thing, the dateadd in your query, I'd convert that into SQL functions rather than CF, as follows. so.. Appt_Time #DateAdd(h, -48,#Now()#)# would become something like .. Appt_Time dateadd(Hour,-48,GetDate()) It's always better to make SQL server do the work imho. -Original Message- From: Tracy Bost [mailto:[EMAIL PROTECTED]] Sent: 02 January 2002 16:55 To: CF-Talk Subject: RE: Speeding up select queries This query takes about 47ms to execute, which I can live with that: cfquery name=getlateorders datasource=#AppDSN# SELECT FileNo, Order_ID, Appraiser_ID, HouseNo, Street, UnitNo, City, Cou nty, State_ID, Date_Ordered,time_ordered,Appt_Date,Appt_Time,AMorPM, Lender_ID , Zip, (SELECT State FROM T_States WHERE State_ID = O.State_ID) AS State, (SEL ECT Client_ID FROM T_Lenders WHERE Lender_ID = O.Lender_ID) AS Client_ID FROM T_Orders O WHERE StatusType_ID = 3 AND Appt_Time #DateAdd(h , - 48,#Now()#)# /cfquery BUT when I add this to the same query: AND Zip IN (#ziplist#) It takes anywhere from 45 to 50 seconds to execute. ziplist is a variable taken from a session.ziplist that contains the user's authorized zipcodes. It j ust started going slow, maybe there is a threshold after so many zipcodes are in the list it will slow things down tremendously ? Quoting Sandy Clark [EMAIL PROTECTED]: General rules of thumb I use. Avoid Select * always specify your fields. Fastest is to select fields in the following datatype order, integers, numeric, strings according to field size. Memo or Long text fields are always last. The latter one is most important because all fields selected after a long text field will load as unbound fields and will slow the sql way down. -Original Message- From: Tracy Bost [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 02, 2002 11:22 AM To: CF-Talk Subject: Speeding up select queries What is the best approach to speed up select statements with a sql2000 database? The data is constantly being updated,deleted,inserted, throug h out the day with each user seeing data depending on his/her access level an d assigned zip codes. I worry that using cachedwithin will not work in this senario, as using that will not allow to show data that has recently changed. ANY help, thoughts much appreciated. --- - -- Visit The Most Powerful Tool on the Farm at http://www.ifarm.com Get the latest on Ag News, Market Reports, FREE email, and much more. __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Speeding up select queries
If the ID list is really long, you could imagine how many 'OR' statements you are sticking into your query, and how slow it will make it run. I suspect restructuring your query to avoid the huge 'IN' list will speed your query up substantially. one trick is to shove those IDs into a temp table use that to do an INNER JOIN against the main table. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.311 / Virus Database: 172 - Release Date: 12/27/2001 __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Speeding up select queries
I'm not familiar with the query analysis capabilities of sql2000, but you could look to see how that query might be behaving with respect to the database and get some suggestions for optimization that way, with any too ls available. Perhaps you could try indexing that table on the zip code (if it's not already indexed), or create a combined index, and see if that helps. If you're not the DBA for the database, have a chat with that per son about it. Regards, Joel Parramore -Original Message- From: Tracy Bost [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 02, 2002 11:55 AM To: CF-Talk Subject: RE: Speeding up select queries This query takes about 47ms to execute, which I can live with that: cfquery name=getlateorders datasource=#AppDSN# SELECT FileNo, Order_ID, Appraiser_ID, HouseNo, Street, UnitNo, City, C ou nty, State_ID, Date_Ordered,time_ordered,Appt_Date,Appt_Time,AMorPM, Lender_ ID , Zip, (SELECT State FROM T_States WHERE State_ID = O.State_ID) AS State, (S EL ECT Client_ID FROM T_Lenders WHERE Lender_ID = O.Lender_ID) AS Client_ID FROM T_Orders O WHERE StatusType_ID = 3 AND Appt_Time #DateAdd( h , - 48,#Now()#)# /cfquery BUT when I add this to the same query: AND Zip IN (#ziplist#) It takes anywhere from 45 to 50 seconds to execute. ziplist is a variab le taken from a session.ziplist that contains the user's authorized zipcodes. It j ust started going slow, maybe there is a threshold after so many zipcodes a re in the list it will slow things down tremendously ? Quoting Sandy Clark [EMAIL PROTECTED]: General rules of thumb I use. Avoid Select * always specify your fields. Fastest is to select fields in the following datatype order, integers , numeric, strings according to field size. Memo or Long text fields a re always last. The latter one is most important because all fields selected after a long text field will load as unbound fields and will slow the sql way down. -Original Message- From: Tracy Bost [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 02, 2002 11:22 AM To: CF-Talk Subject: Speeding up select queries What is the best approach to speed up select statements with a sql2000 database? The data is constantly being updated,deleted,inserted, thro ug h out the day with each user seeing data depending on his/her access level an d assigned zip codes. I worry that using cachedwithin will not work in this senario, as using that will not allow to show data that has recently changed. ANY help, thoughts much appreciated. - -- - -- Visit The Most Powerful Tool on the Farm at http://www.ifarm.com Get the latest on Ag News, Market Reports, FREE email, and much more. __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Speeding up select queries
It will be far more efficient to do the whole query in SQL rather than creating 2 queries in CF. Let SQL do the work, it is very good at it. I suspect that you IN clause is causing the Query optimizer to have problems determining which index to use which is why you are seeing a huge difference in run time between the query with or without the ZIP list. You need to look at the plan for both queries and see which indexes it is using (the latter may be forcing a table scan). You can force the use of the right index as part of your query, just make sure you are using the right one. Converting this to a Stored procedure will save in little time as it will not have to compile the procedure every time it is run, but it may make the IN clause hard to pass (though in General I always recommend using stored procedures over pass through SQL. I would also change the nested queries to joins. Maybe something like... SELECT O.FileNo, O.Order_ID, O.Appraiser_ID, O.HouseNo, O.Street, O.UnitNo, O.City, O.County, O.State_ID, O.Date_Ordered, O.time_ordered, O.Appt_Date, O.Appt_Time, O.AMorPM, O.Lender_ID, O.Zip, S.State AS State, L.Lender AS ClientID FROMT_Orders O, T_States S, T_Lenders L WHERE StatusType_ID = 3 AND Appt_Time #DateAdd(h, -48,#Now()#)# AND O.StateID = S.StateID AND O.Lender_ID = L.Lender_ID AND O.Zip IN (#ziplist#) Justin -Original Message- From: Sandy Clark [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 02, 2002 12:13 PM To: CF-Talk Subject: RE: Speeding up select queries If you are using CF5, why not do this in 2 queries. Query the database and then query the query. cfquery name=getalllateorders datasource=#AppDSN# SELECT FileNo, Order_ID, Appraiser_ID, HouseNo, Street, UnitNo, City, Cou nty, State_ID, Date_Ordered,time_ordered,Appt_Date,Appt_Time,AMorPM, Lender_ID , Zip, (SELECT State FROM T_States WHERE State_ID = O.State_ID) AS State, (SEL ECT Client_ID FROM T_Lenders WHERE Lender_ID = O.Lender_ID) AS Client_ID FROM T_Orders O WHERE StatusType_ID = 3 AND Appt_Time #DateAdd(h , - 48,#Now()#)# /cfquery cfquery name=getlateorders dbtype=query Select * from getalllateorders Where Zip IN (#ziplist#) /cfquery This way you are only selecting zips from a much smaller recordsource. Should be much faster. -Original Message- From: Tracy Bost [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 02, 2002 11:55 AM To: CF-Talk Subject: RE: Speeding up select queries This query takes about 47ms to execute, which I can live with that: cfquery name=getlateorders datasource=#AppDSN# SELECT FileNo, Order_ID, Appraiser_ID, HouseNo, Street, UnitNo, City, Cou nty, State_ID, Date_Ordered,time_ordered,Appt_Date,Appt_Time,AMorPM, Lender_ID , Zip, (SELECT State FROM T_States WHERE State_ID = O.State_ID) AS State, (SEL ECT Client_ID FROM T_Lenders WHERE Lender_ID = O.Lender_ID) AS Client_ID FROM T_Orders O WHERE StatusType_ID = 3 AND Appt_Time #DateAdd(h , - 48,#Now()#)# /cfquery BUT when I add this to the same query: AND Zip IN (#ziplist#) It takes anywhere from 45 to 50 seconds to execute. ziplist is a variable taken from a session.ziplist that contains the user's authorized zipcodes. It j ust started going slow, maybe there is a threshold after so many zipcodes are in the list it will slow things down tremendously ? Quoting Sandy Clark [EMAIL PROTECTED]: General rules of thumb I use. Avoid Select * always specify your fields. Fastest is to select fields in the following datatype order, integers, numeric, strings according to field size. Memo or Long text fields are always last. The latter one is most important because all fields selected after a long text field will load as unbound fields and will slow the sql way down. -Original Message- From: Tracy Bost [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 02, 2002 11:22 AM To: CF-Talk Subject: Speeding up select queries What is the best approach to speed up select statements with a sql2000 database? The data is constantly being updated,deleted,inserted, throug h out the day with each user seeing data depending on his/her access level an d assigned zip codes. I worry that using cachedwithin will not work in this senario, as using that will not allow to show data that has recently changed. ANY help, thoughts much appreciated. -- - - -- Visit The Most Powerful Tool on the Farm at http://www.ifarm.com Get the latest on Ag News, Market Reports, FREE email, and much more. __ Get Your
