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.
