Hello,
I have a test database with table A containing 10,000 rows and a table
B containing 100,000 rows. Rows in B are "children" of rows in A -
each row in A has 10 related rows in B (ie. B has a foreign key to A).
Using ODBC I am executing the following loop 10,000 times, expressed
below in pseudo-code:
"select * from A order by a_pk option (fast 1)"
"fetch from A result set"
"select * from B where where fk_to_a = 'xxx' order by b_pk option
(fast 1)"
"fetch from B result set" repeated 10 times
In the above psueod-code 'xxx' is the primary key of the current A
row. NOTE: it is not a mistake that we are repeatedly doing the A
query and retrieving only the first row.
When the queries use fast-forward-only cursors this takes about 2.5
minutes. When the queries use dynamic cursors this takes about 1 hour.
Does anyone know why the dynamic cursor is killing performance?
Because of the SQL Server ODBC driver it is not possible to have
nested/multiple fast-forward-only cursors, hence I need to explore
other alternatives.
I can only assume that a different query plan is getting constructed
for the dynamic cursor case versus the fast forward only cursor, but I
have no way of finding out what that query plan is.
All help appreciated.
Kevin