two insert statments takes long time to process

Question

Post reply

two insert statments takes long time to process

dlam 18073

Mr or Mrs. 500

Points: 548
More actions
November 16, 2010 at 6:37 pm

#231707

Hi everyone,
I got a stored procedures with two insert statment in it.
it takes more than 10 sec to finish.
with the same amount of row to insert,
if I run this two insert separately (in two stored procedures) it just take 2 to 3 sec for each.
does anyone have an idea?
thx!!

Viewing 15 posts - 1 through 14 (of 14 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Not without seeing the stored procedures, the tables affected, and data you are trying to insert.

There may be more that is needed, but that would be a good start.

You have to remember, we can't see what you see from here.

dlam 18073 Mr or Mrs. 500 Points: 548 More actions · Answer 2

below is my code, i dont know why two insert togeher in the sp will take much longer time to finish the process

CREATE procedure [usp_addRecords] (

@id as int,

@clientNumber as int

AS

INSERT INTO tblOrder(

clientNumber)

VALUES(

@clientNumber);

insert into tblClientList (clientNumber)

select clientNumber

from tblOrder

where not exists

(select *

from tblClientList

where tblOrder.clientNumber=tblClientList.clientNumber)

group by clientNumber

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 3

Seems you have overcomplicated things here.You are inserting into tblClientList after selecting from tblOrder and then checking in tblClientList whether it exists or not.

Wont just having an unique key on clientNumber in tblClientList suffice the requirement?

Correct me if I am wrong.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

dlam 18073 Mr or Mrs. 500 Points: 548 More actions · Answer 4

I dont get it. clientNumber in tblClientList is primary key.

can you tell me abit more in detail. thx!!

tommyh SSCertifiable Points: 6302 More actions · Answer 5

Well the

insert into tblClientList (clientNumber)

select clientNumber

from tblOrder

where not exists

(select *

from tblClientList

where tblOrder.clientNumber=tblClientList.clientNumber)

group by clientNumber

part is slightly extreme. It will look on all rows in the tblOrder and make sure that a that clientNumber exists in the tblClientList. Not just the row that was added. So slightly overkill.

CREATE procedure [usp_addRecords] (@id as int, @clientNumber as int)

AS

INSERT INTO tblOrder(clientNumber) VALUES(@clientNumber)

if not exists (select * from tblClientList where clientNumber = @clientNumber)

insert into tblClientList (clientNumber) values (@clientNumber)

Would be better i think because it will just check if the clientNumber that you just added exists or not. And if not add it to the tblClientList.

If you can a foreign key sounds like a plan. You would then have to change the procedure a bit though

CREATE procedure [usp_addRecords] (@id as int, @clientNumber as int)

AS

if not exists (select * from tblClientList where clientNumber = @clientNumber)

insert into tblClientList (clientNumber) values (@clientNumber)

INSERT INTO tblOrder(clientNumber) VALUES(@clientNumber)

Have to insert into the tblClientList first or else the FK will kick in 🙂

Oh and i do hope you have a unique index/primary key on the clientNumber in the tblClientList... could make it slow otherwise. (and also to prevent duplicates)

/T

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 6

dlam 18073 (11/16/2010)
I dont get it. clientNumber in tblClientList is primary key.
can you tell me abit more in detail. thx!!

Then why are you checking whether the clientNumber exists?If there is a duplicate clientNumber being inserted an error message indicating Violation of primary key constraint will be thrown.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

dlam 18073 Mr or Mrs. 500 Points: 548 More actions · Answer 7

thx ppls to give me help!

I did a test on two different sp (test with 3400 rows),

one is my sp, processing time 46 sec

CREATE procedure [usp_addRecords] (

@id as int, @clientNumber as int)

AS

INSERT INTO tblOrder(clientNumber) VALUES(@clientNumber);

insert into tblClientList (clientNumber)

select clientNumber

from tblOrder

where not exists

(select * from tblClientList

where tblOrder.clientNumber=tblClientList.clientNumber)

group by clientNumber

another one from tommyh, processing time 31sec

CREATE procedure [usp_addRecords] (@id as int, @clientNumber as int)

AS

INSERT INTO tblOrder(clientNumber) VALUES(@clientNumber)

if not exists (select * from tblClientList where clientNumber = @clientNumber)

insert into tblClientList (clientNumber) values (@clientNumber)

tommyh's one spent less time then my one, also I did another test, use same number of rows. I split my sp in two

first one processing time 17sec

CREATE procedure [usp_addRecords] (

@id as int, @clientNumber as int

AS

INSERT INTO tblOrder(clientNumber)

VALUES(@clientNumber);

second one , processing time 0.7sec

CREATE procedure [usp_addclientNumbers]

as

insert into tblClientList (clientNumber)

select clientNumber

from tblOrder

where not exists

(select * from tblClientList

where tblOrder.clientNumber=tblClientList.clientNumber)

group by clientNumber

question:why this two insert processes split into two separate procedures spent less time, compare to group these into one procedure?

How does store procedure work?

Does it insert all the value to tblOrder, then check any new client to insert into tblClientList?

hope my qusetion doesnt confus any ppl!

tommyh SSCertifiable Points: 6302 More actions · Answer 8

Im going to guess that the test when you broke them up into 2 SPs isnt entirerly accurate.

Im guessing you made 3400 usp_addRecords followed by 3400 usp_addclientNumbers. Which isnt how the original SP works. The HUGE difference is the nr of inserts.

The original SP inserts a row into tblOrder checks tblClientList to see if the ClientNr exists there and if not adds it. So in worst case your going to get 2 inserts per execution. So being negative and all im going to go with that. So your going to get 3400 + 3400 inserts (and 3400 selects... the subquery).

Now if you run 3400 usp_addRecords followed by 3400 usp_addclientNumbers your going to get 3400 + 1 inserts (and 3400 selects). So why only +1 insert you ask? Well since you have a subquery your going to get a single insert with 3400 rows. But 1 insert with many rows is WAY WAY faster then many inserts with 1 row. The following 3399 executions arent going to insert anything... already done with the first execution.

As you tested yourself 3400 inserts cost 17s. Simple math will tell us that 3400+3400 inserts will give 17+17 so 34s (close to the 31 your test got). The second one has 3400 so im going with 17s here as well. The 3400 selects? As you have shown yourself its less then 1s so hardly worth mentioning.

So this really has nothing to do with how a SP work or not. Its simply about what your asking SQL to do for you.

/T

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 9

Sachin Nandanwar (11/17/2010)
dlam 18073 (11/16/2010)
I dont get it. clientNumber in tblClientList is primary key.
can you tell me abit more in detail. thx!!
Then why are you checking whether the clientNumber exists?If there is a duplicate clientNumber being inserted an error message indicating Violation of primary key constraint will be thrown.

Gosh... I just wouldn't do it that way, Sachin. It reminds me of spaghetti code... throw it against the wall and see if it sticks. If it causes a ROLLBACK, it get's really expensive.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dlam 18073 Mr or Mrs. 500 Points: 548 More actions · Answer 10

Thx for ur detail explain tommyh!

something that's stil not clear from my mind.

I know my second insert is not effective, and I understand your code(only check the @clientNumber, not all of the row from the tblClientList)

base on my last post,

the testing time:

46s <> 17s + 0.7s

that is a big difference.

I stil dont get it why there is the difference.

ngreene SSC-Addicted Points: 461 More actions · Answer 11

Always try to use LEFT JOIN (or RIGHT) instead of NOT IN or NOT EXIST. LEFT JOIN is going to have faster performance.

insert into tblClientList (clientNumber)

SELECT a.clientNumber

from tblOrder a

LEFT JOIN tblClientList b on a.clientNumber = b.ClientNumber

WHERE b.clientNumber IS NULL

LutzM SSC Guru Points: 107049 More actions · Answer 12

Edit: never mind. Misread the question

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 13

ngreene (12/3/2010)
Always try to use LEFT JOIN (or RIGHT) instead of NOT IN or NOT EXIST. LEFT JOIN is going to have faster performance.
insert into tblClientList (clientNumber)
SELECT a.clientNumber
from tblOrder a
LEFT JOIN tblClientList b on a.clientNumber = b.ClientNumber
WHERE b.clientNumber IS NULL

This was the case with SS2K, don't know about SS2K5, but certainly not so with SS2K8 where the performance of EXISTS / NOT EXISTS / IN / NOT IN is often similar or the same as the equivalent JOIN - because a JOIN may be used in the actual plan.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 14

dlam 18073 (11/21/2010)
thx ppls to give me help!
I did a test on two different sp (test with 3400 rows),
one is my sp, processing time 46 sec
another one from tommyh, processing time 31sec
tommyh's one spent less time then my one, also I did another test, use same number of rows. I split my sp in two
first one processing time 17sec
second one , processing time 0.7sec
question:why this two insert processes split into two separate procedures spent less time, compare to group these into one procedure?
How does store procedure work?
Does it insert all the value to tblOrder, then check any new client to insert into tblClientList?
hope my qusetion doesnt confus any ppl!

I didn't know SS2K8 would run on a 64k Amstrad.

Seriously, those run times should be milliseconds, not seconds. What indexes have you got on those tables? How many rows do they contain? Can you post the DDL?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]