December 2, 2018 at 4:41 am
The below query is taking twenty seconds to display 53,000 records. This query has five left joins with views. But when I comment out the column (MAN.F_PHR AS MANU) in the select statement, it takes three seconds to display the 53,000 records.How can I optimize this query? The view( V_PROD_ALIAS_MANU MAN) contains two left joins. The (MAN.F_PHRE AS MANU)column is from the view. Is there any way to optimize this query?.I am using this query in my stored procedure.
SELECT DISTINCT TP.PRODUCT AS ID, TP.NAME AS [NAME], TP.LANGUAGE AS LANGCODE, CONVERT(VARCHAR,TP.F_DATE_REVISED,120) AS RDATE, CASE WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUM,'¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUM,'¿',' '))),' ','; ') = '') THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUM,'¿',' '))),' ','; ') ELSE REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') END AS CASNUM , TP.F_CUSTOM1 AS cus1, TP.F_CUSTOM2 AS cus2, (SELECT TC.F_COUNTRY_NAME FROM COUNTRIES TC WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5, MAN.F_PHR AS MANU, CASE WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') THEN TP.F_CUSTOM3 ELSE SYN.F_DATA END AS SYN, IC.F_DATA AS ICO, 'SDS - ' + (SELECT TL.F_LAN_NAME FROM T_LANGUAGE TL WHERE TL.F_LANGUAGE = TP.F_LANGUAGE) + ' - PDF' AS DOC, '' AS COVER, CAST(TP.F_GUID AS VARCHAR(36)) + '_PDF' AS [GUID], 'PDF' AS SDSFROM PDF TPLEFT JOIN V_PROD_ALIAS_SYN SYN ON TP.F_PRODUCT = SYN.F_PRODUCTLEFT JOIN V_PROD_ALIAS_SITE SIT ON TP.F_PRODUCT = SIT.F_PRODUCTLEFT JOIN V_PROD_ALIAS_ICO IC ON TP.F_PRODUCT = IC.F_PRODUCTLEFT JOIN V_PROD_ALIAS_MANU MAN ON TP.F_PRODUCT = MAN.F_PRODUCTLEFT JOIN V_PROD_ALIAS_CASN CASN ON TP.F_PRODUCT = CASN.F_PRODUCT
Below Query after commenting the column (MAN.F_PHRE AS MANU) in the select statement takes three seconds to display 53,000 records.SELECT DISTINCT TP.PRODUCT AS ID, TP.NAME AS [NAME], TP.LANGUAGE AS LANGCODE, CONVERT(VARCHAR, TP.F_DATE_REVISED, 120) AS RDATE, CASE WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUM,'¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUM,'¿',' '))),' ','; ') = '') THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUM,'¿',' '))),' ','; ') ELSE REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') END AS CASNUM , TP.F_CUSTOM1 AS cus1, TP.F_CUSTOM2 AS cus2, (SELECT TC.F_COUNTRY_NAME FROM COUNTRIES TC WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5, -- MAN.F_PHR AS MANU, CASE WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') THEN TP.F_CUSTOM3 ELSE SYN.F_DATA END AS SYN, IC.F_DATA AS ICO, 'SDS - ' + (SELECT TL.F_LAN_NAME FROM T_LANGUAGE TL WHERE TL.F_LANGUAGE = TP.F_LANGUAGE) + ' - PDF' AS DOC, '' AS COVER, CAST(TP.F_GUID AS VARCHAR(36)) + '_PDF' AS [GUID], 'PDF' AS SDSFROM PDF TPLEFT JOIN V_PROD_ALIAS_SYN SYN ON TP.F_PRODUCT = SYN.F_PRODUCTLEFT JOIN V_PROD_ALIAS_SITE SIT ON TP.F_PRODUCT = SIT.F_PRODUCTLEFT JOIN V_PROD_ALIAS_ICO IC ON TP.F_PRODUCT = IC.F_PRODUCTLEFT JOIN V_PROD_ALIAS_MANU MAN ON TP.F_PRODUCT = MAN.F_PRODUCTLEFT JOIN V_PROD_ALIAS_CASN CASN ON TP.F_PRODUCT = CASN.F_PRODUCT
December 2, 2018 at 9:30 am
jkramprakash - Sunday, December 2, 2018 4:41 AMThe below query is taking twenty seconds to display 53,000 records. This query has five left joins with views. But when I comment out the column (MAN.F_PHR AS MANU) in the select statement, it takes three seconds to display the 53,000 records.How can I optimize this query? The view(V_PROD_ALIAS_MANU MAN)contains two left joins. The (MAN.F_PHRE AS MANU)columnis from the view. Is there any way to optimize this query?.I am using this query in my stored procedure.
SELECT DISTINCT TP.PRODUCT AS ID, TP.NAME AS [NAME], TP.LANGUAGE AS LANGCODE, CONVERT(VARCHAR,TP.F_DATE_REVISED,120) AS RDATE, CASE WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUM,'¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUM,'¿',' '))),' ','; ') = '') THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUM,'¿',' '))),' ','; ') ELSE REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') END AS CASNUM , TP.F_CUSTOM1 AS cus1, TP.F_CUSTOM2 AS cus2, (SELECT TC.F_COUNTRY_NAME FROM COUNTRIES TC WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5, MAN.F_PHR AS MANU, CASE WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') THEN TP.F_CUSTOM3 ELSE SYN.F_DATA END AS SYN, IC.F_DATA AS ICO, 'SDS - ' + (SELECT TL.F_LAN_NAME FROM T_LANGUAGE TL WHERE TL.F_LANGUAGE = TP.F_LANGUAGE) + ' - PDF' AS DOC, '' AS COVER, CAST(TP.F_GUID AS VARCHAR(36)) + '_PDF' AS [GUID], 'PDF' AS SDSFROM PDF TPLEFT JOIN V_PROD_ALIAS_SYN SYN ON TP.F_PRODUCT = SYN.F_PRODUCTLEFT JOIN V_PROD_ALIAS_SITE SIT ON TP.F_PRODUCT = SIT.F_PRODUCTLEFT JOIN V_PROD_ALIAS_ICO IC ON TP.F_PRODUCT = IC.F_PRODUCTLEFT JOIN V_PROD_ALIAS_MANU MAN ON TP.F_PRODUCT = MAN.F_PRODUCTLEFT JOIN V_PROD_ALIAS_CASN CASN ON TP.F_PRODUCT = CASN.F_PRODUCT
Below Query after commenting the column (MAN.F_PHRE AS MANU) in the select statement takes three seconds to display 53,000 records.SELECT DISTINCT TP.PRODUCT AS ID, TP.NAME AS [NAME], TP.LANGUAGE AS LANGCODE, CONVERT(VARCHAR, TP.F_DATE_REVISED, 120) AS RDATE, CASE WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUM,'¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUM,'¿',' '))),' ','; ') = '') THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUM,'¿',' '))),' ','; ') ELSE REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') END AS CASNUM , TP.F_CUSTOM1 AS cus1, TP.F_CUSTOM2 AS cus2, (SELECT TC.F_COUNTRY_NAME FROM COUNTRIES TC WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5, -- MAN.F_PHR AS MANU, CASE WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') THEN TP.F_CUSTOM3 ELSE SYN.F_DATA END AS SYN, IC.F_DATA AS ICO, 'SDS - ' + (SELECT TL.F_LAN_NAME FROM T_LANGUAGE TL WHERE TL.F_LANGUAGE = TP.F_LANGUAGE) + ' - PDF' AS DOC, '' AS COVER, CAST(TP.F_GUID AS VARCHAR(36)) + '_PDF' AS [GUID], 'PDF' AS SDSFROM PDF TPLEFT JOIN V_PROD_ALIAS_SYN SYN ON TP.F_PRODUCT = SYN.F_PRODUCTLEFT JOIN V_PROD_ALIAS_SITE SIT ON TP.F_PRODUCT = SIT.F_PRODUCTLEFT JOIN V_PROD_ALIAS_ICO IC ON TP.F_PRODUCT = IC.F_PRODUCTLEFT JOIN V_PROD_ALIAS_MANU MAN ON TP.F_PRODUCT = MAN.F_PRODUCTLEFT JOIN V_PROD_ALIAS_CASN CASN ON TP.F_PRODUCT = CASN.F_PRODUCT
Have you got an index on V_PROD_ALIAS_MANU(F_PRODUCT)?CREATE INDEX IX_V_PROD_ALIAS_MANU_F_PRODUCT ON V_PROD_ALIAS_MANU(F_PRODUCT) INCLUDE (F_PHR)
If it's a view you will have to make the view schema bound to add an index.
If you want a more detailed answer (maybe indexes can be added to the underlying tables in the view) you will have to provide the definition of the view and what indexes are on the tables in the view.
December 2, 2018 at 11:28 pm
This is the view query.It contains union all operator so not able to create index for this view.Is there any way to optimize this query?
CREATE VIEW [dbo].[V_PROD_ALIAS_MANU] AS
SELECT DISTINCT PT.F_PRODUCT AS F_PRODUCT, PT.F_TEXT_CODE AS F_TEXT_CODE, PHT.F_PHRASE AS F_PHR FROM T_PROD_TEXT PT
LEFT JOIN T_PHR PHL
ON PT.F_TEXT_CODE = PHL.F_TEXT_CODE
INNER JOIN PHR_TRA PHT
ON PHL.F_PHRASE_ID = PHT.F_PHRASE_ID
WHERE PT.F_DATA_CODE = 'MANU' AND PHT.F_LANGUAGE = 'EN'
UNION ALL
SELECT DISTINCT PT.F_ALIAS AS F_PRODUCT, PT.F_TEXT_CODE AS F_TEXT_CODE, PHT.F_PHRASE AS F_PHR FROM T_PROD_ALI_TEXT PT
LEFT JOIN T_PHR PHL
ON PT.F_TEXT_CODE = PHL.F_TEXT_CODE
INNER JOIN PHR_TRA PHT
ON PHL.F_PHRASE_ID = PHT.F_PHRASE_ID
WHERE PT.F_DATA_CODE = 'MANU' AND PHT.F_LANGUAGE = 'EN'
Index is available for below join conditions
1 PT.F_TEXT_CODE = PHL.F_TEXT_CODE
2 PT.F_DATA_CODE='MANU'
3 PHT.F_LANGUAGE='EN'
4 PT.F_TEXT_CODE = PHL.F_TEXT_CODE
INDEX IS NOT AVAILABLE FOR column(PHL.F_PHRASE_ID) and available for (PHT.F_PHRASE_ID)
1 PHL.F_PHRASE_ID = PHT.F_PHRASE_ID
Now i tried to create view with schema binding.View is created but not able to index on F_PHR column because it is nvarchar(max).
December 3, 2018 at 1:17 am
Can you post the execution plan.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 3, 2018 at 2:01 am
First, just providing the DDL for the view you are using isn't enough since the view is also querying other views. We would actually need the DDL for all the views and the tables as well the definitions for all the indexes on the tables.
What you may need to do is rewrite the view accessing the table directly instead of through other views. Code reuse makes sense for the most part, but can cause issues as you begin to nest views inside of other views. This is escpecial true when you don't actually use all the data returned from the nested views in your top level view.
December 3, 2018 at 5:08 am
ALTER VIEW [dbo].[V_PROD_ALIAS_MANU] WITH SCHEMABINDING AS
SELECT DISTINCT PT.F_PRODUCT AS F_PRODUCT, PT.F_TEXT_CODE AS F_TEXT_CODE, PHT.F_PHRASE AS F_PHR FROM T_PROD_TEXT PT
LEFT JOIN T_PHR PHL
ON PT.F_TEXT_CODE = PHL.F_TEXT_CODE
INNER JOIN PHR_TRA PHT
ON PHL.F_PHRASE_ID = PHT.F_PHRASE_ID
WHERE PT.F_DATA_CODE = 'MANU' AND PHT.F_LANGUAGE = 'EN'
UNION ALL
SELECT DISTINCT PT.F_ALIAS AS F_PRODUCT, PT.F_TEXT_CODE AS F_TEXT_CODE, PHT.F_PHRASE AS F_PHR FROM T_PROD_ALI_TEXT PT
LEFT JOIN T_PHR PHL
ON PT.F_TEXT_CODE = PHL.F_TEXT_CODE
INNER JOIN PHR_TRA PHT
ON PHL.F_PHRASE_ID = PHT.F_PHRASE_ID
WHERE PT.F_DATA_CODE = 'MANU' AND PHT.F_LANGUAGE = 'EN'
GO
CREATE UNIQUE CLUSTERED INDEX IX_V_PROD_ALIAS_MANU_F_PRODUCT ON [dbo].[V_PROD_ALIAS_MANU](F_PRODUCT);
The other option is to tune the query within the view by adding indexes to the underlying tables.
December 3, 2018 at 5:17 am
i tried to create index for that column(f_product) but it is showing following error message.
Cannot create index on view dbo.V_PROD_ALIAS_MANU' because it contains one or more UNION, INTERSECT, or EXCEPT operators. Consider creating a separate indexed view for each query that is an input to the UNION, INTERSECT, or EXCEPT operators of the original view.
December 3, 2018 at 5:36 am
jkramprakash - Monday, December 3, 2018 5:17 AMi tried to create index for that column(f_product) but it is showing following error message.Cannot create index on view dbo.V_PROD_ALIAS_MANU' because it contains one or more UNION, INTERSECT, or EXCEPT operators. Consider creating a separate indexed view for each query that is an input to the UNION, INTERSECT, or EXCEPT operators of the original view.
Looks like you've got the advice you need from the error message.
Personally, I'd look at optimizing the query within the view by adding some indexes to the tables in the view to get it to return results faster.
December 3, 2018 at 5:51 am
You could try these indexes, then look at the execution plan to see which ones are used and drop them if the aren't:CREATE INDEX IX_T_PROD_TEXT_F_PRODUCT ON T_PROD_TEXT(F_PRODUCT,F_DATA_CODE);
CREATE INDEX IX_T_PROD_ALI_TEXT_F_PRODUCT ON T_PROD_ALI_TEXT(F_PRODUCT,F_DATA_CODE);
CREATE INDEX IX_T_PHR_F_TEXT_CODE ON T_PHR(F_TEXT_CODE);
CREATE INDEX IX_T_PHR_F_PHRASE_ID ON T_PHR(F_PHRASE_ID);
CREATE INDEX IX_PHR_TRA_F_PHRASE_ID ON PHR_TRA(F_PHRASE_ID,F_LANGUAGE);
Also, if you view the execution plan it might suggest indexes to add. You can also run the query through the "Database Engine Tuning Advisor" and see what that recommends.
December 4, 2018 at 1:23 am
Jonathan AC Roberts - Monday, December 3, 2018 5:51 AMYou could try these indexes, then look at the execution plan to see which ones are used and drop them if the aren't:CREATE INDEX IX_T_PROD_TEXT_F_PRODUCT ON T_PROD_TEXT(F_PRODUCT,F_DATA_CODE);
CREATE INDEX IX_T_PROD_ALI_TEXT_F_PRODUCT ON T_PROD_ALI_TEXT(F_PRODUCT,F_DATA_CODE);
CREATE INDEX IX_T_PHR_F_TEXT_CODE ON T_PHR(F_TEXT_CODE);
CREATE INDEX IX_T_PHR_F_PHRASE_ID ON T_PHR(F_PHRASE_ID);
CREATE INDEX IX_PHR_TRA_F_PHRASE_ID ON PHR_TRA(F_PHRASE_ID,F_LANGUAGE);
Also, if you view the execution plan it might suggest indexes to add. You can also run the query through the "Database Engine Tuning Advisor" and see what that recommends.
Thanks.
1 In above indexes i created IX_T_PROD_ALI_TEXT_F_PRODUCT and IX_T_PHR_F_PHRASE_ID indexes only.
2 In T_Prod_Text table F_product is primary key and separate index is already available for F_DATA_CODE
so i was not created index on T_PROD_TEXT table and separate index is already available for F_Text_Code.
3 After creating indexes IX_T_PROD_ALI_TEXT_F_PRODUCT and IX_T_PHR_F_PHRASE_ID performance is improved slightly.
IF I store the results of view in temp table and use the temp table in the left join instead of view in my procedure will it improve performance further?Pls suggest.
Example Creating temp table from view
IF OBJECT_ID(N'tempdb..#MANUTEMP') IS NOT NULL
DROP TABLE #MANUTEMP;
SELECT * INTO #MANUTEMP FROM V_PROD_ALIAS_MANU
Using this temp table #MANUTEMP in my left join of actual query instead of V_PROD_ALIAS_MANU view.
is it correct way to improve performance?
December 4, 2018 at 3:48 am
jkramprakash - Tuesday, December 4, 2018 1:23 AMJonathan AC Roberts - Monday, December 3, 2018 5:51 AMYou could try these indexes, then look at the execution plan to see which ones are used and drop them if the aren't:CREATE INDEX IX_T_PROD_TEXT_F_PRODUCT ON T_PROD_TEXT(F_PRODUCT,F_DATA_CODE);
CREATE INDEX IX_T_PROD_ALI_TEXT_F_PRODUCT ON T_PROD_ALI_TEXT(F_PRODUCT,F_DATA_CODE);
CREATE INDEX IX_T_PHR_F_TEXT_CODE ON T_PHR(F_TEXT_CODE);
CREATE INDEX IX_T_PHR_F_PHRASE_ID ON T_PHR(F_PHRASE_ID);
CREATE INDEX IX_PHR_TRA_F_PHRASE_ID ON PHR_TRA(F_PHRASE_ID,F_LANGUAGE);
Also, if you view the execution plan it might suggest indexes to add. You can also run the query through the "Database Engine Tuning Advisor" and see what that recommends.Thanks.
1 In above indexes i created IX_T_PROD_ALI_TEXT_F_PRODUCT and IX_T_PHR_F_PHRASE_ID indexes only.
2 In T_Prod_Text table F_product is primary key and separate index is already available for F_DATA_CODE
so i was not created index on T_PROD_TEXT table and separate index is already available for F_Text_Code.
3 After creating indexes IX_T_PROD_ALI_TEXT_F_PRODUCT and IX_T_PHR_F_PHRASE_ID performance is improved slightly.IF I store the results of view in temp table and use the temp table in the left join instead of view in my procedure will it improve performance further?Pls suggest.
Example Creating temp table from view
IF OBJECT_ID(N'tempdb..#MANUTEMP') IS NOT NULL
DROP TABLE #MANUTEMP;
SELECT * INTO #MANUTEMP FROM V_PROD_ALIAS_MANUUsing this temp table #MANUTEMP in my left join of actual query instead of V_PROD_ALIAS_MANU view.
is it correct way to improve performance?
Yes, that's a good way to do it, provided the insert statement doesn't take too long.
You would probably also find it beneficial to index the temporary table.
Also, check the execution plan to see which indexes are used.
December 5, 2018 at 9:32 am
Thank You.Finally found the issue.My view contains one column PHT.F_Phrase.I am selecting this column in my original select statement.Actually this column (F_Phrase) in PHR_TRA Table is NVARCHAR(MAX).So it reduces the performance of my select statement.Is there any method to improve performance of nvarchar(max) column because we do not have rights to change the size of this column.
December 10, 2018 at 9:44 am
Jonathan AC Roberts - Sunday, December 2, 2018 9:30 AMjkramprakash - Sunday, December 2, 2018 4:41 AMThe below query is taking twenty seconds to display 53,000 records. This query has five left joins with views. But when I comment out the column (MAN.F_PHR AS MANU) in the select statement, it takes three seconds to display the 53,000 records.How can I optimize this query? The view(V_PROD_ALIAS_MANU MAN)contains two left joins. The (MAN.F_PHRE AS MANU)columnis from the view. Is there any way to optimize this query?.I am using this query in my stored procedure.
SELECT DISTINCT TP.PRODUCT AS ID, TP.NAME AS [NAME], TP.LANGUAGE AS LANGCODE, CONVERT(VARCHAR,TP.F_DATE_REVISED,120) AS RDATE, CASE WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUM,'¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUM,'¿',' '))),' ','; ') = '') THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUM,'¿',' '))),' ','; ') ELSE REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') END AS CASNUM , TP.F_CUSTOM1 AS cus1, TP.F_CUSTOM2 AS cus2, (SELECT TC.F_COUNTRY_NAME FROM COUNTRIES TC WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5, MAN.F_PHR AS MANU, CASE WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') THEN TP.F_CUSTOM3 ELSE SYN.F_DATA END AS SYN, IC.F_DATA AS ICO, 'SDS - ' + (SELECT TL.F_LAN_NAME FROM T_LANGUAGE TL WHERE TL.F_LANGUAGE = TP.F_LANGUAGE) + ' - PDF' AS DOC, '' AS COVER, CAST(TP.F_GUID AS VARCHAR(36)) + '_PDF' AS [GUID], 'PDF' AS SDSFROM PDF TPLEFT JOIN V_PROD_ALIAS_SYN SYN ON TP.F_PRODUCT = SYN.F_PRODUCTLEFT JOIN V_PROD_ALIAS_SITE SIT ON TP.F_PRODUCT = SIT.F_PRODUCTLEFT JOIN V_PROD_ALIAS_ICO IC ON TP.F_PRODUCT = IC.F_PRODUCTLEFT JOIN V_PROD_ALIAS_MANU MAN ON TP.F_PRODUCT = MAN.F_PRODUCTLEFT JOIN V_PROD_ALIAS_CASN CASN ON TP.F_PRODUCT = CASN.F_PRODUCT
Below Query after commenting the column (MAN.F_PHRE AS MANU) in the select statement takes three seconds to display 53,000 records.SELECT DISTINCT TP.PRODUCT AS ID, TP.NAME AS [NAME], TP.LANGUAGE AS LANGCODE, CONVERT(VARCHAR, TP.F_DATE_REVISED, 120) AS RDATE, CASE WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUM,'¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUM,'¿',' '))),' ','; ') = '') THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUM,'¿',' '))),' ','; ') ELSE REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') END AS CASNUM , TP.F_CUSTOM1 AS cus1, TP.F_CUSTOM2 AS cus2, (SELECT TC.F_COUNTRY_NAME FROM COUNTRIES TC WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5, -- MAN.F_PHR AS MANU, CASE WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') THEN TP.F_CUSTOM3 ELSE SYN.F_DATA END AS SYN, IC.F_DATA AS ICO, 'SDS - ' + (SELECT TL.F_LAN_NAME FROM T_LANGUAGE TL WHERE TL.F_LANGUAGE = TP.F_LANGUAGE) + ' - PDF' AS DOC, '' AS COVER, CAST(TP.F_GUID AS VARCHAR(36)) + '_PDF' AS [GUID], 'PDF' AS SDSFROM PDF TPLEFT JOIN V_PROD_ALIAS_SYN SYN ON TP.F_PRODUCT = SYN.F_PRODUCTLEFT JOIN V_PROD_ALIAS_SITE SIT ON TP.F_PRODUCT = SIT.F_PRODUCTLEFT JOIN V_PROD_ALIAS_ICO IC ON TP.F_PRODUCT = IC.F_PRODUCTLEFT JOIN V_PROD_ALIAS_MANU MAN ON TP.F_PRODUCT = MAN.F_PRODUCTLEFT JOIN V_PROD_ALIAS_CASN CASN ON TP.F_PRODUCT = CASN.F_PRODUCT
Have you got an index on V_PROD_ALIAS_MANU(F_PRODUCT)?
CREATE INDEX IX_V_PROD_ALIAS_MANU_F_PRODUCT ON V_PROD_ALIAS_MANU(F_PRODUCT) INCLUDE (F_PHR)
If it's a view you will have to make the view schema bound to add an index.
If you want a more detailed answer (maybe indexes can be added to the underlying tables in the view) you will have to provide the definition of the view and what indexes are on the tables in the view.
Thank you.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply