PKirby PKirby - 5 days ago 5
SQL Question

Optimize performance on SQL table - indexing

I have a table in my DB which contains 5 million records:

CREATE TABLE [dbo].[PurchaseFact](
[Branch] [int] NOT NULL,
[ProdAnal] [varchar](30) NULL,
[Account] [varchar](12) NULL,
[Partno] [varchar](24) NULL,
[DteGRN] [date] NULL,
[DteAct] [date] NULL,
[DteExpect] [date] NULL,
[OrderNo] [bigint] NULL,
[GRNNO] [varchar](75) NULL,
[SuppAdv] [varchar](75) NULL,
[Supplier] [varchar](12) NULL,
[OrdType] [varchar](4) NULL,
[UnitStock] [varchar](4) NULL,
[OrderQty] [float] NULL,
[RecdQty] [float] NULL,
[Batch] [varchar](100) NULL,
[CostPr] [float] NULL,
[Reason] [varchar](2) NULL,
[TotalCost] [float] NULL,
[Magic] [bigint] IDENTITY(1,1) NOT NULL,
PRIMARY KEY CLUSTERED
(
[Magic] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


As you can see from the above - a
CLUSTERED INDEX
is being used on the
MAGIC
column which is a
UNIQUE
column.

Data retrieval time for the following
SELECT statement
is well over 8mins which causes reporting issues:

SELECT Branch,
Supplier,
ProdAnal,
DteGRN AS Date,
PartNo AS Partno,
OrderNo,
OrderQty,
TotalCost,
CostPr
FROM dbo.PurchaseFact src
WHERE YEAR(DteGRN) = 2016


Excluding the
WHERE clause
also doesn't make the query run any faster.
I have tried, together with the
CLUSTERED index
to include a
UNIQUE index
in the hopes that it would run faster but to no avail :

CREATE UNIQUE INDEX Unique_Index ON dbo.PurchaseFact ([Branch], [Supplier], [Magic])
INCLUDE ([ProdAnal], [Account], [Partno], [DteAct], [DteExpect], [OrderNo], [GRNNO],
[SuppAdv], [OrdType], [UnitStock])


Is there any way I can optimize performance time on this table or should I resort to archiving old data?

Any advice would be greatly appreciated.

Answer

This is your where clause:

WHERE YEAR(DteGRN) = 2016

If the table has 5 million rows, then this is going to return a lot of data, assuming any reasonable distribution of dates. The volume of data is probably responsible for the length of time for the query.

One thing you can do is to rephrase the WHERE and then put an index on the appropriate column:

WHERE DteGRN >= '2016-01-01' and DteGRN < '2017-01-01'

This can then take advantage of an index on PurchaseFact(DteGRN). However, given the likely number of rows being returned, the index probably will not help very much.

The bigger question is why your reporting application is bringing back all the rows from 2016, rather than summarizing them inside the database. I suspect you have an architecture issue with the reporting application.

Comments