Query Performance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query Performance

Author

Topic

skc18
Starting Member

9 Posts

Posted - 2013-02-19 : 16:14:59

We have a stord proc that executes and creates records using following logic.
Below is the current set-up and is used extensively.

-------------------------------
Declare @TID Int,
@TypID Int,
@Seq int,
@Name varchar(100),
@Count Int,
@Total int

Declare @Table Table (TID Int Primary, TypID Int, Seq Int, [Name] Varchar(100))
Insert into @Table VALUES (200, 2, 1, 'A')
Insert into @Table VALUES (201, 2, 2, 'B')
Insert into @Table VALUES (202, 2, 4, 'C')
Insert into @Table VALUES (203, 2, 5, 'D')
Insert into @Table VALUES (204, 2, 6, 'E')
Insert into @Table VALUES (205, 2, 7, 'F')
Insert into @Table VALUES (206, 2, 9, 'G')
Insert into @Table VALUES (207, 2, 10, 'H')
Insert into @Table VALUES (208, 2, 11, 'I')
Insert into @Table VALUES (209, 2, 12, 'J')
Insert into @Table VALUES (210, 2, 13, 'K')
Insert into @Table VALUES (211, 2, 14, 'L')

--SELECT * FROM @tABLE

Create taBLE #Temp_Table (RecID Int Identity, TID Int, Type Int, Seq Int, Name Varchar(100))
Insert Into #Temp_Table
Select TID, TypId, Seq, [Name] from @Table
Order by TypID, Seq

--Select * from #temp_Table

Select @Total=count(*) from #temp_Table
Set @Count = 1
While @Count<=@Total
Begin
Select @TID=TID,
@TypID=TypID,
@Seq=Seq,
@Name=[Name],
From #temp_Table
Where RecID=@RecCounter

Execute StoredProc @TID, @TypID, @Seq, @Name

Set @Count=@Count+1
End
-----------------------------

I am trying to re-write this process in following way so that Temp Table does not need to be created.

But from a performance stand-point, when we're to execute multiple jobs with millions of records every day--which process is better?

Select @Total=count(*) from @Table Where TypID=2
Set @Count = 1
While @Count<=@Total
Begin
Select @TID=TID,
@TypID=TypID,
@Seq=Seq,
@Name=[Name],
From
(Select Row_Number() Over (Partition By TypID
Order by Seq) as RecID, TID, TypID, Seq,[Name]
from @Table
Where TypID=2) Tbl
Where RecID=@RecCounter

Execute StoredProc @TID, @TypID, @Seq, @Name

Set @Count=@Count+1
End

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-20 : 00:57:46

Why you need this much code?
Create taBLE #Temp_Table (RecID Int Identity, TID Int, Type Int, Seq Int, Name Varchar(100))
Insert Into #Temp_Table
Select TID, TypId, Seq, [Name] from @Table
Order by TypID, Seq


Select row_number() OVER(ORDER BY TypID, Seq) RecID, TID, TypId, Seq, Name
INTO #tempTable 
from @Table
Order by TypID, Seq

--
Chandu

skc18
Starting Member

9 Posts

Posted - 2013-02-20 : 11:25:17

Bandi, My real question is the impact of creating temp table inside stored proc and how it affects the performance.

on my post, what i was refering to was the data is inserted into temp table from a table and then when loop operation is performed, recID is generated from the temp table.

So my suggestion was to remove temp table from the actual stored proc and generate recID through row_number() inside while loop.

Although I've seen slight improvement in performance of a query when removing temp table, I am trying to find if the change that i am suggesting is worth a try and need an expert opinion so that i can push for a change.

TY

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-20 : 11:32:44

Impact of creating a temp table or getting the parameters from the stored procedure directly without using the temp table may be minimal. If the stored procedure "StoredProc" is anything more than a trivial procedure, that may be where the resources are being consumed. You can test that theory by commenting out the line "Execute StoredProc @TID, @TypID, @Seq, @Name" and running the query to see if that runs much faster.

skc18
Starting Member

9 Posts

Posted - 2013-02-20 : 12:29:42

James,
Included client statistics and executed query by commenting out ''execute storedproc''
Trial 2 Trial 1
Client Execution Time 11:26:48 11:26:38
Query Profile Statistics
Number of INSERT, DELETE and UPDATE statements 12 26 19.0000
Rows affected by INSERT, DELETE, or UPDATE statements 12 48 30.0000
Number of SELECT statements 26 52 39.0000
Rows returned by SELECT statements 26 52 39.0000
Number of transactions 12 26 19.0000
Network Statistics
Number of server roundtrips 1 3 2.0000
TDS packets sent from client 1 3 2.0000
TDS packets received from server 1 3 2.0000
Bytes sent from client 2252 6658 4455.0000
Bytes received from server 671 1415 1043.0000
Time Statistics
Client processing time 0 16 8.0000
Total execution time 234 578 406.0000
Wait time on server replies 234 562 398.0000

skc18
Starting Member

9 Posts

Posted - 2013-02-20 : 12:38:18

James, could not elaborate on previous comment-- accidentally hit sumbit button.

For Trial 1 used temp table and for trial 2 removed temp table.

Results show that removing temp table works good-- but what would you suggest? would you leave the code as is or modify it?

TY

Client Execution Time 11:34:51 11:34:36
Query Profile Statistics
Number of INSERT, DELETE and UPDATE statements 12 12 12.0000
Rows affected by INSERT, DELETE, or UPDATE statements 12 12 12.0000
Number of SELECT 26 0 13.0000
Rows returned by SELECT statements 26 0 13.0000
Number of transactions 12 12 12.0000
Network Statistics
Number of server roundtrips 1 1 1.0000
TDS packets sent from client 1 1 1.0000
TDS packets received from server 1 1 1.0000
Bytes sent from client 2252 3284 2768.0000
Bytes received from server 671 354 512.5000
Time Statistics
Client processing time 16 31 23.5000
Total execution time 234 296 265.0000
Wait time on server replies 218 265 241.5000

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-20 : 12:52:57

So what it seems like is that invoking the stored procedure for each row in the table is what is causing the slowdown. The only way I can think of optimizing that is to open up the stored procedure code to see if it can be rewritten to do the operations that it does on a set (i.e., for all the rows in the table) rather than on one set of parameters (i.e., data from one row in the table) at time.

skc18
Starting Member

9 Posts

Posted - 2013-02-20 : 13:38:21

My hands are tight on changing the code inside invoking sproc. As for now will leave the scripts as is.

Thank you for your comments, learnt a lot.

:)

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-02-20 : 14:42:23

You can gain some efficiencies in the code. Most importantly, by indexing the temp table.

Create taBLE #Temp_Table (RecID Int Identity, TID Int, Type Int, Seq Int, Name Varchar(100),
PRIMARY KEY RecID)
Insert Into #Temp_Table
Select TID, TypId, Seq, [Name] from @Table
Order by TypID, Seq
-- must IMMEDIATELY follow the INSERT
Select @Total=@@ROWCOUNT --don't need to count rows to get the total#
--Select @Total=count(*) from #temp_Table --not needed

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-20 : 15:09:02

quote:
Originally posted by bandi

Why you need this much code?
Create taBLE #Temp_Table (RecID Int Identity, TID Int, Type Int, Seq Int, Name Varchar(100))
Insert Into #Temp_Table
Select TID, TypId, Seq, [Name] from @Table
Order by TypID, Seq

Select row_number() OVER(ORDER BY TypID, Seq) RecID, TID, TypId, Seq, Name
INTO #tempTable
from @Table
Order by TypID, Seq

--
Chandu

I agree that declaring the table structure is not "required" in order to funciotn. But, certain places (like mine) but do not allow "self-declaring" temp table on producton servers. I consider it lazy programing.. some people like to take advantage of these types of features.. YMMV. :)

skc18
Starting Member

9 Posts

Posted - 2013-02-21 : 22:42:05

quote:
Originally posted by ScottPletcher

You can gain some efficiencies in the code. Most importantly, by indexing the temp table.

Create taBLE #Temp_Table (RecID Int Identity, TID Int, Type Int, Seq Int, Name Varchar(100),
PRIMARY KEY RecID)
Insert Into #Temp_Table
Select TID, TypId, Seq, [Name] from @Table
Order by TypID, Seq
-- must IMMEDIATELY follow the INSERT
Select @Total=@@ROWCOUNT --don't need to count rows to get the total#
--Select @Total=count(*) from #temp_Table --not needed

Select @Total=@@ROWCOUNT -- nice :) much better than count(*) thanks
As for indexing temp table-- i agree and have suggested.

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2013-02-22 : 04:31:14

James K is absolutely correct.

All of the optimization you would make around this will be completely trivial compared to adapting the stored proc that writes the record to work on a set.

Remember that a set can still contain one element so as long as you re factored correctly you could keep the existing signature of the sp as a stub that just calls the set based version with one element set.

Thinking in sets should be your default.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

skc18
Starting Member

9 Posts

Posted - 2013-02-22 : 11:37:49

Thank you for all the information.

On my original post, the table variable declared was an exact copy of an actual table and how data was stored on a table. For the sake of tesing, the data was inserted into table variable.
I should've made it clear at the beginning :(

--------
The original code that I am working on--

Inside SPROC, what it does is it creates temp table with an identity column and inserts values into temp table from a source table.

---
Let's say the code does not have invoking sproc inside while loop.
In that case, would you all agree that it would be efficient to remove the temp table logic inside the proc and generate Identity from Row_Number()?

If you can--can you please provide the impact of temp tables on performance or some reading materials!!

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources