CODEWITHSUNDEEP
sql-servert-sqlviewsql-server-2016
Cenderze
Cenderze

Reputation: 1212

Retrieve data from a view is very time consuming using SQL Server

I've created a view in SQL server 2016. The view is created with the statement:

CREATE VIEW dbo.StockEOMonth
AS
   SELECT       CAST(StoreKey AS numeric(4, 0)) AS StoreKey, 
                DateKey, 
                SUM(StockValue) AS StockValue,
                SUM(StockQuantity) AS StockQuantity
   FROM         Stock
   WHERE        CONVERT(date, CONVERT(varchar(10), DateKey)) = 
                EOMONTH(CONVERT(date, CONVERT(varchar(10), DateKey)))
   GROUP BY DateKey, storekey

The view itself is rather straight-forward. I return the total StockQuantity and StockValue balance for each Store at the last day of the month (the reason why I use SUM() is because the underlying table, Stock, includes the balances for each Product but I am only interested in the total balance for each store).

This view returns merely 4000 records. However, when I use the Query:

SELECT top(10) StockValue, StockQuantity 
FROM dbo.StockEOMonth

the Query takes 13 minutes to return only 10 rows.

I'm not sure how to optimize such a trivial view. Does anyone have an idea? I could create the table as an actual table, but it would like to have it as a view if possible.

EDIT

I've experianced the slowness in retrieving data in both SSMS and SSAS where I have gotten ErrorCode 3240034318 from the instance where I've started to use the view as a data source for a measure Group, where the ErrorCode seem to be related to Query TimeOut.

Upvotes: 0

Views: 475

Answers (1)

cloudsafe
cloudsafe

Reputation: 2504

The WHERE clause is not SARGable. You should remove the function on the left side of the where clause:

WHERE DateKey = EOMONTH(DateKey)

If the formatting doesn't work, put functions on the right to make it match the format of DateKey.

If you haven't already tuned the indexes, try this:

CREATE NONCLUSTERED INDEX [ix_test] ON [stock]
(
    [datekey] ASC
)
INCLUDE (   [StoreKey],
    [StockValue],
    [StockQuantity]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

Upvotes: 1

Related Questions