Adding indexes to speed up your Voucher Detail Report

I was working with a fellow business analyst today, and we were researching why some of our voucher detail reports were taking over 13 minutes to complete.
We took the right approach by gathering as much information as possible before attempting to fix the problem.

1)We started by profiling the database to capture the sql generated by the report.

2)We also ran PSSDiag while the report was running to collect information about the state of the SQL Server while the report was run.

3)Next we ran a Performance Analysis log on the PSSDiag output and also ran the SQL Server RML utilities against the trace files that were generated.

As you might have guessed, none of the steps taken above raised any red flags.

So, we took a look at the SQL that was captured by the profiler during the report creation. We parsed up the SQL and ran it manually within SQL Management Studio.

Initially, we were startled by how large the SQL statement was. After running it in Management studio, we were then concerned about the statement taking well over 13 minutes to produce a small number of rows.

We took the same SQL statement we ran it management studio and processed it through the Database Tuning Advisor and found that several indexes and statistics were missing and were recommended based on our sql statement.

I want to point out that the all of recommendations made by the database tuning advisor are highly dependent upon the options we used to run the voucher detail report and our configuration of our vision system. These same indexes may do nothing for you.

My goal of this post was to at least show the handful of indexes that reduced our query time from 13 minutes down to under 10 seconds. As I mentioned before, these queries may not work for your environment and may make reports run slower. As always, please test anything I am doing on this site on a development server and ensure proper testing before placing anything on a production environment.

Below are the indexes. Maybe you will consider something similar if you are having performance problems with your voucher detail reports.

CREATE NONCLUSTERED INDEX [NC_IDX_LedgerAP_CASH_REQ_1] ON [dbo].[LedgerAP]
(
[Voucher] ASC,
[Vendor] ASC,
[Line] ASC,
[WBS1] ASC,
[WBS2] ASC,
[WBS3] ASC,
[TransType] ASC,
[SubType] ASC,
[Period] ASC
)
INCLUDE ( [Account],
[Desc2],
[PartialPayment],
[Discount],
[BilledWBS1],
[BilledWBS2],
[BilledWBS3],
[BilledInvoice],
[TaxAmount],
[TransactionAmount],
[AmountSourceCurrency]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE NONCLUSTERED INDEX [NC_IDX_LedgerAP_CASH_REQ_2] ON [dbo].[LedgerAP]
(
[Voucher] ASC,
[Vendor] ASC,
[Line] ASC,
[TransType] ASC,
[WBS1] ASC,
[WBS2] ASC,
[WBS3] ASC,
[SubType] ASC,
[Period] ASC
)
INCLUDE ( [Account],
[Desc2],
[PartialPayment],
[Discount],
[BilledWBS1],
[BilledWBS2],
[BilledWBS3],
[BilledInvoice],
[TaxAmount],
[TransactionAmount],
[AmountSourceCurrency]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
go

CREATE NONCLUSTERED INDEX [NC_IDX_LEDGERAP_CASH_REQ_3] ON [dbo].[LedgerAR]
(
[AutoEntry] ASC,
[Invoice] ASC,
[WBS1] ASC,
[WBS2] ASC,
[WBS3] ASC,
[TransType] ASC,
[SubType] ASC
)
INCLUDE ( [AmountSourceCurrency]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
go

CREATE NONCLUSTERED INDEX [NC_IDX_LEDGERAR_CASH_REQ_1] ON [dbo].[LedgerAR]
(
[WBS1] ASC,
[Invoice] ASC,
[TransType] ASC,
[SubType] ASC,
[AutoEntry] ASC
)
INCLUDE ( [AmountSourceCurrency]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
go

CREATE NONCLUSTERED INDEX [NC_IDX_VO_CASH_REQ_1] ON [dbo].[VO]
(
[PaidPeriod] ASC,
[LiabCode] ASC,
[PayTerms] ASC,
[PaymentDate] ASC,
[Company] ASC,
[Vendor] ASC,
[Voucher] ASC,
[TransDate] ASC,
[BankCode] ASC,
[Invoice] ASC,
[InvoiceDate] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
go

CREATE NONCLUSTERED INDEX [NC_IDX_VO_CASH_REQ_2] ON [dbo].[VO]
(
[LiabCode] ASC,
[Company] ASC,
[PayTerms] ASC,
[Vendor] ASC,
[Voucher] ASC,
[TransDate] ASC,
[BankCode] ASC,
[Invoice] ASC,
[InvoiceDate] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE NONCLUSTERED INDEX [NC_IDX_VO_CASH_REQ_3] ON [dbo].[VO]
(
[LiabCode] ASC,
[Company] ASC,
[PayTerms] ASC,
[Vendor] ASC,
[Voucher] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go