Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    3

    Unanswered: How to enhance query performance?

    I have a query that will check the data in table1 if it exist in table2 - table8. The problem is the speed maybe because of the large data. It will take maybe a month or more to complete the execution of this script. Is there any way that i can enhance the speed of this query? BTW column pid are all index. Thanks

    QUERY:
    create table #temptable1 (
    pid char(15)
    )
    create table #no_txn(
    pid char(15)
    )
    go

    insert into #temptable1
    select pid from table1 --500k rows

    declare
    @pid char(15)

    select @pid = pid from #temptable1
    while @@rowcount <> 0
    BEGIN
    BEGIN
    IF exists(select 1 from table2 noholdlock where pid = @pid) --20M rows
    begin
    GOTO NEXTRECORD
    end
    ELSE IF exists(select 1 from table3 noholdlock where pid = @pid) --20M rows
    begin
    GOTO NEXTRECORD
    end
    ELSE IF exists(select 1 from table4 noholdlock where pid = @pid) --20M rows
    begin
    GOTO NEXTRECORD
    end
    ELSE IF exists(select 1 from table5 noholdlock where pid = @pid) --20M rows
    begin
    GOTO NEXTRECORD
    end
    ELSE IF exists(select 1 from table6 noholdlock where pid = @pid) --20M rows
    begin
    GOTO NEXTRECORD
    end
    ELSE IF exists(select 1 from table7 noholdlock where pid = @pid) --20M rows
    begin
    GOTO NEXTRECORD
    end
    ELSE IF exists(select 1 from table8 noholdlock where pid = @pid) --20M rows
    begin
    GOTO NEXTRECORD
    end
    ELSE
    begin
    INSERT INTO #no_txn
    SELECT pid
    FROM #temptable1
    WHERE pid = @pid
    end
    END
    NEXTRECORD:
    begin
    delete from #temptable1 where pid = @pid
    select @pid = pid from #temptable1
    end
    END

    select #no_txn.pid
    from #no_txn

    DROP TABLE #temptable1
    DROP TABLE #no_txn

    go

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    You are doing row by row processing instead of set based
    This is just as bad (if not worse) than cursor processing.
    Can you explain what you are trying to do and why the need for the 2 #temp tables

  3. #3
    Join Date
    Jul 2009
    Posts
    3
    the data in table1 will be check if it exists in table2 - table. in this case if it exist in table2 then theres no need the check in the other tables. sorry im new to sql thats why i come with this kind of script. can u help me?

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Code:
    select pid from table1
    where not exists (select 1 from table2 where table2.pid=table1.pid)
      and not exists (select 1 from table3 where table3.pid=table1.pid)
      and not exists (select 1 from table4 where table4.pid=table1.pid)
      and not exists (select 1 from table5 where table5.pid=table1.pid)
      and not exists (select 1 from table6 where table6.pid=table1.pid)
      and not exists (select 1 from table7 where table7.pid=table1.pid)
      and not exists (select 1 from table8 where table8.pid=table1.pid)
    order by pid

  5. #5
    Join Date
    Jul 2009
    Posts
    3
    Thank you sir for your help :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •