March 23, 2004 at 2:47 pm
I have a view which is built up of script
CREATE View dbo.XXX as(
Select DISTINCT
CTRY_CD,
PROD_ID,
PACK_ID PPK_ID,
convert(VARCHAR(30), PROD_ID) + '-' + convert(VARCHAR(30), PACK_ID) PFC_CD,
--STRNT_UNIT_CD + STRNT_MEAS_CD + ADDL_STRNT_UNIT_CD PPK_STRNT_DESC,
PPK_DESC
From ABC.dbo.MKT_PROD_PK
UNION
SELECT CTRY_CD,
PROD_ID,
PPK_ID,
convert(VARCHAR(30), PROD_ID) + '-' + convert(VARCHAR(30), PPK_ID) PFC_CD,
--PPK_STRNT_DESC,
PPK_DESC
FROM XYZ..MKT_UNFID_PPK_DIM U
WHERE NOT EXISTS (SELECT 1
FROM ABC.dbo.MKT_PROD_PK M
where U.PROD_ID = M.PROD_ID
AND U.PPK_ID = M.PACK_ID
and U.CTRY_CD = M.CTRY_CD))
If I uncomment the statement shown in bold the query runs pretty slow compared to without that statement. Any clue why ist happeneing . Is it because concatenation slows down the query?? any help will be greatly apprecaited.'
TIA
March 24, 2004 at 9:03 am
Using Distinc and then doing concatenation is definitely going to slow down the query. Distinct can cause a table scan. You would be better off to use a group by in the first part of your union statement.
Select
CTRY_CD,
PROD_ID,
PACK_ID PPK_ID,
convert(VARCHAR(30), PROD_ID) + '-' + convert(VARCHAR(30), PACK_ID) PFC_CD,
STRNT_UNIT_CD + STRNT_MEAS_CD + ADDL_STRNT_UNIT_CD PPK_STRNT_DESC,
PPK_DESC
From
ABC.dbo.MKT_PROD_PK
Group By
CTRY_CD,
PROD_ID,
PACK_ID,
STRNT_UNIT_CD,
STRNT_MEAS_CD,
ADDL_STRNT_UNIT_CD,
PPK_DESC
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply