March 27, 2012 at 9:53 am
Hi,
I have a stored procedure which processes a number of users which are collected separately in 4 temporary tables
I collect them something like this:
SELECT userId INTO #temp FROM Users where category = 1 and active = @active and paying = @pay
SELECT userId INTO #temp2 FROM Users where category = 2 and active = @active and paying = @pay
SELECT userId INTO #temp3 FROM Users where category = 3 and active = @active and paying = @pay
SELECT userId INTO #temp4 FROM Users where category = 4 and active = @active and paying = @pay
Since I need to process only the users which are in all those categories I tried to optimize the procedure and I gathered them in a final table like this:
SELECT DISTINCT userId
FROM #temp t
INNER JOIN #temp2 t2 ON t.userId = t2.userId
INNER JOIN #temp3 t3 ON t.userId = t3.userId
INNER JOIN #temp4 t4 ON t.userId = t4.userId
And I used only this table instead of joining or 4 of them(this is the way it was previously implemented)
Is there a more professional way to do this?
I would like to optimize this since I have a lot of data to process
Thank you
March 27, 2012 at 10:47 am
with out DDL and sample data from the UserID table your populating your temp tables from it would be a little tough. This may work though:
SELECT u1.userID FROM Users u1
INNER JOIN Users u2
ON u1.userID = u2.userID
AND u2.category = 2
AND u2.active = @active
AND u2.paying = @pay
INNER JOIN Users u3
ON u1.userID = u3.userID
AND u3.category = 3
AND u3.active = @active
AND u3.paying = @pay
INNER JOIN Users u4
ON u1.userID = u4.userID
AND u4.category = 4
AND u4.active = @active
AND u4.paying = @pay
WHERE u1.category = 1
AND u1.active = @active
AND u1.paying = @pay
Table DDL and sample data along with what your results would be will provide a better answer as we have something to chew on.
EDIT: copy and paste error
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 28, 2012 at 3:13 am
I just needed an idea on how should I approach differently the query, more optimized. I will try it with your suggestion.
Thank you
March 28, 2012 at 4:59 pm
shnex (3/28/2012)
I just needed an idea on how should I approach differently the query, more optimized. I will try it with your suggestion.Thank you
no problem. self joins can get a bit complicated but i take the approach of which sets do i want to compare write a query to get that set out the figure out the self joins to compare them all. gets rid of temp tables and lets the query optimizer have its way.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply