-1

My query is taking 2 hours to get 2.5 million rows from the database. I have checked the execution plan also. Indexing is also working fine.

This is the query:

SELECT 
    GeneralJournalAccountEntry.RecId as [Transaction #],
    GENERALJOURNALACCOUNTENTRY.LEDGERACCOUNT AS [LEDGER WITH DIMENSIONS],
    DIMENSIONATTRIBUTE.NAME AS [DIMENSION TYPE],
    DIMENSIONATTRIBUTELEVELVALUE.DISPLAYVALUE as [DIMENSION VALUE]
FROM 
    DIMENSIONATTRIBUTE
INNER JOIN 
    DIMENSIONATTRIBUTEVALUE ON DIMENSIONATTRIBUTE.RECID = DIMENSIONATTRIBUTEVALUE.DIMENSIONATTRIBUTE
INNER JOIN 
    DIMENSIONATTRIBUTELEVELVALUE ON DIMENSIONATTRIBUTEVALUE.RECID = DIMENSIONATTRIBUTELEVELVALUE.DIMENSIONATTRIBUTEVALUE
INNER JOIN 
    DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION ON DIMENSIONATTRIBUTELEVELVALUE.DIMENSIONATTRIBUTEVALUEGROUP = DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION.DIMENSIONATTRIBUTEVALUEGROUP
INNER JOIN 
    GENERALJOURNALACCOUNTENTRY ON DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION.DIMENSIONATTRIBUTEVALUECOMBINATION = GENERALJOURNALACCOUNTENTRY.LEDGERDIMENSION
INNER JOIN 
    GENERALJOURNALENTRY ON GENERALJOURNALENTRY.PARTITION = GENERALJOURNALACCOUNTENTRY.PARTITION AND GENERALJOURNALENTRY.RECID = GENERALJOURNALACCOUNTENTRY.GENERALJOURNALENTRY
WHERE
    DIMENSIONATTRIBUTE.NAME <> 'MainAccount'
Salman A
  • 262,204
  • 82
  • 430
  • 521

1 Answers1

-1

Try This May Help You

If the query never completes, you must be getting the estimated execution plan, and not the actual. Since it has one row, you either have the slowest computer may be:

Use WITH (NOLOCK)

Nits Patel
  • 380
  • 3
  • 15
  • What does `WITH (NOLOCK)` do? – Salman A Oct 10 '19 at 12:41
  • @SalmanA i think it seems: When you use the NOLOCK query hint you are telling the storage engine that you want to access the data no matter if the data is locked by another process or not. – Nits Patel Oct 10 '19 at 12:54
  • 1
    NOLOCK equates to SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED and it's usually a very bad idea unless you understood exactly what you're getting. A dirty read, which is possible from this, means you can obtain a value that never existed according to ACID. – Dave Brown Oct 10 '19 at 13:48