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
 General SQL Server Forums
 New to SQL Server Programming
 Can anyone speed up this query?

Author  Topic 

Rasta Pickles
Posting Yak Master

174 Posts
Posted - 2012-08-19 : 12:05:26
Apologies, I've inherited this so I don't know whether it's phenomenally inefficient or as good as it gets.

It involves three tables; AGREEMENTS contains 700,000 records, AGREEMENT_PROFILES contains 7,000,000 records and TRANSACTION_HEADER contains 9,500,000 records.

Retrieving data for two days takes 11 minutes.


DECLARE @StartDate DATE,
		@StopDate DATE
SET @StartDate = '2012-06-30' --change as necessary
SET @StartDate = DATEADD(DAY, 1, @StartDate)
SET @StopDate = '2013-01-01'  --change as necessary
DECLARE @test AS TABLE (DATEX DATETIME, FINANCIER_ID INT, OUTSTANDING_LOAN_TOTAL DECIMAL(12,2))
WHILE @StartDate < @StopDate
BEGIN
INSERT INTO @test
SELECT @StartDate AS DATEX, FINANCIER_ID,
              SUM(CASE WHEN TH.TRANSACTION_TYPE_ID IN (1,2)  
                                  THEN ISNULL(TH.PREMIUM_VALUE,0)  
                                  ELSE (ISNULL(TH.PREMIUM_VALUE,0) ) * -1  
                           END)  
                           AS OUTSTANDING_LOAN_TOTAL  
FROM AGREEMENTS AS AG  
       INNER JOIN TRANSACTION_HEADER AS TH  
              ON TH.AGREEMENT_ID = AG.AGREEMENT_ID  
WHERE (  
              AG.CANCELLATION_DATE IS NULL  
              OR AG.CANCELLATION_DATE > @StartDate 
         )  
       AND   
              (AG.RETIRED_DATE IS NULL  
              OR AG.RETIRED_DATE > @StartDate)  
       AND TH.TRANSACTION_TYPE_ID IN (1, 2, 4, 9, 10, 20, 21, 24)  
       AND TH.EFFECTIVE_DATE < @StartDate
       AND TH.AGREEMENT_ID IN (SELECT APX.AGREEMENT_ID FROM AGREEMENT_PROFILES AS APX
JOIN AGREEMENTS AS AX ON AX.AGREEMENT_ID = APX.AGREEMENT_ID
WHERE APX.DUE_DATE < @StartDate
AND (APX.PAID_DATE IS NULL OR APX.PAID_DATE >=  @StartDate)
AND APX.CHARGE_NUMBER IS NULL
AND (AX.CANCELLATION_DATE IS NULL OR AX.CANCELLATION_DATE >= @StartDate)
AND APX.AGREEMENT_ID IN (SELECT TOP 1 THX.AGREEMENT_ID FROM TRANSACTION_HEADER AS THX 
WHERE THX.AGREEMENT_ID = APX.AGREEMENT_ID AND THX.INSTALMENT_NUMBER = APX.INSTALMENT_NUMBER 
AND THX.CHARGE_NUMBER IS NULL AND THX.TRANSACTION_TYPE_ID IN (10, 20)
AND THX.EFFECTIVE_DATE < @StartDate)
AND 24 <> (SELECT TOP 1 THX.TRANSACTION_TYPE_ID FROM TRANSACTION_HEADER AS THX 
WHERE THX.AGREEMENT_ID = APX.AGREEMENT_ID AND THX.INSTALMENT_NUMBER = APX.INSTALMENT_NUMBER 
AND THX.CHARGE_NUMBER IS NULL 
AND THX.EFFECTIVE_DATE < @StartDate
ORDER BY EFFECTIVE_DATE DESC))
GROUP BY FINANCIER_ID
SET @StartDate = DATEADD(DAY, 1, @StartDate)
END
select * from @test


Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-19 : 13:52:02
i see lot of subqueries which can be an issue especially in case of large datasets

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Rasta Pickles
Posting Yak Master

174 Posts
Posted - 2012-08-19 : 14:04:54
Well 11 minutes to retrieve 2 days worth of data would seem, by virtue of that elongated duration, to vindicate what you are alluring to.

Any suggestions?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-19 : 14:28:21
try changing them to joins

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Rasta Pickles
Posting Yak Master

174 Posts
Posted - 2012-08-19 : 14:32:27
Thank you for your guidance.

Given that this forum is a "new to SQL Server programming", you might have well told me to program a probe to land on the moon

But I will take your advice, thank you again
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-08-19 : 17:05:36
There are two things you see in your query that slows it down. One is the loop. You should be able to get all the data in one query instead of having to loop through it. Second is the the subqueries. It should be possible to join the tables rather than have a subquery.

Given below is an attempt to remove the loops. What I am trying to do is to create a calendar table and then join to it. But this may be completely wrong because I don't have any test data to test and I don't really understand the logic.

If you choose to use it, first change the start date and end date to be the same or different by just one day. See if that gives you the right results. If it does, then make it a two-day difference and see if that works.

Regarding the subquery, it takes too much thinking (on my part) to figure it out :) If this improves the performance, you may not need it.
DECLARE @StartDate  DATE,
        @StopDate   DATE

SET @StartDate = '2012-06-30' --change as necessary
SET @StartDate = DATEADD(DAY, 1, @StartDate)
SET @StopDate = '2013-01-01'  --change as necessary

CREATE TABLE #Calendar(Dt DATETIME NOT NULL PRIMARY KEY CLUSTERED);
;WITH N(n) AS
( SELECT 1 UNION ALL SELECT n+1 FROM N WHERE n <= DATEDIFF(dd,@StartDate,@StopDate))
INSERT INTO #Calendar SELECT DATEADD(dd,n-1,@StartDate)  FROM N
OPTION (MAXRECURSION 0);



--WHILE @StartDate < @StopDate
--BEGIN
--  INSERT INTO @test
    SELECT Dt AS DATEX,
           FINANCIER_ID,
           SUM(
               CASE 
                    WHEN TH.TRANSACTION_TYPE_ID IN (1, 2) THEN ISNULL(TH.PREMIUM_VALUE, 0)
                    ELSE (ISNULL(TH.PREMIUM_VALUE, 0)) * -1
               END
           ) AS OUTSTANDING_LOAN_TOTAL
    FROM   AGREEMENTS AS AG
           INNER JOIN TRANSACTION_HEADER AS TH
                ON  TH.AGREEMENT_ID = AG.AGREEMENT_ID
           CROSS JOIN #Calendar
    WHERE  (
               AG.CANCELLATION_DATE IS NULL
               OR AG.CANCELLATION_DATE > Dt
           )
           AND (AG.RETIRED_DATE IS NULL OR AG.RETIRED_DATE > Dt)
           AND TH.TRANSACTION_TYPE_ID IN (1, 2, 4, 9, 10, 20, 21, 24)
           AND TH.EFFECTIVE_DATE < Dt
           AND TH.AGREEMENT_ID IN (SELECT APX.AGREEMENT_ID
                                   FROM   AGREEMENT_PROFILES AS APX
                                          JOIN AGREEMENTS AS AX
                                               ON  AX.AGREEMENT_ID = APX.AGREEMENT_ID
                                   WHERE  APX.DUE_DATE < Dt
                                          AND (APX.PAID_DATE IS NULL OR APX.PAID_DATE >= Dt)
                                          AND APX.CHARGE_NUMBER IS NULL
                                          AND (
                                                  AX.CANCELLATION_DATE IS NULL
                                                  OR AX.CANCELLATION_DATE >= Dt
                                              )
                                          AND APX.AGREEMENT_ID IN (SELECT TOP 1 
                                                                          THX.AGREEMENT_ID
                                                                   FROM   
                                                                          TRANSACTION_HEADER AS 
                                                                          THX
                                                                   WHERE  THX.AGREEMENT_ID = 
                                                                          APX.AGREEMENT_ID
                                                                          AND 
                                                                              THX.INSTALMENT_NUMBER = 
                                                                              APX.INSTALMENT_NUMBER
                                                                          AND 
                                                                              THX.CHARGE_NUMBER 
                                                                              IS 
                                                                              NULL
                                                                          AND 
                                                                              THX.TRANSACTION_TYPE_ID IN (10, 20)
                                                                          AND 
                                                                              THX.EFFECTIVE_DATE 
                                                                              < 
                                                                              Dt)
                                          AND 24 <> (
                                                  SELECT TOP 1 THX.TRANSACTION_TYPE_ID
                                                  FROM   TRANSACTION_HEADER AS 
                                                         THX
                                                  WHERE  THX.AGREEMENT_ID = APX.AGREEMENT_ID
                                                         AND THX.INSTALMENT_NUMBER = 
                                                             APX.INSTALMENT_NUMBER
                                                         AND THX.CHARGE_NUMBER 
                                                             IS NULL
                                                         AND THX.EFFECTIVE_DATE 
                                                             < Dt
                                                  ORDER BY
                                                         EFFECTIVE_DATE DESC
                                              ))
    GROUP BY
           FINANCIER_ID
DROP TABLE #Calendar;    
--    SET @StartDate = DATEADD(DAY, 1, @StartDate)
--END
--SELECT *
--FROM   @test
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2012-08-20 : 03:24:25
Which version of SQL Server are you using?


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site