RE: Slightly OT: CFQuery, Oracle and Limiting rows
Rob I have asked a similar question on this forum - I think the best bet is to cache the query results and then use the query a query functionality (assuming you are using CF5 or above). How best to cache the query will depend on whether the results are specific to users or not. If not then you can cache in the Application scope. If it is specific to users then I think you can cache in the session scope although my questio (which I am still awaiting responses to) asks how best to do this... Andy -Original Message- From: Edwards Robert (air0rae) [mailto:[EMAIL PROTECTED]] Sent: 28 August 2002 14:43 To: CF-Talk Subject: Slightly OT: CFQuery, Oracle and Limiting rows I have a query that returns about 5000+ rows of data. Currently it takes about 51 seconds to execute (it's a very complex query). I'm only displaying 100 records per page using start and end rows in a cfloop. The problem is that the query gets executed every time I go to a page and it take 50 seconds or so each time. I know I can use MAXROWS in my cfquery tag to limit the return to 100 rows, but that will only get me the first 100. Does anyone know how to set it up so I can get the second 100, or third or fortieth? I would guess this needs to be done in oracle instead of CF, but there may be a custom tag I don't know about. (About the data: There is no iterator or counter on the rows so I cannot do a select where rowid between 1 and 100 or 101 and 200 and so on. Each row has a unique value in it, and can be added to or deleted from at any time. Also, the data needs to be real-time so caching a query is out of the question as well.) Any suggestions would be greatly appreciated. My phone is ringing off the hook with complaints of the slow response. Thanks, Rob Edwards Phone: (502) 359-1627 Systems Management Tools Pager: (502) 478-1116 United Parcel Service Fax:(502) 359-0094 EMail: [EMAIL PROTECTED] (2B || !2B) == ? __ Get the mailserver that powers this list at http://www.coolfusion.com 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: Slightly OT: CFQuery, Oracle and Limiting rows
I had the same problem. I used a next/previous button system that showed 10 records at a time. If this is what you're looking for I'll post the code. Phil -Original Message- From: Edwards Robert (air0rae) [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 9:43 AM To: CF-Talk Subject: Slightly OT: CFQuery, Oracle and Limiting rows I have a query that returns about 5000+ rows of data. Currently it takes about 51 seconds to execute (it's a very complex query). I'm only displaying 100 records per page using start and end rows in a cfloop. The problem is that the query gets executed every time I go to a page and it take 50 seconds or so each time. I know I can use MAXROWS in my cfquery tag to limit the return to 100 rows, but that will only get me the first 100. Does anyone know how to set it up so I can get the second 100, or third or fortieth? I would guess this needs to be done in oracle instead of CF, but there may be a custom tag I don't know about. (About the data: There is no iterator or counter on the rows so I cannot do a select where rowid between 1 and 100 or 101 and 200 and so on. Each row has a unique value in it, and can be added to or deleted from at any time. Also, the data needs to be real-time so caching a query is out of the question as well.) Any suggestions would be greatly appreciated. My phone is ringing off the hook with complaints of the slow response. Thanks, Rob Edwards Phone: (502) 359-1627 Systems Management Tools Pager: (502) 478-1116 United Parcel Service Fax:(502) 359-0094 EMail: [EMAIL PROTECTED] (2B || !2B) == ? __ Get the mailserver that powers this list at http://www.coolfusion.com 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: Slightly OT: CFQuery, Oracle and Limiting rows
I've got code that does that already. (thanks for the offer anyway). my problem is that when I click the button to go to the next one hundred, the cfquery still returns 5000+ records but the cfloop goes to start row 101 and end row 200. I'm looking at possibilities with oracle's ROWNUM psudofield. If I figure out anything on my own, I'll post it here. Rob Edwards Phone: (502) 359-1627 Systems Management Tools Pager: (502) 478-1116 United Parcel Service Fax:(502) 359-0094 EMail: [EMAIL PROTECTED] (2B || !2B) == ? -Original Message- From: Phillip Perry [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 10:01 AM To: CF-Talk Subject: RE: Slightly OT: CFQuery, Oracle and Limiting rows I had the same problem. I used a next/previous button system that showed 10 records at a time. If this is what you're looking for I'll post the code. Phil -Original Message- From: Edwards Robert (air0rae) [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 9:43 AM To: CF-Talk Subject: Slightly OT: CFQuery, Oracle and Limiting rows I have a query that returns about 5000+ rows of data. Currently it takes about 51 seconds to execute (it's a very complex query). I'm only displaying 100 records per page using start and end rows in a cfloop. The problem is that the query gets executed every time I go to a page and it take 50 seconds or so each time. I know I can use MAXROWS in my cfquery tag to limit the return to 100 rows, but that will only get me the first 100. Does anyone know how to set it up so I can get the second 100, or third or fortieth? I would guess this needs to be done in oracle instead of CF, but there may be a custom tag I don't know about. (About the data: There is no iterator or counter on the rows so I cannot do a select where rowid between 1 and 100 or 101 and 200 and so on. Each row has a unique value in it, and can be added to or deleted from at any time. Also, the data needs to be real-time so caching a query is out of the question as well.) Any suggestions would be greatly appreciated. My phone is ringing off the hook with complaints of the slow response. Thanks, Rob Edwards Phone: (502) 359-1627 Systems Management Tools Pager: (502) 478-1116 United Parcel Service Fax:(502) 359-0094 EMail: [EMAIL PROTECTED] (2B || !2B) == ? __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm 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: Slightly OT: CFQuery, Oracle and Limiting rows
Sorry, just re-read the post. My code relys on ordered number list. Since your id's are all unique it won't work for you. good luck Phil -Original Message- From: Phillip Perry [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 10:01 AM To: CF-Talk Subject: RE: Slightly OT: CFQuery, Oracle and Limiting rows I had the same problem. I used a next/previous button system that showed 10 records at a time. If this is what you're looking for I'll post the code. Phil -Original Message- From: Edwards Robert (air0rae) [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 9:43 AM To: CF-Talk Subject: Slightly OT: CFQuery, Oracle and Limiting rows I have a query that returns about 5000+ rows of data. Currently it takes about 51 seconds to execute (it's a very complex query). I'm only displaying 100 records per page using start and end rows in a cfloop. The problem is that the query gets executed every time I go to a page and it take 50 seconds or so each time. I know I can use MAXROWS in my cfquery tag to limit the return to 100 rows, but that will only get me the first 100. Does anyone know how to set it up so I can get the second 100, or third or fortieth? I would guess this needs to be done in oracle instead of CF, but there may be a custom tag I don't know about. (About the data: There is no iterator or counter on the rows so I cannot do a select where rowid between 1 and 100 or 101 and 200 and so on. Each row has a unique value in it, and can be added to or deleted from at any time. Also, the data needs to be real-time so caching a query is out of the question as well.) Any suggestions would be greatly appreciated. My phone is ringing off the hook with complaints of the slow response. Thanks, Rob Edwards Phone: (502) 359-1627 Systems Management Tools Pager: (502) 478-1116 United Parcel Service Fax:(502) 359-0094 EMail: [EMAIL PROTECTED] (2B || !2B) == ? __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm 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: Slightly OT: CFQuery, Oracle and Limiting rows
Edwards Robert (air0rae) wrote: I have a query that returns about 5000+ rows of data. Currently it takes about 51 seconds to execute (it's a very complex query). That is long. What are you doing, table scan on a million rows? Full text searching? Get somebody who knows Oracle to look at the query and the database schema. If ODBC, set a blockfactor of 100, it might save half a second :) I'm only displaying 100 records per page using start and end rows in a cfloop. The problem is that the query gets executed every time I go to a page and it take 50 seconds or so each time. I know I can use MAXROWS in my cfquery tag to limit the return to 100 rows, but that will only get me the first 100. Does anyone know how to set it up so I can get the second 100, or third or fortieth? I would guess this needs to be done in oracle instead of CF, but there may be a custom tag I don't know about. (About the data: There is no iterator or counter on the rows so I cannot do a select where rowid between 1 and 100 or 101 and 200 and so on. Each row has a unique value in it, and can be added to or deleted from at any time. Also, the data needs to be real-time so caching a query is out of the question as well.) I think it is unlikely that there is a solution for this in the area of getting just a part of the resultset. You could use WHERE rowcount BETWEEN 100 AND 200 to get the second batch of 100 records back, but I don't think that would matter much for your query execution time (you need an ORDER BY with that which in turn means that internally the db still has to get all rows). If you need better performance, look at the database setup, or losen the realtime restriction (technically your current setup is not realtime anyway because Oracle uses MVCC). Sometimes it might be an option to just give the user the first 300 results and ask them to modify their query to be more specific (need to see the explain output to see if that can be used to reduce runtime). Jochem __ Get the mailserver that powers this list at http://www.coolfusion.com 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: Slightly OT: CFQuery, Oracle and Limiting rows
Actually, upon further review my code doesn't rely on ordered numbers. I'll post the code anyway, it may benefit someone on the list. !--- Previous Next buttons --- CFSET MaxRows = 10 CFPARAM name=start default=1 CFQUERY datasource=cnyidea name=customers SELECT CompanyName, CustomerID FROM Customers WHERE CategoryID = 1001 ORDER BY CompanyName /cfquery CFSET PrevStart = Start - MaxRows CFSET NextStart = Start + MaxRows CFSET current = StartCFIF PrevStart GTE 1 CFOUTPUT FORM action=sometemplate.cfm method=post INPUT type=hidden name=start value=#PrevStart# INPUT type=Image(you could also use submit button src=../images/oldideas2.gif border=0 /form /cfoutput /cfif /td td valign=bottom CFIF NextStart LTE customers.RecordCount CFOUTPUT FORM action=sometemplate.cfm method=post INPUT type=hidden name=start value=#NextStart# INPUT type=Image(you could also use submit button src=yourimagehere border=0 /form /cfoutput /cfif !--- END Previous Next buttons --- -Original Message- From: Edwards Robert (air0rae) [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 10:08 AM To: CF-Talk Subject: RE: Slightly OT: CFQuery, Oracle and Limiting rows I've got code that does that already. (thanks for the offer anyway). my problem is that when I click the button to go to the next one hundred, the cfquery still returns 5000+ records but the cfloop goes to start row 101 and end row 200. I'm looking at possibilities with oracle's ROWNUM psudofield. If I figure out anything on my own, I'll post it here. Rob Edwards Phone: (502) 359-1627 Systems Management Tools Pager: (502) 478-1116 United Parcel Service Fax:(502) 359-0094 EMail: [EMAIL PROTECTED] (2B || !2B) == ? -Original Message- From: Phillip Perry [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 10:01 AM To: CF-Talk Subject: RE: Slightly OT: CFQuery, Oracle and Limiting rows I had the same problem. I used a next/previous button system that showed 10 records at a time. If this is what you're looking for I'll post the code. Phil -Original Message- From: Edwards Robert (air0rae) [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 9:43 AM To: CF-Talk Subject: Slightly OT: CFQuery, Oracle and Limiting rows I have a query that returns about 5000+ rows of data. Currently it takes about 51 seconds to execute (it's a very complex query). I'm only displaying 100 records per page using start and end rows in a cfloop. The problem is that the query gets executed every time I go to a page and it take 50 seconds or so each time. I know I can use MAXROWS in my cfquery tag to limit the return to 100 rows, but that will only get me the first 100. Does anyone know how to set it up so I can get the second 100, or third or fortieth? I would guess this needs to be done in oracle instead of CF, but there may be a custom tag I don't know about. (About the data: There is no iterator or counter on the rows so I cannot do a select where rowid between 1 and 100 or 101 and 200 and so on. Each row has a unique value in it, and can be added to or deleted from at any time. Also, the data needs to be real-time so caching a query is out of the question as well.) Any suggestions would be greatly appreciated. My phone is ringing off the hook with complaints of the slow response. Thanks, Rob Edwards Phone: (502) 359-1627 Systems Management Tools Pager: (502) 478-1116 United Parcel Service Fax:(502) 359-0094 EMail: [EMAIL PROTECTED] (2B || !2B) == ? __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm 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: Slightly OT: CFQuery, Oracle and Limiting rows
Assuming you can do *TOP* within Oracle (i don't use it, so i don't know) and there is *any* field you can order by, the following works great for me (for SQL Server): select top X item_ID fromattorney where attorney_ID in ( select top Y attorney_ID fromattorney order byattorney_ID DESC ) order byattorney_ID where x = total number of rows you want and y = total_records - start_row if i have 1000 rows and want records 1 - 10 x = 10 y = 1000 - 1 + 1 = 1000 if i want 401 - 420 x = 20 y = 1000 - 401 + 1 = 600 Hope that helps. - j jim curran technical director nylon technology 212.691.1134 [EMAIL PROTECTED] -Original Message- From: Edwards Robert (air0rae) [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 9:43 AM To: CF-Talk Subject: Slightly OT: CFQuery, Oracle and Limiting rows I have a query that returns about 5000+ rows of data. Currently it takes about 51 seconds to execute (it's a very complex query). I'm only displaying 100 records per page using start and end rows in a cfloop. The problem is that the query gets executed every time I go to a page and it take 50 seconds or so each time. I know I can use MAXROWS in my cfquery tag to limit the return to 100 rows, but that will only get me the first 100. Does anyone know how to set it up so I can get the second 100, or third or fortieth? I would guess this needs to be done in oracle instead of CF, but there may be a custom tag I don't know about. (About the data: There is no iterator or counter on the rows so I cannot do a select where rowid between 1 and 100 or 101 and 200 and so on. Each row has a unique value in it, and can be added to or deleted from at any time. Also, the data needs to be real-time so caching a query is out of the question as well.) Any suggestions would be greatly appreciated. My phone is ringing off the hook with complaints of the slow response. Thanks, Rob Edwards Phone: (502) 359-1627 Systems Management Tools Pager: (502) 478-1116 United Parcel Service Fax:(502) 359-0094 EMail: [EMAIL PROTECTED] (2B || !2B) == ? __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm 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: Slightly OT: CFQuery, Oracle and Limiting rows
Here's what i came up with (this is oracle 8.i+ specific) Select * from ( Obscenely long and complex query RANK() OVER (ORDER BY some_col ASC NULLS LAST) as Row_Rank ) Where Row_Rank between #startrow# and #endrow# This gives me 100 rows back at a time. I'm still getting an obscene time in the query so i'm trying to come up with ways to speed things up a little more. One idea is to rank on the primary table first as a subquery and then do the complex joins using the where in (subquery) clause so i only join on 100 rows at a time instead of 5000+. Another is to slightly denormalize (currently i have a complex left between join using ipranges) to speed up processing. Some background on this project (what little i am allowed to reveal): This is a tool that runs on every machine and checks in with a webserver/database on a given interval. The web server sets the interval by returning a time code. Lots of data sent in a small package including Mac address and ip address. Our IP addresses are broken down by building, so if i want to see machines in a given building i do something like (Where ipoc1 between ipocb1 and ipoce1 and ipco2 between ipcob2 and ipoce2 and ipoc3 between ipocb3 and ipoce3 and ipoc4 between ipocb4 and ipcoe4) with left joins done on the between to get null for any areas not in the iprange (the above code is basicly finding between each octet in the ip address). Yes, this does need to be real time so i can see at any given moment what machines are where and other information. One possibility is to determine the building when checking in and inserting the building name or id in the main table so i don't have to do 4 between joins for every ip address. Hence, the slight denormalization. Thanks for all the help though. Currently the processing time for all the rows is about 57000 milliseconds (57 seconds). If i get it working through the denormalization i'll post the time improvement here if anyone is interested. Rob Edwards Phone: (502) 359-1627 Systems Management Tools Pager: (502) 478-1116 United Parcel Service Fax:(502) 359-0094 EMail: [EMAIL PROTECTED] (2B || !2B) == ? -Original Message- From: Jim Curran [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 10:35 AM To: CF-Talk Subject: RE: Slightly OT: CFQuery, Oracle and Limiting rows Assuming you can do *TOP* within Oracle (i don't use it, so i don't know) and there is *any* field you can order by, the following works great for me (for SQL Server): select top X item_ID fromattorney where attorney_ID in ( select top Y attorney_ID fromattorney order byattorney_ID DESC ) order byattorney_ID where x = total number of rows you want and y = total_records - start_row if i have 1000 rows and want records 1 - 10 x = 10 y = 1000 - 1 + 1 = 1000 if i want 401 - 420 x = 20 y = 1000 - 401 + 1 = 600 Hope that helps. - j jim curran technical director nylon technology 212.691.1134 [EMAIL PROTECTED] -Original Message- From: Edwards Robert (air0rae) [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 9:43 AM To: CF-Talk Subject: Slightly OT: CFQuery, Oracle and Limiting rows I have a query that returns about 5000+ rows of data. Currently it takes about 51 seconds to execute (it's a very complex query). I'm only displaying 100 records per page using start and end rows in a cfloop. The problem is that the query gets executed every time I go to a page and it take 50 seconds or so each time. I know I can use MAXROWS in my cfquery tag to limit the return to 100 rows, but that will only get me the first 100. Does anyone know how to set it up so I can get the second 100, or third or fortieth? I would guess this needs to be done in oracle instead of CF, but there may be a custom tag I don't know about. (About the data: There is no iterator or counter on the rows so I cannot do a select where rowid between 1 and 100 or 101 and 200 and so on. Each row has a unique value in it, and can be added to or deleted from at any time. Also, the data needs to be real-time so caching a query is out of the question as well.) Any suggestions would be greatly appreciated. My phone is ringing off the hook with complaints of the slow response. Thanks, Rob Edwards Phone: (502) 359-1627 Systems Management Tools Pager: (502) 478-1116 United Parcel Service Fax:(502) 359-0094 EMail: [EMAIL PROTECTED] (2B || !2B) == ? __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http
Re: Slightly OT: CFQuery, Oracle and Limiting rows
Edwards Robert (air0rae) wrote: One idea is to rank on the primary table first as a subquery and then do the complex joins using the where in (subquery) clause so i only join on 100 rows at a time instead of 5000+. Another is to slightly denormalize (currently i have a complex left between join using ipranges) to speed up processing. That should help. Some background on this project (what little i am allowed to reveal): This is a tool that runs on every machine and checks in with a webserver/database on a given interval. The web server sets the interval by returning a time code. Lots of data sent in a small package including Mac address and ip address. Our IP addresses are broken down by building, so if i want to see machines in a given building i do something like (Where ipoc1 between ipocb1 and ipoce1 and ipco2 between ipcob2 and ipoce2 and ipoc3 between ipocb3 and ipoce3 and ipoc4 between ipocb4 and ipcoe4) with left joins done on the between to get null for any areas not in the iprange (the above code is basicly finding between each octet in the ip address). I thought Oracle had IP and MAC datatypes? If you just need to see what is where NOW, this screams for a second table with just the latest data. Just put a trigger on the table you have now and insert whatever is inserted there into a second table. Upon insert in that second table, fire a trigger that deletes the old entry for that machine. That way you should have a quite small table with just the latest data, and use the big table only if you need historic data. (You could achieve the same using partial indexes and a boolean, but I don't know if Oracle supports partial indexes.) Yes, this does need to be real time so i can see at any given moment what machines are where and other information. Well, currently it isn't realtime. You are about 57 seconds behind. And if 10 people hit the application at the same time and the available resources are distributed equally you are 10 times 57 seconds behind (baring caching etc.). One possibility is to determine the building when checking in and inserting the building name or id in the main table so i don't have to do 4 between joins for every ip address. Hence, the slight denormalization. Seeing the query helps. Seeing the explain output helps even more. Jochem __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. 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: Slightly OT: CFQuery, Oracle and Limiting rows
You should read this article, as it points out some of the pitfalls of doing this: http://gethelp.devx.com/techtips/oracle_pro/10min/10min0402/10min0402.asp Ways to speed up your query include: - index the columns you're joining on - use materialized views for joining those tables whose data doesn't change - use explain plan to see whether it's actually the joins slowing you down before you denormalize - avoid WHERE FIELD LIKE '%something' if you can --- Edwards Robert (air0rae) [EMAIL PROTECTED] wrote: Here's what i came up with (this is oracle 8.i+ specific) Select * from ( Obscenely long and complex query RANK() OVER (ORDER BY some_col ASC NULLS LAST) as Row_Rank ) Where Row_Rank between #startrow# and #endrow# This gives me 100 rows back at a time. I'm still getting an obscene time in the query so i'm trying to come up with ways to speed things up a little more. One idea is to rank on the primary table first as a subquery and then do the complex joins using the where in (subquery) clause so i only join on 100 rows at a time instead of 5000+. Another is to slightly denormalize (currently i have a complex left between join using ipranges) to speed up processing. Some background on this project (what little i am allowed to reveal): This is a tool that runs on every machine and checks in with a webserver/database on a given interval. The web server sets the interval by returning a time code. Lots of data sent in a small package including Mac address and ip address. Our IP addresses are broken down by building, so if i want to see machines in a given building i do something like (Where ipoc1 between ipocb1 and ipoce1 and ipco2 between ipcob2 and ipoce2 and ipoc3 between ipocb3 and ipoce3 and ipoc4 between ipocb4 and ipcoe4) with left joins done on the between to get null for any areas not in the iprange (the above code is basicly finding between each octet in the ip address). Yes, this does need to be real time so i can see at any given moment what machines are where and other information. One possibility is to determine the building when checking in and inserting the building name or id in the main table so i don't have to do 4 between joins for every ip address. Hence, the slight denormalization. Thanks for all the help though. Currently the processing time for all the rows is about 57000 milliseconds (57 seconds). If i get it working through the denormalization i'll post the time improvement here if anyone is interested. Rob Edwards Phone: (502) 359-1627 Systems Management Tools Pager: (502) 478-1116 United Parcel Service Fax:(502) 359-0094 EMail:[EMAIL PROTECTED] (2B || !2B) == ? -Original Message- From: Jim Curran [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 10:35 AM To: CF-Talk Subject: RE: Slightly OT: CFQuery, Oracle and Limiting rows Assuming you can do *TOP* within Oracle (i don't use it, so i don't know) and there is *any* field you can order by, the following works great for me (for SQL Server): selecttop X item_ID from attorney where attorney_ID in( select top Y attorney_ID fromattorney order byattorney_ID DESC ) order by attorney_ID where x = total number of rows you want and y = total_records - start_row if i have 1000 rows and want records 1 - 10 x = 10 y = 1000 - 1 + 1 = 1000 if i want 401 - 420 x = 20 y = 1000 - 401 + 1 = 600 Hope that helps. - j jim curran technical director nylon technology 212.691.1134 [EMAIL PROTECTED] -Original Message- From: Edwards Robert (air0rae) [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 9:43 AM To: CF-Talk Subject: Slightly OT: CFQuery, Oracle and Limiting rows I have a query that returns about 5000+ rows of data. Currently it takes about 51 seconds to execute (it's a very complex query). I'm only displaying 100 records per page using start and end rows in a cfloop. The problem is that the query gets executed every time I go to a page and it take 50 seconds or so each time. I know I can use MAXROWS in my cfquery tag to limit the return to 100 rows, but that will only get me the first 100. Does anyone know how to set it up so I can get the second 100, or third or fortieth? I would guess this needs to be done in oracle instead of CF, but there may be a custom tag I don't know about. (About the data: There is no iterator or counter on the rows so I cannot do a select where rowid between 1 and 100 or 101 and 200 and so on. Each row has a unique value in it, and can be added to or deleted from at any time. Also, the data needs to be real-time so caching a query is out of the question as well.) Any suggestions
