Speed up query assistance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Speed up query assistance

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2010-07-02 : 05:17:14

Hi All,

I am running a query (below) which basically loops through a table of debits and credits to populate the corresponding debit (and where the credit is not alreay been matched)

Now for you experts, just by having a quick look at the code below, would you be able to offer me any tips to speed the process up?

I have created indexes on the key fields, can I swap around the AND/WHERE clauses to speed it up?

Thanks


Declare		@sql varchar(1000)
			,@Ledger varchar(1000)

			, @sqlstring nvarchar(2000)
			, @counter int
			, @Max int
			, @SRN int

Set @Ledger = 91

SET @Counter = 1
WHILE @Counter <= @Max
		BEGIN
			SET @sqlstring = '
							UPDATE A
								SET A.CREDIT_SRN = B.SRN
							FROM TEMP_FILTER_81_SRNS A
							INNER JOIN TEMP_GL_ALL B
								ON A.DEBIT = B.CREDIT
								AND A.DATE = B.DATE
								AND A.LEDGER = B.LEDGER
								AND B.LEDGER = ' +@Ledger + '
								AND A.CURRENCY = B.CURRENCY
								AND B.SRN NOT IN (SELECT DEBIT_SRN FROM FILTER_LOG UNION SELECT CREDIT_SRN FROM FILTER_LOG)
								AND B.SRN NOT IN
								(
									--EXCLUDE CREDIT DUPES
									SELECT A.SRN 
									FROM TEMP_GL_ALL A
									INNER JOIN
									(
										SELECT DATE, CREDIT, CURRENCY, LEDGER, COUNT(*) AS TOTAL
										FROM TEMP_GL_ALL
										WHERE LEDGER = ' + @Ledger + '
										AND SRN NOT IN (SELECT DEBIT_SRN FROM FILTER_LOG UNION SELECT CREDIT_SRN FROM FILTER_LOG)
										GROUP BY DATE, CREDIT, CURRENCY, LEDGER
										HAVING COUNT(*) > 1
									) B
									ON A.DATE = B.DATE
									AND A.CREDIT = B.CREDIT
									AND A.CURRENCY = B.CURRENCY
									AND A.LEDGER = B.LEDGER
								)
							WHERE ID = ' + cast(@Counter as varchar(1000)) +  ' 
							AND B.SRN NOT IN (SELECT CREDIT_SRN FROM TEMP_FILTER_81_SRNS)'
			SET @Counter = @Counter + 1
			EXEC (@sqlstring)
			--PRINT @sqlstring	
		END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-02 : 05:31:21

I don't see a reason for dynamic sql.
NOT IN () is slower than NOT EXISTS () in most cases.

No, you're never too old to Yak'n'Roll if you're too young to die.

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2010-07-02 : 05:54:44

The reason I use dynamic sql is to loop through each record at a time to match to the credit_srn, and then make sure that credit_srn is not already in the table (as I cannnot have dupe credit_srn's), Any suggestions to do this another way would be greatly appreciated. Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-02 : 06:05:38

In case of @Max (which I can't see) is set to 1000 then your statement is looping from 1 to 1000 doing always the same stuff with just another ID.

So you also can do it in ONE GO without looping and without dynamic sql.

Just take you update statement and change this part of the WHERE clause
WHERE ID = ' + cast(@Counter as varchar(1000)) + '

to

WHERE ID < 1001

or

WHERE ID <= @Max

Sure you have to remove the concats if you are going without dynamic sql!

No, you're never too old to Yak'n'Roll if you're too young to die.

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2010-07-02 : 06:07:51

I've never used NOT EXISTS before, could you assist in converting this to a not exists statement?

AND B.SRN NOT IN (SELECT DEBIT_SRN FROM FILTER_LOG UNION SELECT CREDIT_SRN FROM FILTER_LOG)

Also, can you have a multiple NOT EXISTS statement, i.e.

NOT EXISTS (xxx)
AND NOT EXISTS (xxx)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-02 : 07:38:25

Give it a try please (I am not able to test it):

declare @Ledger varchar(1000) -- Why is this varchar(1000)??
declare @Max int

set @Ledger= '91'
set @Max=1000

UPDATE A
   SET A.CREDIT_SRN = B.SRN
FROM TEMP_FILTER_81_SRNS A
INNER JOIN TEMP_GL_ALL B
    ON A.DEBIT = B.CREDIT
    AND A.DATE = B.DATE
    AND A.LEDGER = B.LEDGER
    AND B.LEDGER = @Ledger
    AND A.CURRENCY = B.CURRENCY
    AND NOT EXISTS (select * from (SELECT DEBIT_SRN AS SRN FROM FILTER_LOG 
                                   UNION 
                                   SELECT CREDIT_SRN AS SRN FROM FILTER_LOG
                                  ) dt
                    WHERE dt.SRN = B.SRN
                   )
    
    AND NOT EXISTS (select * from (
                                    SELECT A1.SRN 
                                    FROM TEMP_GL_ALL A1
                                    INNER JOIN
                                    (
                                        SELECT DATE, CREDIT, CURRENCY, LEDGER, COUNT(*) AS TOTAL
                                        FROM TEMP_GL_ALL A2
                                        WHERE LEDGER = @Ledger
                                        AND NOT EXISTS (SELECT * FROM (SELECT DEBIT_SRN AS SRN FROM FILTER_LOG 
                                                                       UNION 
                                                                       SELECT CREDIT_SRN AS SRN FROM FILTER_LOG
                                                                      ) dt3
                                                        WHERE dt3.SRN = A2.SRN
                                                       )
                                        GROUP BY DATE, CREDIT, CURRENCY, LEDGER
                                        HAVING COUNT(*) > 1
                                    ) B1
                                    ON A1.DATE = B1.DATE
                                    AND A1.CREDIT = B1.CREDIT
                                    AND A1.CURRENCY = B1.CURRENCY
                                    AND A1.LEDGER = B1.LEDGER
                                  ) dt2
                    WHERE dt2.SRN = B.SRN
                   )
WHERE ID <=  @Max
  AND NOT EXISTS ( SELECT * FROM TEMP_FILTER_81_SRNS WHERE CREDIT_SRN = B.SRN)

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources