please help me kill my cursor!

Question

please help me kill my cursor!

Colin MacKenzie

SSC-Addicted

Points: 480
More actions
July 27, 2005 at 7:07 pm

#68234

Hey all

I have 3 tables, I need to marry data from 2 of them into the third. SHOULD be fairly easy.

table 1: Users (EmailAddress, FirstName, CreateDate, [other columns])
table 2: CouponCodes (CouponCode, Assigned)
table 3: NewUsers (EmailAddress, FirstName, CouponCode)

I need to get all new users from the Users table, based on the CreateDate, into the NewUsers table. Each user in the NewUsers table also has to be assigned a CouponCode. Each code can only be assigned once. I currently have a proc written that'll do the work, but it uses a cursor... when you're dealing with 2+ million users, cursor's bite.

Anyone know how I can insert new users into the NewUsers table, and assign a distinct coupon code at the same time? Or am I stuck with a cursor?

Thanks a ton!
Cheers

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

how do you assign the coupon codes now?! do you just look for any that are "not assigned" and assign one to a new user ?!

how does your CouponCodes table get its' couponcodes ?! Is it some kind of a lookup table ?! - no - it can't be with 2+ million users & each code being assigned once!!! (just talking to myself here...) - does this table have anything else besides these 2 fields ?! How is the code generated ?

could you pl. provide some more details ?!

**ASCII stupid question, get a stupid ANSI !!!**

Leo Leong SSCommitted Points: 1615 More actions · Answer 2

i got a simple suggestion here.

first, select all new user into a identity temp table. for example,

create table user_temp(

key bigint identity(1, 1),

firstName varchar(50),

emailAddress varchar(100)

)

insert into user_temp

select firstName, emailAddress from users where createDate >= 'your date'

second, select all unasssigned coupon into another identity temp table.

create table coupon_temp(

key bigint identity(1, 1),

couponCode varchar(50)

)

insert into coupon_temp

select couponCode from CouponCodes where Assigned = 'N'

finally, you simply use a join to link these 2 tables based on the key like this:

select a.emailAddress, a.firstName, b.couponCode

from user_temp a left join coupon_temp b

on a.key = b.key

this may run faster than your cursor as long as you have indexed your createDate and Assigned columns in the tables.

This is just a suggestion. it still depends on what kind of data you are manipulating. May be there is a faster logic to get the result specifically for you.

Leo

dilip-196624 Ten Centuries Points: 1220 More actions · Answer 3

U can simply create a stored proc and pass parameter as createdate and then use insert statement to insert values into newusertable..

For coupons, as said u can use identity() function to generate a new id for each record added in new user table..it depends it can also be max(couponid) + 1 etc..

If u need more help with sql, key in more details with some data..

dilip

Colin MacKenzie SSC-Addicted Points: 480 More actions · Answer 4

just your suggestions...

30 second run is definately better than a 8 - 9 minute run

thanks for the input... now time to play with indexes and get it under 10 seconds!

Cheers