Re: [ACFUG Discuss] Speeding up execution time against 2.3 m records
Derrick- Are you executing one query in CF, then feeding the resulting dataset back into another CFQUERY tag? If so, you are essentially pulling a huge dataset into CF and feeding the entire thing back into another query, which would be understandably slow. Does this not accomplish the same thing? Pardon my brain fart, but what's the select doing here? Setting a couple of values into a variable? SELECT (@FROMSAV:= ipfrom) as ipfrom, (@TOSAV:= ipto) as ipto -Cameron On 9/6/06, Derrick Peavy [EMAIL PROTECTED] wrote: I have a database with 2.3 million records, one table, 9 fields, optimized as much as possible (enum is used where possible, and other tips, also no nulls). MySQL 4.1.13 is the DB with the JDBC 3.1.7 driver. My problem is that to get the record I need, I have to look up the record based on a value which falls between A and B, A and B being the two columns in the DB. I get execution times ranging from 50+ seconds (not ms), to as little as 2-3 seconds. You can see an example here: http://www.universaladvertising.com/atest.cfm If anyone is familiar with PHPMyAdmin as a MySQL DB management tool, consider that the following executes in 0.0005 seconds: SELECT (@FROMSAV:= ipfrom) as ipfrom, (@TOSAV:= ipto) as ipto FROM ipcitylatlong WHERE 3626918649 = ipfrom AND 3626918649 = ipto; SELECT * FROMipcitylatlong WHERE @FROMSAV = ipfrom AND @TOSAV = ipto Yes, I realize that's actually two queries. In fact, PHP (and I'm not comparing PHP/CF), breaks this out into 5 queries, but one connection, but also executes in 0.0005 seconds. Query of queries does not help. And transaction wrapping makes no difference. My question is, while I understand the danger of what I am asking here, how can you pass the two SQL queries above through one Cold Fusion CFQUERY call? I don't think it's actually possible. But, maybe somebody has an idea, or a way to look at this differently. Incidentally, the following SQL is actually a little faster than the above SQL, but still does not come close to touching the 0.0005 time via PHPMyAdmin: SELECT * FROM ipcitylatlong WHERE #ipnumber# = ipTo _ Derrick Peavy Sales and Web Services Universal Advertising http://www.universaladvertising.com ___ -- Cameron Childress Sumo Consulting Inc http://www.sumoc.com --- cell: 678.637.5072 aim: cameroncf email: [EMAIL PROTECTED] - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -
Re: [ACFUG Discuss] Speeding up execution time against 2.3 m records
Yeah, why not join the table to itself. It should be faster than a subselect, which is the other option. -dhs Dean H. Saxe, CISSP, CEH [EMAIL PROTECTED] Free speech exercised both individually and through a free press, is a necessity in any country where people are themselves free. -- Theodore Roosevelt, 1918 On Sep 7, 2006, at 7:26 PM, Cameron Childress wrote: Derrick- Are you executing one query in CF, then feeding the resulting dataset back into another CFQUERY tag? If so, you are essentially pulling a huge dataset into CF and feeding the entire thing back into another query, which would be understandably slow. Does this not accomplish the same thing? Pardon my brain fart, but what's the select doing here? Setting a couple of values into a variable? SELECT (@FROMSAV:= ipfrom) as ipfrom, (@TOSAV:= ipto) as ipto -Cameron On 9/6/06, Derrick Peavy [EMAIL PROTECTED] wrote: I have a database with 2.3 million records, one table, 9 fields, optimized as much as possible (enum is used where possible, and other tips, also no nulls). MySQL 4.1.13 is the DB with the JDBC 3.1.7 driver. My problem is that to get the record I need, I have to look up the record based on a value which falls between A and B, A and B being the two columns in the DB. I get execution times ranging from 50+ seconds (not ms), to as little as 2-3 seconds. You can see an example here: http://www.universaladvertising.com/atest.cfm If anyone is familiar with PHPMyAdmin as a MySQL DB management tool, consider that the following executes in 0.0005 seconds: SELECT (@FROMSAV:= ipfrom) as ipfrom, (@TOSAV:= ipto) as ipto FROM ipcitylatlong WHERE 3626918649 = ipfrom AND 3626918649 = ipto; SELECT * FROMipcitylatlong WHERE @FROMSAV = ipfrom AND @TOSAV = ipto Yes, I realize that's actually two queries. In fact, PHP (and I'm not comparing PHP/CF), breaks this out into 5 queries, but one connection, but also executes in 0.0005 seconds. Query of queries does not help. And transaction wrapping makes no difference. My question is, while I understand the danger of what I am asking here, how can you pass the two SQL queries above through one Cold Fusion CFQUERY call? I don't think it's actually possible. But, maybe somebody has an idea, or a way to look at this differently. Incidentally, the following SQL is actually a little faster than the above SQL, but still does not come close to touching the 0.0005 time via PHPMyAdmin: SELECT * FROM ipcitylatlong WHERE #ipnumber# = ipTo _ Derrick Peavy Sales and Web Services Universal Advertising http://www.universaladvertising.com ___ -- Cameron Childress Sumo Consulting Inc http://www.sumoc.com --- cell: 678.637.5072 aim: cameroncf email: [EMAIL PROTECTED] - To unsubscribe from this list, manage your profile @ http:// www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -
Re: [ACFUG Discuss] Speeding up execution time against 2.3 m records
On 9/7/06, Cameron Childress [EMAIL PROTECTED] wrote: Does this not accomplish the same thing? Heh - I accidentally deleted what I put here, but it was essentially a subselect. A self join as Dean suggested also might work. The key is just to minimise the data MySQL and CF have to shuttle back and forth and keep any larger datasets inside the MySQL machine. -Cameron - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -
Re: Re: [ACFUG Discuss] Speeding up execution time against 2.3 m records
Would that still be the reason for the huge slowdown between php and CF? I would think there would be something else there causing that... just curious. On 9/7/06, Cameron Childress [EMAIL PROTECTED] wrote: On 9/7/06, Cameron Childress [EMAIL PROTECTED] wrote: Does this not accomplish the same thing? Heh - I accidentally deleted what I put here, but it was essentially a subselect. A self join as Dean suggested also might work. The key is just to minimise the data MySQL and CF have to shuttle back and forth and keep any larger datasets inside the MySQL machine. -Cameron - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com - -- Steven Ross web application interface developer http://www.zerium.com [mobile] 404-488-4364 [fax] 928-484-4364 - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -
