Optimization of view

sapator · January 5, 2017, 2:12pm

Hi.
I have this view that i call

SELECT CAST(CONVERT(VARCHAR, CASE WHEN Datepart(hh, CollectedDate) < 6 THEN DATEADD(d, -1, CollectedDate) ELSE CollectedDate END, 112) AS DATETIME) AS CollectedDate,
       CollectedUserCode, ISNULL(User_strLastName,'')  + ' ' + ISNULL(User_strFirstName,'') AS CollectedUserName,
       CollectedWorkStationCode, tblWorkstation.Workstation_strName AS CollectedWorkStationName,
       ItemCode, ItemDescription,
       SUM(Quantity) AS Quantity, 
       SUM(GrossValue) AS GrossValue, 
       SUM(VATValue) AS VATValue, 
       SUM(NetValue) AS NetValue
FROM [dbo].[ZZ_vwConsCollectedDTL]
LEFT OUTER JOIN dbo.tblWorkstation WITH (NOLOCK) ON tblWorkstation.Workstation_strCode = CollectedWorkStationCode
LEFT OUTER JOIN dbo.tblUser WITH (NOLOCK) ON tblUser.User_intUserNo = CollectedUserCode
WHERE CAST(CONVERT(VARCHAR, CASE WHEN Datepart(hh, CollectedDate) < 6 THEN DATEADD(d, -1, CollectedDate) ELSE CollectedDate END, 112) AS DATETIME) between '20170101' and '20170102'
   
GROUP BY CAST(CONVERT(VARCHAR, CASE WHEN Datepart(hh, CollectedDate) < 6 THEN DATEADD(d, -1, CollectedDate) ELSE CollectedDate END, 112) AS DATETIME), 
         CollectedUserCode, 
         ISNULL(User_strLastName,'')  + ' ' + ISNULL(User_strFirstName,''),
         CollectedWorkStationCode, tblWorkstation.Workstation_strName,
         ItemCode, ItemDescription

The view itself is as:

CREATE VIEW [dbo].[ZZ_vwConsCollectedDTL] AS

    SELECT  CASE WHEN ISNULL(TransI_decActualNoOfItems,0) < 0 THEN TransI_dtmRealTransTime --Refund
                 ELSE CASE WHEN tblBooking_Header.BookingH_intNextBookingNo IS NULL THEN TransI_dtmRealTransTime ELSE TransI_dtmDateCollected END
            END AS CollectedDate,

            CASE WHEN ISNULL(TransI_decActualNoOfItems,0) < 0 THEN User_intUserNo --Refund
                 ELSE CASE WHEN tblBooking_Header.BookingH_intNextBookingNo IS NULL THEN User_intUserNo ELSE ZZ_tblTrans_Inventory_EXT.TransI_intPickupUser END
            END AS CollectedUserCode, 

            CASE WHEN ISNULL(TransI_decActualNoOfItems,0) < 0 THEN Workstation_strCode --Refund
                 ELSE CASE WHEN tblBooking_Header.BookingH_intNextBookingNo IS NULL THEN Workstation_strCode ELSE ZZ_tblTrans_Inventory_EXT.TransI_strPickupWorkstn END
            END AS CollectedWorkStationCode,
        
            tblItem.HOPK                        AS ItemCode, 
            tblItem.Item_strItemDescription    AS ItemDescription,

            ISNULL(TransI_decActualNoOfItems,0) AS Quantity,
	        ISNULL(TransI_curValueEach,0)	    AS GrossUP,
	        CAST(ROUND(ISNULL(TransI_curValueEach,0)*ISNULL(TransI_decActualNoOfItems,0),2) AS MONEY)  AS GrossValue,
	
	        CAST(ROUND((ISNULL(TransI_curSTaxEach,0)+
		                ISNULL(TransI_curSTaxEach2,0)+
		                ISNULL(TransI_curSTaxEach3,0)+
		                ISNULL(TransI_curSTaxEach4,0))*ISNULL(TransI_decActualNoOfItems,0),2) AS MONEY)  AS VATValue,

	        --NET = GROSS - TAX
	        CAST(ROUND(ISNULL(TransI_curValueEach,0)*ISNULL(TransI_decActualNoOfItems,0),2)-
	            (ROUND((ISNULL(TransI_curSTaxEach,0)+
		                ISNULL(TransI_curSTaxEach2,0)+
		                ISNULL(TransI_curSTaxEach3,0)+
		                ISNULL(TransI_curSTaxEach4,0))*ISNULL(TransI_decActualNoOfItems,0),2)) AS MONEY)  AS NetValue

    FROM VISTA.dbo.tblTrans_Inventory WITH (NOLOCK)
    INNER JOIN VISTA.dbo.tblItem WITH (NOLOCK)
	    ON tblItem.Item_strItemId = tblTrans_Inventory.Item_strItemId
    LEFT OUTER JOIN VISTA.dbo.ZZ_tblTrans_Inventory_EXT WITH (NOLOCK)
        ON  tblTrans_Inventory.TransI_lgnNumber   = ZZ_tblTrans_Inventory_EXT.TransI_lgnNumber
        AND tblTrans_Inventory.TransI_intSequence = ZZ_tblTrans_Inventory_EXT.TransI_intSequence
    LEFT JOIN VISTA.DBO.tblBooking_Header WITH (NOLOCK) ON tblBooking_Header.TransC_lgnNumber = tblTrans_Inventory.TransI_lgnNumber

    WHERE tblItem.Item_strBookingFee <> 'Y' 
        AND TransI_dtmDateCollected < '9999-01-01 00:00:00.000'

It takes a very log time and i am trying to do some optimization.

The execution plan , the main issue seems to be the tblTrans_Inventory

If you can see on the missing index point at TransI_dtmDateCollected but the problem is that TransI_dtmDateCollected is already included in 2 non clustered indexes

and

So any thoughts?
Thanks.

gbritton · January 5, 2017, 2:27pm

for starters, get rid of NOLOCK. You'll get bad results sometimes.

I did notice this:

Why the second clause? Do you have dates > '9999-01-01 00:00:00.000' in that column?

What percentage of rows have tblItem.Item_strBookingFee <> 'Y' ?

If only a small percentage, consider a filtered index on that column.

Consider an index on TransC_lgnNumber in the tblBooking_Header and tblTrans_Inventory tables and also an index on Item_strItemId in the tblItem and tblTrans_Inventory tables

sapator · January 5, 2017, 3:17pm

Hi.
Thanks for the answer.

tblItem.Item_strBookingFee is about 50-60% of rows as these are tickets that have a booking fee and that fee is written on the tbltrans_inventory table and we do not need them for the actual items here.

What the application does is if a date is collected automatically it puts a 999-01-01' it won't be searched again.
So these dates are ignored but the actual dates are from 2017 now on.

I forgot to mention that I'm not if we are allowed to add indexes as this is a company we do business with app.
But the indexes you mention are there by the company, except from the item_stritemid one.

Also should i consider a non clustered index for transI_dtmDateCollected on the ZZ_tblTrans_Inventory_EXT (this is an external inventory table we use so we can bind some data that is in the air).
And is there some issue using the dates this way or some problem with cases or this impact performance only a little bit?
Thanks.

gbritton · January 5, 2017, 4:00pm

Pity about the tables you can't index. OTOH you could create indexed views on them, could you not?

But I don't see that referenced in any predicate in your query or view (or did I miss it?)

Would you consider putting a table qualifier on the

AND TransI_dtmDateCollected < '9999-01-01 00:00:00.000'

Just so dummies like me can see which table it comes from?

yosiasz · January 5, 2017, 4:24pm

You should get some sort of award for a thorough and detailed question.

Is the application a web application? How important is it to show the latest data in this application
Could you flatten this data you need for your application into one table and query that table instead?

We had a situation in our stock photography application where the foto submitters needed to see sales of their images and royalty data, a sort of sales dashboard. We decided to make this data flatted into one table with all of the data. A sql job would poll the source sales data every hour or so (OLTP) and modify flattened table accordingly. Gained major performance and stability of site

ScottPletcher · January 5, 2017, 6:09pm

Likely the only way to properly deal with all performance issues on the
VISTA.dbo.tblTrans_Inventory
table is to change how that table is clustered. But, since you're not allowed to do that, I think you're stuck. Since it seems you're having to check ~30+% of the rows in the table, you'd need a completely covering index on that table to handle your query, and the overhead of that would probably be about as much as just scanning the table for this query.

sapator · January 6, 2017, 11:37pm

Thanks for all the answers.
TransI_dtmDateCollected < '9999-01-01 00:00:00.000' is from table tblTrans_Inventory
Yes I can create indexed views but I'm not sure what to do? Should, for example create a view on the tblTrans_Inventory table and put indexes? Also would that impact performance or it's just a shot to see if anything changes?

I can't create an SQL job as the system is live and it impact performance every time we run it.
The tables are part of a wide app range that include web apps, web services and forms and is hit by hundred or thousands of people every hour so the data change every minute so flattening the data in one table could be valid for about 1 minute and then the data should be inserted again.Long story short, this is a big headache.

The problem with changing indexes is that if we run an update (that it is an exe that will change software and db) then the indexes (as we have seen) may get rebuild so every custom index we may have created need to be created again.
The problem also is that what I'm showing here this is a portion of tables used. There are about 500 tables used so we can't really truck down index changes and recreate. Also if we "break" something" and we have messed with the supportive company tables then we get the blame for relevant and irrelevant issues, so the prime directive from the executive directors is "Do not touch the tables "

Thanks.

Ifor · January 9, 2017, 1:10pm

You could look into moving the data asynchronously to another database either with transactional replication and filters or, if you are allowed to add triggers, with service broker. Both these options are a fiddle to setup and monitor but you should be able to get near real time results with minimal interference on the original DB.

gbritton · January 9, 2017, 2:34pm

good place to start! Test in in a non-prod environment first, of course

sapator · January 9, 2017, 9:22pm

Will let you know know.
Thanks.

sapator · January 12, 2017, 9:09am

Hi.
I got a problem.
When it try to create a view it says:
"Cannot create index. Object 'tblTrans_Inventory' was created with the following SET options off: 'ANSI_NULLS'."

I can't really make a copy table of tblTrans_Inventory as it's about 60GB on some company sites.
I can create it on the test server if you know the way to do that (and copy the data over) just to see if the view I am trying will have any impact, so I can discuss it with the manager and see if we can work something out.

Anyhow, here is the view I try to create and get the error:

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO




CREATE VIEW ZZ_vwTrans_Inventory WITH SCHEMABINDING AS 


SELECT 
TransI_lgnNumber,
TransI_intSequence,	
TransI_strType,
TransI_strRefNo,
Item_strItemId,
TransI_strStatus,
Vendor_strCode,
TransI_intParentSeq,
Location_strCode,
TransI_dtmDateTime,
TransI_curValueEach,
TransI_curSTaxEach,
TransI_curAvailable,
User_intUserNo,
Workstation_strCode,
TransI_curNetTotal,
TransI_curDiscount,
DiscountR_strRptCode,
TransI_decNoOfItems,
TransI_decCostEach,
TransI_decSTaxCostEach,
TransI_strDescription,
TransI_dtmDateCollected,
TransI_lngSessionId,
TransI_lngExportBatchNo	

FROM dbo.tblTrans_Inventory



GO

CREATE UNIQUE CLUSTERED INDEX idx_vwTransIDtmCollected on dbo.ZZ_vwTrans_Inventory (TransI_lgnNumber)
CREATE UNIQUE CLUSTERED INDEX idx_vwTransIDtmCollected2 on dbo.ZZ_vwTrans_Inventory (TransI_dtmDateCollected)
CREATE UNIQUE CLUSTERED INDEX idx_vwTransIDtmCollected3 on dbo.ZZ_vwTrans_Inventory (Item_strItemId,Location_strCode)
CREATE UNIQUE CLUSTERED INDEX idx_vwTransIDtmCollected4 on dbo.ZZ_vwTrans_Inventory (TransI_decNoOfItems)

GO

Maybe is the SQL version as is 2008

jotorre_riversidedpss.org · January 12, 2017, 5:09pm

Try:

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO
CREATE VIEW ZZ_vwTrans_Inventory WITH SCHEMABINDING AS 
SELECT 
TransI_lgnNumber,
TransI_intSequence,	
TransI_strType,
TransI_strRefNo,
Item_strItemId,
TransI_strStatus,
Vendor_strCode,
TransI_intParentSeq,
Location_strCode,
TransI_dtmDateTime,
TransI_curValueEach,
TransI_curSTaxEach,
TransI_curAvailable,
User_intUserNo,
Workstation_strCode,
TransI_curNetTotal,
TransI_curDiscount,
DiscountR_strRptCode,
TransI_decNoOfItems,
TransI_decCostEach,
TransI_decSTaxCostEach,
TransI_strDescription,
TransI_dtmDateCollected,
TransI_lngSessionId,
TransI_lngExportBatchNo	
FROM dbo.tblTrans_Inventory
GO
CREATE UNIQUE CLUSTERED INDEX idx_vwTransIDtmCollected on dbo.ZZ_vwTrans_Inventory (TransI_lgnNumber)
CREATE UNIQUE INDEX idx_vwTransIDtmCollected2 on dbo.ZZ_vwTrans_Inventory (TransI_dtmDateCollected)
CREATE UNIQUE INDEX idx_vwTransIDtmCollected3 on dbo.ZZ_vwTrans_Inventory (Item_strItemId,Location_strCode)
CREATE UNIQUE INDEX idx_vwTransIDtmCollected4 on dbo.ZZ_vwTrans_Inventory (TransI_decNoOfItems)
GO

Also any procedure against an indexed view or table with indexed computed column must have the same set options as above to work.

gbritton · January 12, 2017, 5:47pm

You can only have one clustered index per table, since the table is a clustered index if it's not a heap.

jeffw8713 · January 12, 2017, 9:09pm

I just took a look through this - and I think you should rewrite your original query so that it does not utilize the view. Incorporate the table/code from the view directly...

When you do that - you will notice that your view is calculating the CollectedDate and that value can be one of two values: TransI_dtmRealTransTime, TransI_dtmDateCollected

CASE WHEN ISNULL(TransI_decActualNoOfItems,0) < 0 
     THEN TransI_dtmRealTransTime --Refund
     ELSE CASE WHEN tblBooking_Header.BookingH_intNextBookingNo IS NULL 
               THEN TransI_dtmRealTransTime 
               ELSE TransI_dtmDateCollected 
           END
 END AS CollectedDate,

And in your outer query you are using this:

WHERE CAST(CONVERT(VARCHAR, CASE WHEN Datepart(hh, CollectedDate) < 6 THEN DATEADD(d, -1, CollectedDate) ELSE CollectedDate END, 112) AS DATETIME) between '20170101' and '20170102'

If I am reading this correctly - when the CollectedDate (either of the above columns - depending on the value returned) is prior to 6am then substract one day from that date - if not then use it as is - and include the results if the result is on 20170101 or 20170102.

Even when SQL Server can recognize that an index would be useful - it cannot actually utilize that index because the above criteria is not SARGable.

This will probably run much quicker if you separate it into multiple queries using UNION ALL and each query only selects the data specific to the criteria for that expression...

SELECT ...
FROM ...
WHERE TransI_decActualNoOfItems < 0
AND TransI_dtmRealTransTime >= '20170101 06:00'
AND TransI_dtmRealTransTime < '20170102 06:00'
UNION ALL
SELECT ...
FROM ...
WHERE tblBooking_Header.BookingH_intNextBookingNo IS NULL
AND TransI_dtmRealTransTime >= '20170101 06:00'
AND Transl_dtmRealTransTime <  '20170102 06:00'
UNION ALL
SELECT ...
FROM ...
WHERE tblBooking_Header.BookingH_intNextBookingNo IS NOT NULL
AND TransI_dtmDateCollected >= '20170101 06:00'
AND TransI_dtmDateCollected <  '20170102 06:00'

I am assuming you are trying to get everything from 6am to 6am...

jotorre_riversidedpss.org · January 12, 2017, 11:56pm

Thanks @gbritton I completely overlooked that and edited my script to only one clustered index. My reply was due to my experiences with indexed views and persisted indexed computed columns getting an error on a set option included in the following not set the same as the following:

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO

sapator · January 13, 2017, 8:12am

Hi.
By using the suggested view - fix I still get:
Cannot create index. Object 'tblTrans_Inventory' was created with the following SET options off: 'ANSI_NULLS'.

sapator · January 13, 2017, 8:16am

As the view to srpoc concerned, i tried to create a sargable query but the times are about the same.
I did not try the union thing , I'm a little confused on how to do this with UNIONS. Does not seem easy or the right way to go.
Anyhow here is the query:

declare 	@DateFrom DATETIME
declare 	@DateTo DATETIME
declare @CollectedUserCodeV nvarchar(50) 
set @CollectedUserCodeV = '-1'
 declare 	@CollectedWorkstationCodeV nvarchar(50) 
 set @CollectedWorkstationCodeV = '-1'

 set @DateFrom = '20170101' 
 set @DateTo = '20170115'
	
	Select @DateFrom = DateAdd(Hour, 6, @DateFrom), @DateTo = DateAdd(Hour, 6, @DateTo)
--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
declare @CollectedUserCode int
declare @CollectedWorkstationCode nvarchar(50)

if (@CollectedUserCodeV = '-1' or @CollectedUserCodeV = 'UserNo')
Begin
set @CollectedUserCode = -1
end
else
begin
set @CollectedUserCode = cast(@CollectedUserCodeV as int)
end

if (@CollectedWorkstationCodeV = '-1' or @CollectedWorkstationCodeV = 'WrkstnCode')
begin
set @CollectedWorkstationCode ='-1'
end
else
begin
set @CollectedWorkstationCode = @CollectedWorkstationCodeV
end

	

	SELECT  CASE WHEN DateAdd(Hour, 6, CollectedDate) < 6 THEN DATEADD(d, -1, CollectedDate) ELSE CollectedDate END AS CollectedDate,
       CollectedUserCode, ISNULL(User_strLastName,'')  + ' ' + ISNULL(User_strFirstName,'') AS CollectedUserName,
       CollectedWorkStationCode, tblWorkstation.Workstation_strName AS CollectedWorkStationName,
       ItemCode, ItemDescription,
       SUM(Quantity) AS Quantity, 
       SUM(GrossValue) AS GrossValue, 
       SUM(VATValue) AS VATValue, 
       SUM(NetValue) AS NetValue
FROM [dbo].[ZZ_vwConsCollectedDTL]
LEFT OUTER JOIN dbo.tblWorkstation WITH (NOLOCK) ON tblWorkstation.Workstation_strCode = CollectedWorkStationCode
LEFT OUTER JOIN dbo.tblUser WITH (NOLOCK) ON tblUser.User_intUserNo = CollectedUserCode
WHERE collectedDate between @DateFrom and  @DateTo
 and ( ( @CollectedUserCode = -1 ) OR ( ZZ_vwConsCollectedDTL.CollectedUserCode = @CollectedUserCode))
 and ( ( @CollectedWorkstationCode = '-1' ) OR ( ZZ_vwConsCollectedDTL.CollectedWorkStationCode = @CollectedWorkstationCode))
  
 
GROUP BY CASE WHEN DateAdd(Hour, 6, CollectedDate) < 6 THEN DATEADD(d, -1, CollectedDate) ELSE CollectedDate END, 
         CollectedUserCode, 
         ISNULL(User_strLastName,'')  + ' ' + ISNULL(User_strFirstName,''),
         CollectedWorkStationCode, tblWorkstation.Workstation_strName,
         ItemCode, ItemDescription

and the execution plan.

sapator · January 25, 2017, 3:49pm

Hi.
Is there something I can change on the view so I can get better result, because as I can see the view will do a table scan,
or it's the index that I cannot change on the table (or create a new view because of the ANSI errors)?
Thanks.

[code]
SELECT TransI_decActualNoOfItems,
TransI_dtmRealTransTime,
tblBooking_Header.BookingH_intNextBookingNo,
TransI_dtmDateCollected,

        tblItem.HOPK                        AS ItemCode, 
        tblItem.Item_strItemDescription    AS ItemDescription,

        ISNULL(TransI_decActualNoOfItems,0) AS Quantity,
        ISNULL(TransI_curValueEach,0)	    AS GrossUP,
        CAST(ROUND(ISNULL(TransI_curValueEach,0)*ISNULL(TransI_decActualNoOfItems,0),2) AS MONEY)  AS GrossValue,

        CAST(ROUND((ISNULL(TransI_curSTaxEach,0)+
	                ISNULL(TransI_curSTaxEach2,0)+
	                ISNULL(TransI_curSTaxEach3,0)+
	                ISNULL(TransI_curSTaxEach4,0))*ISNULL(TransI_decActualNoOfItems,0),2) AS MONEY)  AS VATValue,

        --NET = GROSS - TAX
        CAST(ROUND(ISNULL(TransI_curValueEach,0)*ISNULL(TransI_decActualNoOfItems,0),2)-
            (ROUND((ISNULL(TransI_curSTaxEach,0)+
	                ISNULL(TransI_curSTaxEach2,0)+
	                ISNULL(TransI_curSTaxEach3,0)+
	                ISNULL(TransI_curSTaxEach4,0))*ISNULL(TransI_decActualNoOfItems,0),2)) AS MONEY)  AS NetValue

FROM VISTA.dbo.tblTrans_Inventory WITH (NOLOCK)
INNER JOIN VISTA.dbo.tblItem WITH (NOLOCK)
    ON tblItem.Item_strItemId = tblTrans_Inventory.Item_strItemId
LEFT OUTER JOIN VISTA.dbo.ZZ_tblTrans_Inventory_EXT WITH (NOLOCK)
    ON  tblTrans_Inventory.TransI_lgnNumber   = ZZ_tblTrans_Inventory_EXT.TransI_lgnNumber
    AND tblTrans_Inventory.TransI_intSequence = ZZ_tblTrans_Inventory_EXT.TransI_intSequence
LEFT JOIN VISTA.DBO.tblBooking_Header WITH (NOLOCK) 
ON tblBooking_Header.TransC_lgnNumber = tblTrans_Inventory.TransI_lgnNumber

WHERE tblItem.Item_strBookingFee <> 'Y' --Concession Products
    AND TransI_dtmDateCollected < '9999-01-01 00:00:00.000' --Only Collected Items

[\code]

gbritton · January 25, 2017, 4:27pm

A couple of things jump out:

ON tblItem.Item_strItemId = tblTrans_Inventory.Item_strItemId

yet the plan does table scans on both tables (bottom right). Can you put indexes on this column in both tables?

Also, can you expand the compute scalar operation? Not clear to me what that is doing BEFORE the hash match

Lastly, get rid of WITH (NOLOCK).

sapator · January 26, 2017, 11:08am

Hi.
I'm including the indexes. As I've said, i cannot interfere with the tables but I think the 2 tables are indexed correctly here.
I'm also including the scalar operation
the live query runs without nolock, I have this on the test server so just ignore the nolock
Thanks.