Dynamic Cursor versus Forward Only Cursor gives Poor Performance

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.

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

--
David Portas
SQL Server MVP
--

dwh problem

Hi,
I have to choose between
a) using one single fact table
b) using one fact table and several dimension tables
I know that if you have no dimension tables (i.e. you store all the
information on one single fact table) your sql will not include any joins. I
understand joins penalize performance so it would make sense to go for this
option. Can someone tell me if a star schema approach with surrogate keys on
the fact table pointing to each dimension table would be less performant than
the single fact table approach? What would be more efficient if I want to use
Business Objects as the reporting tool? What are the pros and cons of each
option?
If anyone has previously had to make a similar decision I would very much
appreciate the help.
Thanks a lot,
Roberto
Hi rufian,
I can't completely answer your question, but my experience so far has been
that using the surrogate keys is worth doing. I have built fact tables before
that should everything in one table and it didn't significantly improve
performance.
Analysis services 2005 also seems to be happier with the star schema, but I
don't know much about it.
One of the biggest benefits of the star schema is that you can add more
attributes and dimensions much more easily.
If you know how big your fact table will be, then the decision may be made
for you. As soon as your fact table starts getting big, you should use
dimension tables. If you have 1 million rows in the fact table, there is no
way I would skip the dimension tables. Even for 100,000 I probably wouldn't
skip them.
I hope that helps.
"rufian" wrote:

> Hi,
> I have to choose between
> a) using one single fact table
> b) using one fact table and several dimension tables
> I know that if you have no dimension tables (i.e. you store all the
> information on one single fact table) your sql will not include any joins. I
> understand joins penalize performance so it would make sense to go for this
> option. Can someone tell me if a star schema approach with surrogate keys on
> the fact table pointing to each dimension table would be less performant than
> the single fact table approach? What would be more efficient if I want to use
> Business Objects as the reporting tool? What are the pros and cons of each
> option?
> If anyone has previously had to make a similar decision I would very much
> appreciate the help.
> Thanks a lot,
> Roberto
>
|||I completely agree: the single fact table is not easy to manage in the
future; moreover, it is less performant due to the very high
denormalization (you create an higher I/O work for SQL Server).
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
Eager to Learn ha scritto:
[vbcol=seagreen]
> Hi rufian,
> I can't completely answer your question, but my experience so far has been
> that using the surrogate keys is worth doing. I have built fact tables before
> that should everything in one table and it didn't significantly improve
> performance.
> Analysis services 2005 also seems to be happier with the star schema, but I
> don't know much about it.
> One of the biggest benefits of the star schema is that you can add more
> attributes and dimensions much more easily.
> If you know how big your fact table will be, then the decision may be made
> for you. As soon as your fact table starts getting big, you should use
> dimension tables. If you have 1 million rows in the fact table, there is no
> way I would skip the dimension tables. Even for 100,000 I probably wouldn't
> skip them.
> I hope that helps.
> "rufian" wrote:

dwh problem

Hi,
I have to choose between
a) using one single fact table
b) using one fact table and several dimension tables
I know that if you have no dimension tables (i.e. you store all the
information on one single fact table) your sql will not include any joins. I
understand joins penalize performance so it would make sense to go for this
option. Can someone tell me if a star schema approach with surrogate keys on
the fact table pointing to each dimension table would be less performant tha
n
the single fact table approach? What would be more efficient if I want to us
e
Business Objects as the reporting tool? What are the pros and cons of each
option?
If anyone has previously had to make a similar decision I would very much
appreciate the help.
Thanks a lot,
RobertoHi rufian,
I can't completely answer your question, but my experience so far has been
that using the surrogate keys is worth doing. I have built fact tables befor
e
that should everything in one table and it didn't significantly improve
performance.
Analysis services 2005 also seems to be happier with the star schema, but I
don't know much about it.
One of the biggest benefits of the star schema is that you can add more
attributes and dimensions much more easily.
If you know how big your fact table will be, then the decision may be made
for you. As soon as your fact table starts getting big, you should use
dimension tables. If you have 1 million rows in the fact table, there is no
way I would skip the dimension tables. Even for 100,000 I probably wouldn't
skip them.
I hope that helps.
"rufian" wrote:

> Hi,
> I have to choose between
> a) using one single fact table
> b) using one fact table and several dimension tables
> I know that if you have no dimension tables (i.e. you store all the
> information on one single fact table) your sql will not include any joins.
I
> understand joins penalize performance so it would make sense to go for thi
s
> option. Can someone tell me if a star schema approach with surrogate keys
on
> the fact table pointing to each dimension table would be less performant t
han
> the single fact table approach? What would be more efficient if I want to
use
> Business Objects as the reporting tool? What are the pros and cons of each
> option?
> If anyone has previously had to make a similar decision I would very much
> appreciate the help.
> Thanks a lot,
> Roberto
>|||I completely agree: the single fact table is not easy to manage in the
future; moreover, it is less performant due to the very high
denormalization (you create an higher I/O work for SQL Server).
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
Eager to Learn ha scritto:
[vbcol=seagreen]
> Hi rufian,
> I can't completely answer your question, but my experience so far has been
> that using the surrogate keys is worth doing. I have built fact tables bef
ore
> that should everything in one table and it didn't significantly improve
> performance.
> Analysis services 2005 also seems to be happier with the star schema, but
I
> don't know much about it.
> One of the biggest benefits of the star schema is that you can add more
> attributes and dimensions much more easily.
> If you know how big your fact table will be, then the decision may be made
> for you. As soon as your fact table starts getting big, you should use
> dimension tables. If you have 1 million rows in the fact table, there is n
o
> way I would skip the dimension tables. Even for 100,000 I probably wouldn'
t
> skip them.
> I hope that helps.
> "rufian" wrote:
>