By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 1,541 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

Issue with inserting record in db table.

P: 41
Hi
i have one database table Table1.which contains almost 20000000 recordes.
record to this table are inserted through storedprocedure. storedprocedure takes parameter as
"value",
Beginningdate,
Endate .

which will insert one record for each day between Beginning date and EndDate.
Before inserting record i check is record exsist for date,if exsist i will update value otherwise insert new record.

so in there is 3000 days diff between Beginning date And EndDate its taking so much time so plz help me.

Here is code of sp

BEGIN

WHILE @BeginningDate <= @EndDate

BEGIN --- Start While LOOP

IF NOT EXISTS
(
SELECT * FROM Table1 WITH (NOLOCK)
WHERE BeginningDate = @BeginningDate

)

BEGIN --- Start Insert Sql Querry

INSERT INTO Table1
(
Val,
BeginningDate,
EndDate

)
Values
(
@Val,
@BeginningDate,
@BeginningDate

)

END --- Endof Insert Sql Querry

ELSE

BEGIN --- Start Update Sql Querry

UPDATE Table1 WITH (ROWLOCK)

SET

Val=@Val

WHERE

BeginningDate = @BeginningDate

END --- Endof Update Sql Querry

SET @BeginningDate = @BeginningDate + 1;

END --- Endof While LOOP
END
Jun 16 '08 #1
Share this Question
Share on Google+
2 Replies


deepuv04
Expert 100+
P: 227
Hi,
what i think is instead of looping,
1. take all the dates between beginning date and end date into temporary table.
2. Update the Table1 with value where the dates in Table1 and Temporary table
matches.
3. Delete the common dates from Temporary table.( Now you have new dates in
the temporary table)
4. Insert all the new records into Table1.

and the code is like:

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. DECLARE @Table TABLE
  4. ( ID INT IDENTITY(1,1),
  5.   Date datetime
  6. )
  7.  
  8. Declare @startdate datetime 
  9. Declare @enddate datetime
  10.  
  11.  
  12. Set @startdate = '2008-06-01' 
  13. Set @enddate = '2008-06-16' 
  14.  
  15. INSERT INTO @Table(Date)
  16. Select dateadd(dd,number,@startdate) 
  17. from master.dbo.spt_values 
  18. where master.dbo.spt_values.type='p' 
  19.       AND dateadd(dd,number,@startdate)<=(@enddate)
  20.  
  21. -- Update all existing records
  22. UPDATE Table1 WITH (ROWLOCK)
  23. SET Val=@Val
  24. FROM Table1 INNER JOIN
  25.       @Table as T on Table1.BeginningDate = T.Date
  26.  
  27. -- Remove all existing records
  28. DELETE FROM @Table 
  29. FROM @Table as T INNER JOIN
  30.      Table1 ON Table1.BeginningDate = T.Date
  31.  
  32. -- Insert new records
  33. INSERT INTO Table1(Val,BeginningDate,EndDate)
  34. SELECT @Val,Date,Date
  35. FROM @Table as T
  36.  
  37.  
Hope this will help you.

Thanks
Jun 16 '08 #2

Expert 100+
P: 145
Edit:^^^^ I like deepuv04's solution.

hmmm. sounds like a very interesting problem. A trigger might help displace some off load some of the comparisons you perform in your stored procedure. However, i cannot be sure what kind of performance benefits you will get considering by design you wish to compare all date values in a given BeginDate EndDate range.

if storedprocedure is the only way you use to add data to Table1. you could write an INSTEAD OF trigger for INSERT on Table1 that handles the decision making of whether to insert or update a row. that way your original stored procedure will only be responsible for INSERTS to TABLE1.


Expand|Select|Wrap|Line Numbers
  1. CREATE TRIGGER InsteadTrigger on TABLE1
  2. INSTEAD OF INSERT
  3. AS
  4. BEGIN
  5. DECLARE @BD  Varchar
  6. DECLARE @Val Varchar
  7. SET @BD = inserted.BeginningDate
  8. SET @Val = inserted.Val
  9.   if((SELECT Count(ID) FROM TABLE1 WHERE TABLE1.BeginningDate = @BD) > 0)
  10.       BEGIN
  11.           UPDATE Table1 WITH (ROWLOCK)
  12.           SET
  13.           Val=@Val
  14.           WHERE
  15.           BeginningDate = @BD
  16.       END
  17.   ELSE
  18.           BEGIN
  19.               INSERT INTO TABLE1
  20.               (
  21.               Val,
  22.               BeginningDate,
  23.               EndDate
  24.               )
  25.               VALUES
  26.               (
  27.               @Val,
  28.               @BD,
  29.               @BD
  30.               )
  31.           END
  32. END
  33. GO
Jun 16 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.