You know what, I just remembered a trick I brought up to this group a few times over the past couple of years, but not for a while now. It may apply in your case, certainly worth looking at. Years ago I noticed when running a SQL-SELECT query via a Parameterized View against a Pervasive table, with STATUS BAR ON and TALK ON, that the records from the source table were taking their sweet old time trickling across the LAN to my PC. I was expecting to get several hundred thousand records, and at the rate they were coming over I expected to have a multi-hour delay in my processing. I had an active prompt in the Command Window while the records were coming across, and for some reason (desperation?) I executed a GO BOTTOM command on the cursor and the remaining records FLEW across the LAN with no further hesitation, like somebody applied a flame-thrower to the Server's ass.
I sent that in a FoxPro Advisor tip, which was being reviewed by Ed Leafe as a Contirbuting Editor at the time. He found he could accomplish the same kid of rapid transfer of records without moving the Record Pointer by using a RECCOUNT() command. In almost all my code when I execute a REQUERY() (usually on a Parameterized View) I immediately follow up with RECCOUNT() in my code just to slam the records over as fast as they can come across the LAN. Works like a charm. I do this for several large VFP and PostgreSQL tables being hit with Parameterized Views, with some tables being over 160,000,000 records (PostGreSQL table for Federal DoNotCall listing updates for clients each month). I hope that can be used in your situation. If so I sure would like to know so I can gloat a bit <g>... Gil > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of NickC > Sent: Saturday, September 06, 2008 1:03 PM > To: [EMAIL PROTECTED] > Subject: RE: SQL > not optimised > > > Hi Ted, > > I like your theory but unfortunately still doesn't help speed this up. > According to SYS(3054) it is being optimised but even so still > takes almost > a minute to extract one record. > > However I have noticed that it is only the full table select > which is slow, > a select on a thin table of OrderNo only is instant. > Let me explain that further: > I can create a table containing only the Order number field - > SELECT OrdNo FROM Orders INTO TABLE OrderPOOnly > If I then run the Select on that it is Optimised and runs very fast - > SELECT MIN(OrdNo) FROM OrderPOOnly WHERE OrdNo > cLastOrder ORDER BY > OrdNo ASC > But when I run the same thing on the full table it takes one minute to > retrieve one record - > SELECT MIN(OrdNo) FROM Orders WHERE OrdNo > cLastOrder ORDER BY > OrdNo ASC > > Very strange! > > > > -----Original Message----- > > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf > > Of Ted Roche > > Sent: 06 September 2008 14:26 > > To: [email protected] > > Subject: Re: SQL > not optimised > > > > On Thu, Sep 4, 2008 at 3:42 PM, Nick Causton > <[EMAIL PROTECTED]> > > wrote: > > > > > > * Next > > > SELECT TOP 1 * FROM Orders WHERE Orders.OrderNo > (clastOrderNumber) > > ORDER > > > BY OrderNo DESC > > > > > > * Prev > > > SELECT TOP 1 * FROM Orders WHERE Orders.OrderNo < (clastOrderNumber) > > ORDER > > > BY OrderNo ASC > > > > > > Problem is VFP does not use the index for this and retrieving the > > > next/previous record takes nearly a minute when it is running over the > > > network. > > > > > > Any suggestions how to get around this? > > > > > > > How is it you know that VFP is not using the index? Have you examined > > the Rushmore results of the query using the SYS() function? > > > > It's more likely that Fox is processing the entire query, sorting the > > entire dataset result, then truncating ito the TOP 1 record, which is > > what you asked it to do, a pretty intensive intermediate step. Why not > > try SELECT MAX(OrderNo) from Orders where OrderNo < lastOrderNo ... > > or.. SELECT MIN(OrderNo) from Orders where OrderNo > lastOrderNo and > > use that single record result as the key for getting the Next/Previous > > record? > > > > SELECT * FROM Orders where OrderNo IN (your subselect for Max() > or Min()) > > > > -- > > Ted Roche > > Ted Roche & Associates, LLC > > http://www.tedroche.com > > > > [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
