Solved

T-SQL:  How to Make a View Run Faster

Posted on 2014-02-24
15
901 Views
Last Modified: 2014-02-28
Hello:

Other than changing the view into a stored procedure, is there anything that can be done to make a view run faster?  Below is my query in my view.

Thanks!

TBSupport

SELECT AAG30000.JRNENTRY as [Journal Entry], AAG30000.aaTRXSource as [AA Transaction Source], AAG30001.DEBITAMT as [Debit Amount], AAG30001.CRDTAMNT as [Credit Amount],
GL00105.ACTNUMST as [Account Number], GL00100.ACTDESCR as [Account Description], AAG00401.aaTrxDimCode as [Transaction Dimension Code], AAG00401.aaTrxDimCodeDescr as [Transaction Dimension Code Description],
GL20000.ORMSTRNM as [Originating Master Name], GL20000.TRXDATE as [Transaction Date], GL20000_1.DSCRIPTN as [Transaction Description]
FROM AAG30003
INNER JOIN AAG30000 on
AAG30000.aaGLHdrID =  AAG30003.aaGLHdrID
 INNER JOIN AAG30001
 on
 AAG30000.aaGLHdrID =  AAG30001.aaGLHdrID
  INNER JOIN GL00100
 on
 AAG30001.ACTINDX =  GL00100.ACTINDX
   INNER JOIN GL00105
 on
 GL00100.ACTINDX =  GL00105.ACTINDX
 INNER JOIN AAG00401
 on
 AAG30003.aaTrxDimID =  AAG00401.aaTrxDimID and
 AAG30003.aaTrxCodeID = AAG00401.aaTrxDimCodeID
 INNER JOIN GL20000 ON
 AAG30000.JRNENTRY = GL20000.JRNENTRY
 INNER JOIN GL20000 AS GL20000_1
 ON AAG30001.ACTINDX = GL20000_1.ACTINDX
where GL20000_1.DSCRIPTN <> ''
GROUP BY AAG30001.aaGLDistID, AAG30000.JRNENTRY, AAG30000.aaTRXSource, AAG30001.DEBITAMT, AAG30001.CRDTAMNT, GL00105.ACTNUMST, GL00100.ACTDESCR, AAG00401.aaTrxDimCode, AAG00401.aaTrxDimCodeDescr,
GL20000.ORMSTRNM, GL20000.TRXDATE, GL20000_1.DSCRIPTN
UNION ALL
SELECT AAG40000.JRNENTRY as [Journal Entry], AAG40000.aaTRXSource as [AA Transaction Source],
 AAG40001.DEBITAMT as [Debit Amount], AAG40001.CRDTAMNT as [Credit Amount],
GL00105.ACTNUMST as [Account Number], GL00100.ACTDESCR as [Account Description], AAG00401.aaTrxDimCode as [Transaction Dimension Code], AAG00401.aaTrxDimCodeDescr as [Transaction Dimension Code Description],
GL30000.ORMSTRNM as [Originating Master Name], GL30000.TRXDATE as [Transaction Date], GL30000_1.DSCRIPTN as [Transaction Description]
FROM AAG40003
INNER JOIN AAG40000 on
AAG40000.aaGLHdrID =  AAG40003.aaGLHdrID
 INNER JOIN AAG40001
 on
 AAG40000.aaGLHdrID =  AAG40001.aaGLHdrID
  INNER JOIN GL00100
 on
 AAG40001.ACTINDX =  GL00100.ACTINDX
   INNER JOIN GL00105
 on
 GL00100.ACTINDX =  GL00105.ACTINDX
 INNER JOIN AAG00401
 on
 AAG40003.aaTrxDimID =  AAG00401.aaTrxDimID and
 AAG40003.aaTrxCodeID = AAG00401.aaTrxDimCodeID
 INNER JOIN GL30000 ON
 AAG40000.JRNENTRY = GL30000.JRNENTRY
  INNER JOIN GL30000 AS GL30000_1
 ON AAG40001.ACTINDX = GL30000_1.ACTINDX
 where GL30000_1.DSCRIPTN <> ''
GROUP BY AAG40001.aaGLDistID, AAG40000.JRNENTRY, AAG40000.aaTRXSource,
AAG40001.DEBITAMT, AAG40001.CRDTAMNT, GL00105.ACTNUMST, GL00100.ACTDESCR, AAG00401.aaTrxDimCode, AAG00401.aaTrxDimCodeDescr,
GL30000.ORMSTRNM, GL30000.TRXDATE, GL30000_1.DSCRIPTN
0
Comment
Question by:TBSupport
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39884334
can you provide an explain plan (.sqlplan file) for that query?
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39884464
You've got to get rid of those huge GROUP BY lists.  Grouping on that many columns of all those data types will never perform well.

The only reason you would really need such a massive GROUP BY is if the JOINs are missing elements and causing duplicate rows, which you use the GROUP BY to "hide".
0
 
LVL 1

Author Comment

by:TBSupport
ID: 39884536
The only problem with not using the GROUP BYs is that, if I don't, SQL throws an error saying that I have to use GROUP BYs or use some sort of aggregate.  SQL doesn't leave me much of a choice.

And, I do need to use a GROUP BY on the first field that I have grouped.

If you know of a way to get rid of the other GROUP BYs, though, I'm game.

Thanks!

TBSupport
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39885734
Odd, I don't see any aggregate function, like SUM() or COUNT().  Thus I'm not sure why SQL would insist on a GROUP BY for that query.
0
 
LVL 1

Author Comment

by:TBSupport
ID: 39885749
That's the thing.  Since there's no aggregates, SQL demands a GROUP BY.

Does anyone have any ideas?  I'm on a tight deadline and am at my wit's end on this.

Thanks!

TBSupport
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39885756
>> That's the thing.  Since there's no aggregates, SQL demands a GROUP BY. <<

But it should be the other way around.  SQL should require a GROUP BY only if there are aggregates in the query.
0
 
LVL 1

Author Comment

by:TBSupport
ID: 39886872
Whatever.  The bottom line is that I need this to go faster.  

Honestly, I think that the problem is with the GL20000 table.  Without that table, the query runs faster.  But, I need that table--period.

TBSupport
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39887197
try 2:
can you provide an explain plan (.sqlplan file) for that query?
0
 
LVL 1

Author Comment

by:TBSupport
ID: 39887332
How do I do that?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39887570
Whatever.  The bottom line is that I need this to go faster.  
Then as Scott has patiently pointed out to you, you need to remove the GROUP BY clause.  If you are not prepared to do that, for whatever reason, then you are probably going to be SOL.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39887869
for Explain Plans please refer to:

XML Showplans
http://technet.microsoft.com/en-us/library/ms189298(v=sql.105).aspx

Displaying Execution Plans by Using the Showplan SET Options (Transact-SQL)
http://technet.microsoft.com/en-us/library/ms180765(v=sql.105).aspx
0
 
LVL 1

Author Comment

by:TBSupport
ID: 39889719
Attached is my Execution Plan.  Now, in order to get the query to run and, therefore, generate this plan, I had to put in an additional piece of criteria in each where statement of each UNION ALL clause.

The additional criteria is "and AAG30000.JRNENTRY = 'a journal entry number'".  If I hadn't done this, I would have had to wait literally days for the query to finish processing.  That's why it's important for me to find a way to speed this up without having to drop the GROUP BY clause.

Thanks!

TBSupport
AA.sqlplan
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39890629
Thanks for the execution plan but you are substantially altering the plan by including these I fear
  and AAG30000.JRNENTRY = '80125'
  and AAG40000.JRNENTRY = '80103'

for the plan provided:

on the table GL2000 there's the index [CL8GL20000]  that covers DSCRIPTN and this used to meet
GL20000_1.DSCRIPTN <> ''

It seems no similar index exists on table GL3000 and the most expensive operation is a RID lookup for
GL30000_1.DSCRIPTN <> ''

---------
Regardless of this plan, you do need to start looking at why you currently need to use group by...

Presumably you have done this to remove "unwanted repetition" in the results (i.e. to get a distinct result). What I would be doing is "starting small" then progressively adding joins until I identify where the data repetition commences. Once you know that then you can improve the query - we can't do this for you as we don't have the tables and data.

-- starting small
select
  count(*)
, count(distinct AAG30000.JRNENTRY)
FROM AAG30003
      INNER JOIN AAG30000
            ON AAG30000.aaGLHdrID = AAG30003.aaGLHdrID

-- progressively add joins
select
  count(*)
, count(distinct AAG30000.JRNENTRY)
FROM AAG30003
      INNER JOIN AAG30000
            ON AAG30000.aaGLHdrID = AAG30003.aaGLHdrID
      INNER JOIN AAG30001
            ON AAG30000.aaGLHdrID = AAG30001.aaGLHdrID

watch for big jumps in the count(*) result, that's probably the cause of data being repeated.
0
 
LVL 1

Author Comment

by:TBSupport
ID: 39890671
I need a GROUP BY on the first field that I listed in the clause.  I don't need it on the rest.  Is there a way to accomplish that, per chance?
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39890694
Yes*, by using aggregate functions on all other columns.

But this may simply make the performance situation worse (or no better than it is now) and potentially also provide misleading data.

The real solution is to understand where the repetition comes from - there is no silver bullet for this. You just have to understand your table structures.


* e.g.
/* but I would not recommend you actually do this!! */
SELECT
      AAG30000.JRNENTRY          AS [Journal Entry]
    , max(AAG30000.aaTRXSource)       AS [AA Transaction Source]
    , max(AAG30001.DEBITAMT)          AS [Debit Amount]
    , max(AAG30001.CRDTAMNT)          AS [Credit Amount]
    , max(GL00105.ACTNUMST)           AS [Account Number]
    , max(GL00100.ACTDESCR)           AS [Account Description]
    , max(AAG00401.aaTrxDimCode)      AS [Transaction Dimension Code]
    , max(AAG00401.aaTrxDimCodeDescr) AS [Transaction Dimension Code Description]
    , max(GL20000.ORMSTRNM)           AS [Originating Master Name]
    , max(GL20000.TRXDATE)            AS [Transaction Date]
    , max(GL20000_1.DSCRIPTN)         AS [Transaction Description]
FROM AAG30003
      INNER JOIN AAG30000
            ON AAG30000.aaGLHdrID = AAG30003.aaGLHdrID
      INNER JOIN AAG30001
            ON AAG30000.aaGLHdrID = AAG30001.aaGLHdrID
      INNER JOIN GL00100
            ON AAG30001.ACTINDX = GL00100.ACTINDX
      INNER JOIN GL00105
            ON GL00100.ACTINDX = GL00105.ACTINDX
      INNER JOIN AAG00401
            ON AAG30003.aaTrxDimID = AAG00401.aaTrxDimID
                  AND AAG30003.aaTrxCodeID = AAG00401.aaTrxDimCodeID
      INNER JOIN GL20000
            ON AAG30000.JRNENTRY = GL20000.JRNENTRY
      INNER JOIN GL20000 AS GL20000_1
            ON AAG30001.ACTINDX = GL20000_1.ACTINDX
WHERE GL20000_1.DSCRIPTN <> ''
      AND AAG30000.JRNENTRY = '80125'
GROUP BY
      AAG30000.JRNENTRY

Open in new window

0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

705 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

18 Experts available now in Live!

Get 1:1 Help Now