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

Transact-SQL (2000)

Cursors

Author

Topic

rkc01
Starting Member

43 Posts

Posted - 2003-01-07 : 18:06:22

I know that cursors are taboo but this was the only way I could figure out who's credit card was double billed. Can someone suggest a better way? A client said they were getting calls from customers that had been double billed. The table that I had to query to confirm contained responses from the clearing house, (approval, declined, etc.). A double billing would be a record written to this table that contained the same credit card number, transaction date time and 2 different processed date times, (indicating the the card had been billed twice). Here's the cursor I used:

if exists (select * from sysobjects where id = object_id('dbo.Dups') and sysstat & 0xf = 3)
drop table dbo.Dups
GO

CREATE TABLE dbo.Dups (
creditcardnumber varchar(60) NOT NULL ,
[datetime] datetime Not NULL ,
processeddatetime datetime Not NULL ,
clearingmethod varchar(60) NULL ,
amount money NULL ,
urc char(1) NULL ,
referencenumber varchar(6) NULL ,
longresponse varchar(40) NULL
)
GO

DECLARE @creditcardnumber varchar (60)
DECLARE @datetime datetime
DECLARE @processeddatetime datetime
DECLARE @clearingmethod varchar (60)
DECLARE @amount money
DECLARE @urc char (1)
DECLARE @referencenumber varchar (6)
Declare @longresponse varchar (40)
DECLARE @Dups int

DECLARE FindDups INSENSITIVE CURSOR
FOR

select creditcardnumber, [datetime], processeddatetime, clearingmethod, amount, urc, referencenumber, longresponse from creditcardresponse
where processeddatetime between '12/23/2002' and '12/23/2002 23:59:59'
and
urc = 'A'
and
longresponse like '%approval%'
and
longresponse not like '%tran%'
and
clearingmethod like '%OFFLINE%'
order by creditcardnumber, [datetime]

FOR READ ONLY

OPEN FindDups
FETCH NEXT FROM FindDups INTO @creditcardnumber, @datetime, @processeddatetime, @clearingmethod, @amount, @urc, @referencenumber, @longresponse

WHILE (@@fetch_status = 0)
BEGIN
BEGIN
SELECT @Dups = Count(*)
FROM creditcardresponse (NOLOCK)
WHERE creditcardnumber = @creditcardnumber
and
[datetime] = @datetime

IF @Dups > 1
INSERT INTO dbo.Dups VALUES(@creditcardnumber, @datetime, @processeddatetime, @clearingmethod, @amount, @urc, @referencenumber, @longresponse)

END

FETCH NEXT FROM FindDups INTO @creditcardnumber, @datetime, @processeddatetime, @clearingmethod, @amount, @urc, @referencenumber, @longresponse
END

CLOSE FindDups

SELECT * FROM dbo.Dups

DEALLOCATE FindDups
GO

I later turned it into a stored procedure that I could just feed two date time ranges. SO many times I've seen statements about NOT using cursors if it can be avoided, (not just here but in many books as well).

Thanks,

Rob

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-07 : 18:19:54

select creditcardnumber, [datetime], processeddatetime, clearingmethod, amount, urc, referencenumber, longresponse
from creditcardresponse
INNER JOIN (SELECT creditcardnumber, [datetime] FROM creditcardresponse
WHERE processeddatetime BETWEEN '12/23/2002' AND '12/23/2002 23:59:59'
GROUP BY creditcardnumber, [datetime] HAVING Count(*)>1) Z
ON creditcardresponse.creditcardnumber=Z.creditcardnumber
AND creditcardresponse.[datetime]=Z.[datetime]
where processeddatetime between '12/23/2002' and '12/23/2002 23:59:59'
and urc = 'A'
and longresponse like '%approval%'
and longresponse not like '%tran%'
and clearingmethod like '%OFFLINE%'
order by creditcardnumber, [datetime]

I'm pretty sure this can be written without using a subquery, but I didn't want to assume anything. The subquery finds all of the dupe card number/datetime combinations, which is then joined to the outer query. Some of it is redundant, someone should be able to tweak this for you.

For future reference, DO NOT name columns after reserved words!! Like datetime. Bad Bad Bad. Unless you absolutely cannot change that column name, you need to change that column name.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-07 : 18:42:24

I am oversimplifying, but how about something like:

SELECT Cardnumber, [DateTime]
FROM
creditcardresponse
WHERE
(all of your criteria in here)
GROUP BY Cardnumber, [DateTime]
HAVING Min(ProcessDate) <> Max(ProcessDate)

Probably basically the same as Rob's, except using my fun Min<>Max HAVING clause. I think the one thing Rob's solution might need is the WHERE criteria in the subquery as well, if there are other kinds of records in there that would affect the COUNT(*).

- Jeff

rkc01
Starting Member

43 Posts

Posted - 2003-01-08 : 16:39:03

Thanks Rob, that did the trick.

Thanks to you as well Jeff.

~Rob

rkc01
Starting Member

43 Posts

Posted - 2003-01-09 : 14:26:02

I was wondering if anyone can explain why the second query, (using a join) is so much faster than the one using the cursor. Im my mind they are both doing the same thing, comparing one record at a time to every record in the table. Just can't get my head around what makes the join so much faster. Is it the underlying way in which SQL Server handles the 2?

Thx,

Rob

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-01-09 : 14:32:50

Do not question the power of set-based processing!

Jonathan
{0}

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-01-09 : 14:33:58

All seriousness aside, you should examine the execution plans for each method to see what efficiences SQL Server can use when presented with a set manipulation vs. one-at-a-time processing.

Jonathan
{0}

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-09 : 14:45:00

quote:
Is it the underlying way in which SQL Server handles the 2?

Exactly!

The tweezer analogy:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22863

When you use a cursor, you introduce overhead necessary to NAVIGATE the rows being returned. You can fetch a row, but you have to TELL the cursor to fetch it. Otherwise it just sits there like a dumb shit waiting for you to tell it what to do. In navigating a cursor, there is additional overhead required for determining if the row being fetched exists, was deleted, or just newly inserted since the cursor was opened (depending on what kind of cursor you use)

Simply using a SELECT avoids all that; it spoons up all of the sugar grains in one shot instead of picking each one up with the tweezer/cursor. Since a cursor requires a SELECT statement anyway, it will effectively use the same query optimizations, BUT avoiding the cursor eliminates the overhead of processing one row at a time. It's not particular to a JOIN either. In using the cursor you completely avoided a JOIN anyway, and what you are actually doing is running the 2nd, inner query EACH TIME FOR EACH ROW being fetched in the 1st, outer cursor. This is effectively what a correlated subquery would do as well, only it can utilize internal SQL Server optimizations that a cursor may not be able to...and even if the cursor did, the fetching operation is still picking one row at a time.

A JOIN, on the other hand, can grab all of the corresponding rows in one shot, and may even be able to get the data directly from the index without needing to access the table at all. A cursor will always access the table no matter how the query might be optimized.

There's more detail on the effects of the various cursor types in Books Online, and Inside SQL Server has very good sections on the inner mechanics of cursors, and also when and where a cursor is a (relatively) good choice over other methods.

Edited by - robvolk on 01/09/2003 14:46:39

rkc01
Starting Member

43 Posts

Posted - 2003-01-09 : 20:15:42

Thanks Rob. Your analogy and explanation helped a lot. I'm in the middle of Kalen's book right now. Good Stuff.

Thanks to you as well Jonathan. I haven't looked at the differnece in the execution plans yet but I will shortly.

-Rob

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources