SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query Performance
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

skc18
Starting Member

9 Posts

Posted - 02/19/2013 :  16:14:59  Show Profile  Reply with Quote
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

Edited by - skc18 on 02/19/2013 16:16:46

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 02/20/2013 :  00:57:46  Show Profile  Reply with Quote
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
Go to Top of Page

skc18
Starting Member

9 Posts

Posted - 02/20/2013 :  11:25:17  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 02/20/2013 :  11:32:44  Show Profile  Reply with Quote
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.
Go to Top of Page

skc18
Starting Member

9 Posts

Posted - 02/20/2013 :  12:29:42  Show Profile  Reply with Quote
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
Go to Top of Page

skc18
Starting Member

9 Posts

Posted - 02/20/2013 :  12:38:18  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 02/20/2013 :  12:52:57  Show Profile  Reply with Quote
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.
Go to Top of Page

skc18
Starting Member

9 Posts

Posted - 02/20/2013 :  13:38:21  Show Profile  Reply with Quote
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.

:)

Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
346 Posts

Posted - 02/20/2013 :  14:42:23  Show Profile  Reply with Quote
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

Edited by - ScottPletcher on 02/20/2013 14:43:23
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 02/20/2013 :  15:09:02  Show Profile  Reply with Quote
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. :)


Edited by - Lamprey on 02/20/2013 15:09:54
Go to Top of Page

skc18
Starting Member

9 Posts

Posted - 02/21/2013 :  22:42:05  Show Profile  Reply with Quote
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
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 02/22/2013 :  04:31:14  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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/
Go to Top of Page

skc18
Starting Member

9 Posts

Posted - 02/22/2013 :  11:37:49  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000