Click here to monitor SSC
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
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 22
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
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
Never seen this.
Rudyx - the Doctor
Rudyx - the Doctor
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3290 Visits: 2476

No.

I was just curious if it was version/service pack/hot fix related since it only occurred on 1 of 3 SQL Servers - hence the request for version information looking for a difference.



Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Michael Earl-395764
Michael Earl-395764
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2707 Visits: 23078

Everyone is trying to help, it is just difficult to give a real answer without being able to reproduce the issue. If you have lost the source code for this, I think you are basically finished in your quest to find a reason.

As far as everyone trying to help you with optimization and your process itself, think of it like this. If you took your car in to get your brakes replaced and the mechanic did not tell you that you had a bad tie rod end, wouldn't you be pissed when your steering failed a couple of days later?

Please don't get frustrated and fault people for trying to help.


mengus
mengus
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 162
A late response to the original post,











Assigning a Primary Key to a table variable can be beneficial and harmful. How it is use, the results may vary.







I have previous experience where Primary Keys on a column for table variables can be beneficial. This was used for counting orders activated/assigned/created/completed/cancelled between time intervals.







Brief Summary:







I had used a "Tally" or "Numbers" table variable. (from a SQL CookBook) I also created a "Temp" table variable to stored the calculated time intervals, example every 5 minutes for the last 7 days. Updating the "Temp" table variable with data gathered from our "Orders" table. In my case, I created an index on the "Temp" table variable by using Primary Key on "StartTime" and "EndTime." For a result-set of 2880 rows, it took less than 1 second. For testing purposes, I removed the primary key declarations and I basically had to cancel the query after 5 seconds. Unfortunately, in my case, without the use of primary keys, my code took roughly 7-10 seconds to return the 2880 rows.











My Sample Code:







DECLARE @EndDate DateTime



DECLARE @NumDaysAgo INT



DECLARE @MinsApart INT







SET @EndDate = '09/01/2007'



SET @NumDaysAgo=10



SET @MinsApart=5











declare @Temp table



(







ID INT IDENTITY,



StartTime Datetime,



EndTime DateTime,



OrdersCreated INT,



OrdersActivated INT,



OrdersAssigned INT,



OrdersCancelled INT,



OrdersCompleted INT



,



PRIMARY KEY



(



StartTime ASC,



EndTime ASC



)



)











DECLARE @Numbers TABLE



(



ID INT PRIMARY KEY



)







DECLARE @Helper TABLE



(



ID INT



)







INSERT INTO @Helper



SELECT ID FROM



((SELECT 1 AS ID)



UNION



(SELECT 2 AS ID)



UNION



(SELECT 3 AS ID)



UNION



(SELECT 4 AS ID)



UNION



(SELECT 5 AS ID)



UNION



(SELECT 6 AS ID)



UNION



(SELECT 7 AS ID)



UNION



(SELECT 8 AS ID)



UNION



(SELECT 9 AS ID)



UNION



(SELECT 10 AS ID)



) AS A







INSERT INTO @Numbers



SELECT (1000*(Thousands.ID-1)) + (100*(Hundreds.ID-1)) + (10*(Tens.ID-1)) + (Ones.ID)



From @Helper Thousands,@Helper Hundreds,@Helper Tens, @Helper Ones



Order by Thousands.ID ASC, Hundreds.ID ASC, Tens.ID ASC, Ones.ID







SET @EndDate = datediff(d,@NumDaysAgo-1,@EndDate)











INSERT INTO @Temp(StartTime, EndTime)



SELECT DATEADD(mi,@MinsApart*(n.ID-1),@EndDate),DATEADD(mi,@MinsApart*n.ID,@EndDate)



FROM @Numbers n



WHERE n.ID<CONVERT(INT,(@NumDaysAgo*24.0*60/@MinsApart) + 1)







-- Note: For the purpose of this posting, the "modified" code below is not the original.



-- The original contains 4 individual/distinct update sets for each type of count.







UPDATE t



SET OrdersCreated = Helper.HelpCount



FROM @Temp t



INNER JOIN



(SELECT t2.ID, COUNT(o.OrderID) AS helpCount



FROM @Temp t2, dbo.Order o



WHERE o.Created BETWEEN t2.StartTime AND t2.EndTime



GROUP BY t2.ID



) AS Helper



ON t.ID = Helper.ID







-- ....



-- Update other counts



--...











SELECT StartTime, EndTime



, ISNULL(OrdersCreated,0) AS Created



, ISNULL(OrdersActivated,0) AS Activated



, ISNULL(OrdersAssigned,0) AS Assigned



, ISNULL(OrdersCancelled,0) AS Cancelled



, ISNULL(OrdersCompleted,0) AS Completed



FROM @Temp















For case of the original post, since each row from the table variable is being processed one at a time, the Primary Key will not be beneficial in improving performance.







However, I would suggest checking the size of the tempdb(s) in for the Production environment to see if there was enough space allocated at the time when the Primary Key was present in the table variable. Just my assumptions, but the tempdb(s) may not have been configured with the appropriate space allocation. Usually, Production environments have larger tempdb(s) primarily because there are more activities than Development and/or Beta environments.







Regarding performance of the stored procedure, you may need to add additional logic to "the WHILE Loop" such as find a way to do it "SET-based" oriented which will be beneficial.











Regards,

Wameng Vang

MCTS
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45066 Visits: 39904
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
China Satya Rao T
China Satya Rao T
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 26
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
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45066 Visits: 39904
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47239 Visits: 44377
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
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45066 Visits: 39904
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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