472,146 Members | 1,312 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,146 software developers and data experts.

Dynamic Cursor versus Forward Only Cursor gives Poor Performance


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.

Jul 20 '05 #1
1 7296
Please explain what you are trying to do here. Cursors are usually best
avoided and typically perform much less efficiently than set-based
solutions. If you describe the problem in more detail someone should be able
to suggest an alternative that doesn't use a cursor. Post DDL (CREATE TABLE
statements), some sample data (INSERT statements) and show your required

David Portas
SQL Server MVP
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Richard | last post: by
5 posts views Thread by Ralph | last post: by
reply views Thread by Shailesh | last post: by
5 posts views Thread by Todd Huish | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.