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

Transact-SQL
,
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