Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 980
  • Last Modified:

T-SQL: How to Make a View Run Faster

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
TBSupport
Asked:
TBSupport
  • 6
  • 5
  • 3
  • +1
1 Solution
 
PortletPaulCommented:
can you provide an explain plan (.sqlplan file) for that query?
0
 
Scott PletcherSenior DBACommented:
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
 
TBSupportAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Scott PletcherSenior DBACommented:
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
 
TBSupportAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
>> 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
 
TBSupportAuthor Commented:
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
 
PortletPaulCommented:
try 2:
can you provide an explain plan (.sqlplan file) for that query?
0
 
TBSupportAuthor Commented:
How do I do that?
0
 
Anthony PerkinsCommented:
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
 
PortletPaulCommented:
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
 
TBSupportAuthor Commented:
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
 
PortletPaulCommented:
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
 
TBSupportAuthor Commented:
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
 
PortletPaulCommented:
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 6
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now