Issue with inserting record in db table.

[Question123]

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

[deepuv04]

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:

 
 
DECLARE @Table TABLE
( ID INT IDENTITY(1,1),
  Date datetime
)
 
Declare @startdate datetime 
Declare @enddate datetime
 
 
Set @startdate = '2008-06-01' 
Set @enddate = '2008-06-16' 
 
INSERT INTO @Table(Date)
Select dateadd(dd,number,@startdate) 
from master.dbo.spt_values 
where master.dbo.spt_values.type='p' 
      AND dateadd(dd,number,@startdate)<=(@enddate)
 
-- Update all existing records
UPDATE Table1 WITH (ROWLOCK)
SET Val=@Val
FROM Table1 INNER JOIN
      @Table as T on Table1.BeginningDate = T.Date
 
-- Remove all existing records
DELETE FROM @Table 
FROM @Table as T INNER JOIN
     Table1 ON Table1.BeginningDate = T.Date
 
-- Insert new records
INSERT INTO Table1(Val,BeginningDate,EndDate)
SELECT @Val,Date,Date
FROM @Table as T
 
 

Hope this will help you.

Thanks

[Jerry Winston]

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.

CREATE TRIGGER InsteadTrigger on TABLE1
INSTEAD OF INSERT
AS
BEGIN
DECLARE @BD  Varchar
DECLARE @Val Varchar
SET @BD = inserted.BeginningDate
SET @Val = inserted.Val
  if((SELECT Count(ID) FROM TABLE1 WHERE TABLE1.BeginningDate = @BD) > 0)
      BEGIN
          UPDATE Table1 WITH (ROWLOCK)
          SET
          Val=@Val
          WHERE
          BeginningDate = @BD
      END
  ELSE
          BEGIN
              INSERT INTO TABLE1
              (
              Val,
              BeginningDate,
              EndDate
              )
              VALUES
              (
              @Val,
              @BD,
              @BD
              )
          END
END
GO