reza_ali202000
چهارشنبه 06 خرداد 1394, 00:44 صبح
سلام دوستان یه کوئری دارم که میاد تمام فاکتورها رو میخونه و بعد میاد تمام پرداخت دریافت ها رو میخونه مال هر مشتری مشخص میکنه که بدهکار هس یا بستانکار با مبلغش جلوش مینویسه.
خوب این کوئری برای واکشی 1میلیون رکورد حدود 11 ثانیه طول میکشه. دوستان نظری ندارن که بتونم این مقدار زمان رو کاهش بدم؟
SELECT COALESCE (SUM(CONVERT(decimal, Price)) - COALESCE
((SELECT SUM(CONVERT(decimal, Price)) AS Expr1
FROM FinancialTransaction
WHERE (PersonCode = @cod) AND (Pay = 'True') OR
(Pay = 'True') AND (Entrust = @cod)), 0), 0) +
(SELECT COALESCE (SUM(CONVERT(decimal, InvoiceDetails_1.Price) * InvoiceDetails_1.Count) -
(SELECT COALESCE (SUM(CONVERT(decimal, COALESCE (Discount, 0))), 0) AS Expr1
FROM Invoice
WHERE (PersonCode = @cod) AND (Type = 'K')), 0) -
(SELECT COALESCE (SUM(CONVERT(decimal, InvoiceDetails_1.Price) * InvoiceDetails_1.Count) -
(SELECT COALESCE (SUM(CONVERT(decimal, COALESCE (Discount, 0))), 0) AS Expr1
FROM Invoice AS Invoice_2
WHERE (PersonCode = @cod) AND (Type = 'F')), 0) AS Expr1
FROM Invoice AS Invoice_1 INNER JOIN
InvoiceDetails AS InvoiceDetails_1 ON Invoice_1.Code = InvoiceDetails_1.InvoiceCode
WHERE (Invoice_1.PersonCode = @cod) AND (Invoice_1.Type = 'F')) AS Expr1
FROM Invoice AS Invoice_1 INNER JOIN
InvoiceDetails AS InvoiceDetails_1 ON Invoice_1.Code = InvoiceDetails_1.InvoiceCode
WHERE (Invoice_1.PersonCode = @cod) AND (Invoice_1.Type = 'K')) AS Expr1
FROM FinancialTransaction AS FinancialTransaction_1
WHERE (PersonCode = @cod) AND (Pay = 'False') OR
(Pay = 'False') AND (Entrust = @cod)
خوب این کوئری برای واکشی 1میلیون رکورد حدود 11 ثانیه طول میکشه. دوستان نظری ندارن که بتونم این مقدار زمان رو کاهش بدم؟
SELECT COALESCE (SUM(CONVERT(decimal, Price)) - COALESCE
((SELECT SUM(CONVERT(decimal, Price)) AS Expr1
FROM FinancialTransaction
WHERE (PersonCode = @cod) AND (Pay = 'True') OR
(Pay = 'True') AND (Entrust = @cod)), 0), 0) +
(SELECT COALESCE (SUM(CONVERT(decimal, InvoiceDetails_1.Price) * InvoiceDetails_1.Count) -
(SELECT COALESCE (SUM(CONVERT(decimal, COALESCE (Discount, 0))), 0) AS Expr1
FROM Invoice
WHERE (PersonCode = @cod) AND (Type = 'K')), 0) -
(SELECT COALESCE (SUM(CONVERT(decimal, InvoiceDetails_1.Price) * InvoiceDetails_1.Count) -
(SELECT COALESCE (SUM(CONVERT(decimal, COALESCE (Discount, 0))), 0) AS Expr1
FROM Invoice AS Invoice_2
WHERE (PersonCode = @cod) AND (Type = 'F')), 0) AS Expr1
FROM Invoice AS Invoice_1 INNER JOIN
InvoiceDetails AS InvoiceDetails_1 ON Invoice_1.Code = InvoiceDetails_1.InvoiceCode
WHERE (Invoice_1.PersonCode = @cod) AND (Invoice_1.Type = 'F')) AS Expr1
FROM Invoice AS Invoice_1 INNER JOIN
InvoiceDetails AS InvoiceDetails_1 ON Invoice_1.Code = InvoiceDetails_1.InvoiceCode
WHERE (Invoice_1.PersonCode = @cod) AND (Invoice_1.Type = 'K')) AS Expr1
FROM FinancialTransaction AS FinancialTransaction_1
WHERE (PersonCode = @cod) AND (Pay = 'False') OR
(Pay = 'False') AND (Entrust = @cod)