Inserting into large table

Question

Post reply

Inserting into large table

Aspet Golestanian Namagerdi

SSCarpal Tunnel

Points: 4444
More actions
October 14, 2011 at 6:40 pm

#249422

We have a large table, that large number of Insert transactions are hitting it per second.Recently some of this transactions are taking very long(1 sec) and and at the same time some others are 10 ms and this slowness happens randomly.When I ran the SQL Profiler ,I noticed that there is Select statment after Stored Procedure call,which in that Select statment return the Output values of stored Procedure.The SP that I got from SQL profiler has structure like this:
declare @p1 bigint
set @p1=0
declare @p2 datetime
set @p2=NULL
exec MySP @ID=@p1 output,@EntryDateTime=@p2 OUTPUT ,@P3= 12,@P4 = 'test'
SELECT @p1, @p2
Basically in our code(C#), we have a loop that is calling MySP Stored Procedure.But For Some reason "SELECT @p1, @p2" is being called after SP's execution(which I cought from SQL profiler).Is there any way to force SQL SERVER to not add that Select statment?

Viewing 13 posts - 1 through 12 (of 12 total)

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

SpringTownDBA SSCertifiable Points: 6917 More actions · Answer 1

Sql server isn't adding that select, your c# code is.

What kinds of waits are you seeing?

http://www.simple-talk.com/sql/performance/investigating-sql-server-2008-wait-events-with-xevents/

Aspet Golestanian Namagerdi SSCarpal Tunnel Points: 4444 More actions · Answer 2

Thanks for your reply,what should I set in code to pypass that select statment.Because without that select statment ,it's running very fast.

Lowell SSC Guru Points: 323518 More actions · Answer 3

your procedure has two output parameters.

if you don't need the values returned, you could change the code like this:

declare @p1 bigint

set @p1=0

declare @p2 datetime

set @p2=NULL

exec MySP @ID=@p1 output,@EntryDateTime=@p2 OUTPUT ,@P3= 12,@P4 = 'test'

SELECT @p1, @p2

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Aspet Golestanian Namagerdi SSCarpal Tunnel Points: 4444 More actions · Answer 4

Actually I need the output.

If I remove the select statment runs very fast.Is there anyway to remove that select statment.this code has been generated from Stored Procedure call in C#

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

Aspet Golestanian Namagerdi (10/15/2011)
Actually I need the output.
If I remove the select statment runs very fast.Is there anyway to remove that select statment.this code has been generated from Stored Procedure call in C#

I think the select statement is a red herring - it's cost is likely to be insignificant in comparison to the stored procedure.

How can you say it runs very fast when you remove it - then ask how to remove it?

Can you post the actual plan for the stored procedure?

[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]

Aspet Golestanian Namagerdi SSCarpal Tunnel Points: 4444 More actions · Answer 6

I created a loop like (This is how it runs in productions,in a loop the SP is being called):

DECLARE @Temp AS INTEGER

SET @Temp = 1

WHILE @Temp <1000

Begin

declare @p1 bigint

set @p1=0

declare @p2 datetime

set @p2=NULL

exec MySP @ID=@p1 output,@EntryDateTime=@p2 OUTPUT ,@P3= 12,@P4 = 'test'

SELECT @p1, @p2

Set @Temp = @Temp +1

End

I noticed when the select statment exists,the whole batch takes about 30 sec to run,but when I commented the select statment the batch runs in 0 sec.Also I kept the Select statment and ran the SQLProfiler and I noticed that randomly a delay(3-500 milisseconds) is being created between sql statments(i mean running each individual statment is fast(each one runs around 0 milisecond),how can I check what the reason of that delay or wait ?Do we have any feature in SQL profiler?

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

Aspet Golestanian Namagerdi (10/15/2011)
I created a loop like (This is how it runs in productions,in a loop the SP is being called):
DECLARE @Temp AS INTEGER
SET @Temp = 1
WHILE @Temp <1000
Begin
declare @p1 bigint
set @p1=0
declare @p2 datetime
set @p2=NULL
exec MySP @ID=@p1 output,@EntryDateTime=@p2 OUTPUT ,@P3= 12,@P4 = 'test'
SELECT @p1, @p2
Set @Temp = @Temp +1
End
I noticed when the select statment exists,the whole batch takes about 30 sec to run,but when I commented the select statment the batch runs in 0 sec.Also I kept the Select statment and ran the SQLProfiler and I noticed that randomly a delay(3-500 milisseconds) is being created between sql statments(i mean running each individual statment is fast(each one runs around 0 milisecond),how can I check what the reason of that delay or wait ?Do we have any feature in SQL profiler?

The reason why it's slow is because you have a While Loop... plain and simple. Depending on the indexes in the table and if "MySP" does an INSERT, sometimes you'll create a page split (clustered index) or extent split (non clustered index) not to mention the creation of an execution plan for every iteration.

Lose the loop... lose the problem.

--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)

Aspet Golestanian Namagerdi SSCarpal Tunnel Points: 4444 More actions · Answer 8

But it does not seems the problem is related to the l0op,When I remove the Select statment it workes pretty fast.Also when I used the xEvents here is the result:

wtype wcounttotal_timesignal_time

NETWORK_IO 18 14193 0

PAGEIOLATCH_EX 3 0 0

PAGEIOLATCH_SH 8 50 0

SOS_SCHEDULER_YIELD 1 0 0

WRITELOG 998 738 0

Aspet Golestanian Namagerdi SSCarpal Tunnel Points: 4444 More actions · Answer 9

wtype wcounttotal_timesignal_time

NETWORK_IO 18 14193 0

PAGEIOLATCH_EX 3 0 0

PAGEIOLATCH_SH 8 50 0

SOS_SCHEDULER_YIELD 1 0 0

WRITELOG 998 738 0

SpringTownDBA SSCertifiable Points: 6917 More actions · Answer 10

Aspet Golestanian Namagerdi (10/15/2011)
wtype wcounttotal_timesignal_time
NETWORK_IO 18 14193 0
PAGEIOLATCH_EX 3 0 0
PAGEIOLATCH_SH 8 50 0
SOS_SCHEDULER_YIELD 1 0 0
WRITELOG 998 738 0

networkio typically means that the client can't keep up with the server.

did you capture these against a real spid doing work or from ssms and your test code above?

Thanks,

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 11

Aspet Golestanian Namagerdi (10/15/2011)
But it does not seems the problem is related to the l0op,When I remove the Select statment it workes pretty fast.Also when I used the xEvents here is the result:
wtype wcounttotal_timesignal_time
NETWORK_IO 18 14193 0
PAGEIOLATCH_EX 3 0 0
PAGEIOLATCH_SH 8 50 0
SOS_SCHEDULER_YIELD 1 0 0
WRITELOG 998 738 0

That's because you have two different problems and my suggestion was to remove the loop so that the query might run faster than "pretty fast". It would also allow the "SELECT" statment you're so concerned with to execute only once which would make it faster, as well.

Now would be a good time to tell us what the stored procedure you're calling actually does...

--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)

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 12

Jeff Moden (10/15/2011)
Aspet Golestanian Namagerdi (10/15/2011)
But it does not seems the problem is related to the l0op,When I remove the Select statment it workes pretty fast.Also when I used the xEvents here is the result:
wtype wcounttotal_timesignal_time
NETWORK_IO 18 14193 0
PAGEIOLATCH_EX 3 0 0
PAGEIOLATCH_SH 8 50 0
SOS_SCHEDULER_YIELD 1 0 0
WRITELOG 998 738 0
That's because you have two different problems and my suggestion was to remove the loop so that the query might run faster than "pretty fast". It would also allow the "SELECT" statment you're so concerned with to execute only once which would make it faster, as well.
Now would be a good time to tell us what the stored procedure you're calling actually does...

It'd also be useful to see the underlying ddl for tables, views etc.