Stephen,
> Have you dropped the indexes above on the production table and then
> recreated them?
Yep, even created a completely new table by:
COPY STRUCTURE TO ARRAY
CREATE TABLE FROM ARRAY
APPEND FROM
But still has same problem.
> If you move the current pointer value + - 5,000 do you still see the
> same time? Or does the time start to get smaller?
No change
> I bet if you took off that stupid index for dept you would get better
> time on your local hit. I bet that you are doing a table scan because
> you have two similar indexes and it doesn't know which one to use so
> it DOESN'T use either.
Still very slow.
Rgds,
Nick
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
> Of Stephen Russell
> Sent: 09 September 2008 21:29
> To: ProFox Email List
> Subject: Re: SQL > not optimised
>
> On Tue, Sep 9, 2008 at 1:57 PM, NickC <[EMAIL PROTECTED]> wrote:
>
>
> > INDEX ON OrdNo TAG OrdNo
> > INDEX ON LEFT(OrdNo,1) TAG Dept
>
> Have you dropped the indexes above on the production table and then
> recreated them?
>
> I understand that getting 200 + columns of data is what is in the way
> even if it was only 1 row.
>
> If you move the current pointer value + - 5,000 do you still see the
> same time? Or does the time start to get smaller?
>
> I bet if you took off that stupid index for dept you would get better
> time on your local hit. I bet that you are doing a table scan because
> you have two similar indexes and it doesn't know which one to use so
> it DOESN'T use either.
>
> I bet if you put on your machine something like TaskInfo you would see
> a HUGE amount of network traffic as well as local disk IO for the full
> table queries.
>
> Drop all but 1 index on OrdNo and I think your going to get better
> results. At least this is how you unbottleneck SQL Server, and or
> MySQL or DB2. None of that trim or left() stuff, just the blasted
> column.
>
> HTH
>
> --
> Stephen Russell
> Sr. Production Systems Programmer
> Mimeo.com
> Memphis TN
>
> 901.246-0159
>
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
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.
