October 31, 2003 at 6:09 am
My requirement is to insert no. of records (approx 32000) in a table (size around 32 Lakh at present).
Before inserting I have to search if that record is already present in table or not (This means each record is to be scanned for in a table already having 32 lakh records). I have to modify one field of incoming record depending on whether the record is already present or not. Once I have modified I will go ahead and insert the record.
I am taking all the new records to be inserted in a Cursor. I am iterating through the cursor and with each iteration I determine if it is already present or not, update the field and insert it in the table. I am giving the code as below:
It would be a great help if you can suggest any way of improving the performance in terms of rewriting the queries or tuning the server parameters or creating specific indexes.
For Statistics it took 43 minutes to insert 2157 records :
Code
********************************************************************************************************************************************************************************
Declare ISM_NEW_RECORDS cursor for select clientid from delclientids
open ISM_NEW_RECORDS
Fetch next from ISM_NEW_RECORDS into @custname
while(@@Fetch_status <> -1)
Begin
/*For the current customer number (first 9 digits of clientid field) check if the entry is already present in PartyWork Table */
select @count = (select count(*) from partywork where substring(clientid,1,9) = substring(@custname,1,9))
-- print 'count-->' + convert(char(2), @count)
/* IF the entry is not present, it suggest this is the new customer for CDDB. In this case a new PartyRef ( first 8 digit of partyidref field) is to be generated */
IF (@count = 0)
BEGIN
-- print 'inside first if' ;
/* @Partyref variable will hold the maximum of the partyref ( first 8 digits of partyidref field), incrementing it by 1 will generate the new partyref */
Select @partyref = ( select max(substring(partyidref, 1,8)) from partywork);
-- print @partyref
/* incremeting the @partyref field as suggested above */
Select @partyref = @partyref + 1
/* The current record in cursor is a new customer, a partyref has been generated now the record is to be inserted in partywork table */
insert partywork
select
@custname,
'CIS',
'LV',
Partyidref = (case when len(@partyref) = 1 then '0000000' + @partyref + '00000100'
when len(@partyref) = 2 then '000000' + @partyref+ '00000100'
when len(@partyref) = 3 then '00000' + @partyref + '00000100'
when len(@partyref) = 4 then '0000' + @partyref + '00000100'
when len(@partyref) = 5 then '000' + @partyref + '00000100'
when len(@partyref) = 6 then '00' + @partyref + '00000100'
when len(@partyref) = 7 then '0' + @partyref + '00000100'
when len(@partyref) = 8 then @partyref + '00000100'
end)
END
/* If the entry is present. It suggests that the customer is not new for CDDB and the record which has come from CIS is probably owner/child/single
** account belonging to the existing customer. In such case partyref ( first 8 digits of partyidref field) is not to be generated afresh.
** Only the sequence no. (the next 6 digits) have to be incremented. This can be done by taking the first 14 digits of partyidref field and incrementing
** Only if the customer has more than 999999 different accounts with CIS will this logic fail (which is highly unlikely). So incremeting the first 14 digits
** seems to be a safe bet.
*/
--IF (@count > 0)
else
BEGIN
-- print 'inside second if' ;
/* The @NextSeq variable will hold the maximum value of partyref + sequence (i.e. first 14 digits of partyidref field).
** This variable will be incremented to generate the new partyidref value for the current record in cursor.
*/
Select @NextSeq = (select max(substring(partyidref,1,14)) from partywork where substring(clientid,1,9) = substring(@custname,1,9))
-- print @nextseq
/* Incrementing the @nextseq variable as suggested above */
select @nextseq = @nextseq + 1;
-- print @nextseq
/* Now the details of this new record will be inserted in PartyWork table along with newly generated Partyidref field */
insert partywork
select
@custname,
'CIS',
'LV',
Partyidref = (case when len(@NextSeq ) = 1 then '0000000000000' + @NextSeq + '00'
when len(@NextSeq ) = 2 then '000000000000' + @NextSeq + '00'
when len(@NextSeq ) = 3 then '00000000000' + @NextSeq + '00'
when len(@NextSeq ) = 4 then '0000000000' + @NextSeq + '00'
when len(@NextSeq ) = 5 then '000000000' + @NextSeq + '00'
when len(@NextSeq ) = 6 then '00000000' + @NextSeq + '00'
when len(@NextSeq ) = 7 then '0000000' + @NextSeq + '00'
when len(@NextSeq ) = 8 then '000000' + @NextSeq + '00'
when len(@NextSeq ) = 9 then '00000' + @NextSeq + '00'
when len(@NextSeq ) = 10 then '0000' + @NextSeq + '00'
when len(@NextSeq ) = 11 then '000' + @NextSeq + '00'
when len(@NextSeq ) = 12 then '00' + @NextSeq + '00'
when len(@NextSeq ) = 13 then '0' + @NextSeq + '00'
when len(@NextSeq ) = 14 then @NextSeq + '00'
end)
END
Fetch next from ISM_NEW_RECORDS into @custname
END
/* Finally dealloacting the cursor */
Close ISM_NEW_RECORDS
DEALLOCATE ISM_NEW_RECORDS
**************************************************************************************************************************
Thanks & Regards,
Gp
November 3, 2003 at 8:00 am
This was removed by the editor as SPAM
November 5, 2003 at 4:16 pm
It seems to be be quite complicated how you are storing your data.
Never the less here are some ideas which could help you to speed up a bit your query?
1:)
quote:
select @count = (select count(*) from partywork where substring(clientid,1,9) = substring(@custname,1,9))
won't use any index even you have one defined on clientid.
Better would be a query like that:
if exists (select 1 from partywork where clientid like substring(@custname,1,9) + '%'
begin
...
2:)
quote:
Select @partyref = ( select max(substring(partyidref, 1,8)) from partywork);
Here again you are paying a nice table scan.
quote:
Select @partyref = @partyref + 1
what about the concurrency?
How can you be shure that nobody will run the same query and nobody will affect the same code?
I didn't see any begin tran and commit tran
Better would be an a separate identity column and eventually a calculated column to handle your specific partyidref format
3:)
quote:
Partyidref = (case when len(@partyref) = 1 then '0000000' + @partyref + '00000100'when len(@partyref) = 2 then '000000' + @partyref+ '00000100'
when len(@partyref) = 3 then '00000' + @partyref + '00000100'
when len(@partyref) = 4 then '0000' + @partyref + '00000100'
when len(@partyref) = 5 then '000' + @partyref + '00000100'
when len(@partyref) = 6 then '00' + @partyref + '00000100'
when len(@partyref) = 7 then '0' + @partyref + '00000100'
when len(@partyref) = 8 then @partyref + '00000100'
end)
why making life so complicated?
a better and nicer solution would be:
Partyidref = right('0000000' + @partyref + '00000100', 16),
...
This is a universal approach
Same remarks for the ELSE part of your query of cours you have to change the value in the right function.
All this should speed up your inserts by at least a magnitude
Never the less you should also check if you can avoid the cursor and do your inserts set based
Bye
Gabor
Bye
Gabor
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply