August 21, 2006 at 8:26 am
Hi, I need to join two big tables and return all rows of the tables. The problem is the two tables are big so the performance of the query is very bad, I plan to select from one table in a batch, put the batch in a temporary table, join the temporary table with the other table, drop the temporary table, then repeat it. I tried the following way but it is not working - hanging, please let me know where is the problem:
SET ROWCOUNT 1000
WHILE (1=1)
BEGIN
SELECT col1, col2
INTO #Temp1
FROM tab1 WITH (NOLOCK)
SELECT #Temp1.col2 AS rep1, (tab2.col3 - tab2.col4) AS rep2 FROM #Temp1 WITH (NOLOCK)
LEFT OUTER JOIN tab2 WITH (NOLOCK) ON (#Temp1.col1 = tab2.col1)
IF (@@ROWCOUNT = 0)
BEGIN
DROP TABLE #Temp1
BREAK
END
ELSE
BEGIN
DROP TABLE #Temp1
END
END
August 21, 2006 at 8:47 am
Hi,
I'm not so sure that running the statements in a batch is going to help performance. You're still joining onto tab2 (albeit with less rows) but you're going to do it repeatedly in a loop.
Secondly, the reason it's hanging is because you're repeatedly dropping #Temp1 and reinserting the same values into it. Essentially, @@ROWCOUNT is never going to be equal to 0 so you'll never BREAK out of the loop.
Personally, I'd drop the idea of running the statement in a batch and focus on tunning the table with some indexes perhaps.
How many rows of data are we talking about in each table? And why do you want all of the rows?
August 21, 2006 at 8:55 am
tab1 has about 1 milllion rows, tab2 has about 5 million rows. The idea is not locking these two tables one time for 5 minutes, instead, hopefully, multiple times, but seconds each time, thus, allow other programs to use these these tables in the mean time.
Index has been created on tab1.col1 and tab2.col1, but performance is still very slow.
"the reason it's hanging is because you're repeatedly dropping #Temp1 and reinserting the same values into it. Essentially, @@ROWCOUNT is never going to be equal to 0 so you'll never BREAK out of the loop."
How to work around this problem?
Thanks a lot
August 21, 2006 at 8:58 am
"And why do you want all of the rows?" It is a business requirement, this resultset will be feeded to some other program.
August 21, 2006 at 9:03 am
Hi,
if you use the NOLOCK hint as you were doing so in the original example you shouldn't place any locks on the tables and it shouldn't therefore affect other users.
August 21, 2006 at 11:08 am
If you need to do it for just one time you can tell the query analyser to put the results to a file instead of showing them up. (CTRL+SHIFT+F)
Cheers,
Zubeyir
August 21, 2006 at 11:52 am
Friends,
I have modified the script as follows, it works fine, the only problem is the result returns in batch like this:
rep1 rep2
1 100.0
2 200.0
...
rep1 rep2
1001 100.0
1002 100.0
...
But I want the result to be continuous:
rep1 rep2
1 100.0
2 200.0
...
1001 100.0
1002 100.0
...
************************************************************
INTO #Temp1
FROM tab1 WITH (NOLOCK)
WHILE (1=1)
BEGIN
SELECT #Temp1.col2 AS rep1, (tab2.col3 - tab2.col4) AS rep2 FROM #Temp1 WITH (NOLOCK)
LEFT OUTER JOIN tab2 WITH (NOLOCK) ON (#Temp1.col1 = tab2.col1)
SELECT @RowCount = @@ROWCOUNT
IF (@ROWCOUNT = 0)
BEGIN
DROP TABLE #Temp1
BREAK
END
ELSE
BEGIN
DELETE FROM #Temp1
END
END
SET ROWCOUNT 0
************************************************************
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply