Solved

Performance on Sum / Group By in a sql expression

Posted on 2010-08-27
7
382 Views
Last Modified: 2012-05-10
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.[DESCRIPTION],Sum(T_1.AMOUNT)

From DBO.GL7TRANSACTIONS AS GL7TRANSACTIONS
LEFT OUTER JOIN DBO.GL7TRANSACTIONDISTRIBUTIONS AS T_1 ON GL7TRANSACTIONS.GL7TRANSACTIONSID = 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.GL7ACCOUNTSID = 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.GL7BATCHESID = T_5.GL7BATCHESID

Where ((GL7TRANSACTIONS.[POSTDATE] 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.[GL7GENERALINFOID]  =  1
      AND T_5.[STATUS] NOT IN (8,9))

group by T_3.[Accountnumber],T_3.[Description]
0
Comment
Question by:akessel
  • 3
  • 2
  • 2
7 Comments
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 150 total points
Comment Utility
Your query can't be tuned further..
Probably you can try rewriting those two table valued functions into a single one for better performance.
And the proper construct would be
select T_3.[ACCOUNTNUMBER],T_3.[DESCRIPTION],Sum(T_1.AMOUNT)
From DBO.GL7TRANSACTIONS AS GL7TRANSACTIONS
INNER JOIN DBO.GL7BATCHES AS T_5 ON GL7TRANSACTIONS.GL7BATCHESID = T_5.GL7BATCHESID
LEFT OUTER JOIN DBO.GL7TRANSACTIONDISTRIBUTIONS AS T_1 ON GL7TRANSACTIONS.GL7TRANSACTIONSID = 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.GL7ACCOUNTSID = T_3.GL7ACCOUNTSID
LEFT OUTER JOIN DBO.GL7ACCOUNTCODES AS T_4 ON T_3.GL7ACCOUNTCODESID = T_4.GL7ACCOUNTCODESID
Where ((GL7TRANSACTIONS.[POSTDATE] 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.[GL7GENERALINFOID]  =  1
      AND T_5.[STATUS] NOT IN (8,9))
group by T_3.[Accountnumber],T_3.[Description]

Open in new window

0
 
LVL 41

Accepted Solution

by:
ralmada earned 350 total points
Comment Utility
Why you need to join to those functions? can you just join to the tables directly? that will certainly improve your performance. That will also make you be able to take advantage of indexes that you need to create for the columns you have in your joins and the where and group by clauses.
0
 

Author Comment

by:akessel
Comment Utility
I am using as a base, sql that is generated from a tool the vendor supplies in their built in querying tool - in their client sofware.  But ....  I did extricate the query from this join:

LEFT OUTER JOIN DBO.tf_GL7SECUREPROJECTS(1, 1, 0) AS T_2 ON T_1.GL7PROJECTSID = T_2.GL7PROJECTSID

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, 1, 0) and see if there is a straight table that gives me the same info - I only need the account number and description and the middle segment of the account description to execute the query and get the results I need, you would think I wouldn't need a derived table, but ...
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
akessel,

Have you tried using the table directly and are you able to see some good performance on that..
0
 

Author Closing Comment

by:akessel
Comment Utility
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?
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
>>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
0
 

Author Comment

by:akessel
Comment Utility
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.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now