Do I really need a cursor?

I've built an application to import transactions into the database. Bad transactions go in a separate table and dupe transactions get updated. Currently, it takes about 2 hours to import ~40K records using the code below. Obviously I'd like this to run as fast as possible and since cursors are a real drag I was wondering if there was a more efficient way to accomplish this.

DECLARE
@.contact_id int,
@.product_code char(9),
@.status_date datetime,
@.business_code char(4),
@.expire_date datetime,
@.prod_status char(4),
@.transaction_id int,
@.emailAddress varchar(50),
@.journal_id int

BEGIN TRAN
DECLARE transaction_import_cursor CURSOR
FOR SELECT transaction_id, product_code, emailAddress, status_date, business_code, expire_date, prod_status from transactions_batch_tmp
OPEN transaction_import_cursor
FETCH NEXT FROM transaction_import_cursor INTO @.transaction_id, @.product_code, @.emailAddress, @.status_date, @.business_code, @.expire_date, @.prod_status
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SELECT top 1 contacts.contact_id AS contact_id, transactions_batch_tmp.status_date AS status_date, transactions_batch_tmp.product_code AS product_code,
transactions_batch_tmp.business_code AS business_code, transactions_batch_tmp.expire_date AS expire_date,
transactions_batch_tmp.prod_status AS product_status
FROM transactions_batch_tmp INNER JOIN
journal INNER JOIN
contacts ON journal.contact_id = contacts.contact_id ON transactions_batch_tmp.emailAddress = contacts.emailAddress AND
transactions_batch_tmp.product_code = journal.product_code INNER JOIN
products ON transactions_batch_tmp.product_code = products.product_code
WHERE rtrim(ltrim(contacts.emailAddress)) = @.emailAddress AND journal.product_code = @.product_code
ORDER BY transactions_batch_tmp.status_date desc
IF @.@.ROWCOUNT = 0
BEGIN
print 'NEW transaction! ' + @.product_code + @.emailAddress
insert into journal (contact_id, product_code, status_date, business_code, expire_date, entryTypeID, product_status, date_entered)
SELECT distinct rtrim(ltrim(contacts.contact_id)) as cid, rtrim(ltrim(products.product_code)), transactions_batch_tmp.status_date,
rtrim(ltrim(transactions_batch_tmp.business_code)) , transactions_batch_tmp.expire_date, 21, rtrim(ltrim(transactions_batch_tmp.prod_status)), getDate()
FROM contacts INNER JOIN (transactions_batch_tmp INNER JOIN products ON transactions_batch_tmp.product_code=products.produ ct_code) ON contacts.emailAddress=transactions_batch_tmp.email Address
WHERE transactions_batch_tmp.transaction_id=@.transaction _id
END
ELSE
BEGIN
--print 'UPDATE transaction! ' + @.product_code + @.emailAddress
UPDATE journal
SET status_date =
(SELECT max(tmp.status_date)
FROM transactions_batch_tmp tmp, contacts c, products p, journal j
WHERE tmp.emailaddress = @.emailAddress
AND tmp.emailaddress = rtrim(c.emailaddress)
AND c.contact_id = j.contact_id
AND j.product_code = @.product_code
AND j.product_code = tmp.product_code)
FROM transactions_batch_tmp tmp, contacts c, products p, journal j
WHERE tmp.emailaddress = @.emailAddress
AND tmp.emailaddress = rtrim(c.emailaddress)
AND c.contact_id = j.contact_id
AND j.product_code = @.product_code
AND j.product_code = tmp.product_code
END
FETCH NEXT FROM transaction_import_cursor INTO @.transaction_id, @.product_code, @.emailAddress, @.status_date, @.business_code, @.expire_date, @.prod_status
END
CLOSE transaction_import_cursor
DEALLOCATE transaction_import_cursor
COMMIT TRAN

/** purge data from temp error table before writing bad records for this batch **/
truncate table tran_import_error;

/** write bad records (missing product code or email address) to temp_error table **/
insert into tran_import_error (transaction_id, product_code, emailAddress, date_entered)
SELECT DISTINCT transactions_batch_tmp.transaction_id, transactions_batch_tmp.product_code, transactions_batch_tmp.emailAddress, getDate()
FROM transactions_batch_tmp
where transactions_batch_tmp.emailaddress not in (select emailaddress from contacts)
OR
transactions_batch_tmp.product_code not in (select product_code from products)

TIAI don't see anything in your code that requires a cursor. It would run much faster as set-based INSERT and UPDATE statements.|||Well, how would I handle the update part without a cursor? I need to make sure that *only* unique contact_id-product_code values exist in the journal table.

Thanks.|||Add some bit flag and notes columns to your import table. Then you can run data checks against the records prior to importing them. Flag any duplicates or bad records and add a note as to why they were flagged. Then import only the non-flagged records. Delete the non-flagged records when you are done, and you are left with a list of bad records that you can review or discard.|||blindman - Thanks for your help.

I'm almost there (I hope), but was wondering if there was a more efficient way to delete the dupe records than having to write two separate queries. I need to keep the most recent product_code-status_date transaction for *each* person. This runs after I insert ALL the records in the journal table.

--delete dupe trans with status_date as the flag
DELETE journal FROM journal, contacts
JOIN
(select product_code, contact_id, max(status_date) as max_status_date
from journal
group by product_code, contact_id) AS G
ON G.[contact_id] = contacts.[contact_id]
WHERE journal.[status_date] < G.[max_status_date]
AND G.[product_code] = journal.[product_code];

--delete dupe trans with journal_id as the flag
DELETE journal FROM journal, contacts
JOIN
(select product_code, contact_id, max(journal_id) as maxID
from journal
group by product_code, contact_id) AS G
ON G.[contact_id] = contacts.[contact_id]
WHERE journal.[journal_id] < G.[MaxID]
AND G.[product_code] = journal.[product_code];

Thanks again.|||The contact table has nothing to do with your delete, except to limit the deleted records to those that have a contact_id. I assume that contact_id is part of journal's natural key and that all records have a valid contact_id, so drop if from both your queries. (If you do need it for filtering, join it in the subquery.)

--delete dupe trans with status_date as the flag
DELETE
FROM journal
INNER JOIN
(select product_code, contact_id, max(status_date) as max_status_date
from journal
group by product_code, contact_id) AS G
ON journal.[product_code] = G.[product_code]
and journal.[contact_id] = G.[contact_id]
and journal.[status_date] < G.[max_status_date]

--delete dupe trans with journal_id as the flag
DELETE journal
FROM journal
INNER JOIN
(select product_code, contact_id, max(journal_id) as maxID
from journal
group by product_code, contact_id) AS G
ON journal.[product_code] = G.[product_code]
and journal.[contact_id] = G.[contact_id]
and journal.[journal_id] < G.[MaxID]

It also appears that the first query should handle all product_code/contact_id duplicates except those with that share exactly the same status_date. If status_date stores only whole-date values, then I guess I see the point of the second delete statement, but otherwise I wouldn't expect you to get a high rowcount from it.

Now to your question; can this be done as a single SQL statement? Yes, but it would essentially require two nested subqueries, so I don't think you would get a big performance boost from it, and you would certainly have to sacrifice code clarity. I recommend that you leave it as two separate deletes.

Do I need a cursor here

I have a web page where users can change information about themselves
and submitted to a database. The database holds these changes in the
table UserChanges. When the user completes the changes the database for
the website is updated and changes are reflected right away. The user
can then go in again and make further changes thus creating another
record in the UserChanges table. Once a day these changes are brought
down to our main database. I would like to bring down only the latest
record for each users instead of bring down all their records they
created that day. The problem is that one of the fields is a bit field
that indicates if the email address was changed or not. This could
cause problems if the user changed their email address on a previous
record they created but not on the last one.
Example
ID PersonID EmailAddress EmailChange Downloaded
1 200 test@.test.com False False
2 200 blank@.blank.com True False
3 200 blank@.blank.com False False
What I want to do is after the user submits the record
1.Check to see if they have any previous record that have not been
downloaded yet
2.If previous records exists check to see if any of the records email
change flag is set to True.
3.If it is, then update the email change flag to True in the last
record.
Is this possible to do with out using a cursor?A trigger should be able to do it:
create trigger tri_UserChanges on UserChanges after insert
as
if @.@.rowcount = 0
return
update u
set
EmailChange = 1
from
UserChanges u
join inserted i on i.ID = u.ID
where exists
(
select
*
from
UserChanges u2
where
u2.PersonID = i.PersonID
and u2.ID <> i.ID
and u2.EmailChange = 1
and u2.Doenloaded = 0
)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"rocky20" <goldbond_8@.hotmail.com> wrote in message
news:1140289185.842651.4140@.o13g2000cwo.googlegroups.com...
I have a web page where users can change information about themselves
and submitted to a database. The database holds these changes in the
table UserChanges. When the user completes the changes the database for
the website is updated and changes are reflected right away. The user
can then go in again and make further changes thus creating another
record in the UserChanges table. Once a day these changes are brought
down to our main database. I would like to bring down only the latest
record for each users instead of bring down all their records they
created that day. The problem is that one of the fields is a bit field
that indicates if the email address was changed or not. This could
cause problems if the user changed their email address on a previous
record they created but not on the last one.
Example
ID PersonID EmailAddress EmailChange Downloaded
1 200 test@.test.com False False
2 200 blank@.blank.com True False
3 200 blank@.blank.com False False
What I want to do is after the user submits the record
1.Check to see if they have any previous record that have not been
downloaded yet
2.If previous records exists check to see if any of the records email
change flag is set to True.
3.If it is, then update the email change flag to True in the last
record.
Is this possible to do with out using a cursor?|||Just as a side note, you may want to reconsider your design. As you
can obviously see, a bit field doesn't really tell you much :) I'm
also assuming that "previous" has meaning to you, because a rows in a
table don't really have an order to them.
Here's a quick-and-dirty stab at it:
DECLARE @.s TABLE (ID int, PersonID int, EmailAddress varchar(20),
EmailChange bit, Downloaded bit)
INSERT INTO @.s
SELECT 1, 200, 'test@.test.com', 0, 0
UNION ALL
SELECT 2, 200, 'blank@.blank.com', 1, 0
UNION ALL
SELECT 3, 200, 'blank@.blank.com', 0, 0
UNION ALL
SELECT 4, 500, 'test@.test.com', 0, 0
UNION ALL
SELECT 5, 500, 'blank@.blank.com', 1, 0
UNION ALL
SELECT 6, 500, 'blank@.blank.com', 0, 1
SELECT *
FROM @.s
SELECT DISTINCT s.ID, s.PersonID, s.EmailAddress,
EmailChange = COALESCE(s2.EmailChange, s.EmailChange),
s.Downloaded
FROM @.s s LEFT JOIN @.s s2 ON s.PersonID = s2.PersonID
AND s.ID > s2.ID
AND s2.EmailChange = 1
AND s2.Downloaded = 0
WHERE s.ID IN (SELECT MAX(ID)
FROM @.s
WHERE Downloaded=0
GROUP BY PersonID)
HTH,
Stu|||rocky20 wrote:
> I have a web page where users can change information about themselves
> and submitted to a database. The database holds these changes in the
> table UserChanges. When the user completes the changes the database for
> the website is updated and changes are reflected right away. The user
> can then go in again and make further changes thus creating another
> record in the UserChanges table. Once a day these changes are brought
> down to our main database. I would like to bring down only the latest
> record for each users instead of bring down all their records they
> created that day. The problem is that one of the fields is a bit field
> that indicates if the email address was changed or not. This could
> cause problems if the user changed their email address on a previous
> record they created but not on the last one.
> Example
> ID PersonID EmailAddress EmailChange Downloaded
> 1 200 test@.test.com False False
> 2 200 blank@.blank.com True False
> 3 200 blank@.blank.com False False
>
> What I want to do is after the user submits the record
> 1.Check to see if they have any previous record that have not been
> downloaded yet
> 2.If previous records exists check to see if any of the records email
> change flag is set to True.
> 3.If it is, then update the email change flag to True in the last
> record.
> Is this possible to do with out using a cursor?
It doesn't seem like you'll need a cursor to do this. I'm not clear
about a few things though. Firstly what is/are the keys in this table?
Secondly how do we know which row is the latest? Don't use an IDENTITY
column to track the latest row. Add a DATETIME column to do that.
Finally, what's the point of the EmailChange column? It looks redundant
to me, given that you preserve the history of the email values anyway.
I think you should drop EmailChange.
Assuming you have a column to indicate the date and time of the change
you can retrieve the latest version like this:
/* Get the new email address only where the
latest version hasn't been downloaded */
SELECT emailaddress
FROM UserChanges AS U
WHERE changed_datetime =
(SELECT MAX(changed_datetime)
FROM UserChanges
WHERE personid = U.personid
HAVING MAX(changed_datetime) =
MAX(CASE WHEN downloaded = 'False' THEN changed_datetime END));
AND downloaded = 'False' /* should be 0? */
(untested)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Doesn't that hit you as "a bit" redundant? (sorry, had to do the pun)
Just over-write the old email with the new one, based on a timestamp
One of your major problems is that you do not know that fields and
records are not part of SQL; columns and rows are different creatures.
When someone logs into the routine. check to see if they have data in
the working tables. If not, copy all the old data over to your working
table. Let the user UPDATE the working data on a column by column
basis in the working database. Or if you really need to keep every
change, then add that timestamp to get the last copy.
Then do your data scrubbing and replace the old data with the new. I
would guess that a VIEW with INSTEAD OF TRIGGERs would help.
No cursors needed.

Do for each - how to write it more graceful?

"Do something for each row of the query"
The only way I know is:

--
declare @.C cursor
set @.C= cursor for
select F from T where ...
declare @.F int
open @.C
while 0=0 begin
fetch next from @.C into @.F
if not(@.@.FETCH_STATUS = 0) break
exec myStoredProc @.F
end
close @.C
deallocate @.C
--

How to write it simpler, maybe with implicit cursors?
For example, in the Borland Interbase it would be like:

--
declare variable F integer;
for select F from T into :F
do execute procedure myStoredProc :F;
--

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!What does myStoredProc actually do? If the procedure just does some data
manipulation then maybe you can rewrite that code based on your cursor
query. For example:

SELECT ...
FROM Something
WHERE f
IN
(select F from T where ...)

As another alternative to a cursor you can try something like this, which
may be reasonably acceptable if the column F is unique and indexed.

DECLARE @.f INTEGER

WHILE EXISTS
(SELECT *
FROM T
WHERE f>@.f
OR @.f IS NULL)
BEGIN
SET @.f =
(SELECT MIN(f)
FROM T
WHERE f>@.f
OR @.f IS NULL)
EXEC myStoredProc @.F
END

--
David Portas
SQL Server MVP
--|||Thank you for these two ideas!
But... first case is not quite fit for me. My stored procedure is too
complicated, it makes some queries to remote server, calls some extended
procedures and so on.
Second case seems better, but (IMHO) it's a trick. And this trick (IMHO)
is too slow relative to case with cursor and fetch. And it looks like
T-SQL syntax limitation - no any common and short way to do something
for each row, like cycle by select :(

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Other options:

1) Build a Dynamic SQL statement in a loop and then execute it.

2) Put the loop in the middle tier or client code.

3) For non-production use you could try the undocumented xp_execresultset.
Example:

EXEC master..xp_execresultset
'SELECT ''EXEC myStoredProc ''+CAST(f AS VARCHAR) FROM T','DBNAME'

--
David Portas
SQL Server MVP
--|||Evgeny Gopengauz (evgop@.ucs.ru) writes:
> "Do something for each row of the query"
> The only way I know is:
> --
> declare @.C cursor
> set @.C= cursor for
> select F from T where ...
> declare @.F int
> open @.C
> while 0=0 begin
> fetch next from @.C into @.F
> if not(@.@.FETCH_STATUS = 0) break
> exec myStoredProc @.F
> end
> close @.C
> deallocate @.C
> --
> How to write it simpler, maybe with implicit cursors?

There are a couple of options, you can use SELECT MIN or SELECT TOP 1.
I would however recommend to stick with the cursors, they are in my
opinion the best way to iterate when you need to iterate. Solutions
with MIN or TOP 1 can have bad performance if there is no good index.

One tip is to make the cursor INSENSITIVE, since keyset-driven cursors
(the default) can sometimes have absymal performance when nailing
down which rows to operate on. Also insensitive saves you from
surprises if you update rows selected by the cursor.

Finally, I never use cursor variables, but always static names.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql