Optimise SQL View

1

I have developed Database View but it takes much time to execute can anyone please help me to speed this up.

ALTER VIEW [dbo].ABC
AS
SELECT
TOP (100) PERCENT
ISNULL(REPLICATE('0', 3 - LEN(CMP.BUKRS_SCALA)) + CMP.BUKRS_SCALA, PRI_SALES.BUKRS) AS [CompCode],
PRI_SALES.BUKRS AS [CompCode_SAP],
PRI_SALES.VGBEL_VBRP AS [DANo],
CONVERT(DATETIME, PRI_SALES.FKDAT, 101) AS [TranDate],
(
CASE
WHEN PRI_SALES.BUKRS = '4000' AND CUST.ZXFELD_SCALA_WIL = 'X'
THEN ISNULL(ISNULL(REPLICATE('0', 5 - LEN(CUST.KUNNR_SCALA3)) + CUST.KUNNR_SCALA3, REPLICATE('0', 6 - LEN(CUST.KUNNR_SCALA3)) + CUST.KUNNR_SCALA3), PRI_SALES.KUNNR)
ELSE ISNULL(ISNULL(REPLICATE('0', 5 - LEN(CUST.KUNNR_SCALA)) + CUST.KUNNR_SCALA, REPLICATE('0', 6 - LEN(CUST.KUNNR_SCALA)) + CUST.KUNNR_SCALA), PRI_SALES.KUNNR)
END

) AS [DistribuitorID],
PRI_SALES.KUNNR AS [DistribuitorID_SAP],
ISNULL(REPLICATE('0', 6 - LEN(MAT.MATNR_SCALA)) + MAT.MATNR_SCALA, PRI_SALES.MATNR) AS [ItemID],
PRI_SALES.MATNR AS [ItemID_SAP],
PRI_SALES.FKIMG AS [Quantity],
'0' AS [PricingListID],
PRI_SALES.VBELN_VBRK AS [InvoiceNo],
(
	CASE
		WHEN (PRI_SALES.HSDAT = '00000000' OR PRI_SALES.HSDAT IS NULL) THEN '1/1/1900'
		ELSE CONVERT(DATETIME, PRI_SALES.HSDAT, 101)
	END

) AS [MfgDate],
(
	CASE
		WHEN (PRI_SALES.VFDAT = '00000000' OR PRI_SALES.VFDAT IS NULL) THEN '1/1/1900'
		ELSE CONVERT(DATETIME, PRI_SALES.VFDAT, 101)
	END

) AS [ExpDate],
PRI_SALES.CHARG AS [BatchID],

(PRI_SALES.KNUMH_VBAK + PRI_SALES.KOPOS_VBAK) AS [PriceCondNo],
ROUND(CONVERT(FLOAT, PRI_SALES.KBETR_MRP), 2) AS [RetailPrice],
ROUND(CONVERT(FLOAT, PRI_SALES.KBETR_TP), 2) AS [TradePrice],
ROUND(CONVERT(FLOAT, PRI_SALES.KBETR_IP), 2) AS [InvoicePrice],
PRI_SALES.KDGRP AS [CustomerGroupID],
PRI_SALES.KDGRP AS [CustomerGroupID_SAP],
PRI_SALES.KVGR1 AS [CustomerGroup1ID],
PRI_SALES.KVGR1 AS [CustomerGroup1ID_SAP],
(
		CASE
			WHEN (PRI_SALES.KDGRP NOT LIKE '0[2-6]' AND PRI_SALES.REGIO = '01') THEN '001'
			WHEN (PRI_SALES.KDGRP NOT LIKE '0[2-6]' AND PRI_SALES.REGIO = '02') THEN '014'
			WHEN (PRI_SALES.KDGRP NOT LIKE '0[2-6]' AND PRI_SALES.REGIO = '03') THEN '012'
			WHEN (PRI_SALES.KDGRP NOT LIKE '0[2-6]' AND PRI_SALES.REGIO = '04') THEN '002'
			WHEN (PRI_SALES.KDGRP NOT LIKE '0[2-6]' AND PRI_SALES.REGIO = '05') THEN '015'
			WHEN (PRI_SALES.KDGRP NOT LIKE '0[2-6]' AND PRI_SALES.REGIO = '06') THEN '003'
			WHEN PRI_SALES.KDGRP = '02' THEN '022'
			WHEN PRI_SALES.KDGRP = '04' THEN '013'
			WHEN (PRI_SALES.KDGRP = '03' AND PRI_SALES.KVGR1 = 'MKR') THEN '016'
			WHEN (PRI_SALES.KDGRP = '03' AND PRI_SALES.KVGR1 = 'MET') THEN '017'
			WHEN (PRI_SALES.KDGRP = '03' AND PRI_SALES.KVGR1 = 'AGR') THEN '019'
			WHEN (PRI_SALES.KDGRP = '03' AND PRI_SALES.KVGR1 = 'MAF') THEN '025'
			WHEN PRI_SALES.KDGRP = '05' THEN '008'
			WHEN PRI_SALES.KDGRP = '06' THEN '009'
			ELSE PRI_SALES.REGIO
		END

) AS [RegionID],
(
	CASE
		WHEN PRI_SALES.KDGRP LIKE '0[2,3,4,5,6]' THEN (PRI_SALES.KDGRP + '-' + PRI_SALES.KVGR1)
		ELSE PRI_SALES.REGIO
	END

) AS [RegionID_SAP]

FROM
PRIMARY_SALES AS PRI_SALES
LEFT OUTER JOIN
COMPANY AS CMP
ON
PRI_SALES.BUKRS = CMP.BUKRS
AND
CMP.FLAG = '1'
LEFT OUTER JOIN
[APPSERVER].[SAPERP].[dbo].[VW_SAPERPPRD_CUSTOMER AS CUST
ON
PRI_SALES.KUNNR = CUST.KUNNR
AND
CMP.BUKRS_SCALA = CUST.BUKRS_SCALA
AND
CMP.FLAG = CUST.FLAG
LEFT OUTER JOIN
MATERIAL AS MAT
ON
PRI_SALES.MATNR = MAT.MATNR
AND
CMP.BUKRS_SCALA = MAT.BUKRS_SCALA
AND
CMP.FLAG = MAT.FLAG

WHERE
CONVERT(DATETIME, PRI_SALES.FKDAT, 101) >= '9/26/2014'

ORDER BY
[CompCode_SAP],
[CompCode],
[DistribuitorID_SAP],
[DistribuitorID],
[DANo],
[InvoiceNo],
PRI_SALES.POSNR_VBRP;

GO

Thanks in advance.

2

One thing is standing out in the where clause : (without knowing your db layer)

CONVERT(DATETIME, PRI_SALES.FKDAT, 101) >= '9/26/2014'

This is not SARGable

We don't know your's structure , so it is better for you and easy for us, to post the table definitions, what indexes you have ...

3

That doesn't work (having TOP 100 PERCENT / ORDER BY in a View) so get rid of that and do the ORDER BY in the thing that queries the VIEW.

I agree, most probably the CONVERT on the PRI_SALES.FKDAT in the WHERE clause that is making it slow.

Quite possibly also the 4-part naming:

LEFT OUTER JOIN
[APPSERVER].[SAPERP].[dbo].[VW_SAPERPPRD_CUSTOMER AS CUST

as that may be pulling ALL rows from REMOTE and THEN doing the JOIN locally. In that situation we either duplicate the remote table locally, or we deberatey ONLY pull the appropraite rows from the Remote (to satisfy this query) using, for example, OPENQUERY() (and then join THAT into the query instead, of use OPENQUERY() to populate a temporary table, locally, and then join that.

I would also prefix all tables with the schema (presumably ".dbo.") so that SQL doesn't have to check which schema is the default for the currently connected user.