How can i write this query more efficiently?

Question

This Query works in a loop and thus its performance is too slow. FUID is provided by a while loop.

SELECT (SELECT TOP 1 AmountPaid 
        from [xyz].[dbo].AmountReceived 
          WHERE C.IID = [xyz].[dbo].AmountReceived.IID 
        order by ReceivingDate asc)
FROM [xyz].[dbo].Customer C
  Where C.BuisnessDate >= DATEADD(m,DATEDIFF(m,0,'2015-03-31'),0) 
  AND C.BuisnessDate <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,'2015-03-31')+1,0)) AND C.FUID=16 
  AND DATEDIFF(M,C.RiskDate,'2015-03-31') <=3

Customer table contains these related columns:

 +------------+----+----------+----------+
 |   IID  | FUID |BusinessDate|RiskDate  |
 +--------+------+------------+----------+
 | 22433  | 13   |2013-05-02  |2007-05-23|
 | 22443  | 26   |2014-02-18  |2011-09-07|
 | 22906  | 32   |2014-12-22  |2015-01-12|

AmountReceived table:

 +--------+---------------+-------------+
 |   IID  |AmountPaid     |ReceivingDate|
 +--------+---------------+-------------+
 | 22433  | 13800         |2015-02-02   |
 | 22443  | 1290          |2014-12-18   |
 | 22906  | 408           |2014-10-22   |

which DB you are using `mysql` or `sqlserver` ? and add some sample data and what is your expected output . — Ameya Deshpande, Apr 10 '15 at 04:20
I really hope business is just typoed here, and not actually misspelled in the column names. That would drive me crazy... — APH, Apr 10 '15 at 04:49
Only **FUID** provided through loop. loop executes from 1 to 47 — Ahmed, Apr 10 '15 at 05:15
@aphrael I am using correct column names these tables are too much larg so i posted only query related columns — Ahmed, Apr 10 '15 at 05:21
@user3849695 - what do you do with the result of the select in your loop? you may be able to do what thepirat000 suggests. do you return 47 selects / insert it into another table? — ughai, Apr 10 '15 at 05:21
You can also change the last portion of the where to be [sargable](http://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable): instead of `DATEDIFF(M,C.RiskDate,'2015-03-31') <=3`, do `C.RiskDate >= '2014-12-01'` — thepirat000, Apr 10 '15 at 05:22
@thepirat000 i need these each FUID reacord distinctly for further calculations — Ahmed, Apr 10 '15 at 05:24
thank you all for helping me. Special thnx to @thepirat000 and *ughai* :) — Ahmed, Apr 10 '15 at 10:37

ughai · Accepted Answer · 2015-04-10T06:16:01.410

If I understand your question and you only get FUID in your WHILE, you need something like this

;WITH CTE AS 
(
SELECT  C.FUID,AR.AmountPaid,
ROW_NUMBER()OVER(PARTITION BY C.FUID ORDER BY AR.ReceivingDate ASC) rn
FROM [xyz].[dbo].Customer C
    INNER JOIN [xyz].[dbo].AmountReceived AR
        ON C.IID = AR.IID 
WHERE C.BuisnessDate >= DATEADD(m,DATEDIFF(m,0,'2015-03-31'),0) 
    AND C.BuisnessDate <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,'2015-03-31')+1,0))
    AND C.FUID BETWEEN 1 AND 47 
    AND C.RiskDate >= '2014-12-01'
)
SELECT C.FUID,AR.AmountPaid
FROM CTE 
WHERE rn = 1

Also added the suggestion by thepirat000 in comments above to change DATEDIFF(M,C.RiskDate,'2015-03-31') <=3 to C.RiskDate >= '2014-12-01'

_ughai_ your query did great job it reduce 42000 logical reads to 260. :O thnx a lot :) can you explain logic behind this query? — Ahmed, Apr 10 '15 at 10:27

How can i write this query more efficiently?

1 Answers1