Wednesday, July 26, 2006

faster join

-- slower 91.56%
alter PROCEDURE x_Financing_ExceedsDueDate
@NumberOfDays INT
AS
DECLARE @DayToday DATETIME
SET @DayToday = convert(DATETIME,CONvERT(vARCHAR,GETDATE(),112))
SELECT
X.InvoiceNo, X.PmtStructureNo, Fullname = COALESCE(i.Fullname, m.FUllname),D.PayImmediately,
DaysExceed = DATEDIFF(DAY, BillStatementDate, @DayToday)
FROM AccountInstallmentFinancingDetailFinal DINNER JOIN ( SELECT InvoiceNo, PmtStructureNo, PSO = MAX(PaymentSortOrder) FROM AccountInstallmentFinancingDetailFinal GROUP BY InvoiceNo, PmtStructureNo ) XON X.InvoiceNo = D.InvoiceNoAND X.PmtStructureNo = D.PmtStructureNoAND X.PSO = D.PaymentSortOrder
LEFT JOIN AccountInstallment ION I.InvoiceNo = D.InvoiceNo
LEFT JOIN Financing_AssumptionOfMortgage mON m.AoMNo = d.InvoiceNo
WHERE D.BillStatementDate IS NOT NULL AND @DayToday >= BillStatementDate + @NumberOfDays AND PayImmediately > 0

-- faster 8.44%

ALTER PROCEDURE Financing_ExceedsDueDate
@NumberOfDays INT
AS
DECLARE @DayToday DATETIME
SET @DayToday = convert(DATETIME,CONvERT(vARCHAR,GETDATE(),112))
SELECT
X.InvoiceNo, X.PmtStructureNo, A.Fullname,D.PayImmediately,
DaysExceed = DATEDIFF(DAY, BillStatementDate, @DayToday)
FROM AccountInstallmentFinancingDetailFinal DINNER JOIN ( SELECT InvoiceNo, PmtStructureNo, PSO = MAX(PaymentSortOrder) FROM AccountInstallmentFinancingDetailFinal GROUP BY InvoiceNo, PmtStructureNo ) XON X.InvoiceNo = D.InvoiceNoAND X.PmtStructureNo = D.PmtStructureNoAND X.PSO = D.PaymentSortOrder
INNER JOIN ( SELECT InvoiceNo, Fullname FROM AccountInstallment UNION SELECT AoMNo, Fullname FROM Financing_AssumptionOfMortgage ) A
ON X.InvoiceNo = A.InvoiceNo
WHERE D.BillStatementDate IS NOT NULL AND @DayToday >= BillStatementDate + @NumberOfDays AND PayImmediately > 0

4 Comments:

Anonymous Majortotosite Pro said...

I have a hard time describing my thoughts on content, but I really felt I should here. Your article is really great. I like the way you wrote this information Landlord 토토

11:07 AM

 
Anonymous Racesite Pro said...

This is a great article thanks for sharing this informative information. I will visit your blog regularly for some latest post. 경마

11:08 AM

 
Anonymous Oncasinosite Net said...

I have to thank you for the efforts you have put in penning this blog. I really hope to view the same high-grade content by you in the future as well. In fact, your creative writing abilities has inspired me to get my very own blog now 경마사이트

11:08 AM

 
Anonymous Totopick Pro said...

Thank you so much for sharing this great blog. Very inspiring and helpful too. Hope you continue to share more of your ideas.I will definitely love to read. 사설토토

11:09 AM

 

Post a Comment

<< Home