Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

"primary key" in Table Variable Expand / Collapse
Author
Message
Posted Wednesday, June 28, 2006 2:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, November 22, 2009 1:26 AM
Points: 13, 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
Post #290916
Posted Thursday, June 29, 2006 9:28 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
Can we see the rest of the code. There's no way we can answer that without testing ourselves.
Post #291236
Posted Monday, April 30, 2007 10:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, November 22, 2009 1:26 AM
Points: 13, 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
Post #362198
Posted Tuesday, May 1, 2007 9:46 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619

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?

Post #362333
Posted Tuesday, May 1, 2007 10:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, November 22, 2009 1:26 AM
Points: 13, 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
Post #362523
Posted Wednesday, May 2, 2007 4:51 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
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?
Post #362566
Posted Wednesday, May 2, 2007 8:05 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:31 AM
Points: 433, Visits: 982
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.






Post #362622
Posted Thursday, May 3, 2007 6:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078

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.

Post #362926
Posted Thursday, May 3, 2007 7:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 28, 2012 1:09 PM
Points: 21, Visits: 209
You might want to check the various Set options on your production and dev boxes. Thinks like ArithAbort and NoCount.
Post #362937
Posted Thursday, May 3, 2007 10:00 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:29 AM
Points: 3,214, Visits: 2,343

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."
Post #363032
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse