July 10, 2007 at 4:30 am
Hi,
I have written the following select statement, which returns data within about 30 seconds.
SELECT
R.[Creation Date], E.[Posting Date], E.[G_L Account No_],
CASE E.[Document Type] WHEN 0 THEN 'Unknown' WHEN 1 THEN 'Payment' WHEN 2 THEN 'Invoice' WHEN 3 THEN 'Credit Memo' WHEN 4 THEN 'Finance Charge Memo'
WHEN 5 THEN 'Reminder' WHEN 6 THEN 'Refund' ELSE 'Error' END AS [Document Type], E.Description,
CASE E.[Source Type] WHEN 0 THEN 'Unknown' WHEN 1 THEN 'Customer' WHEN 2 THEN 'Vendor' WHEN 3 THEN 'Bank Account' WHEN 4 THEN 'Fixed Asset'
ELSE 'Error' END AS [Source Type], MAX(CASE LED.[Dimension Code] WHEN 'CENTRE' THEN LED.[Dimension Value Code] ELSE '' END) AS centre_code,
MAX(CASE LED.[Dimension Code] WHEN 'PRODUCT' THEN LED.[Dimension Value Code] ELSE '' END) AS product_code,
MAX(CASE LED.[Dimension Code] WHEN 'SITTING' THEN LED.[Dimension Value Code] ELSE '' END) AS sitting_code,
MAX(CASE LED.[Dimension Code] WHEN 'COURSE TYPE' THEN LED.[Dimension Value Code] ELSE '' END) AS course_type_code, SUM(E.Amount)
AS Amount
FROM
[G_L Register] AS R INNER JOIN
[G_L Entry]
AS E ON E.[Entry No_] BETWEEN R.[From Entry No_] AND R.[To Entry No_] INNER JOIN
[Ledger Entry Dimension]
AS LED ON
E
.[Entry No_] = LED.[Entry No_]
GROUP
BY E.[G_L Account No_], E.[Document Type], E.[Document No_], E.[Source Type], E.[Entry No_],
LED
.[Table ID], R.No_, R.[Creation Date], E.[Posting Date], E.Description
HAVING
(LED.[Table ID] = 17) AND (R.[Creation Date] = @Creationdate) AND (SUM(E.Amount) <> 0)
ORDER
BY E.[G_L Account No_]
However...I have encapsulated the select in a stored procedure and it now takes around 38 mins for any data to come back.
Any ideas why this may be?
Thanks
July 10, 2007 at 4:48 am
July 10, 2007 at 4:55 am
The execution plan shows an 18% cost on the join, a 47% cost on the clustered index scan on one table and a 22% cost on another clutered index scan. This is the same for the select statement on its own and the stored procedure which doesn't really explain why one takes over 30 mins longer.
Will using a custom plan guide help?
July 10, 2007 at 7:53 am
Hi David,
As for your latest question, a custom plan might help, but it most likely won't. I see a 3 table join some where clause filtering, group and ordering. Here are some things I would try.
1) Turn STATISTICS IO/TIME ON while doing this. You will get the number of scans, reads and access time for the tables. That should help seeing what is really taking all of the time.
2) Start slowly simplifying your query to narrow down the problem. i.e. comment out your order by, group by and summaries. Does it still take > 30 min? If so, look at your where clause filtering. How selective are your filters? If 1 or more is pretty selective, consider adding an index or 2 and testing. (Just for testing, overall system load could be impeded by overindexing) Also, try getting a merge join between E and LED by choice of p-keys, etc. (Requirements are both tables are ordered on the same column(s) and one needs to be unique on that/those column(s))
3) If one of those tables is really huge, and you have control over physical structure, consider table partitioning. SQL '05 has made it a lot easier to implement!
4) Pull the scalar clauses out of the having clause. They should be in the where, even if it works either way. Having them seperated will make it easier to try different things with filtering and joins.
Overall, I think if you play with the query and the indexes, there's a quick solution out there somewhere!
Thanks,
Eric
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply