"primary key" in Table Variable

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Strategies » "primary key" in Table Variable

20 posts, Page 1 of 2

1 2 »»

"primary key" in Table Variable

Rate Topic

Display Mode

Topic Options

Author

Message

Paul -244830

Posted Wednesday, June 28, 2006 2:01 PM

Grasshopper

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

Ninja's_RGR'us

Posted Thursday, June 29, 2006 9:28 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 9:37 PM
Points: 21,359, Visits: 9,542

Can we see the rest of the code. There's no way we can answer that without testing ourselves.

Post #291236

Paul -244830

Posted Monday, April 30, 2007 10:12 PM

Grasshopper

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

Ninja's_RGR'us

Posted Tuesday, May 01, 2007 9:46 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 9:37 PM
Points: 21,359, Visits: 9,542

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

Paul -244830

Posted Tuesday, May 01, 2007 10:54 PM

Grasshopper

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

Ninja's_RGR'us

Posted Wednesday, May 02, 2007 4:51 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 9:37 PM
Points: 21,359, Visits: 9,542

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

schleep

Posted Wednesday, May 02, 2007 8:05 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 12:35 PM
Points: 345, Visits: 605

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

Michael Earl-395764

Posted Thursday, May 03, 2007 6:20 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, 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

Dan Smith-306792

Posted Thursday, May 03, 2007 7:05 AM

Grasshopper

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

rudy - Doctor "X"

Posted Thursday, May 03, 2007 10:00 AM

Hall of Fame

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:11 PM
Points: 3,108, Visits: 2,114

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 »

20 posts, Page 1 of 2

1 2 »»

Permissions