Help in Optimizing a query

Question

Help in Optimizing a query

Meghana

SSC Eights!

Points: 830
More actions
April 26, 2005 at 6:12 am

#362418

Hi
SELECT adi.*
FROM dbo.wrkAccountingDocument ad
JOIN dbo.AccountingDocumentItem adi
        ON ad.AccountingDocumentNbr = adi.AccountingDocumentNbr
AND ad.FiscalYearNbr     = adi.FiscalYearNbr
AND ad.MSGLCompanyCode   = adi.MSGLCompanyCode
The above query is taking 3 hrs to run to fetch 1781130 rows

AccountingDocumentitem table has a clustered index on AccountingDocumentNbr, AccountingDocumentItemNbr, MSGLCompanyCode, FiscalYearNbr
and a non clustered index on AccountingDocumentNbr, MSGLCompanyCode, FiscalYearNbr
when I looked at the execute plan for this query 81% of the cost was because of Clustered index scan on AccountingDocumentitem
Can someone tell me what should I look for to optimize this query
Regards
Meghana

Regards,
Meghana

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

What is dbo.wrkAccountingDocument a VIEW? TABLE? is there an INDEX on ad.AccountingDocumentNbr ?

The NON-CLUSTERED index also may be causing problems because the 1st column is the same 1st column as the CLUSTERED index.

You have no INDEX on FiscalYearNbr so.... The query has no choice but to perform a TABLE SCAN on the CLUSTERED INDEX to find the data you want.

Try adding an INDEX to the FiscalYearNbr OR re-arrange the CLUSTERED INDEX to have that as the 2nd column as see if performance changes

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Does wrkAccountingDocument have any indexes.

Also, can you post the DDL of the table (CREATE statement) so I can see the structure and let me know which columns if any are unique.

As is I would look at making at least this change.

Clustered Index to AccountingDocumentNbr, MSGLCompanyCode, FiscalYearNbr

Non-Clustered Index to AccountingDocumentItemNbr

The reason is that every non-clustered index inherits the data from the clustered index as well. The wider you make your clustered index the wider the others will be.

It is best to keep the Clustered index as narrow as possible and create the other indexes based aroudn the use of those columns as well if your scenario will apply.

Based on your current index strucutre it look like that it does and the changes I suggest should show an improvement possibly but may not fully.

In fact if you afterwards you were to do

SELECT AccountingDocumentNbr, MSGLCompanyCode, FiscalYearNbr FROM dbo.AccountingDocumentItem WHERE AccountingDocumentItemNbr = 12

it would actually use the data from the index itself and not look to the table (this is called a COVERING INDEX).

Now to further enhance your performance you have to keep in mind the wider the data the more time it will take to traverse the network if you are using a remote database, but you will impact your computers memory as well and may find your page file goes nuts once you outclass the physical RAM. Now depending on your machine the IO of the hard drive, the cpu, the ram and if remote the nic will have a big impact on the time it takes to display the data.

Instead of using SELECT adi.* FROM, only call the columns you actually need to work with. This may make no difference for your case or may make a huge difference but it is something you need to keep in mind when working with the data.

Meghana SSC Eights! Points: 830 More actions · Answer 3

yes the wrkaccountingdocument has following non clustered indexes

Idx1 on columns (AccountingDocumentNbr, AccountingDocumentTypeCode, MSGLCompanyCode, FiscalYearNbr, PostingDate)

idx2 on columns (AccountingDocumentNbr, FiscalYearNbr, MSGLCompanyCode)

idx3 on columns (InterfaceReferenceKeyId, InterfaceReferenceCode, AccountingDocumentNbr, FiscalYearNbr, MSGLCompanyCode)

please find the create table statement

CREATE TABLE [AccountingDocumentItem] (

[MSGLCompanyCode] [char] (4) NOT NULL ,

[AccountingDocumentNbr] [varchar] (10) NOT NULL ,

[FiscalYearNbr] [decimal](4, 0) NOT NULL ,

[AccountingDocumentItemNbr] [decimal](3, 0) NOT NULL ,

[ClearingDate] [datetime] NULL ,

[ClearingEntryDate] [datetime] NULL ,

[ClearingAccountingDocumentNbr] [varchar] (10) NULL ,

[PostingKeyCode] [char] (2) NULL ,

[AccountTypeCode] [char] (1) NULL ,

[SpecialGLAccountSelectionCode] [char] (1) NULL ,

[DebitCreditCode] [char] (1) NULL ,

[TaxCode] [char] (2) NULL ,

[LocalCurrencyAmt] [money] NULL ,

[DocumentCurrencyAmt] [money] NULL ,

[LocalCurrencyOrigReductionAmt] [money] NULL ,

[GLUpdatedAmt] [money] NULL ,

[TransactionCurrencyCode] [char] (3) NULL ,

[TaxTypeCode] [char] (1) NULL ,

[AllocationNbr] [varchar] (18) NULL ,

[LineItemText] [varchar] (50) NULL ,

[AccountingTransactionTypeCode] [char] (3) NULL ,

[GLTransactionTypeCode] [varchar] (4) NULL ,

[CostCenterCode] [varchar] (10) NULL ,

[InternalOrderNbr] [char] (12) NULL ,

[BillingDocumentNbr] [varchar] (10) NULL ,

[SalesDocumentNbr] [varchar] (10) NULL ,

[SalesDocumentItemNbr] [decimal](6, 0) NULL ,

[ScheduleItemNbr] [decimal](4, 0) NULL ,

[MSAccount2Code] [char] (10) NULL ,

[CustomerNbr] [varchar] (10) NULL ,

[VendorNbr] [varchar] (10) NULL ,

[DueDateCalculationBaselineDate] [datetime] NULL ,

[PaymentTermsCode] [varchar] (4) NULL ,

[CashDiscountDaysQty] [decimal](3, 0) NULL ,

[NetPaymentTermDayQty] [decimal](3, 0) NULL ,

[CashDiscountPct] [decimal](5, 3) NULL ,

[DocCurrencyCashDiscountAmt] [money] NULL ,

[DocCurrencyEligibleDiscountAmt] [money] NULL ,

[PaymentMethodCode] [char] (1) NULL ,

[NetPaymentAmt] [money] NULL ,

[ProfitCenterCode] [varchar] (10) NULL ,

[ClearingReversedInd] [char] (1) NULL ,

[InvoiceAccountingDocumentNbr] [varchar] (10) NULL ,

[InvoiceFiscalYearNbr] [decimal](4, 0) NULL ,

[InvoiceAccountingDocItemNbr] [decimal](3, 0) NULL ,

[MaterialNbr] [varchar] (18) NULL ,

[PlantCode] [varchar] (4) NULL ,

[MaterialQty] [decimal](13, 3) NULL ,

[PricingUnitQty] [decimal](5, 0) NULL ,

[ValuationAreaCode] [varchar] (4) NULL ,

[ValuationTypeCode] [varchar] (10) NULL ,

[dwDeletedInd] [char] (1) NOT NULL CONSTRAINT [DF__Accountin__dwDel__698C7BFE] DEFAULT ('N'),

[dwDeletedDate] [datetime] NULL ,

[dwInsertDate] [datetime] NULL CONSTRAINT [DF__Accountin__dwIns__6A80A037] DEFAULT (getdate()),

[dwLastModifiedDate] [datetime] NULL CONSTRAINT [DF__Accountin__dwLas__6B74C470] DEFAULT (getdate()),

[ssInsertDate] [datetime] NULL CONSTRAINT [DF__Accountin__ssIns__6C68E8A9] DEFAULT (getdate()),

[ValueDate] [datetime] NULL ,

[TradingPartnerCode] [varchar] (6) NULL ,

CONSTRAINT [PK_AccountingDocumentItem] PRIMARY KEY CLUSTERED

(

[AccountingDocumentNbr],

[AccountingDocumentItemNbr],

[MSGLCompanyCode],

[FiscalYearNbr]

&nbsp WITH FILLFACTOR = 90

)

CREATE TABLE [wrkAccountingDocument] (

[AccountingDocumentNbr] [varchar] (10) NOT NULL ,

[FiscalYearNbr] [decimal](4, 0) NOT NULL ,

[MSGLCompanyCode] [char] (4) NOT NULL ,

[AccountingDocumentTypeCode] [char] (2) NULL ,

[AccountingDocumentDate] [datetime] NULL ,

[PostingDate] [datetime] NULL ,

[FiscalPeriodNbr] [decimal](2, 0) NULL ,

[ReversingAccountingDocumentNbr] [varchar] (10) NULL ,

[ReversingDocumentFiscalYearNbr] [decimal](4, 0) NULL ,

[CurrencyCode] [varchar] (3) NULL ,

[ExchangeRateAmt] [decimal](9, 5) NULL ,

[AccountingDocumentStatusCode] [char] (1) NULL ,

[InterfaceReferenceCode] [varchar] (5) NULL ,

[InterfaceReferenceKeyId] [varchar] (20) NULL ,

[LocalCurrencyCode] [varchar] (3) NULL ,

[FlaggedForReversalInd] [char] (1) NULL ,

[CreatedDtTm] [datetime] NULL ,

[CreatedByUserName] [varchar] (12) NULL

)

GO

other indexes on AccountingDocumentitem

idxa - BillingDocumentNbr, MSAccount2Code

idxb- AccountingDocumentNbr, AccountingDocumentItemNbr, MSGLCompanyCode, FiscalYearNbr

idxc - ClearingAccountingDocumentNbr

idxd - CustomerNbr, AccountingDocumentNbr, AccountingDocumentItemNbr, FiscalYearNbr, MSGLCompanyCode

idxe - AccountingDocumentNbr, FiscalYearNbr, MSGLCompanyCode

p.S: I was too lazy to list down all the columns in select statement ..so mentioned adi.*

actually in my query I have all those columns named

Appreciate your help

-Meghana

Regards,

Meghana

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Based on what I see this might help.

Consider change the indexes on AccountingDocumentitem to

Clustered Index

AccountingDocumentNbr, FiscalYearNbr, MSGLCompanyCode

Non-Clustered Indexes

1) BillingDocumentNbr, MSAccount2Code

2) ClearingAccountingDocumentNbr

3) CustomerNbr

4) AccountingDocumentItemNbr

Remember your clustered index is effectively included in every non-clustered index so there is no need to repeat the columns.

Next you should make the most unique column the first one in the index when it is a composite index.

On wrkAccountingDocument consider these indexes

Non-Clustered Indexes

1) AccountingDocumentNbr, FiscalYearNbr, MSGLCompanyCode

2) AccountingDocumentTypeCode, PostingDate

3) InterfaceReferenceKeyId, InterfaceReferenceCode

Again same rules for composite indecies, put the most unique first.

Then as a follow-up read about Index Intersection and it will explain where I am thinking this should go.

http://www.sql-server-performance.com/nb_index_intersection.asp

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx

Meghana SSC Eights! Points: 830 More actions · Answer 5

Thanks your suggestions helped in optimizing many other queries

Regards,

Meghana

Michael Du Bois SSCertifiable Points: 5143 More actions · Answer 6

Meghana, How much of an inprovement did you see after making the changes?

Thanks

Mike

Antares686 SSC Guru Points: 125444 More actions · Answer 7

Antares686

SSC Guru

Points: 125444

April 28, 2005 at 6:16 am

#555491

I would be curious as well.

Meghana SSC Eights! Points: 830 More actions · Answer 8

It came down to 12 minutes for the query i took example of

Actually this code was written way back in year 98. I am actually asked to tune the queries.

Can you let me know of some sites I can read about how to fine tune the queries

Appreciate all the help

Regards

Meghana

Regards,

Meghana

Antares686 SSC Guru Points: 125444 More actions · Answer 9

Just FYI the index changes may have had some impact but that much seems like it was possible more of a maintance issue. The problem is you just never know fo sure.

This site has several articles.

http://www.sql-server-perfomance.com

an google for the enough espeically using site:microsoft.com to dig around.

At this point thou you will want to look at your execution plans of your queries to see what the engine is doing.