"primary key" in Table Variable

Question

"primary key" in Table Variable

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Paul (5/3/2007)
Forget my original question...
Make believe that this has nothing to do with cursors, stored procs or anything else. I just want to know if anyone has ever seen "Primary Key" assigned to a column in a table var, crash a stored proc.
That's it. That's the entire question.
I don't mean to sound ungratefulm but many people have posted answers to a question I am not asking. I am not attempting to speed up a stored proc, I am not attempting to alter my code. The code I have works fine. I am just asking if anyone else has ever seen this behavior in SQL 2000.
Thanks

Yes, I've seen it... many times... it usually happens when someone updates the primary key column of a table using a join with that same table and some parallelism takes place. It usually only occurs on production boxes because they are much more likely to spawn the right kind of parallelism.

And that's why we wanted you to post the code... so we can check.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

China Satya Rao T Valued Member Points: 54 More actions · Answer 2

Hi All,

I also ran into the exact problem (SS 2000)

I have a report which is timing out in all the environments (Offshore, Dev and QA).

The ASP report consists of a query which is using a table variable.

If I run the Report WITH primary key on the table variable it is taking 5 mins.

In the execution plan, the cost of table variable population is 16%

In the main query, cost of clustured index scan on table variable is 38% (even though table variable has only one row.)

If I run the query WITH OUT primary key on the table variable it is taking just 3 seconds.

In the execution plan, the cost of table variable population is 0.1%

In the main query, cost of table scan on table variable is 0%

This behavior is consistent across all DEV, QA and Prod Environments. It is very strange!!!!

Satya

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

That sounds more like problems with a table variable. Why aren't you using a temp table, instead?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

China Satya Rao T (11/10/2008)
In the execution plan, the cost of table variable population is 0.1%
In the main query, cost of table scan on table variable is 0%

The cost of operations against table variables cannot be considered accurate. Accurate costing requires that the optimiser knows how many rows will be affected and the optimiser cannot know that with table variables because they have no statistics.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Jeff Moden (11/10/2008)
That sounds more like problems with a table variable. Why aren't you using a temp table, instead?

Heh... sorry... I forgot... you're happy with the code you have.

To answer your question... yes, I've seen the problem. It's usually in conjuction with Table Variables for when I've seen it and I don't know why.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)