June 28, 2006 at 2:01 pm
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
June 29, 2006 at 9:28 am
Can we see the rest of the code. There's no way we can answer that without testing ourselves.
April 30, 2007 at 10:12 pm
The code is as simple as that.
declare @cnt Int,
@max-2 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-2 = (select max(somekey) from @t)
WHILE @cnt < @max-2
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
May 1, 2007 at 9:46 am
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?
May 1, 2007 at 10:54 pm
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-2 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-2 = (select MAX(somekey) from @t)
WHILE @cnt < @max-2
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
May 2, 2007 at 4:51 am
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?
May 2, 2007 at 8:05 am
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.
May 3, 2007 at 6:20 am
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.
May 3, 2007 at 7:05 am
You might want to check the various Set options on your production and dev boxes. Thinks like ArithAbort and NoCount.
May 3, 2007 at 10:00 am
Aside from posting the actual code could you post the results of:
select @@version
from you dev, test and prod SQL Servers ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
May 3, 2007 at 3:45 pm
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
May 3, 2007 at 3:50 pm
Never seen this.
May 3, 2007 at 3:57 pm
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.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
May 3, 2007 at 6:32 pm
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.
September 26, 2007 at 6:56 am
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
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply