akessel
asked on
Performance on Sum / Group By in a sql expression
I have a Sum/Group By query - without the sum, it takes about 4 seconds to execute in query analyzer, with the sum it takes 23 seconds (and I assume will get far worse as the date range increases).
Can anyone help me figure out if there is a better way to construct this query to improve the performance of the "SUM"
Code
select T_3.[ACCOUNTNUMBER],T_3.[D ESCRIPTION ],Sum(T_1. AMOUNT)
From DBO.GL7TRANSACTIONS AS GL7TRANSACTIONS
LEFT OUTER JOIN DBO.GL7TRANSACTIONDISTRIBU TIONS AS T_1 ON GL7TRANSACTIONS.GL7TRANSAC TIONSID = T_1.GL7TRANSACTIONSID
LEFT OUTER JOIN DBO.tf_GL7SECUREPROJECTS(1 , 1, 0) AS T_2 ON T_1.GL7PROJECTSID = T_2.GL7PROJECTSID
LEFT OUTER JOIN DBO.tf_GL7SECUREACCOUNTS(1 , 1, 0) AS T_3 ON GL7TRANSACTIONS.GL7ACCOUNT SID = T_3.GL7ACCOUNTSID
LEFT OUTER JOIN DBO.GL7ACCOUNTCODES AS T_4 ON T_3.GL7ACCOUNTCODESID = T_4.GL7ACCOUNTCODESID
INNER JOIN DBO.GL7BATCHES AS T_5 ON GL7TRANSACTIONS.GL7BATCHES ID = T_5.GL7BATCHESID
Where ((GL7TRANSACTIONS.[POSTDAT E] BETWEEN '2010-06-01' AND '2010-07-31 23:59:59.997'
AND T_2.[UNMASKEDPROJECTID] = 'NYihrc'
AND (T_4.ACCOUNTCODE) like '6%')
AND GL7TRANSACTIONS.[GL7GENERA LINFOID] = 1
AND T_5.[STATUS] NOT IN (8,9))
group by T_3.[Accountnumber],T_3.[D escription ]
Can anyone help me figure out if there is a better way to construct this query to improve the performance of the "SUM"
Code
select T_3.[ACCOUNTNUMBER],T_3.[D
From DBO.GL7TRANSACTIONS AS GL7TRANSACTIONS
LEFT OUTER JOIN DBO.GL7TRANSACTIONDISTRIBU
LEFT OUTER JOIN DBO.tf_GL7SECUREPROJECTS(1
LEFT OUTER JOIN DBO.tf_GL7SECUREACCOUNTS(1
LEFT OUTER JOIN DBO.GL7ACCOUNTCODES AS T_4 ON T_3.GL7ACCOUNTCODESID = T_4.GL7ACCOUNTCODESID
INNER JOIN DBO.GL7BATCHES AS T_5 ON GL7TRANSACTIONS.GL7BATCHES
Where ((GL7TRANSACTIONS.[POSTDAT
AND T_2.[UNMASKEDPROJECTID] = 'NYihrc'
AND (T_4.ACCOUNTCODE) like '6%')
AND GL7TRANSACTIONS.[GL7GENERA
AND T_5.[STATUS] NOT IN (8,9))
group by T_3.[Accountnumber],T_3.[D
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
akessel,
Have you tried using the table directly and are you able to see some good performance on that..
Have you tried using the table directly and are you able to see some good performance on that..
ASKER
Both commens suggesting using the table directly were on the right track, when I took that approach, which I was able to do easily, it cut execution speed 60%.
What the heck is a table valued function, anyway?
What the heck is a table valued function, anyway?
>>What the heck is a table valued function, anyway?<<
a Table-valued function, is just a function that returns a table as a result. Think of it as a view with steroids (because you can do more things in a function than in a view). More info here.
http://msdn.microsoft.com/en-us/library/ms191165.aspx
a Table-valued function, is just a function that returns a table as a result. Think of it as a view with steroids (because you can do more things in a function than in a view). More info here.
http://msdn.microsoft.com/en-us/library/ms191165.aspx
ASKER
Ah ... I see them out there .. these in particular were designed to hide availability of some data from some users who do input, not necessary for the purpose I am using them for since it is read-only.
ASKER
LEFT OUTER JOIN DBO.tf_GL7SECUREPROJECTS(1
by using the GL7PROJECTTSID directly in the where clasue, and that seems to have made a huge difference.
Frankly I'm not to sure what's going on in the tf.dbo tables but its a complex database, with someone limited visibility into what is going on. I'm just playing it safe and doing it the way they do it in their query tools.
I will take a look at DBO.tf_GL7SECUREACCOUNTS(1