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 *
FROM ipcitylatlong
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
-------------------------------------------------------------
