I just rewrote a query as a test a few weeks ago that replaced what you have below. The search would query for a count first then grab the 50/100 etc records to be displayed for paging.
When someone queried the whole database it was taking upwards of 50 seconds to run both queries. I moved them into a stored proc. Queried the information and inserting it into a temp table with an auto id. Then I would query records 1-50, 51-100, 101-150 etc. depending where on the page the user was. I also am able to do a quick count of records on that temp table to get total records which is much after than essentially running same query twice. The total time dropped to 13-22 seconds for the query. I haven't tested under heavy load to see how the temp table stuff will hold up but it could be one path to follow for you. Maybe others have some thoughts on the temp table method. -----Original Message----- From: Baz [mailto:[EMAIL PROTECTED] Sent: Thursday, February 02, 2006 9:25 AM To: CF-Talk Subject: SQL Encapsulation? Like any other code, there are times when portions of your SQL queries need to be re-used or duplicated. A common example is having a method that returns a COUNT of a query based on some conditions, and having another method that returns some rows from that query. For instance: *** getCount() *** SELECT Count(OrderID) as OrderIDCount FROM OrderTable INNER JOIN StatusTable ON (OrderTable.OrderID= StatusTable.OrderID) WHERE (whatever) *** getRows() *** SELECT TOP 50 OrderID, StatusID FROM OrderTable INNER JOIN StatusTable ON (OrderTable.OrderID= StatusTable.OrderID) WHERE (whatever) In the preceding code, the FROM clause is repeated in both methods, as is the WHERE clause. Now in a real-world complex app, that FROM would actually be 10-15 lines long, and the WHERE could have hundreds of lines of complex logic based on passed in arguments. Plus you may need not 2, but 5, 10, 20 methods using the same FROM and/or WHERE and/or SELECT. So my question is how do people handle this? If you had 15 methods with complex WHERE and FROM clauses that are repeated intermittently, and then something changes, it's an error-prone hassle to copy paste all the affected sections. Thoughts? Baz ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231139 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
