Join Vs Cursor Opinion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Join Vs Cursor Opinion

Author

Topic

TheSpongebob
Starting Member

8 Posts

Posted - 2006-02-04 : 14:47:07

Hi

I have a performance question .
I have a query that runs against a master and a detail table. If I join the two, my query runs in 55 seconds. If I cursor the master, and then select the detail ... it takes forever ( 10 minutes ? ). Am I forgetting something about cursors?

btw - I want to put this query into an existing cursor script.

thanx !

RAndy

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< JOIN
USE Northwind

PRINT 'Started : ' + cast(getdate() as char(20))

SET NOCOUNT ON

select t.acct, tx_year,
max(case when prov='on' then amt else 0 END) as ONamt,
max(case when prov='ab' then amt else 0 END) as ABamt,
max(case when prov='pq' then amt else 0 END) as PQamt,
max(case when prov='sk' then amt else 0 END) as NWAmt

FROM test t,
testAcct ta

where t.acct > 0
and t.acct = ta.acct

group by t.acct, tx_year

SET NOCOUNT OFF

PRINT 'Ended : ' + cast(getdate() as char(20))

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< CURSOR

USE Northwind

PRINT 'Started : ' + cast(getdate() as char(20))
DECLARE myCurs CURSOR READ_ONLY
FOR SELECT acct FROM TESTAcct WHERE acct < 1000

DECLARE @myAcct int
DECLARE @tx_year int
DECLARE @on bigint
DECLARE @ab bigint
DECLARE @pq bigint
DECLARE @sk bigint

SET NOCOUNT ON

OPEN myCurs
FETCH NEXT FROM myCurs INTO @myAcct
WHILE (@@fetch_status <> -1)
BEGIN

select tx_year,
max(case when prov='ab' then amt else 0 END) as ABamt,
max(case when prov='on' then amt else 0 END) as ONamt,
max(case when prov='pq' then amt else 0 END) as PQamt,
max(case when prov='nw' then amt else 0 END) as NWAmt

FROM test

where acct = @myAcct
group by tx_year

FETCH NEXT FROM myCurs INTO @myAcct

END

CLOSE myCurs
DEALLOCATE myCurs

SET NOCOUNT OFF

PRINT 'Ended : ' + cast(getdate() as char(20))

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< TABLES
CREATE TABLE [Test] (
[acct] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[tx_year] [int] NOT NULL ,
[prov] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[amt] [numeric](10, 2) NULL CONSTRAINT [DF_Test_amt] DEFAULT (0),
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[acct],
[tx_year],
[prov]
) ON [PRIMARY]
) ON [PRIMARY]
GO

- 2M rows

CREATE TABLE [TestAcct] (
[acct] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_TestAcct] PRIMARY KEY CLUSTERED
(
[acct]
) ON [PRIMARY]
) ON [PRIMARY]
GO

8M rows

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-04 : 14:54:37

Would you expect otherwise?

Even ignoring the processing the cursor has to produce a lot of resultsets rather than one.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

TheSpongebob
Starting Member

8 Posts

Posted - 2006-02-04 : 15:03:55

hmmm .... Obviously, I am wrong but I would expect the cursor and the join to be very close. My train of though is that the join is just another fully covered hit on a table. Wether you do it up front, or do it in the join, I was expecting simialr results. I don't understand where the unaccounted for time came from.

thanx nr

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2006-02-04 : 16:35:46

quote:
Originally posted by TheSpongebob

hmmm .... Obviously, I am wrong but I would expect the cursor and the join to be very close. My train of though is that the join is just another fully covered hit on a table. Wether you do it up front, or do it in the join, I was expecting simialr results. I don't understand where the unaccounted for time came from.

thanx nr

Joins are almost always much faster than cursors. Remember that Relational Databases are built with sets in mind and are optimized to work with them. When you do joins you thinking in terms of sets and when you do cursors your doing 1 row at a time.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-04 : 16:45:41

Nope - usually cursors are hundreds/thousands of times slower.
The execption can be with correlated subqueries where the performance can be fairly close.

Usually use of a cursor shows lack of sql experience or a poorly designed system.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

TheSpongebob
Starting Member

8 Posts

Posted - 2006-02-04 : 18:03:53

thanx NR. can you point me to any good discussions on the topic ?
I have a feeling you might just know of some ...

"Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy."

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-04 : 19:15:17

Just do a search for cursor on this site.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Non-conformer
Starting Member

14 Posts

Posted - 2006-02-06 : 10:34:48

I don't agree with this philosophy of "never, ever, ever, EVER use a cursor!!" True, cursors carry a lot a lot of overhead and it's generally better to write queries withOUT one than WITH one, but there are some circumstances - rare, but they do exist - where using a cursor is simply better.

It all comes down to one basic axiom: use the right tool for the right job.

Keep it simple.
NC

manjuram
Starting Member

1 Post

Posted - 2006-02-06 : 16:22:19

I was just going through the forum ......... i was stuck at this topic as a week back i happened to write a SP using cursor. I used cursors as i though it is not possible to drive the logic without cursors.

After reading this topic ........... i am puzzed if it could be possible to do it with out cursors.

I am describing the scenario below...... pls help me on how it could be derived without cursors.

I have a table X with field a,b,c. I have to group the table based on field 'a' and find the top 2 based on field 'c'. so the final output should have a,b,c where c the top 2 in each group.

thanks
Manju

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-07 : 10:09:30

Refer point 2 here
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources