June 3, 2010 at 8:55 am
Is it possible to have two temp tables within a stored proc where I iterate over the records of temp table B and then modify temp table A records based on some condition/values within rows of temp table B? The reason I ask is because I have attempted to perform such operation and pretty much causes the stored proc to time out. I am in the process of rewriting a script that used a cursor and tried the aforementioned as a solution but it does not appear to be working. Also, I have commented out each table one by one and the script works populating each table. It seems that there is an issue when iterating over results and updating the other table that's causing such a lengthy time for processing. Any ideas would be welcomed? Thanks, in advance.
June 3, 2010 at 8:58 am
Nevermind. Found what it was. Forgot to increment the row index.
June 3, 2010 at 8:59 am
Yes, it is possible. You can have several temp tables within the same stored procedure and it should function just fine.
Your issues probably have more to do with the fact that you are RBAR'ing through the tables with a cursor. Post your SP, along with relevent table DDL and sample data and we'll help you solve your problem.
June 3, 2010 at 9:08 am
Thanks for the reply and willingness to help John. See my previous post before yours. All is working fine now. Once again, thanks.
June 3, 2010 at 10:15 am
Glad to hear that you are working. If you decide to re-write the cursor (it will perform much, much better) than post your code and we'll help out.
June 3, 2010 at 10:41 am
Dan, as John mentioned, if you are using a WHILE Loop or CURSOR to iterate through your tables, and assuming your table is a heavy-set, then it is absoultely unavoidable that your Iteration-Strategy will beat the performance badly.. So if at all you want to have a peek high-performant iterators,then i suggest you post your DDLs in format mentioned in the link in John's first signature line!
June 3, 2010 at 2:41 pm
Dan Fran (6/3/2010)
Thanks for the reply and willingness to help John. See my previous post before yours. All is working fine now. Once again, thanks.
I'll "third" what the others have said... If you are "iterating" over the rows as you said in your first post, then things may be working but they aren't "working fine now".
--Jeff Moden
Change is inevitable... Change for the better is not.
June 4, 2010 at 6:48 am
Here's the DDL. Sorry for the delayed response.
CREATE PROCEDURE [dbo].[myProcedure](
@ID int
)
AS
CREATE TABLE #TableA(
ID int,
ColumnA1 bit DEFAULT(0),
ColumnA2 bit DEFAULT(0),
ColumnA3 bit DEFAULT(0),
ColumnA4 bit DEFAULT(0),
ColumnA5 bit DEFAULT(0)
)
INSERT INTO #TableA(ID) SELECT @ID
--need another temp table to get someTypes for the ID passed in
CREATE TABLE #TableB
(
RowID int Identity(1, 1),
someTypeID int
)
--get the types and read into Table B
Insert Into #TableB(someTypeID)
SELECT someTypeID FROM tblSomeType WHERE ID = @ID
--Get number of records in temporary table and loop through all records
Declare @NumberRecords int, @RowCount int
SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1
Declare @someTypeID int
--check the types in TableB and update columns in table A based on @someTypeID
WHILE @RowCount <= @NumberRecords
BEGIN
SELECT @someTypeID = someTypeID From #TableB Where RowID = @RowCount
IF @someTypeID = 1
BEGIN
UPDATE #TableA SET ColumnA1 = 1
END
IF @someTypeID = 2
BEGIN
UPDATE #TableA SET ColumnA2 = 1
END
IF @someTypeID = 3
BEGIN
UPDATE #TableA SET ColumnA3 = 1
END
IF @someTypeID = 4
BEGIN
UPDATE #TableA SET ColumnA4 = 1
END
IF @someTypeID = 5
BEGIN
UPDATE #TableA SET ColumnA5 = 1
END
SET @RowCount = @RowCount + 1
END
SELECT * FROM #TableA
DROP TABLE #TableA
DROP TABLE #TableB
RETURN 0; --no errors
June 4, 2010 at 7:24 am
I think this might do it for you.
UPDATE a
SET ColumnA1 = CASE WHEN b.SomeTypeID = 1 THEN 1 ELSE a.ColumnA1 END,
ColumnA2 = CASE WHEN b.SomeTypeID = 2 THEN 1 ELSE a.ColumnA2 END,
ColumnA3 = CASE WHEN b.SomeTypeID = 3 THEN 1 ELSE a.ColumnA3 END,
ColumnA4 = CASE WHEN b.SomeTypeID = 4 THEN 1 ELSE a.ColumnA4 END,
ColumnA5 = CASE WHEN b.SomeTypeID = 5 THEN 1 ELSE a.ColumnA5 END
FROM #TableA a
JOIN #TableB b
ON a.ID = b.SomeTypeID
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 4, 2010 at 8:12 am
It looks like what Wayne provided will work for you, but seeing your SP code brings up another question. It appears that you've changed some things "to protect the innocent", but it looks to me like you may not even need the temp tables.
The last relative statement in your SP says:
SELECT * FROM #TableA
Is the purpose of this SP just to return a result set? If so, you don't need to put all of the rows into temp tables just to select them out again. This adds unnecessary overhead to the procedure. We can help you get rid of the temp tables, but we'll need to have the full (or closer to full) INSERT INTO....SELECT statements that you are using to populate the temp tables.
June 4, 2010 at 8:27 am
Good catch John. If we go this route, we'll also need some DDL/DML for sample data. See the first link in my signature for how to do this.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 4, 2010 at 8:47 am
Thanks Wayne. Unfortunately, the tables cannot actually be joined because someTypeID and ID are not the same (or someTypeID is not a foreign key reference to ID). Basically the logic here is that some object (ID) can have some types applied to it (someTypeID).
John, select * from tableA will only return 1 row. All inserts required are in the DDL.
June 4, 2010 at 9:25 am
Why can't the 2 temp tables be JOINed? Did you try Wayne's code?
What is the difference between JOINing the tables together to produce the output and using the ID from one to lookup the IDs in another. If ID = ID then you can JOIN on that condition.
In fact, since your Table A will only ever hold one value (since you are not getting this from a table in the DB, but from the @ID parameter), you don't even need that table. You can run a simple SELECT against your tblSomeType to get your results. This will make your solution SET based (no cursors) and remove the overhead of the temp tables.
SELECT
SUM(CASE WHEN SomeTypeID = 1 THEN 1 ELSE 0 END) as ColumnA1,
SUM(CASE WHEN SomeTypeID = 2 THEN 1 ELSE 0 END) as ColumnA2,
SUM(CASE WHEN SomeTypeID = 3 THEN 1 ELSE 0 END) as ColumnA3,
SUM(CASE WHEN SomeTypeID = 4 THEN 1 ELSE 0 END) as ColumnA4,
SUM(CASE WHEN SomeTypeID = 5 THEN 1 ELSE 0 END) as ColumnA5
FROMtblSomeType
WHEREID = @ID
GROUP BY ID
June 4, 2010 at 9:26 am
By the way, here's your new proc:
CREATE PROCEDURE dbo.myProcedure
@ID int
AS
SELECT
SUM(CASE WHEN SomeTypeID = 1 THEN 1 ELSE 0 END) as ColumnA1,
SUM(CASE WHEN SomeTypeID = 2 THEN 1 ELSE 0 END) as ColumnA2,
SUM(CASE WHEN SomeTypeID = 3 THEN 1 ELSE 0 END) as ColumnA3,
SUM(CASE WHEN SomeTypeID = 4 THEN 1 ELSE 0 END) as ColumnA4,
SUM(CASE WHEN SomeTypeID = 5 THEN 1 ELSE 0 END) as ColumnA5
FROMtblSomeType
WHEREID = @ID
GROUP BY ID
RETURN 0; --no errors
June 4, 2010 at 10:37 am
Thanks John and to everyone else. I will implement the new stored proc. Thanks to everyone for helping out with this one. I'm trying to beef up my skills and all input is definitely appreciated.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply