Query on View is running slower than direct query the base table

Transact-SQL
,
1

Hi,
We have a view that created with multiple tables joins to use it the application. As per check, when we run the query directly the sql is very fast but when use select from the view the sql super slow.

USE [ERPRPT]
GO

/****** Object:  View [dbo].[STD_TURNKEY_INVOICE_VIEW_S]    Script Date: 06/01/2017 13:33:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[STD_TURNKEY_INVOICE_VIEW_S]
AS
SELECT     [InvoiceNo] 
          ,[InvoiceDate] InvoiceDateTime 
          ,REPLACE(CONVERT(CHAR(11), InvoiceDate, 106), ' ', '-') AS InvoiceDate
          ,(select [MAWBNo] FROM [CarsemERP].dbo.[SH_Transit_Invoice] b (nolock) where b.InvoiceNo = a.InvoiceNo) MAWB
          ,(select [HAWBNo] FROM [CarsemERP].dbo.[SH_Transit_Invoice] b (nolock) where b.InvoiceNo = a.InvoiceNo) HAWB
          ,[PurchaseOrder] PONo
          ,[POItem] POLineNo
          ,(select max(custcode) FROM [CarsemERP].dbo.SA_Custmaster b (nolock) where b.custabbr=a.custAbbr) CustCode         
          ,[CustAbbr]
          ,[Package] 
          ,[Device]
          ,(SELECT MAX(OriginalCustomerLotNumber)
            FROM [CarsemERP].dbo.SSR_FG_Integration AS b (nolock)
            WHERE b.processstate in ('Y','PROCESSED')
            and (B.Command like 'TST%')
            and B.CustomerLotNumber=a.LotNumber
            and a.LotNumber IS NOT NULL) As OrigLotNo                     
          ,[Customerlotnumber] LotNumber
          ,[Customerlotnumber] LotNumberAlias
          ,[LotNumber] ReelLotNumber 
          ,[CustomerEDILotNo] EDILotNo
          ,[MergeLotNumber] 
          ,[MergeLotQTY] 
          ,[TempColumn]  
          ,[ProcessType] 
          ,[Process]
          ,[TempColForSummary]
          ,[SHPQTY] 
          ,[AssemblyPrice] UnitPrice
          ,[TotalAmount] ProcessAmount
		  ,NULL TotalUnitPrice
		  ,NULL TotalAmount          	
          --,(select dbo.GetTurnKeyInvTotalUnitPrice(a.Invoiceno, a.LotNumber, a.CustomerLotNumber) as expr1) As TotalUnitPrice
          --,((select dbo.GetTurnKeyInvTotalUnitPrice(a.Invoiceno, a.LotNumber, a.CustomerLotNumber) as expr1)*TotalQty) As TotalAmount          
          ,[TotalQty] TotalInvoiceQty
          ,[TotalInvAmount] TotalInvoiceAmount
          ,[GSTAmt] GSTAmt  -- include GST info
		  ,TotalInvAmount + GSTAmt AS InvAmtGST  -- include GST info          
          ,[productcode]
          ,[mesid]
          ,[indextime]
          ,[testtime]
          ,[testerid]
          ,[handlerid]
          ,[EquipmentSite]   --R001;requested by chloe
          ,(select max([Owner]) from [CarsemERP].dbo.[SA_Equipments] as b (nolock) 
            where b.[EquipmentId]=a.[testerid]) TESTEROWNER    --R001;requested by chloe
          ,(select max([Owner]) from [CarsemERP].dbo.[SA_Equipments] as b (nolock) 
            where b.[EquipmentId]=a.HANDLERID) HANDLEROWNER    --R001;requested by chloe
          ,(select max([description]) from [CarsemERP].dbo.[SA_Equipments] as b (nolock) 
            where b.[EquipmentId]=a.[testerid]) TESTERDESC    --R001;requested by chloe
          ,(select max([description]) from [CarsemERP].dbo.[SA_Equipments] as b (nolock) 
            where b.[EquipmentId]=a.HANDLERID) HANDLERDESC    --R001;requested by chloe    
          ,[pricepersec]
          ,[SHIPTOCODE]
          ,[EXCHANGERATE]
          ,[DIEVALUE]
          ,(SELECT MAX(FIELD001)	--get lotorigin
			FROM [S-SQL-ERPRPTDB].CarsemERP.dbo.SSR_FG_Integration AS b (nolock)
			WHERE b.processstate in ('Y','PROCESSED')
			and (B.Command like 'TST03%FG%' or B.Command like 'TST2.5%')
			--from KLLai and B.CustomerLotNumber like a.LotNumber+'%'  
			and B.CustomerLotNumber = a.LotNumber 
			and a.LotNumber IS NOT NULL) As LotOrigin           
          ,(SELECT MAX(FIELD023)
            FROM [CarsemERP].dbo.SSR_FG_Integration AS b (nolock)
            WHERE b.processstate in ('Y','PROCESSED')
            and (B.Command like 'TST03%FG%')
            and B.CustomerLotNumber=a.LotNumber
            and a.LotNumber IS NOT NULL) As ProdStartDate           
          ,(SELECT MAX(PLANID) 
            FROM [CarsemERP].dbo.SSR_FG_Integration AS b (nolock)
            WHERE b.processstate in ('Y','PROCESSED')
            and (B.Command like 'ASM03%FG%' or B.Command like 'ASM03%PreTest%')
            and b.CustomerLotNumber=a.CustomerLotNumber
            and a.CustomerLotNumber IS NOT NULL) PLANID
           ,(SELECT MAX(alias) 
            FROM [CarsemERP].dbo.SSR_FG_Integration AS b (nolock)
            WHERE b.processstate in ('Y','PROCESSED')
            --and (B.Command like 'ASM03%FG%' or B.Command like 'ASM03%PreTest%')
            and b.CustomerLotNumber=a.CustomerLotNumber
            and b.LotOrderLine='300'
            and a.CustomerLotNumber IS NOT NULL) Alias                      
           ,(SELECT MAX(alias) 
            FROM [CarsemERP].dbo.SSR_FG_Integration AS b (nolock)
            WHERE b.processstate in ('Y','PROCESSED')
            and b.CustomerLotNumber=a.CustomerLotNumber
            and b.LotOrderLine='400'
            and a.CustomerLotNumber IS NOT NULL) TestAlias                      
          ,(SELECT MAX(LotOrderId)
            FROM [CarsemERP].dbo.SSR_FG_Integration AS b (nolock)
            WHERE b.processstate in ('Y','PROCESSED')
            and (B.Command like 'TST03%FG%')
            and B.CustomerLotNumber=a.LotNumber
            and a.LotNumber IS NOT NULL) As LotOrderId
          ,(SELECT MAX(ComponentQty)
            FROM [CarsemERP].dbo.SSR_FG_Integration AS b (nolock)
            WHERE b.processstate in ('Y','PROCESSED')
            --and (B.Command like '%PRETEST%')
            and (B.Command like '%PRETEST%' COLLATE SQL_Latin1_General_CP1_CS_AS)
            and B.CustomerLotNumber=a.LotNumber
            and a.LotNumber IS NOT NULL) As TestInQty                    
		 -- ,(select max(pkggroup) from [sa_package] As SAPkg (nolock) where SAPkg.pkgcode=a.package) As PkgGrp
		 -- ,(select max(pkgfamily) from [sa_package] AS SAPkg (nolock) where SAPkg.pkgcode=a.package) As PkgFamily
		 -- ,(select max(productgroup) from [sa_package] AS SAPkg (nolock) where SAPkg.pkgcode=a.package) As ProdGrp
			 
FROM  [CarsemERP].dbo.[SA_INVASY&TST_RPT] AS a (nolock)
--FROM [CarsemERP].[dbo].[SA_INVASY&TST_RPT] a --WITH (INDEX(Admin_Idx8_2014JULY7)) 
-- Point Production FROM [VERPSVR02-02].[CarsemERP].[dbo].[SA_INVASY&TST_RPT] a 

GO

So when i use below sql to direct use the view, the sql was super slow.

SELECT a.* FROM [S-SQL-ERPRPTDB].[ERPRPT].[dbo].[STD_TURNKEY_INVOICE_VIEW_S] a (NOLOCK) 
WHERE (CONVERT(DATETIME, a.InvoiceDate, 101)) between '2017-05-29 00:00:00' and '2017-05-29 00:00:00'
and a.CustAbbr = 'HAT'
ORDER BY a.InvoiceNo, a.LotNumber
2

That is not SARGable, so SQL will examine every row in the underlying table and not use any indexes.

Seems like [InvoiceDate] in the view refers to original column [InvoiceDateTime] which certainly looks like it is a DateTime datatype, is that not the case? If that is a DATETIME datatype just use

WHERE a.InvoiceDate BETWEEN '20170529 00:00:00' and '20170529 00:00:00'
and a.CustAbbr = 'HAT'

Note that you should not use "-" in string datetime values, the resulting parsing is not entirely ambiguous (but you will probably get away with it nearly-all-the-time)

Also note that this will include InvoiceDate at midnight and nothing on that day with any time element. Perhaps the original InvoiceDateTime is a DATETIME datatype but without any TIME element? Or perhaps your CONVERT is to remove the TIME element? The most efficient, and SARGable, way to include a date-time range is:

WHERE a.InvoiceDate >= '20170529'
and a.InvoiceDate < '20170530' -- << One-past-the-endpoint
and a.CustAbbr = 'HAT'

Its going to need an index on InvoiceDateTime, CustAbbr on [CarsemERP].dbo.[SA_INVASY&TST_RPT] though (and probably with the key columns in that order, rather than the other way round, assuming that InvoiceDateTime is more selective than CustAbbr)

(If you normally report using ORDER BY a.InvoiceNo, a.LotNumber then it will probably help if they are INCLUDED in the index)

I don't get why this is still used so widely, we see it all the time on questions here.

You do know that using NOLOCK means that on occasions your query will miss some rows, or include some rows twice? That's fine, of course, if the accuracy of the report is not important.

Some other observations:

FWIW we use SYNONYMS for all 3 part naming. That means that if the [CarsemERP] database is renamed, or moves to another server ... or you want to "toggle" between TEST and PRODUCTION versions of the DB, you can just change the Synonym rather than having to find & replace all instances in the code

Same again with 4-part naming - use Synonyms for those too in case the server, or its database name, changes. I expect that this remote-server query will be slow, and it may well cause performance problems on the remote server too. Only real option here is pull the data locally. Maybe [S-SQL-ERPRPTDB] is another server in the same rack, which won't be too bad ... compared to a server in another country!

If nothing else its a lurking problem, if that server gets re-sited (or renamed - hence the Synonym suggestion)

All those SELECT MAX are a potential performance issue. Make sure you have suitable covering indexes on all of them, although given the LIKE tests even an Index may not help ... to improve the performance of those probably needs some warehousing of cached data. If nothing else beware that this query will scale really badly as the data volume grows.

1 Like
3

Ah ... I wrongly read part of the VIEW definition.

Original table column is [InvoiceDate], that is made available (in original datatype - probably datetime?) as [InvoiceDateTime]. It is also made available, rather confusingly IMHO, as the original column name of [InvoiceDate] but that is converted to a string.

So for your WHERE clause to be SARGable it needs to be

WHERE a.InvoiceDateTime >= '20170529'
and a.InvoiceDateTime < '20170530' -- << One-past-the-endpoint
and a.CustAbbr = 'HAT'

FWIW we don't use that style of alias name in our code, and its why I got confused, although of course if that is your in-house style your folk will be familiar with it.

We use either

[InvoiceDate] AS InvoiceDateTime 
or
[InvoiceDateTime] = InvoiceDate
1 Like
4

Hi Kristen,
Thanks for the reply. But if we just run the sql it very fast. Just when we use the VIEW it become slow. Any idea as the SQL itself running very fast.

5

Yeah I read that and thought you probably were not running the identical SQL

But if you are running the full SELECT from the VIEW and then below that

WHERE (CONVERT(DATETIME, a.InvoiceDate, 101)) between '2017-05-29 00:00:00' and '2017-05-29 00:00:00'
and a.CustAbbr = 'HAT'
ORDER BY a.InvoiceNo, a.LotNumber

i.e. absolutely no difference to your query from the view, then that will be using the [InvoiceDate] from the UNDERLYING table, which is (presumably??) already in DateTime datatype.

SQL might (but I doubt it) be short-circuiting that and using an INDEX. If you do this it will definitely use an index (if one is available),so should be even faster

WHERE a.InvoiceDate >= '20170529'
and a.InvoiceDate < ''20170530'
and a.CustAbbr = 'HAT'
ORDER BY a.InvoiceNo, a.LotNumber

but when you do that via the VIEW the [InvoiceDate] is referring to the SELECT column as defined in the VIEW and that, as I explained earlier, is converted to String by the VIEW.

So your query of the view is converting the String date back to a DateTime to make the date-range test, and the VIEW is converting the DateTime to a String ...

... so SQL has to run the VIEW code to convert the Date to String, and then convert the String to Date [for your WHERE clause] and I doubt very much indeed that it is smart enough to figure out that that can be short-circuited.

If you are running the WHOLE SELECT statement in your direct-table test? then you should get identical performance against the VIEW if you just change your WHERE clause to use InvoiceDateTime

That's why I said, earlier, that I thought it was a bad idea that the VIEW was presenting the String Date with the same column name as the original column - I think it would be better to alias it as [InvoiceDate_Text] or something like that

1 Like
6

Look into the two execution plans to see how the engine is executing the view and the select and try to find the differences. Maybe the sql engine is using one plan for the view and another one for the direct select. I'd would also recommend to refresh the view
execute sp_refreshview N'[dbo].[STD_TURNKEY_INVOICE_VIEW_S]'

1 Like
7

Kristen,
Thanks for your help. It works like charm now when i change the InvoiceDate to InvoiceDateTime.
Is that better use alias in VIEW to prevent datetime ad string date confusion?
[InvoiceDate] InvoiceDateTime Or [InvoiceDate] AS [InvoiceDateTime_Text]

8

Doesn't matter which syntax, I only suggest that you avoid using an Alias which matches an original column name in the table. So perhaps add a suffix of something like "_TEXT" or "_FORMATTED"

InvoiceDateTime_TEXT
or
InvoiceDateTime_FORMATTED

I hope you got my point about the risk of using NOLOCK? :slight_smile:

9

Kristen,
Yes i understood the risk by using nolock.
I have been telling to my reporting team regard that and the risk but seem they want to use that to avoid lock that might make the query run longer.

10

Hi Scarela,
Thanks for your reply. Yes i have done that too but seem the VIEW still try to convert the InvoiceDate from string to date and this may cause the overhead and make the VIEW run longer. After change the InvoiceDate to InvoiceDateTime the query from VIEW become faster.

11

Might be better to investigate using Read Committed Snapshot (Isolation) instead (its a database setting, so no code changes required, but there are some edge cases that you should be aware of before you just turn it on !! Depending on your code it may dramatically increase use of TEMPDB too.)

NOLOCK is (I think??) unlikely to improve reporting performance, it will stop blocking of Writers though (as will RCSI)

12

Yes I agree, we had much less deadlocking after we turned Read Committed Snapshot on