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
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.