How to select batch from table?

  • zxmgh

    SSC Eights!

    Points: 911

    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

  • SQLZ

    SSChampion

    Points: 12872

    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?

  • zxmgh

    SSC Eights!

    Points: 911

    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

     

  • zxmgh

    SSC Eights!

    Points: 911

    "And why do you want all of the rows?" It is a business requirement, this resultset will be feeded to some other program.

  • SQLZ

    SSChampion

    Points: 12872

    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.

  • Zubeyir

    SSCrazy

    Points: 2559

    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

  • zxmgh

    SSC Eights!

    Points: 911

    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

    ...

     

    ************************************************************

     SET ROWCOUNT 0
     
       SELECT col1, col2

        INTO #Temp1

        FROM tab1 WITH (NOLOCK)

     
    SET ROWCOUNT 1000

        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 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply