SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


"primary key" in Table Variable


"primary key" in Table Variable

Author
Message
Paul -244830
Paul -244830
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 22
I ran into a problem recently with an unexpected solution that I wanted to post on SQLServerCentral. The reason I am posting it here is that I want to know WHY it worked.

I have a stored proc with a simple table variable, something like this:

DECLARE @t TABLE (someID Int primary key)

The proc is only about 100 lines and is fairly straight forward. On my dev box, the query consistently runs around 1.5 seconds. On our test server is ran a consistent 1 second. On the production box, it consistently timed out.

While we were trying to debug the issue, a coworker sent the proc to a friend of his who is a lead DBA at myspace.com of all places. One of his coworkers suggested removing the "primary key" declaration from the table var. All of us were stymied as to why this would make any difference, but once it was removed, it ran fine on the production machine.

I thought this might have been due to a lack of memory available to the SQL server in production, but this was not the case. The production SQL server had another Gig of RAM available to it.

Anyone know why this would make any difference?

Paul Perrick
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68849 Visits: 9671
Can we see the rest of the code. There's no way we can answer that without testing ourselves.
Paul -244830
Paul -244830
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 22
The code is as simple as that.

declare @cnt Int,
@max Int
declare @t TABLE (somekey Int identity(1,1) primary key, someval varchar (100))


insert @t (someval)
select blah from table1 <-- about 1000 rows.

--Following avoids using cursors:
SET @cnt = 1
SET @max = (select max(somekey) from @t)
WHILE @cnt < @max
BEGIN
--Do some other update here.
SET @cnt = @cnt + 1
END


The fact is, if the table var has "primary key" set on the key (which honestly isn't really needed since its a table in memory), the sproc hangs (I let it run for about a 1/2 an hour). If you remove primary key from the table def, it runs in about 40ms.

I just wondered why this would make a difference. I've already learned my lesson and I'm not going to use that construct anymore. I just wondered why, thats all.

Paul
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68849 Visits: 9671

1 - Table varaibles are a very viable solution. You may have found one of the few exceptions there but I would NOT stop using just yet.

2 - You didn't post the actual code so I can't spot the problem with what you supplied.

3 - Why are you not doing a single update statement to update all 100 rows in one batch?


Paul -244830
Paul -244830
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 22
I didn't post the actual code because its long gone. What I needed to do was a multi step process with a derived set of data. Something you would use a cursor for. Since I don't like cursors, my solution is:

declare @cnt int, @max int
set @cnt = 1
declare @t table(somekey Int identity(1,1), tablePK Int)
INSERT @t (tablePK) select somePK from Table1 where blah = blah
set @max = (select MAX(somekey) from @t)
WHILE @cnt < @max
BEGIN
-- DO multi step process here
set @cnt = @cnt + 1
END


All I want to know is, what is the darn difference between a column set as an identity column in a table variable if it has it set as a primary key, or not set as a primary key.

I am not planning on not using a table var, Im not changing anything, I just want to know why setting the column as a primary key would ever make it run SLOWER?

Thanks
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68849 Visits: 9671
That would catch my eye too. I've never met this problem and I can't replicate the problem at the moment. Can you try to do a code sample to replicate the behavior so that I can try to figure it out?
schleep
schleep
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2793 Visits: 1406
Not sure if any of what follows explains what you're seeing:

Have you looked at the execution plans on each of the boxes? Since prod is timing out, can you try it for just a handful of rows?

In my experience, tablevars with more than 10 000 rows are slower than #temp tables, index or no index.

Table vars do not necessarily exist in RAM: if they get big enough, they are created in tempdb, just like #temp tables.

As an aside, while loops and cursors usually amount to row by row processing, one doesn't save much doing it one way or the other. Neither would be an improvement - most of the time.



Michael Earl-395764
Michael Earl-395764
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13559 Visits: 23078

Everyone is right - post the entire code and it will be much easier to assist you.

By any chance, are your dev and test servers single-processor servers or some kind of VMWare, and your production server a milti-processor system?

Lastly, the comment about avoiding a cursor is strange. They would be typically avoided to improve performance (SQL is not great at looping). This procedure, however, avoided it by using a looping process that is most likely slower than a cursor. I am curious as to what you were trying to avoid.


Dan Smith-306792
Dan Smith-306792
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 210
You might want to check the various Set options on your production and dev boxes. Thinks like ArithAbort and NoCount.
Rudyx - the Doctor
Rudyx - the Doctor
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11152 Visits: 2503

Aside from posting the actual code could you post the results of:

select @@version

from you dev, test and prod SQL Servers ?



Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search