Very Computer

Hi all,

We have a fairly mature app that does all the usual SQL things via
ODBC.  On one particular site, this all works nicely, except for a
particular statement.  The statement in question looks like

SELECT  A0.uri,A0.rkRecUri1,A1.recordId,A1.fullRecordId
FROM mytable A0, myothertable A1 (FASTFIRSTROW)
WHERE ( A0.rkRecUri1 = A1.uri )
AND ( ( rkRecUri2 = 86  AND  linkType = '2' ) )
ORDER BY fullRecordId ASC

Now, the statement executes nicely - about 40ms, using the appropriate
indexes on each table (query plan looks perfect).  But, when the
results are then fetched a row at a time, each fetch takes ages
compared to other similar query-and-fetch tasks done before and after
this.  And I mean ages, anything up to 37 seconds!!!!

Other statements being run fetch similar data from similar result sets
in 0 to 10 milliseconds from the same db+server.  No other tasks or
services were being run at the time (this is a test environment).

I believe this is being done with straight SS7 - no service packs.
Driver is 3.70.0623.  The query returns about 90 rows of data, but the
server has enough memory allocated to cache almost the entire db.
Stats were run on the tables in question, and things like cache hit
rates, waits, query plan etc. all look fine.

Has anyone seen anything like this, or does anyone have any ideas what
might be causing it.

Thanks
Fuzzy
:-)

I have seen the same problem with SQL Server 6.5.  I managed to get around
it by creating a temporary table that matches the SELECT and using the
INSERT INTO ... SELECT FROM and making the cursor go over temporary table
instead of original table.  I found out that SQL server sometimes generate
different query plans for straight SELECT vs. a Cursor.  To compound the
problem if you see the plan with NOEXEC ON vs. NOEXEC OFF the plan is
different again for the same Cursor statement.  Since NOEXEC OFF is what
is really important you need to generate plan for your cursor/fetch
statement to see if SQL Server is really using the correct index or not.

Hi Fuzzy

This is quesswork, but have you tried using your aliases in

AND ( ( rkRecUri2 = 86  AND  linkType = '2' ) )
ORDER BY fullRecordId ASC

regards
jensk

Quote:> Hi all,

> We have a fairly mature app that does all the usual SQL things via
> ODBC.  On one particular site, this all works nicely, except for a
> particular statement.  The statement in question looks like

> SELECT  A0.uri,A0.rkRecUri1,A1.recordId,A1.fullRecordId
> FROM mytable A0, myothertable A1 (FASTFIRSTROW)
> WHERE ( A0.rkRecUri1 = A1.uri )
> AND ( ( rkRecUri2 = 86  AND  linkType = '2' ) )
> ORDER BY fullRecordId ASC

> Now, the statement executes nicely - about 40ms, using the appropriate
> indexes on each table (query plan looks perfect).  But, when the
> results are then fetched a row at a time, each fetch takes ages
> compared to other similar query-and-fetch tasks done before and after
> this.  And I mean ages, anything up to 37 seconds!!!!

> Other statements being run fetch similar data from similar result sets
> in 0 to 10 milliseconds from the same db+server.  No other tasks or
> services were being run at the time (this is a test environment).

> I believe this is being done with straight SS7 - no service packs.
> Driver is 3.70.0623.  The query returns about 90 rows of data, but the
> server has enough memory allocated to cache almost the entire db.
> Stats were run on the tables in question, and things like cache hit
> rates, waits, query plan etc. all look fine.

> Has anyone seen anything like this, or does anyone have any ideas what
> might be causing it.

> Thanks
> Fuzzy
> :-)

On Wed, 13 Oct 1999 19:52:08 +0200, "Jens K Jensen"

I'll give it a go.

Thanks
Fuzzy
;-)