Thanks Gil but tried that and no difference. Nick
> -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf > Of Gil Hale > Sent: 06 September 2008 20:43 > To: [email protected] > Subject: RE: SQL > not optimised > > 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.
