please help me kill my cursor!
-
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
-
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 !!!** -
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
-
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
-
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply