|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Help me performance optimizehope you are in for a challenge. I have the following query in a stored procedure. It takes a lot of time to run (hence around 2.5 million records to select from) Can anybody help me optimize this? CREATE PROCEDURE sp_Synergize_GetVariants @Lang char(2) , @kmatnr int AS DECLARE @_languageID char(2) , @_kmatnr int; SET @_languageID = @Lang; SET @_kmatnr = @kmatnr; SELECT dbo.vw_Synergize_Variants.VARIANTID, dbo.vw_Synergize_Variants.GEN_PRODUCT_NO, dbo.vw_Synergize_Variants.GEN_EL_NO, dbo.vw_Synergize_Variants.GEN_EDITION, vw_Synergize_All_Gen_Chars_11.DESCRIPT AS GEN_EDITION_DESCRIPT, dbo.vw_Synergize_Variants.GEN_VARIANT_TYPE, vw_Synergize_All_Gen_Chars_12.DESCRIPT AS GEN_VARIANT_TYPE_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SPECIAL, vw_Synergize_All_Gen_Chars_2.DESCRIPT AS GEN_SPECIAL_DESCRIPT, dbo.vw_Synergize_Variants.GEN_COLOUR_TYPE, vw_Synergize_All_Gen_Chars_3.DESCRIPT AS GEN_COLOUR_TYPE_DESCRIPT, dbo.vw_Synergize_Variants.GEN_COLOUR_SURFACE_2, vw_Synergize_All_Gen_Chars_4.DESCRIPT AS GEN_COLOUR_SURFACE_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_LIGHTSOURCE_SOCKET_BALL_2, vw_Synergize_All_Gen_Chars_5.DESCRIPT AS GEN_LIGHTSOURCE_SOCKET_BALL_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_ARM_TYPE_2, vw_Synergize_All_Gen_Chars_6.DESCRIPT AS GEN_ARM_TYPE_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_BODY, vw_Synergize_All_Gen_Chars_7.DESCRIPT AS GEN_BODY_DESCRIPT, dbo.vw_Synergize_Variants.GEN_CABLE_TYPE_2, vw_Synergize_All_Gen_Chars_8.DESCRIPT AS GEN_CABLE_TYPE_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_CANOPY, vw_Synergize_All_Gen_Chars_9.DESCRIPT AS GEN_CANOPY_DESCRIPT, dbo.vw_Synergize_Variants.GEN_CORNER_ELEMENTS, vw_Synergize_All_Gen_Chars_10.DESCRIPT AS GEN_CORNER_ELEMENTS_DESCRIPT, dbo.vw_Synergize_Variants.GEN_POLE_POST, vw_Synergize_All_Gen_Chars_13.DESCRIPT AS GEN_POLE_POST_DESCRIPT, dbo.vw_Synergize_Variants.GEN_DIMENSIONS_2, vw_Synergize_All_Gen_Chars_14.DESCRIPT AS GEN_DIMENSIONS_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_INSULATION_CLASS_2, vw_Synergize_All_Gen_Chars_15.DESCRIPT AS GEN_INSULATION_CLASS_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_FILTER, vw_Synergize_All_Gen_Chars_16.DESCRIPT AS GEN_FILTER_DESCRIPT, dbo.vw_Synergize_Variants.GEN_FRONT, vw_Synergize_All_Gen_Chars_17.DESCRIPT AS GEN_FRONT_DESCRIPT, dbo.vw_Synergize_Variants.GEN_LIGHT_TECHNIQUE, vw_Synergize_All_Gen_Chars_18.DESCRIPT AS GEN_LIGHT_TECHNIQUE_DESCRIPT, dbo.vw_Synergize_Variants.GEN_LOUVRE, vw_Synergize_All_Gen_Chars_19.DESCRIPT AS GEN_LOUVRE_DESCRIPT, dbo.vw_Synergize_Variants.GEN_MOUNTING_2, vw_Synergize_All_Gen_Chars_20.DESCRIPT AS GEN_MOUNTING_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_REFLECTOR, vw_Synergize_All_Gen_Chars_21.DESCRIPT AS GEN_REFLECTOR_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SHADE_2, vw_Synergize_All_Gen_Chars_22.DESCRIPT AS GEN_SHADE_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SHIELD_DIFFUSOR_2, vw_Synergize_All_Gen_Chars_23.DESCRIPT AS GEN_SHIELD_DIFFUSOR_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SOCKET_COVER_2, vw_Synergize_All_Gen_Chars_24.DESCRIPT AS GEN_SOCKET_COVER_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_STEM_BASE, vw_Synergize_All_Gen_Chars_25.DESCRIPT AS GEN_STEM_BASE_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SUSPENSION, vw_Synergize_All_Gen_Chars_26.DESCRIPT AS GEN_SUSPENSION_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SWITCH, vw_Synergize_All_Gen_Chars_27.DESCRIPT AS GEN_SWITCH_DESCRIPT, dbo.vw_Synergize_Variants.GEN_VOLTAGE_FREQUENCY_2, vw_Synergize_All_Gen_Chars_28.DESCRIPT AS GEN_VOLTAGE_FREQUENCY_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_ADDITIONAL_FEATURES, vw_Synergize_All_Gen_Chars_29.DESCRIPT AS GEN_ADDITIONAL_FEATURES_DESCRIPT, dbo.vw_Synergize_Variants.param FROM dbo.vw_Synergize_Variants LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_29 ON dbo.vw_Synergize_Variants.GEN_ADDITIONAL_FEATURES = vw_Synergize_All_Gen_Chars_29.CHARVALUE AND vw_Synergize_All_Gen_Chars_29.CHARNAME = 'GEN_ADDITIONAL_FEATURES' AND vw_Synergize_All_Gen_Chars_29.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_28 ON dbo.vw_Synergize_Variants.GEN_VOLTAGE_FREQUENCY_2 = vw_Synergize_All_Gen_Chars_28.CHARVALUE AND vw_Synergize_All_Gen_Chars_28.CHARNAME = 'GEN_VOLTAGE_FREQUENCY_2' AND vw_Synergize_All_Gen_Chars_28.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_27 ON dbo.vw_Synergize_Variants.GEN_SWITCH = vw_Synergize_All_Gen_Chars_27.CHARVALUE AND vw_Synergize_All_Gen_Chars_27.CHARNAME = 'GEN_SWITCH' AND vw_Synergize_All_Gen_Chars_27.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_26 ON dbo.vw_Synergize_Variants.GEN_SUSPENSION = vw_Synergize_All_Gen_Chars_26.CHARVALUE AND vw_Synergize_All_Gen_Chars_26.CHARNAME = 'GEN_SUSPENSION' AND vw_Synergize_All_Gen_Chars_26.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_25 ON dbo.vw_Synergize_Variants.GEN_STEM_BASE = vw_Synergize_All_Gen_Chars_25.CHARVALUE AND vw_Synergize_All_Gen_Chars_25.CHARNAME = 'GEN_STEM_BASE' AND vw_Synergize_All_Gen_Chars_25.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_24 ON dbo.vw_Synergize_Variants.GEN_SOCKET_COVER_2 = vw_Synergize_All_Gen_Chars_24.CHARVALUE AND vw_Synergize_All_Gen_Chars_24.CHARNAME = 'GEN_SOCKET_COVER_2' AND vw_Synergize_All_Gen_Chars_24.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_23 ON dbo.vw_Synergize_Variants.GEN_SHIELD_DIFFUSOR_2 = vw_Synergize_All_Gen_Chars_23.CHARVALUE AND vw_Synergize_All_Gen_Chars_23.CHARNAME = 'GEN_SHIELD_DIFFUSOR_2' AND vw_Synergize_All_Gen_Chars_23.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_22 ON dbo.vw_Synergize_Variants.GEN_SHADE_2 = vw_Synergize_All_Gen_Chars_22.CHARVALUE AND vw_Synergize_All_Gen_Chars_22.CHARNAME = 'GEN_SHADE_2' AND vw_Synergize_All_Gen_Chars_22.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_21 ON dbo.vw_Synergize_Variants.GEN_REFLECTOR = vw_Synergize_All_Gen_Chars_21.CHARVALUE AND vw_Synergize_All_Gen_Chars_21.CHARNAME = 'GEN_REFLECTOR' AND vw_Synergize_All_Gen_Chars_21.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_20 ON dbo.vw_Synergize_Variants.GEN_MOUNTING_2 = vw_Synergize_All_Gen_Chars_20.CHARVALUE AND vw_Synergize_All_Gen_Chars_20.CHARNAME = 'GEN_MOUNTING_2' AND vw_Synergize_All_Gen_Chars_20.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_19 ON dbo.vw_Synergize_Variants.GEN_LOUVRE = vw_Synergize_All_Gen_Chars_19.CHARVALUE AND vw_Synergize_All_Gen_Chars_19.CHARNAME = 'GEN_LOUVRE' AND vw_Synergize_All_Gen_Chars_19.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_18 ON dbo.vw_Synergize_Variants.GEN_LIGHT_TECHNIQUE = vw_Synergize_All_Gen_Chars_18.CHARVALUE AND vw_Synergize_All_Gen_Chars_18.CHARNAME = 'GEN_LIGHT_TECHNIQUE' AND vw_Synergize_All_Gen_Chars_18.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_17 ON dbo.vw_Synergize_Variants.GEN_FRONT = vw_Synergize_All_Gen_Chars_17.CHARVALUE AND vw_Synergize_All_Gen_Chars_17.CHARNAME = 'GEN_FRONT' AND vw_Synergize_All_Gen_Chars_17.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_16 ON dbo.vw_Synergize_Variants.GEN_FILTER = vw_Synergize_All_Gen_Chars_16.CHARVALUE AND vw_Synergize_All_Gen_Chars_16.CHARNAME = 'GEN_FILTER' AND vw_Synergize_All_Gen_Chars_16.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_15 ON dbo.vw_Synergize_Variants.GEN_INSULATION_CLASS_2 = vw_Synergize_All_Gen_Chars_15.CHARVALUE AND vw_Synergize_All_Gen_Chars_15.CHARNAME = 'GEN_INSULATION_CLASS_2' AND vw_Synergize_All_Gen_Chars_15.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_14 ON dbo.vw_Synergize_Variants.GEN_DIMENSIONS_2 = vw_Synergize_All_Gen_Chars_14.CHARVALUE AND vw_Synergize_All_Gen_Chars_14.CHARNAME = 'GEN_DIMENSIONS_2' AND vw_Synergize_All_Gen_Chars_14.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_12 ON dbo.vw_Synergize_Variants.GEN_VARIANT_TYPE = vw_Synergize_All_Gen_Chars_12.CHARVALUE AND vw_Synergize_All_Gen_Chars_12.CHARNAME = 'GEN_VARIANT_TYPE' AND vw_Synergize_All_Gen_Chars_12.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_9 ON dbo.vw_Synergize_Variants.GEN_CANOPY = vw_Synergize_All_Gen_Chars_9.CHARVALUE AND vw_Synergize_All_Gen_Chars_9.CHARNAME = 'GEN_CANOPY' AND vw_Synergize_All_Gen_Chars_9.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_13 ON dbo.vw_Synergize_Variants.GEN_POLE_POST = vw_Synergize_All_Gen_Chars_13.CHARVALUE AND vw_Synergize_All_Gen_Chars_13.CHARNAME = 'GEN_POLE_POST' AND vw_Synergize_All_Gen_Chars_13.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_11 ON dbo.vw_Synergize_Variants.GEN_EDITION = vw_Synergize_All_Gen_Chars_11.CHARVALUE AND vw_Synergize_All_Gen_Chars_11.CHARNAME = 'GEN_EDITION' AND vw_Synergize_All_Gen_Chars_11.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_10 ON dbo.vw_Synergize_Variants.GEN_CORNER_ELEMENTS = vw_Synergize_All_Gen_Chars_10.CHARVALUE AND vw_Synergize_All_Gen_Chars_10.CHARNAME = 'GEN_CORNER_ELEMENTS' AND vw_Synergize_All_Gen_Chars_10.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_8 ON dbo.vw_Synergize_Variants.GEN_CABLE_TYPE_2 = vw_Synergize_All_Gen_Chars_8.CHARVALUE AND vw_Synergize_All_Gen_Chars_8.CHARNAME = 'GEN_CABLE_TYPE_2' AND vw_Synergize_All_Gen_Chars_8.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_7 ON dbo.vw_Synergize_Variants.GEN_BODY = vw_Synergize_All_Gen_Chars_7.CHARVALUE AND vw_Synergize_All_Gen_Chars_7.CHARNAME = 'GEN_BODY' AND vw_Synergize_All_Gen_Chars_7.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_6 ON dbo.vw_Synergize_Variants.GEN_ARM_TYPE_2 = vw_Synergize_All_Gen_Chars_6.CHARVALUE AND vw_Synergize_All_Gen_Chars_6.CHARNAME = 'GEN_ARM_TYPE_2' AND vw_Synergize_All_Gen_Chars_6.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_5 ON dbo.vw_Synergize_Variants.GEN_LIGHTSOURCE_SOCKET_BALL_2 = vw_Synergize_All_Gen_Chars_5.CHARVALUE AND vw_Synergize_All_Gen_Chars_5.CHARNAME = 'GEN_LIGHTSOURCE_SOCKET_BALL_2' AND vw_Synergize_All_Gen_Chars_5.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_4 ON dbo.vw_Synergize_Variants.GEN_COLOUR_SURFACE_2 = vw_Synergize_All_Gen_Chars_4.CHARVALUE AND vw_Synergize_All_Gen_Chars_4.CHARNAME = 'GEN_COLOUR_SURFACE_2' AND vw_Synergize_All_Gen_Chars_4.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_3 ON dbo.vw_Synergize_Variants.GEN_COLOUR_TYPE = vw_Synergize_All_Gen_Chars_3.CHARVALUE AND vw_Synergize_All_Gen_Chars_3.CHARNAME = 'GEN_COLOUR_TYPE' AND vw_Synergize_All_Gen_Chars_3.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_2 ON dbo.vw_Synergize_Variants.GEN_SPECIAL = vw_Synergize_All_Gen_Chars_2.CHARVALUE AND vw_Synergize_All_Gen_Chars_2.CHARNAME = 'GEN_SPECIAL' AND vw_Synergize_All_Gen_Chars_2.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars vw_Synergize_All_Gen_Chars_1 ON dbo.vw_Synergize_Variants.GEN_VARIANT_TYPE = vw_Synergize_All_Gen_Chars_1.CHARVALUE AND vw_Synergize_All_Gen_Chars_1.CHARNAME = 'GEN_VARIANT_TYPE' AND vw_Synergize_All_Gen_Chars_1.[Language] = @_languageID LEFT OUTER JOIN dbo.vw_Synergize_All_Gen_Chars ON dbo.vw_Synergize_Variants.GEN_EDITION = dbo.vw_Synergize_All_Gen_Chars.CHARVALUE AND dbo.vw_Synergize_All_Gen_Chars.CHARNAME = 'GEN_EDITION' AND dbo.vw_Synergize_All_Gen_Chars.[Language] = @_languageID WHERE (dbo.vw_Synergize_Variants.GEN_PRODUCT_NO = @_kmatnr) AND (dbo.vw_Synergize_Variants.GEN_VARIANT_TYPE = 1) GO Thanks in advance /Rasmus I presume "vw_" designates view.
Anyway, this is a mess. Perhaps you should share with us the DDL of the tables first, or better yet - start with the business requirements. We can help you dig yourself out of a mud hole, but wouldn't it be better if we could help you not falling into it in the first place? ML --- http://milambda.blogspot.com/ Rasmus Lynggaard wrote:
Show quote > Hi all Egad... Without knowing table schemas, indexes, the definition of the > hope you are in for a challenge. I have the following query in a stored > procedure. > It takes a lot of time to run (hence around 2.5 million records to select > from) > Can anybody help me optimize this? > > CREATE PROCEDURE sp_Synergize_GetVariants > @Lang char(2) > , @kmatnr int > AS > DECLARE > @_languageID char(2) > , @_kmatnr int; > > SET @_languageID = @Lang; > SET @_kmatnr = @kmatnr; > SELECT dbo.vw_Synergize_Variants.VARIANTID, > dbo.vw_Synergize_Variants.GEN_PRODUCT_NO, > dbo.vw_Synergize_Variants.GEN_EL_NO, > dbo.vw_Synergize_Variants.GEN_EDITION, > vw_Synergize_All_Gen_Chars_11.DESCRIPT AS GEN_EDITION_DESCRIPT, > dbo.vw_Synergize_Variants.GEN_VARIANT_TYPE, > vw_Synergize_All_Gen_Chars_12.DESCRIPT AS GEN_VARIANT_TYPE_DESCRIPT, > dbo.vw_Synergize_Variants.GEN_SPECIAL, > vw_Synergize_All_Gen_Chars_2.DESCRIPT AS GEN_SPECIAL_DESCRIPT, > dbo.vw_Synergize_Variants.GEN_COLOUR_TYPE, > vw_Synergize_All_Gen_Chars_3.DESCRIPT AS GEN_COLOUR_TYPE_DESCRIPT, > dbo.vw_Synergize_Variants.GEN_COLOUR_SURFACE_2, > vw_Synergize_All_Gen_Chars_4.DESCRIPT AS GEN_COLOUR_SURFACE_2_DESCRIPT, > > dbo.vw_Synergize_Variants.GEN_LIGHTSOURCE_SOCKET_BALL_2, > vw_Synergize_All_Gen_Chars_5.DESCRIPT AS > GEN_LIGHTSOURCE_SOCKET_BALL_2_DESCRIPT, > dbo.vw_Synergize_Variants.GEN_ARM_TYPE_2, > vw_Synergize_All_Gen_Chars_6.DESCRIPT AS > GEN_ARM_TYPE_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_BODY, > vw_Synergize_All_Gen_Chars_7.DESCRIPT AS > GEN_BODY_DESCRIPT, dbo.vw_Synergize_Variants.GEN_CABLE_TYPE_2, > vw_Synergize_All_Gen_Chars_8.DESCRIPT AS > GEN_CABLE_TYPE_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_CANOPY, > vw_Synergize_All_Gen_Chars_9.DESCRIPT AS > GEN_CANOPY_DESCRIPT, dbo.vw_Synergize_Variants.GEN_CORNER_ELEMENTS, > vw_Synergize_All_Gen_Chars_10.DESCRIPT AS > GEN_CORNER_ELEMENTS_DESCRIPT, dbo.vw_Synergize_Variants.GEN_POLE_POST, > vw_Synergize_All_Gen_Chars_13.DESCRIPT AS > GEN_POLE_POST_DESCRIPT, dbo.vw_Synergize_Variants.GEN_DIMENSIONS_2, > vw_Synergize_All_Gen_Chars_14.DESCRIPT AS > GEN_DIMENSIONS_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_INSULATION_CLASS_2, > vw_Synergize_All_Gen_Chars_15.DESCRIPT AS > GEN_INSULATION_CLASS_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_FILTER, > vw_Synergize_All_Gen_Chars_16.DESCRIPT AS > GEN_FILTER_DESCRIPT, dbo.vw_Synergize_Variants.GEN_FRONT, > vw_Synergize_All_Gen_Chars_17.DESCRIPT AS > GEN_FRONT_DESCRIPT, dbo.vw_Synergize_Variants.GEN_LIGHT_TECHNIQUE, > vw_Synergize_All_Gen_Chars_18.DESCRIPT AS > GEN_LIGHT_TECHNIQUE_DESCRIPT, dbo.vw_Synergize_Variants.GEN_LOUVRE, > vw_Synergize_All_Gen_Chars_19.DESCRIPT AS > GEN_LOUVRE_DESCRIPT, dbo.vw_Synergize_Variants.GEN_MOUNTING_2, > vw_Synergize_All_Gen_Chars_20.DESCRIPT AS > GEN_MOUNTING_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_REFLECTOR, > vw_Synergize_All_Gen_Chars_21.DESCRIPT AS > GEN_REFLECTOR_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SHADE_2, > vw_Synergize_All_Gen_Chars_22.DESCRIPT AS > GEN_SHADE_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SHIELD_DIFFUSOR_2, > vw_Synergize_All_Gen_Chars_23.DESCRIPT AS > GEN_SHIELD_DIFFUSOR_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SOCKET_COVER_2, > vw_Synergize_All_Gen_Chars_24.DESCRIPT AS > GEN_SOCKET_COVER_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_STEM_BASE, > vw_Synergize_All_Gen_Chars_25.DESCRIPT AS > GEN_STEM_BASE_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SUSPENSION, > vw_Synergize_All_Gen_Chars_26.DESCRIPT AS > GEN_SUSPENSION_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SWITCH, > vw_Synergize_All_Gen_Chars_27.DESCRIPT AS > GEN_SWITCH_DESCRIPT, dbo.vw_Synergize_Variants.GEN_VOLTAGE_FREQUENCY_2, > vw_Synergize_All_Gen_Chars_28.DESCRIPT AS > GEN_VOLTAGE_FREQUENCY_2_DESCRIPT, > dbo.vw_Synergize_Variants.GEN_ADDITIONAL_FEATURES, > vw_Synergize_All_Gen_Chars_29.DESCRIPT AS GEN_ADDITIONAL_FEATURES_DESCRIPT, > dbo.vw_Synergize_Variants.param > FROM dbo.vw_Synergize_Variants LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_29 ON > dbo.vw_Synergize_Variants.GEN_ADDITIONAL_FEATURES = > vw_Synergize_All_Gen_Chars_29.CHARVALUE AND > vw_Synergize_All_Gen_Chars_29.CHARNAME = > 'GEN_ADDITIONAL_FEATURES' AND > vw_Synergize_All_Gen_Chars_29.[Language] = > @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_28 ON > dbo.vw_Synergize_Variants.GEN_VOLTAGE_FREQUENCY_2 = > vw_Synergize_All_Gen_Chars_28.CHARVALUE AND > vw_Synergize_All_Gen_Chars_28.CHARNAME = > 'GEN_VOLTAGE_FREQUENCY_2' AND > vw_Synergize_All_Gen_Chars_28.[Language] = > @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_27 ON > dbo.vw_Synergize_Variants.GEN_SWITCH = > vw_Synergize_All_Gen_Chars_27.CHARVALUE AND > vw_Synergize_All_Gen_Chars_27.CHARNAME = 'GEN_SWITCH' > AND vw_Synergize_All_Gen_Chars_27.[Language] = @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_26 ON > dbo.vw_Synergize_Variants.GEN_SUSPENSION = > vw_Synergize_All_Gen_Chars_26.CHARVALUE AND > vw_Synergize_All_Gen_Chars_26.CHARNAME = > 'GEN_SUSPENSION' AND vw_Synergize_All_Gen_Chars_26.[Language] = @_languageID > LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_25 ON > dbo.vw_Synergize_Variants.GEN_STEM_BASE = > vw_Synergize_All_Gen_Chars_25.CHARVALUE AND > vw_Synergize_All_Gen_Chars_25.CHARNAME = > 'GEN_STEM_BASE' AND vw_Synergize_All_Gen_Chars_25.[Language] = @_languageID > LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_24 ON > dbo.vw_Synergize_Variants.GEN_SOCKET_COVER_2 = > vw_Synergize_All_Gen_Chars_24.CHARVALUE AND > vw_Synergize_All_Gen_Chars_24.CHARNAME = > 'GEN_SOCKET_COVER_2' AND vw_Synergize_All_Gen_Chars_24.[Language] = > @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_23 ON > dbo.vw_Synergize_Variants.GEN_SHIELD_DIFFUSOR_2 = > vw_Synergize_All_Gen_Chars_23.CHARVALUE AND > vw_Synergize_All_Gen_Chars_23.CHARNAME = > 'GEN_SHIELD_DIFFUSOR_2' AND > vw_Synergize_All_Gen_Chars_23.[Language] = > @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_22 ON > dbo.vw_Synergize_Variants.GEN_SHADE_2 = > vw_Synergize_All_Gen_Chars_22.CHARVALUE AND > vw_Synergize_All_Gen_Chars_22.CHARNAME = 'GEN_SHADE_2' > AND vw_Synergize_All_Gen_Chars_22.[Language] = @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_21 ON > dbo.vw_Synergize_Variants.GEN_REFLECTOR = > vw_Synergize_All_Gen_Chars_21.CHARVALUE AND > vw_Synergize_All_Gen_Chars_21.CHARNAME = > 'GEN_REFLECTOR' AND vw_Synergize_All_Gen_Chars_21.[Language] = @_languageID > LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_20 ON > dbo.vw_Synergize_Variants.GEN_MOUNTING_2 = > vw_Synergize_All_Gen_Chars_20.CHARVALUE AND > vw_Synergize_All_Gen_Chars_20.CHARNAME = > 'GEN_MOUNTING_2' AND vw_Synergize_All_Gen_Chars_20.[Language] = @_languageID > LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_19 ON > dbo.vw_Synergize_Variants.GEN_LOUVRE = > vw_Synergize_All_Gen_Chars_19.CHARVALUE AND > vw_Synergize_All_Gen_Chars_19.CHARNAME = 'GEN_LOUVRE' > AND vw_Synergize_All_Gen_Chars_19.[Language] = @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_18 ON > dbo.vw_Synergize_Variants.GEN_LIGHT_TECHNIQUE = > vw_Synergize_All_Gen_Chars_18.CHARVALUE AND > vw_Synergize_All_Gen_Chars_18.CHARNAME = > 'GEN_LIGHT_TECHNIQUE' AND vw_Synergize_All_Gen_Chars_18.[Language] = > @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_17 ON > dbo.vw_Synergize_Variants.GEN_FRONT = > vw_Synergize_All_Gen_Chars_17.CHARVALUE AND > vw_Synergize_All_Gen_Chars_17.CHARNAME = 'GEN_FRONT' > AND vw_Synergize_All_Gen_Chars_17.[Language] = @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_16 ON > dbo.vw_Synergize_Variants.GEN_FILTER = > vw_Synergize_All_Gen_Chars_16.CHARVALUE AND > vw_Synergize_All_Gen_Chars_16.CHARNAME = 'GEN_FILTER' > AND vw_Synergize_All_Gen_Chars_16.[Language] = @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_15 ON > dbo.vw_Synergize_Variants.GEN_INSULATION_CLASS_2 = > vw_Synergize_All_Gen_Chars_15.CHARVALUE AND > vw_Synergize_All_Gen_Chars_15.CHARNAME = > 'GEN_INSULATION_CLASS_2' AND > vw_Synergize_All_Gen_Chars_15.[Language] = > @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_14 ON > dbo.vw_Synergize_Variants.GEN_DIMENSIONS_2 = > vw_Synergize_All_Gen_Chars_14.CHARVALUE AND > vw_Synergize_All_Gen_Chars_14.CHARNAME = > 'GEN_DIMENSIONS_2' AND vw_Synergize_All_Gen_Chars_14.[Language] = > @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_12 ON > dbo.vw_Synergize_Variants.GEN_VARIANT_TYPE = > vw_Synergize_All_Gen_Chars_12.CHARVALUE AND > vw_Synergize_All_Gen_Chars_12.CHARNAME = > 'GEN_VARIANT_TYPE' AND vw_Synergize_All_Gen_Chars_12.[Language] = > @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_9 ON > dbo.vw_Synergize_Variants.GEN_CANOPY = > vw_Synergize_All_Gen_Chars_9.CHARVALUE AND > vw_Synergize_All_Gen_Chars_9.CHARNAME = 'GEN_CANOPY' > AND vw_Synergize_All_Gen_Chars_9.[Language] = @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_13 ON > dbo.vw_Synergize_Variants.GEN_POLE_POST = > vw_Synergize_All_Gen_Chars_13.CHARVALUE AND > vw_Synergize_All_Gen_Chars_13.CHARNAME = > 'GEN_POLE_POST' AND vw_Synergize_All_Gen_Chars_13.[Language] = @_languageID > LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_11 ON > dbo.vw_Synergize_Variants.GEN_EDITION = > vw_Synergize_All_Gen_Chars_11.CHARVALUE AND > vw_Synergize_All_Gen_Chars_11.CHARNAME = 'GEN_EDITION' > AND vw_Synergize_All_Gen_Chars_11.[Language] = @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_10 ON > dbo.vw_Synergize_Variants.GEN_CORNER_ELEMENTS = > vw_Synergize_All_Gen_Chars_10.CHARVALUE AND > vw_Synergize_All_Gen_Chars_10.CHARNAME = > 'GEN_CORNER_ELEMENTS' AND > vw_Synergize_All_Gen_Chars_10.[Language] = > @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_8 ON > dbo.vw_Synergize_Variants.GEN_CABLE_TYPE_2 = > vw_Synergize_All_Gen_Chars_8.CHARVALUE AND > vw_Synergize_All_Gen_Chars_8.CHARNAME = > 'GEN_CABLE_TYPE_2' AND vw_Synergize_All_Gen_Chars_8.[Language] = @_languageID > LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_7 ON > dbo.vw_Synergize_Variants.GEN_BODY = > vw_Synergize_All_Gen_Chars_7.CHARVALUE AND > vw_Synergize_All_Gen_Chars_7.CHARNAME = 'GEN_BODY' AND > vw_Synergize_All_Gen_Chars_7.[Language] = @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_6 ON > dbo.vw_Synergize_Variants.GEN_ARM_TYPE_2 = > vw_Synergize_All_Gen_Chars_6.CHARVALUE AND > vw_Synergize_All_Gen_Chars_6.CHARNAME = > 'GEN_ARM_TYPE_2' AND vw_Synergize_All_Gen_Chars_6.[Language] = @_languageID > LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_5 ON > > dbo.vw_Synergize_Variants.GEN_LIGHTSOURCE_SOCKET_BALL_2 = > vw_Synergize_All_Gen_Chars_5.CHARVALUE AND > vw_Synergize_All_Gen_Chars_5.CHARNAME = > 'GEN_LIGHTSOURCE_SOCKET_BALL_2' AND > vw_Synergize_All_Gen_Chars_5.[Language] = @_languageID > LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_4 ON > dbo.vw_Synergize_Variants.GEN_COLOUR_SURFACE_2 = > vw_Synergize_All_Gen_Chars_4.CHARVALUE AND > vw_Synergize_All_Gen_Chars_4.CHARNAME = > 'GEN_COLOUR_SURFACE_2' AND vw_Synergize_All_Gen_Chars_4.[Language] = > @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_3 ON > dbo.vw_Synergize_Variants.GEN_COLOUR_TYPE = > vw_Synergize_All_Gen_Chars_3.CHARVALUE AND > vw_Synergize_All_Gen_Chars_3.CHARNAME = > 'GEN_COLOUR_TYPE' AND vw_Synergize_All_Gen_Chars_3.[Language] = @_languageID > LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_2 ON > dbo.vw_Synergize_Variants.GEN_SPECIAL = > vw_Synergize_All_Gen_Chars_2.CHARVALUE AND > vw_Synergize_All_Gen_Chars_2.CHARNAME = 'GEN_SPECIAL' > AND vw_Synergize_All_Gen_Chars_2.[Language] = @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_1 ON > dbo.vw_Synergize_Variants.GEN_VARIANT_TYPE = > vw_Synergize_All_Gen_Chars_1.CHARVALUE AND > vw_Synergize_All_Gen_Chars_1.CHARNAME = > 'GEN_VARIANT_TYPE' AND vw_Synergize_All_Gen_Chars_1.[Language] = @_languageID > LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars ON > dbo.vw_Synergize_Variants.GEN_EDITION = > dbo.vw_Synergize_All_Gen_Chars.CHARVALUE AND > dbo.vw_Synergize_All_Gen_Chars.CHARNAME = > 'GEN_EDITION' AND dbo.vw_Synergize_All_Gen_Chars.[Language] = @_languageID > WHERE (dbo.vw_Synergize_Variants.GEN_PRODUCT_NO = @_kmatnr) AND > (dbo.vw_Synergize_Variants.GEN_VARIANT_TYPE = 1) > GO > > > Thanks in advance > /Rasmus views, it's impossible for us to make suggestions. Have you at least looked at the execution plan to see where the bottleneck is? On Tue, 12 Sep 2006 02:30:02 -0700, Rasmus Lynggaard <Rasmus
Lyngga***@discussions.microsoft.com> wrote: >Hi all Not from the information provided. Query optimization requires>hope you are in for a challenge. I have the following query in a stored >procedure. >It takes a lot of time to run (hence around 2.5 million records to select >from) >Can anybody help me optimize this? understanding the underlying tables, indexes and data, as well as the code behind any views. (In this particular case, ESPECIALLY the code behind the views.) None of that was provided. If you provide that somone may have some ideas. Roy Harvey Beacon Falls, CT Although there isn't enough information here to provide much help, I would
expect unique indexes on CHARNAME, CHARVALUE and Language columns to help. You might try evaluating proc execution of this proc using the Index Tuning Wizard or Database Tuning Advisor. -- Hope this helps. Dan Guzman SQL Server MVP Show quote "Rasmus Lynggaard" <Rasmus Lyngga***@discussions.microsoft.com> wrote in message news:ADBC4D37-7540-4882-BEA9-91450A7119EC@microsoft.com... > Hi all > hope you are in for a challenge. I have the following query in a stored > procedure. > It takes a lot of time to run (hence around 2.5 million records to select > from) > Can anybody help me optimize this? > > CREATE PROCEDURE sp_Synergize_GetVariants > @Lang char(2) > , @kmatnr int > AS > DECLARE > @_languageID char(2) > , @_kmatnr int; > > SET @_languageID = @Lang; > SET @_kmatnr = @kmatnr; > SELECT dbo.vw_Synergize_Variants.VARIANTID, > dbo.vw_Synergize_Variants.GEN_PRODUCT_NO, > dbo.vw_Synergize_Variants.GEN_EL_NO, > dbo.vw_Synergize_Variants.GEN_EDITION, > vw_Synergize_All_Gen_Chars_11.DESCRIPT AS GEN_EDITION_DESCRIPT, > dbo.vw_Synergize_Variants.GEN_VARIANT_TYPE, > vw_Synergize_All_Gen_Chars_12.DESCRIPT AS GEN_VARIANT_TYPE_DESCRIPT, > dbo.vw_Synergize_Variants.GEN_SPECIAL, > vw_Synergize_All_Gen_Chars_2.DESCRIPT AS GEN_SPECIAL_DESCRIPT, > dbo.vw_Synergize_Variants.GEN_COLOUR_TYPE, > vw_Synergize_All_Gen_Chars_3.DESCRIPT AS GEN_COLOUR_TYPE_DESCRIPT, > dbo.vw_Synergize_Variants.GEN_COLOUR_SURFACE_2, > vw_Synergize_All_Gen_Chars_4.DESCRIPT AS GEN_COLOUR_SURFACE_2_DESCRIPT, > > dbo.vw_Synergize_Variants.GEN_LIGHTSOURCE_SOCKET_BALL_2, > vw_Synergize_All_Gen_Chars_5.DESCRIPT AS > GEN_LIGHTSOURCE_SOCKET_BALL_2_DESCRIPT, > dbo.vw_Synergize_Variants.GEN_ARM_TYPE_2, > vw_Synergize_All_Gen_Chars_6.DESCRIPT AS > GEN_ARM_TYPE_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_BODY, > vw_Synergize_All_Gen_Chars_7.DESCRIPT AS > GEN_BODY_DESCRIPT, dbo.vw_Synergize_Variants.GEN_CABLE_TYPE_2, > vw_Synergize_All_Gen_Chars_8.DESCRIPT AS > GEN_CABLE_TYPE_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_CANOPY, > vw_Synergize_All_Gen_Chars_9.DESCRIPT AS > GEN_CANOPY_DESCRIPT, dbo.vw_Synergize_Variants.GEN_CORNER_ELEMENTS, > vw_Synergize_All_Gen_Chars_10.DESCRIPT AS > GEN_CORNER_ELEMENTS_DESCRIPT, dbo.vw_Synergize_Variants.GEN_POLE_POST, > vw_Synergize_All_Gen_Chars_13.DESCRIPT AS > GEN_POLE_POST_DESCRIPT, dbo.vw_Synergize_Variants.GEN_DIMENSIONS_2, > vw_Synergize_All_Gen_Chars_14.DESCRIPT AS > GEN_DIMENSIONS_2_DESCRIPT, > dbo.vw_Synergize_Variants.GEN_INSULATION_CLASS_2, > vw_Synergize_All_Gen_Chars_15.DESCRIPT AS > GEN_INSULATION_CLASS_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_FILTER, > vw_Synergize_All_Gen_Chars_16.DESCRIPT AS > GEN_FILTER_DESCRIPT, dbo.vw_Synergize_Variants.GEN_FRONT, > vw_Synergize_All_Gen_Chars_17.DESCRIPT AS > GEN_FRONT_DESCRIPT, dbo.vw_Synergize_Variants.GEN_LIGHT_TECHNIQUE, > vw_Synergize_All_Gen_Chars_18.DESCRIPT AS > GEN_LIGHT_TECHNIQUE_DESCRIPT, dbo.vw_Synergize_Variants.GEN_LOUVRE, > vw_Synergize_All_Gen_Chars_19.DESCRIPT AS > GEN_LOUVRE_DESCRIPT, dbo.vw_Synergize_Variants.GEN_MOUNTING_2, > vw_Synergize_All_Gen_Chars_20.DESCRIPT AS > GEN_MOUNTING_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_REFLECTOR, > vw_Synergize_All_Gen_Chars_21.DESCRIPT AS > GEN_REFLECTOR_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SHADE_2, > vw_Synergize_All_Gen_Chars_22.DESCRIPT AS > GEN_SHADE_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SHIELD_DIFFUSOR_2, > vw_Synergize_All_Gen_Chars_23.DESCRIPT AS > GEN_SHIELD_DIFFUSOR_2_DESCRIPT, > dbo.vw_Synergize_Variants.GEN_SOCKET_COVER_2, > vw_Synergize_All_Gen_Chars_24.DESCRIPT AS > GEN_SOCKET_COVER_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_STEM_BASE, > vw_Synergize_All_Gen_Chars_25.DESCRIPT AS > GEN_STEM_BASE_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SUSPENSION, > vw_Synergize_All_Gen_Chars_26.DESCRIPT AS > GEN_SUSPENSION_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SWITCH, > vw_Synergize_All_Gen_Chars_27.DESCRIPT AS > GEN_SWITCH_DESCRIPT, dbo.vw_Synergize_Variants.GEN_VOLTAGE_FREQUENCY_2, > vw_Synergize_All_Gen_Chars_28.DESCRIPT AS > GEN_VOLTAGE_FREQUENCY_2_DESCRIPT, > dbo.vw_Synergize_Variants.GEN_ADDITIONAL_FEATURES, > vw_Synergize_All_Gen_Chars_29.DESCRIPT AS > GEN_ADDITIONAL_FEATURES_DESCRIPT, > dbo.vw_Synergize_Variants.param > FROM dbo.vw_Synergize_Variants LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_29 ON > dbo.vw_Synergize_Variants.GEN_ADDITIONAL_FEATURES = > vw_Synergize_All_Gen_Chars_29.CHARVALUE AND > vw_Synergize_All_Gen_Chars_29.CHARNAME = > 'GEN_ADDITIONAL_FEATURES' AND > vw_Synergize_All_Gen_Chars_29.[Language] = > @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_28 ON > dbo.vw_Synergize_Variants.GEN_VOLTAGE_FREQUENCY_2 = > vw_Synergize_All_Gen_Chars_28.CHARVALUE AND > vw_Synergize_All_Gen_Chars_28.CHARNAME = > 'GEN_VOLTAGE_FREQUENCY_2' AND > vw_Synergize_All_Gen_Chars_28.[Language] = > @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_27 ON > dbo.vw_Synergize_Variants.GEN_SWITCH = > vw_Synergize_All_Gen_Chars_27.CHARVALUE AND > vw_Synergize_All_Gen_Chars_27.CHARNAME = 'GEN_SWITCH' > AND vw_Synergize_All_Gen_Chars_27.[Language] = @_languageID LEFT OUTER > JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_26 ON > dbo.vw_Synergize_Variants.GEN_SUSPENSION = > vw_Synergize_All_Gen_Chars_26.CHARVALUE AND > vw_Synergize_All_Gen_Chars_26.CHARNAME = > 'GEN_SUSPENSION' AND vw_Synergize_All_Gen_Chars_26.[Language] = > @_languageID > LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_25 ON > dbo.vw_Synergize_Variants.GEN_STEM_BASE = > vw_Synergize_All_Gen_Chars_25.CHARVALUE AND > vw_Synergize_All_Gen_Chars_25.CHARNAME = > 'GEN_STEM_BASE' AND vw_Synergize_All_Gen_Chars_25.[Language] = > @_languageID > LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_24 ON > dbo.vw_Synergize_Variants.GEN_SOCKET_COVER_2 = > vw_Synergize_All_Gen_Chars_24.CHARVALUE AND > vw_Synergize_All_Gen_Chars_24.CHARNAME = > 'GEN_SOCKET_COVER_2' AND vw_Synergize_All_Gen_Chars_24.[Language] = > @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_23 ON > dbo.vw_Synergize_Variants.GEN_SHIELD_DIFFUSOR_2 = > vw_Synergize_All_Gen_Chars_23.CHARVALUE AND > vw_Synergize_All_Gen_Chars_23.CHARNAME = > 'GEN_SHIELD_DIFFUSOR_2' AND > vw_Synergize_All_Gen_Chars_23.[Language] = > @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_22 ON > dbo.vw_Synergize_Variants.GEN_SHADE_2 = > vw_Synergize_All_Gen_Chars_22.CHARVALUE AND > vw_Synergize_All_Gen_Chars_22.CHARNAME = > 'GEN_SHADE_2' > AND vw_Synergize_All_Gen_Chars_22.[Language] = @_languageID LEFT OUTER > JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_21 ON > dbo.vw_Synergize_Variants.GEN_REFLECTOR = > vw_Synergize_All_Gen_Chars_21.CHARVALUE AND > vw_Synergize_All_Gen_Chars_21.CHARNAME = > 'GEN_REFLECTOR' AND vw_Synergize_All_Gen_Chars_21.[Language] = > @_languageID > LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_20 ON > dbo.vw_Synergize_Variants.GEN_MOUNTING_2 = > vw_Synergize_All_Gen_Chars_20.CHARVALUE AND > vw_Synergize_All_Gen_Chars_20.CHARNAME = > 'GEN_MOUNTING_2' AND vw_Synergize_All_Gen_Chars_20.[Language] = > @_languageID > LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_19 ON > dbo.vw_Synergize_Variants.GEN_LOUVRE = > vw_Synergize_All_Gen_Chars_19.CHARVALUE AND > vw_Synergize_All_Gen_Chars_19.CHARNAME = 'GEN_LOUVRE' > AND vw_Synergize_All_Gen_Chars_19.[Language] = @_languageID LEFT OUTER > JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_18 ON > dbo.vw_Synergize_Variants.GEN_LIGHT_TECHNIQUE = > vw_Synergize_All_Gen_Chars_18.CHARVALUE AND > vw_Synergize_All_Gen_Chars_18.CHARNAME = > 'GEN_LIGHT_TECHNIQUE' AND vw_Synergize_All_Gen_Chars_18.[Language] = > @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_17 ON > dbo.vw_Synergize_Variants.GEN_FRONT = > vw_Synergize_All_Gen_Chars_17.CHARVALUE AND > vw_Synergize_All_Gen_Chars_17.CHARNAME = 'GEN_FRONT' > AND vw_Synergize_All_Gen_Chars_17.[Language] = @_languageID LEFT OUTER > JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_16 ON > dbo.vw_Synergize_Variants.GEN_FILTER = > vw_Synergize_All_Gen_Chars_16.CHARVALUE AND > vw_Synergize_All_Gen_Chars_16.CHARNAME = 'GEN_FILTER' > AND vw_Synergize_All_Gen_Chars_16.[Language] = @_languageID LEFT OUTER > JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_15 ON > dbo.vw_Synergize_Variants.GEN_INSULATION_CLASS_2 = > vw_Synergize_All_Gen_Chars_15.CHARVALUE AND > vw_Synergize_All_Gen_Chars_15.CHARNAME = > 'GEN_INSULATION_CLASS_2' AND > vw_Synergize_All_Gen_Chars_15.[Language] = > @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_14 ON > dbo.vw_Synergize_Variants.GEN_DIMENSIONS_2 = > vw_Synergize_All_Gen_Chars_14.CHARVALUE AND > vw_Synergize_All_Gen_Chars_14.CHARNAME = > 'GEN_DIMENSIONS_2' AND vw_Synergize_All_Gen_Chars_14.[Language] = > @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_12 ON > dbo.vw_Synergize_Variants.GEN_VARIANT_TYPE = > vw_Synergize_All_Gen_Chars_12.CHARVALUE AND > vw_Synergize_All_Gen_Chars_12.CHARNAME = > 'GEN_VARIANT_TYPE' AND vw_Synergize_All_Gen_Chars_12.[Language] = > @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_9 ON > dbo.vw_Synergize_Variants.GEN_CANOPY = > vw_Synergize_All_Gen_Chars_9.CHARVALUE AND > vw_Synergize_All_Gen_Chars_9.CHARNAME = 'GEN_CANOPY' > AND vw_Synergize_All_Gen_Chars_9.[Language] = @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_13 ON > dbo.vw_Synergize_Variants.GEN_POLE_POST = > vw_Synergize_All_Gen_Chars_13.CHARVALUE AND > vw_Synergize_All_Gen_Chars_13.CHARNAME = > 'GEN_POLE_POST' AND vw_Synergize_All_Gen_Chars_13.[Language] = > @_languageID > LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_11 ON > dbo.vw_Synergize_Variants.GEN_EDITION = > vw_Synergize_All_Gen_Chars_11.CHARVALUE AND > vw_Synergize_All_Gen_Chars_11.CHARNAME = > 'GEN_EDITION' > AND vw_Synergize_All_Gen_Chars_11.[Language] = @_languageID LEFT OUTER > JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_10 ON > dbo.vw_Synergize_Variants.GEN_CORNER_ELEMENTS = > vw_Synergize_All_Gen_Chars_10.CHARVALUE AND > vw_Synergize_All_Gen_Chars_10.CHARNAME = > 'GEN_CORNER_ELEMENTS' AND > vw_Synergize_All_Gen_Chars_10.[Language] = > @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_8 ON > dbo.vw_Synergize_Variants.GEN_CABLE_TYPE_2 = > vw_Synergize_All_Gen_Chars_8.CHARVALUE AND > vw_Synergize_All_Gen_Chars_8.CHARNAME = > 'GEN_CABLE_TYPE_2' AND vw_Synergize_All_Gen_Chars_8.[Language] = > @_languageID > LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_7 ON > dbo.vw_Synergize_Variants.GEN_BODY = > vw_Synergize_All_Gen_Chars_7.CHARVALUE AND > vw_Synergize_All_Gen_Chars_7.CHARNAME = 'GEN_BODY' > AND > vw_Synergize_All_Gen_Chars_7.[Language] = @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_6 ON > dbo.vw_Synergize_Variants.GEN_ARM_TYPE_2 = > vw_Synergize_All_Gen_Chars_6.CHARVALUE AND > vw_Synergize_All_Gen_Chars_6.CHARNAME = > 'GEN_ARM_TYPE_2' AND vw_Synergize_All_Gen_Chars_6.[Language] = > @_languageID > LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_5 ON > > dbo.vw_Synergize_Variants.GEN_LIGHTSOURCE_SOCKET_BALL_2 = > vw_Synergize_All_Gen_Chars_5.CHARVALUE AND > vw_Synergize_All_Gen_Chars_5.CHARNAME = > 'GEN_LIGHTSOURCE_SOCKET_BALL_2' AND > vw_Synergize_All_Gen_Chars_5.[Language] = > @_languageID > LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_4 ON > dbo.vw_Synergize_Variants.GEN_COLOUR_SURFACE_2 = > vw_Synergize_All_Gen_Chars_4.CHARVALUE AND > vw_Synergize_All_Gen_Chars_4.CHARNAME = > 'GEN_COLOUR_SURFACE_2' AND vw_Synergize_All_Gen_Chars_4.[Language] = > @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_3 ON > dbo.vw_Synergize_Variants.GEN_COLOUR_TYPE = > vw_Synergize_All_Gen_Chars_3.CHARVALUE AND > vw_Synergize_All_Gen_Chars_3.CHARNAME = > 'GEN_COLOUR_TYPE' AND vw_Synergize_All_Gen_Chars_3.[Language] = > @_languageID > LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_2 ON > dbo.vw_Synergize_Variants.GEN_SPECIAL = > vw_Synergize_All_Gen_Chars_2.CHARVALUE AND > vw_Synergize_All_Gen_Chars_2.CHARNAME = 'GEN_SPECIAL' > AND vw_Synergize_All_Gen_Chars_2.[Language] = @_languageID LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars > vw_Synergize_All_Gen_Chars_1 ON > dbo.vw_Synergize_Variants.GEN_VARIANT_TYPE = > vw_Synergize_All_Gen_Chars_1.CHARVALUE AND > vw_Synergize_All_Gen_Chars_1.CHARNAME = > 'GEN_VARIANT_TYPE' AND vw_Synergize_All_Gen_Chars_1.[Language] = > @_languageID > LEFT OUTER JOIN > dbo.vw_Synergize_All_Gen_Chars ON > dbo.vw_Synergize_Variants.GEN_EDITION = > dbo.vw_Synergize_All_Gen_Chars.CHARVALUE AND > dbo.vw_Synergize_All_Gen_Chars.CHARNAME = > 'GEN_EDITION' AND dbo.vw_Synergize_All_Gen_Chars.[Language] = @_languageID > WHERE (dbo.vw_Synergize_Variants.GEN_PRODUCT_NO = @_kmatnr) AND > (dbo.vw_Synergize_Variants.GEN_VARIANT_TYPE = 1) > GO > > > Thanks in advance > /Rasmus I'll do a dance with my pants down at PASS if the Index Tuning Wizard can
fix this baby (c: Regards, Greg Linwood SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood Show quote "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:Oc5CqOn1GHA.1304@TK2MSFTNGP05.phx.gbl... > Although there isn't enough information here to provide much help, I would > expect unique indexes on CHARNAME, CHARVALUE and Language columns to help. > You might try evaluating proc execution of this proc using the Index > Tuning Wizard or Database Tuning Advisor. > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "Rasmus Lynggaard" <Rasmus Lyngga***@discussions.microsoft.com> wrote in > message news:ADBC4D37-7540-4882-BEA9-91450A7119EC@microsoft.com... >> Hi all >> hope you are in for a challenge. I have the following query in a stored >> procedure. >> It takes a lot of time to run (hence around 2.5 million records to select >> from) >> Can anybody help me optimize this? >> >> CREATE PROCEDURE sp_Synergize_GetVariants >> @Lang char(2) >> , @kmatnr int >> AS >> DECLARE >> @_languageID char(2) >> , @_kmatnr int; >> >> SET @_languageID = @Lang; >> SET @_kmatnr = @kmatnr; >> SELECT dbo.vw_Synergize_Variants.VARIANTID, >> dbo.vw_Synergize_Variants.GEN_PRODUCT_NO, >> dbo.vw_Synergize_Variants.GEN_EL_NO, >> dbo.vw_Synergize_Variants.GEN_EDITION, >> vw_Synergize_All_Gen_Chars_11.DESCRIPT AS GEN_EDITION_DESCRIPT, >> dbo.vw_Synergize_Variants.GEN_VARIANT_TYPE, >> vw_Synergize_All_Gen_Chars_12.DESCRIPT AS GEN_VARIANT_TYPE_DESCRIPT, >> dbo.vw_Synergize_Variants.GEN_SPECIAL, >> vw_Synergize_All_Gen_Chars_2.DESCRIPT AS GEN_SPECIAL_DESCRIPT, >> dbo.vw_Synergize_Variants.GEN_COLOUR_TYPE, >> vw_Synergize_All_Gen_Chars_3.DESCRIPT AS GEN_COLOUR_TYPE_DESCRIPT, >> dbo.vw_Synergize_Variants.GEN_COLOUR_SURFACE_2, >> vw_Synergize_All_Gen_Chars_4.DESCRIPT AS GEN_COLOUR_SURFACE_2_DESCRIPT, >> >> dbo.vw_Synergize_Variants.GEN_LIGHTSOURCE_SOCKET_BALL_2, >> vw_Synergize_All_Gen_Chars_5.DESCRIPT AS >> GEN_LIGHTSOURCE_SOCKET_BALL_2_DESCRIPT, >> dbo.vw_Synergize_Variants.GEN_ARM_TYPE_2, >> vw_Synergize_All_Gen_Chars_6.DESCRIPT AS >> GEN_ARM_TYPE_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_BODY, >> vw_Synergize_All_Gen_Chars_7.DESCRIPT AS >> GEN_BODY_DESCRIPT, dbo.vw_Synergize_Variants.GEN_CABLE_TYPE_2, >> vw_Synergize_All_Gen_Chars_8.DESCRIPT AS >> GEN_CABLE_TYPE_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_CANOPY, >> vw_Synergize_All_Gen_Chars_9.DESCRIPT AS >> GEN_CANOPY_DESCRIPT, dbo.vw_Synergize_Variants.GEN_CORNER_ELEMENTS, >> vw_Synergize_All_Gen_Chars_10.DESCRIPT AS >> GEN_CORNER_ELEMENTS_DESCRIPT, dbo.vw_Synergize_Variants.GEN_POLE_POST, >> vw_Synergize_All_Gen_Chars_13.DESCRIPT AS >> GEN_POLE_POST_DESCRIPT, dbo.vw_Synergize_Variants.GEN_DIMENSIONS_2, >> vw_Synergize_All_Gen_Chars_14.DESCRIPT AS >> GEN_DIMENSIONS_2_DESCRIPT, >> dbo.vw_Synergize_Variants.GEN_INSULATION_CLASS_2, >> vw_Synergize_All_Gen_Chars_15.DESCRIPT AS >> GEN_INSULATION_CLASS_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_FILTER, >> vw_Synergize_All_Gen_Chars_16.DESCRIPT AS >> GEN_FILTER_DESCRIPT, dbo.vw_Synergize_Variants.GEN_FRONT, >> vw_Synergize_All_Gen_Chars_17.DESCRIPT AS >> GEN_FRONT_DESCRIPT, dbo.vw_Synergize_Variants.GEN_LIGHT_TECHNIQUE, >> vw_Synergize_All_Gen_Chars_18.DESCRIPT AS >> GEN_LIGHT_TECHNIQUE_DESCRIPT, dbo.vw_Synergize_Variants.GEN_LOUVRE, >> vw_Synergize_All_Gen_Chars_19.DESCRIPT AS >> GEN_LOUVRE_DESCRIPT, dbo.vw_Synergize_Variants.GEN_MOUNTING_2, >> vw_Synergize_All_Gen_Chars_20.DESCRIPT AS >> GEN_MOUNTING_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_REFLECTOR, >> vw_Synergize_All_Gen_Chars_21.DESCRIPT AS >> GEN_REFLECTOR_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SHADE_2, >> vw_Synergize_All_Gen_Chars_22.DESCRIPT AS >> GEN_SHADE_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SHIELD_DIFFUSOR_2, >> vw_Synergize_All_Gen_Chars_23.DESCRIPT AS >> GEN_SHIELD_DIFFUSOR_2_DESCRIPT, >> dbo.vw_Synergize_Variants.GEN_SOCKET_COVER_2, >> vw_Synergize_All_Gen_Chars_24.DESCRIPT AS >> GEN_SOCKET_COVER_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_STEM_BASE, >> vw_Synergize_All_Gen_Chars_25.DESCRIPT AS >> GEN_STEM_BASE_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SUSPENSION, >> vw_Synergize_All_Gen_Chars_26.DESCRIPT AS >> GEN_SUSPENSION_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SWITCH, >> vw_Synergize_All_Gen_Chars_27.DESCRIPT AS >> GEN_SWITCH_DESCRIPT, dbo.vw_Synergize_Variants.GEN_VOLTAGE_FREQUENCY_2, >> vw_Synergize_All_Gen_Chars_28.DESCRIPT AS >> GEN_VOLTAGE_FREQUENCY_2_DESCRIPT, >> dbo.vw_Synergize_Variants.GEN_ADDITIONAL_FEATURES, >> vw_Synergize_All_Gen_Chars_29.DESCRIPT AS >> GEN_ADDITIONAL_FEATURES_DESCRIPT, >> dbo.vw_Synergize_Variants.param >> FROM dbo.vw_Synergize_Variants LEFT OUTER JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_29 ON >> dbo.vw_Synergize_Variants.GEN_ADDITIONAL_FEATURES = >> vw_Synergize_All_Gen_Chars_29.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_29.CHARNAME = >> 'GEN_ADDITIONAL_FEATURES' AND >> vw_Synergize_All_Gen_Chars_29.[Language] = >> @_languageID LEFT OUTER JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_28 ON >> dbo.vw_Synergize_Variants.GEN_VOLTAGE_FREQUENCY_2 = >> vw_Synergize_All_Gen_Chars_28.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_28.CHARNAME = >> 'GEN_VOLTAGE_FREQUENCY_2' AND >> vw_Synergize_All_Gen_Chars_28.[Language] = >> @_languageID LEFT OUTER JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_27 ON >> dbo.vw_Synergize_Variants.GEN_SWITCH = >> vw_Synergize_All_Gen_Chars_27.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_27.CHARNAME = >> 'GEN_SWITCH' >> AND vw_Synergize_All_Gen_Chars_27.[Language] = @_languageID LEFT OUTER >> JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_26 ON >> dbo.vw_Synergize_Variants.GEN_SUSPENSION = >> vw_Synergize_All_Gen_Chars_26.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_26.CHARNAME = >> 'GEN_SUSPENSION' AND vw_Synergize_All_Gen_Chars_26.[Language] = >> @_languageID >> LEFT OUTER JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_25 ON >> dbo.vw_Synergize_Variants.GEN_STEM_BASE = >> vw_Synergize_All_Gen_Chars_25.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_25.CHARNAME = >> 'GEN_STEM_BASE' AND vw_Synergize_All_Gen_Chars_25.[Language] = >> @_languageID >> LEFT OUTER JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_24 ON >> dbo.vw_Synergize_Variants.GEN_SOCKET_COVER_2 = >> vw_Synergize_All_Gen_Chars_24.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_24.CHARNAME = >> 'GEN_SOCKET_COVER_2' AND vw_Synergize_All_Gen_Chars_24.[Language] = >> @_languageID LEFT OUTER JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_23 ON >> dbo.vw_Synergize_Variants.GEN_SHIELD_DIFFUSOR_2 = >> vw_Synergize_All_Gen_Chars_23.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_23.CHARNAME = >> 'GEN_SHIELD_DIFFUSOR_2' AND >> vw_Synergize_All_Gen_Chars_23.[Language] = >> @_languageID LEFT OUTER JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_22 ON >> dbo.vw_Synergize_Variants.GEN_SHADE_2 = >> vw_Synergize_All_Gen_Chars_22.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_22.CHARNAME = >> 'GEN_SHADE_2' >> AND vw_Synergize_All_Gen_Chars_22.[Language] = @_languageID LEFT OUTER >> JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_21 ON >> dbo.vw_Synergize_Variants.GEN_REFLECTOR = >> vw_Synergize_All_Gen_Chars_21.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_21.CHARNAME = >> 'GEN_REFLECTOR' AND vw_Synergize_All_Gen_Chars_21.[Language] = >> @_languageID >> LEFT OUTER JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_20 ON >> dbo.vw_Synergize_Variants.GEN_MOUNTING_2 = >> vw_Synergize_All_Gen_Chars_20.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_20.CHARNAME = >> 'GEN_MOUNTING_2' AND vw_Synergize_All_Gen_Chars_20.[Language] = >> @_languageID >> LEFT OUTER JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_19 ON >> dbo.vw_Synergize_Variants.GEN_LOUVRE = >> vw_Synergize_All_Gen_Chars_19.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_19.CHARNAME = >> 'GEN_LOUVRE' >> AND vw_Synergize_All_Gen_Chars_19.[Language] = @_languageID LEFT OUTER >> JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_18 ON >> dbo.vw_Synergize_Variants.GEN_LIGHT_TECHNIQUE = >> vw_Synergize_All_Gen_Chars_18.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_18.CHARNAME = >> 'GEN_LIGHT_TECHNIQUE' AND vw_Synergize_All_Gen_Chars_18.[Language] = >> @_languageID LEFT OUTER JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_17 ON >> dbo.vw_Synergize_Variants.GEN_FRONT = >> vw_Synergize_All_Gen_Chars_17.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_17.CHARNAME = 'GEN_FRONT' >> AND vw_Synergize_All_Gen_Chars_17.[Language] = @_languageID LEFT OUTER >> JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_16 ON >> dbo.vw_Synergize_Variants.GEN_FILTER = >> vw_Synergize_All_Gen_Chars_16.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_16.CHARNAME = >> 'GEN_FILTER' >> AND vw_Synergize_All_Gen_Chars_16.[Language] = @_languageID LEFT OUTER >> JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_15 ON >> dbo.vw_Synergize_Variants.GEN_INSULATION_CLASS_2 = >> vw_Synergize_All_Gen_Chars_15.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_15.CHARNAME = >> 'GEN_INSULATION_CLASS_2' AND >> vw_Synergize_All_Gen_Chars_15.[Language] = >> @_languageID LEFT OUTER JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_14 ON >> dbo.vw_Synergize_Variants.GEN_DIMENSIONS_2 = >> vw_Synergize_All_Gen_Chars_14.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_14.CHARNAME = >> 'GEN_DIMENSIONS_2' AND vw_Synergize_All_Gen_Chars_14.[Language] = >> @_languageID LEFT OUTER JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_12 ON >> dbo.vw_Synergize_Variants.GEN_VARIANT_TYPE = >> vw_Synergize_All_Gen_Chars_12.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_12.CHARNAME = >> 'GEN_VARIANT_TYPE' AND vw_Synergize_All_Gen_Chars_12.[Language] = >> @_languageID LEFT OUTER JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_9 ON >> dbo.vw_Synergize_Variants.GEN_CANOPY = >> vw_Synergize_All_Gen_Chars_9.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_9.CHARNAME = 'GEN_CANOPY' >> AND vw_Synergize_All_Gen_Chars_9.[Language] = @_languageID LEFT OUTER >> JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_13 ON >> dbo.vw_Synergize_Variants.GEN_POLE_POST = >> vw_Synergize_All_Gen_Chars_13.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_13.CHARNAME = >> 'GEN_POLE_POST' AND vw_Synergize_All_Gen_Chars_13.[Language] = >> @_languageID >> LEFT OUTER JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_11 ON >> dbo.vw_Synergize_Variants.GEN_EDITION = >> vw_Synergize_All_Gen_Chars_11.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_11.CHARNAME = >> 'GEN_EDITION' >> AND vw_Synergize_All_Gen_Chars_11.[Language] = @_languageID LEFT OUTER >> JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_10 ON >> dbo.vw_Synergize_Variants.GEN_CORNER_ELEMENTS = >> vw_Synergize_All_Gen_Chars_10.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_10.CHARNAME = >> 'GEN_CORNER_ELEMENTS' AND >> vw_Synergize_All_Gen_Chars_10.[Language] = >> @_languageID LEFT OUTER JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_8 ON >> dbo.vw_Synergize_Variants.GEN_CABLE_TYPE_2 = >> vw_Synergize_All_Gen_Chars_8.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_8.CHARNAME = >> 'GEN_CABLE_TYPE_2' AND vw_Synergize_All_Gen_Chars_8.[Language] = >> @_languageID >> LEFT OUTER JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_7 ON >> dbo.vw_Synergize_Variants.GEN_BODY = >> vw_Synergize_All_Gen_Chars_7.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_7.CHARNAME = 'GEN_BODY' >> AND >> vw_Synergize_All_Gen_Chars_7.[Language] = @_languageID LEFT OUTER JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_6 ON >> dbo.vw_Synergize_Variants.GEN_ARM_TYPE_2 = >> vw_Synergize_All_Gen_Chars_6.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_6.CHARNAME = >> 'GEN_ARM_TYPE_2' AND vw_Synergize_All_Gen_Chars_6.[Language] = >> @_languageID >> LEFT OUTER JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_5 ON >> >> dbo.vw_Synergize_Variants.GEN_LIGHTSOURCE_SOCKET_BALL_2 = >> vw_Synergize_All_Gen_Chars_5.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_5.CHARNAME = >> 'GEN_LIGHTSOURCE_SOCKET_BALL_2' AND >> vw_Synergize_All_Gen_Chars_5.[Language] = >> @_languageID >> LEFT OUTER JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_4 ON >> dbo.vw_Synergize_Variants.GEN_COLOUR_SURFACE_2 = >> vw_Synergize_All_Gen_Chars_4.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_4.CHARNAME = >> 'GEN_COLOUR_SURFACE_2' AND vw_Synergize_All_Gen_Chars_4.[Language] = >> @_languageID LEFT OUTER JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_3 ON >> dbo.vw_Synergize_Variants.GEN_COLOUR_TYPE = >> vw_Synergize_All_Gen_Chars_3.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_3.CHARNAME = >> 'GEN_COLOUR_TYPE' AND vw_Synergize_All_Gen_Chars_3.[Language] = >> @_languageID >> LEFT OUTER JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_2 ON >> dbo.vw_Synergize_Variants.GEN_SPECIAL = >> vw_Synergize_All_Gen_Chars_2.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_2.CHARNAME = >> 'GEN_SPECIAL' >> AND vw_Synergize_All_Gen_Chars_2.[Language] = @_languageID LEFT OUTER >> JOIN >> dbo.vw_Synergize_All_Gen_Chars >> vw_Synergize_All_Gen_Chars_1 ON >> dbo.vw_Synergize_Variants.GEN_VARIANT_TYPE = >> vw_Synergize_All_Gen_Chars_1.CHARVALUE AND >> vw_Synergize_All_Gen_Chars_1.CHARNAME = >> 'GEN_VARIANT_TYPE' AND vw_Synergize_All_Gen_Chars_1.[Language] = >> @_languageID >> LEFT OUTER JOIN >> dbo.vw_Synergize_All_Gen_Chars ON >> dbo.vw_Synergize_Variants.GEN_EDITION = >> dbo.vw_Synergize_All_Gen_Chars.CHARVALUE AND >> dbo.vw_Synergize_All_Gen_Chars.CHARNAME = >> 'GEN_EDITION' AND dbo.vw_Synergize_All_Gen_Chars.[Language] = >> @_languageID >> WHERE (dbo.vw_Synergize_Variants.GEN_PRODUCT_NO = @_kmatnr) AND >> (dbo.vw_Synergize_Variants.GEN_VARIANT_TYPE = 1) >> GO >> >> >> Thanks in advance >> /Rasmus > > Greg Linwood wrote:
> I'll do a dance with my pants down at PASS if the Index Tuning Wizard can Egad! I'm on a roll today, I've used that word twice!> fix this baby (c: > > I'll do a dance with my pants down at PASS if the Index Tuning Wizard can Now that's a picture - I pray the ITW fails miserably :-)> fix this baby (c: Although I don't think anything can really 'fix' the query, I do believe DTA might be able to make some recommendations that will improve performance. I threw out ITW in case Rasmus didn't have SSMS. -- Hope this helps. Dan Guzman SQL Server MVP Show quote "Greg Linwood" <g_linw***@hotmail.com> wrote in message news:u3kITdn1GHA.3516@TK2MSFTNGP06.phx.gbl... > I'll do a dance with my pants down at PASS if the Index Tuning Wizard can > fix this baby (c: > > Regards, > Greg Linwood > SQL Server MVP > http://blogs.sqlserver.org.au/blogs/greg_linwood > > "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message > news:Oc5CqOn1GHA.1304@TK2MSFTNGP05.phx.gbl... >> Although there isn't enough information here to provide much help, I >> would expect unique indexes on CHARNAME, CHARVALUE and Language columns >> to help. You might try evaluating proc execution of this proc using the >> Index Tuning Wizard or Database Tuning Advisor. >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> >> "Rasmus Lynggaard" <Rasmus Lyngga***@discussions.microsoft.com> wrote in >> message news:ADBC4D37-7540-4882-BEA9-91450A7119EC@microsoft.com... >>> Hi all >>> hope you are in for a challenge. I have the following query in a stored >>> procedure. >>> It takes a lot of time to run (hence around 2.5 million records to >>> select >>> from) >>> Can anybody help me optimize this? >>> >>> CREATE PROCEDURE sp_Synergize_GetVariants >>> @Lang char(2) >>> , @kmatnr int >>> AS >>> DECLARE >>> @_languageID char(2) >>> , @_kmatnr int; >>> >>> SET @_languageID = @Lang; >>> SET @_kmatnr = @kmatnr; >>> SELECT dbo.vw_Synergize_Variants.VARIANTID, >>> dbo.vw_Synergize_Variants.GEN_PRODUCT_NO, >>> dbo.vw_Synergize_Variants.GEN_EL_NO, >>> dbo.vw_Synergize_Variants.GEN_EDITION, >>> vw_Synergize_All_Gen_Chars_11.DESCRIPT AS GEN_EDITION_DESCRIPT, >>> dbo.vw_Synergize_Variants.GEN_VARIANT_TYPE, >>> vw_Synergize_All_Gen_Chars_12.DESCRIPT AS GEN_VARIANT_TYPE_DESCRIPT, >>> dbo.vw_Synergize_Variants.GEN_SPECIAL, >>> vw_Synergize_All_Gen_Chars_2.DESCRIPT AS GEN_SPECIAL_DESCRIPT, >>> dbo.vw_Synergize_Variants.GEN_COLOUR_TYPE, >>> vw_Synergize_All_Gen_Chars_3.DESCRIPT AS GEN_COLOUR_TYPE_DESCRIPT, >>> dbo.vw_Synergize_Variants.GEN_COLOUR_SURFACE_2, >>> vw_Synergize_All_Gen_Chars_4.DESCRIPT AS GEN_COLOUR_SURFACE_2_DESCRIPT, >>> >>> dbo.vw_Synergize_Variants.GEN_LIGHTSOURCE_SOCKET_BALL_2, >>> vw_Synergize_All_Gen_Chars_5.DESCRIPT AS >>> GEN_LIGHTSOURCE_SOCKET_BALL_2_DESCRIPT, >>> dbo.vw_Synergize_Variants.GEN_ARM_TYPE_2, >>> vw_Synergize_All_Gen_Chars_6.DESCRIPT AS >>> GEN_ARM_TYPE_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_BODY, >>> vw_Synergize_All_Gen_Chars_7.DESCRIPT AS >>> GEN_BODY_DESCRIPT, dbo.vw_Synergize_Variants.GEN_CABLE_TYPE_2, >>> vw_Synergize_All_Gen_Chars_8.DESCRIPT AS >>> GEN_CABLE_TYPE_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_CANOPY, >>> vw_Synergize_All_Gen_Chars_9.DESCRIPT AS >>> GEN_CANOPY_DESCRIPT, dbo.vw_Synergize_Variants.GEN_CORNER_ELEMENTS, >>> vw_Synergize_All_Gen_Chars_10.DESCRIPT AS >>> GEN_CORNER_ELEMENTS_DESCRIPT, dbo.vw_Synergize_Variants.GEN_POLE_POST, >>> vw_Synergize_All_Gen_Chars_13.DESCRIPT AS >>> GEN_POLE_POST_DESCRIPT, dbo.vw_Synergize_Variants.GEN_DIMENSIONS_2, >>> vw_Synergize_All_Gen_Chars_14.DESCRIPT AS >>> GEN_DIMENSIONS_2_DESCRIPT, >>> dbo.vw_Synergize_Variants.GEN_INSULATION_CLASS_2, >>> vw_Synergize_All_Gen_Chars_15.DESCRIPT AS >>> GEN_INSULATION_CLASS_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_FILTER, >>> vw_Synergize_All_Gen_Chars_16.DESCRIPT AS >>> GEN_FILTER_DESCRIPT, dbo.vw_Synergize_Variants.GEN_FRONT, >>> vw_Synergize_All_Gen_Chars_17.DESCRIPT AS >>> GEN_FRONT_DESCRIPT, dbo.vw_Synergize_Variants.GEN_LIGHT_TECHNIQUE, >>> vw_Synergize_All_Gen_Chars_18.DESCRIPT AS >>> GEN_LIGHT_TECHNIQUE_DESCRIPT, dbo.vw_Synergize_Variants.GEN_LOUVRE, >>> vw_Synergize_All_Gen_Chars_19.DESCRIPT AS >>> GEN_LOUVRE_DESCRIPT, dbo.vw_Synergize_Variants.GEN_MOUNTING_2, >>> vw_Synergize_All_Gen_Chars_20.DESCRIPT AS >>> GEN_MOUNTING_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_REFLECTOR, >>> vw_Synergize_All_Gen_Chars_21.DESCRIPT AS >>> GEN_REFLECTOR_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SHADE_2, >>> vw_Synergize_All_Gen_Chars_22.DESCRIPT AS >>> GEN_SHADE_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SHIELD_DIFFUSOR_2, >>> vw_Synergize_All_Gen_Chars_23.DESCRIPT AS >>> GEN_SHIELD_DIFFUSOR_2_DESCRIPT, >>> dbo.vw_Synergize_Variants.GEN_SOCKET_COVER_2, >>> vw_Synergize_All_Gen_Chars_24.DESCRIPT AS >>> GEN_SOCKET_COVER_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_STEM_BASE, >>> vw_Synergize_All_Gen_Chars_25.DESCRIPT AS >>> GEN_STEM_BASE_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SUSPENSION, >>> vw_Synergize_All_Gen_Chars_26.DESCRIPT AS >>> GEN_SUSPENSION_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SWITCH, >>> vw_Synergize_All_Gen_Chars_27.DESCRIPT AS >>> GEN_SWITCH_DESCRIPT, dbo.vw_Synergize_Variants.GEN_VOLTAGE_FREQUENCY_2, >>> vw_Synergize_All_Gen_Chars_28.DESCRIPT AS >>> GEN_VOLTAGE_FREQUENCY_2_DESCRIPT, >>> dbo.vw_Synergize_Variants.GEN_ADDITIONAL_FEATURES, >>> vw_Synergize_All_Gen_Chars_29.DESCRIPT AS >>> GEN_ADDITIONAL_FEATURES_DESCRIPT, >>> dbo.vw_Synergize_Variants.param >>> FROM dbo.vw_Synergize_Variants LEFT OUTER JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_29 ON >>> dbo.vw_Synergize_Variants.GEN_ADDITIONAL_FEATURES = >>> vw_Synergize_All_Gen_Chars_29.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_29.CHARNAME = >>> 'GEN_ADDITIONAL_FEATURES' AND >>> vw_Synergize_All_Gen_Chars_29.[Language] = >>> @_languageID LEFT OUTER JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_28 ON >>> dbo.vw_Synergize_Variants.GEN_VOLTAGE_FREQUENCY_2 = >>> vw_Synergize_All_Gen_Chars_28.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_28.CHARNAME = >>> 'GEN_VOLTAGE_FREQUENCY_2' AND >>> vw_Synergize_All_Gen_Chars_28.[Language] = >>> @_languageID LEFT OUTER JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_27 ON >>> dbo.vw_Synergize_Variants.GEN_SWITCH = >>> vw_Synergize_All_Gen_Chars_27.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_27.CHARNAME = >>> 'GEN_SWITCH' >>> AND vw_Synergize_All_Gen_Chars_27.[Language] = @_languageID LEFT OUTER >>> JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_26 ON >>> dbo.vw_Synergize_Variants.GEN_SUSPENSION = >>> vw_Synergize_All_Gen_Chars_26.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_26.CHARNAME = >>> 'GEN_SUSPENSION' AND vw_Synergize_All_Gen_Chars_26.[Language] = >>> @_languageID >>> LEFT OUTER JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_25 ON >>> dbo.vw_Synergize_Variants.GEN_STEM_BASE = >>> vw_Synergize_All_Gen_Chars_25.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_25.CHARNAME = >>> 'GEN_STEM_BASE' AND vw_Synergize_All_Gen_Chars_25.[Language] = >>> @_languageID >>> LEFT OUTER JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_24 ON >>> dbo.vw_Synergize_Variants.GEN_SOCKET_COVER_2 = >>> vw_Synergize_All_Gen_Chars_24.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_24.CHARNAME = >>> 'GEN_SOCKET_COVER_2' AND vw_Synergize_All_Gen_Chars_24.[Language] = >>> @_languageID LEFT OUTER JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_23 ON >>> dbo.vw_Synergize_Variants.GEN_SHIELD_DIFFUSOR_2 = >>> vw_Synergize_All_Gen_Chars_23.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_23.CHARNAME = >>> 'GEN_SHIELD_DIFFUSOR_2' AND >>> vw_Synergize_All_Gen_Chars_23.[Language] = >>> @_languageID LEFT OUTER JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_22 ON >>> dbo.vw_Synergize_Variants.GEN_SHADE_2 = >>> vw_Synergize_All_Gen_Chars_22.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_22.CHARNAME = >>> 'GEN_SHADE_2' >>> AND vw_Synergize_All_Gen_Chars_22.[Language] = @_languageID LEFT OUTER >>> JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_21 ON >>> dbo.vw_Synergize_Variants.GEN_REFLECTOR = >>> vw_Synergize_All_Gen_Chars_21.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_21.CHARNAME = >>> 'GEN_REFLECTOR' AND vw_Synergize_All_Gen_Chars_21.[Language] = >>> @_languageID >>> LEFT OUTER JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_20 ON >>> dbo.vw_Synergize_Variants.GEN_MOUNTING_2 = >>> vw_Synergize_All_Gen_Chars_20.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_20.CHARNAME = >>> 'GEN_MOUNTING_2' AND vw_Synergize_All_Gen_Chars_20.[Language] = >>> @_languageID >>> LEFT OUTER JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_19 ON >>> dbo.vw_Synergize_Variants.GEN_LOUVRE = >>> vw_Synergize_All_Gen_Chars_19.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_19.CHARNAME = >>> 'GEN_LOUVRE' >>> AND vw_Synergize_All_Gen_Chars_19.[Language] = @_languageID LEFT OUTER >>> JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_18 ON >>> dbo.vw_Synergize_Variants.GEN_LIGHT_TECHNIQUE = >>> vw_Synergize_All_Gen_Chars_18.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_18.CHARNAME = >>> 'GEN_LIGHT_TECHNIQUE' AND vw_Synergize_All_Gen_Chars_18.[Language] = >>> @_languageID LEFT OUTER JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_17 ON >>> dbo.vw_Synergize_Variants.GEN_FRONT = >>> vw_Synergize_All_Gen_Chars_17.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_17.CHARNAME = >>> 'GEN_FRONT' >>> AND vw_Synergize_All_Gen_Chars_17.[Language] = @_languageID LEFT OUTER >>> JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_16 ON >>> dbo.vw_Synergize_Variants.GEN_FILTER = >>> vw_Synergize_All_Gen_Chars_16.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_16.CHARNAME = >>> 'GEN_FILTER' >>> AND vw_Synergize_All_Gen_Chars_16.[Language] = @_languageID LEFT OUTER >>> JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_15 ON >>> dbo.vw_Synergize_Variants.GEN_INSULATION_CLASS_2 = >>> vw_Synergize_All_Gen_Chars_15.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_15.CHARNAME = >>> 'GEN_INSULATION_CLASS_2' AND >>> vw_Synergize_All_Gen_Chars_15.[Language] = >>> @_languageID LEFT OUTER JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_14 ON >>> dbo.vw_Synergize_Variants.GEN_DIMENSIONS_2 = >>> vw_Synergize_All_Gen_Chars_14.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_14.CHARNAME = >>> 'GEN_DIMENSIONS_2' AND vw_Synergize_All_Gen_Chars_14.[Language] = >>> @_languageID LEFT OUTER JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_12 ON >>> dbo.vw_Synergize_Variants.GEN_VARIANT_TYPE = >>> vw_Synergize_All_Gen_Chars_12.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_12.CHARNAME = >>> 'GEN_VARIANT_TYPE' AND vw_Synergize_All_Gen_Chars_12.[Language] = >>> @_languageID LEFT OUTER JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_9 ON >>> dbo.vw_Synergize_Variants.GEN_CANOPY = >>> vw_Synergize_All_Gen_Chars_9.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_9.CHARNAME = >>> 'GEN_CANOPY' >>> AND vw_Synergize_All_Gen_Chars_9.[Language] = @_languageID LEFT OUTER >>> JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_13 ON >>> dbo.vw_Synergize_Variants.GEN_POLE_POST = >>> vw_Synergize_All_Gen_Chars_13.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_13.CHARNAME = >>> 'GEN_POLE_POST' AND vw_Synergize_All_Gen_Chars_13.[Language] = >>> @_languageID >>> LEFT OUTER JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_11 ON >>> dbo.vw_Synergize_Variants.GEN_EDITION = >>> vw_Synergize_All_Gen_Chars_11.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_11.CHARNAME = >>> 'GEN_EDITION' >>> AND vw_Synergize_All_Gen_Chars_11.[Language] = @_languageID LEFT OUTER >>> JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_10 ON >>> dbo.vw_Synergize_Variants.GEN_CORNER_ELEMENTS = >>> vw_Synergize_All_Gen_Chars_10.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_10.CHARNAME = >>> 'GEN_CORNER_ELEMENTS' AND >>> vw_Synergize_All_Gen_Chars_10.[Language] = >>> @_languageID LEFT OUTER JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_8 ON >>> dbo.vw_Synergize_Variants.GEN_CABLE_TYPE_2 = >>> vw_Synergize_All_Gen_Chars_8.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_8.CHARNAME = >>> 'GEN_CABLE_TYPE_2' AND vw_Synergize_All_Gen_Chars_8.[Language] = >>> @_languageID >>> LEFT OUTER JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_7 ON >>> dbo.vw_Synergize_Variants.GEN_BODY = >>> vw_Synergize_All_Gen_Chars_7.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_7.CHARNAME = 'GEN_BODY' >>> AND >>> vw_Synergize_All_Gen_Chars_7.[Language] = @_languageID LEFT OUTER JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_6 ON >>> dbo.vw_Synergize_Variants.GEN_ARM_TYPE_2 = >>> vw_Synergize_All_Gen_Chars_6.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_6.CHARNAME = >>> 'GEN_ARM_TYPE_2' AND vw_Synergize_All_Gen_Chars_6.[Language] = >>> @_languageID >>> LEFT OUTER JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_5 ON >>> >>> dbo.vw_Synergize_Variants.GEN_LIGHTSOURCE_SOCKET_BALL_2 = >>> vw_Synergize_All_Gen_Chars_5.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_5.CHARNAME = >>> 'GEN_LIGHTSOURCE_SOCKET_BALL_2' AND >>> vw_Synergize_All_Gen_Chars_5.[Language] = >>> @_languageID >>> LEFT OUTER JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_4 ON >>> dbo.vw_Synergize_Variants.GEN_COLOUR_SURFACE_2 = >>> vw_Synergize_All_Gen_Chars_4.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_4.CHARNAME = >>> 'GEN_COLOUR_SURFACE_2' AND vw_Synergize_All_Gen_Chars_4.[Language] = >>> @_languageID LEFT OUTER JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_3 ON >>> dbo.vw_Synergize_Variants.GEN_COLOUR_TYPE = >>> vw_Synergize_All_Gen_Chars_3.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_3.CHARNAME = >>> 'GEN_COLOUR_TYPE' AND vw_Synergize_All_Gen_Chars_3.[Language] = >>> @_languageID >>> LEFT OUTER JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_2 ON >>> dbo.vw_Synergize_Variants.GEN_SPECIAL = >>> vw_Synergize_All_Gen_Chars_2.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_2.CHARNAME = >>> 'GEN_SPECIAL' >>> AND vw_Synergize_All_Gen_Chars_2.[Language] = @_languageID LEFT OUTER >>> JOIN >>> dbo.vw_Synergize_All_Gen_Chars >>> vw_Synergize_All_Gen_Chars_1 ON >>> dbo.vw_Synergize_Variants.GEN_VARIANT_TYPE = >>> vw_Synergize_All_Gen_Chars_1.CHARVALUE AND >>> vw_Synergize_All_Gen_Chars_1.CHARNAME = >>> 'GEN_VARIANT_TYPE' AND vw_Synergize_All_Gen_Chars_1.[Language] = >>> @_languageID >>> LEFT OUTER JOIN >>> dbo.vw_Synergize_All_Gen_Chars ON >>> dbo.vw_Synergize_Variants.GEN_EDITION = >>> dbo.vw_Synergize_All_Gen_Chars.CHARVALUE AND >>> dbo.vw_Synergize_All_Gen_Chars.CHARNAME = >>> 'GEN_EDITION' AND dbo.vw_Synergize_All_Gen_Chars.[Language] = >>> @_languageID >>> WHERE (dbo.vw_Synergize_Variants.GEN_PRODUCT_NO = @_kmatnr) AND >>> (dbo.vw_Synergize_Variants.GEN_VARIANT_TYPE = 1) >>> GO >>> >>> >>> Thanks in advance >>> /Rasmus >> >> > > So do I (c: There's no way I'd bet my pants against the DTA - although also not perfect, it's definitely much better than the ITW! Regards, Greg Linwood SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood Show quote "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:eZs9M0y1GHA.2036@TK2MSFTNGP05.phx.gbl... >> I'll do a dance with my pants down at PASS if the Index Tuning Wizard can >> fix this baby (c: > > Now that's a picture - I pray the ITW fails miserably :-) > > Although I don't think anything can really 'fix' the query, I do believe > DTA might be able to make some recommendations that will improve > performance. I threw out ITW in case Rasmus didn't have SSMS. > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "Greg Linwood" <g_linw***@hotmail.com> wrote in message > news:u3kITdn1GHA.3516@TK2MSFTNGP06.phx.gbl... >> I'll do a dance with my pants down at PASS if the Index Tuning Wizard can >> fix this baby (c: >> >> Regards, >> Greg Linwood >> SQL Server MVP >> http://blogs.sqlserver.org.au/blogs/greg_linwood >> >> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message >> news:Oc5CqOn1GHA.1304@TK2MSFTNGP05.phx.gbl... >>> Although there isn't enough information here to provide much help, I >>> would expect unique indexes on CHARNAME, CHARVALUE and Language columns >>> to help. You might try evaluating proc execution of this proc using the >>> Index Tuning Wizard or Database Tuning Advisor. >>> >>> -- >>> Hope this helps. >>> >>> Dan Guzman >>> SQL Server MVP >>> >>> "Rasmus Lynggaard" <Rasmus Lyngga***@discussions.microsoft.com> wrote in >>> message news:ADBC4D37-7540-4882-BEA9-91450A7119EC@microsoft.com... >>>> Hi all >>>> hope you are in for a challenge. I have the following query in a stored >>>> procedure. >>>> It takes a lot of time to run (hence around 2.5 million records to >>>> select >>>> from) >>>> Can anybody help me optimize this? >>>> >>>> CREATE PROCEDURE sp_Synergize_GetVariants >>>> @Lang char(2) >>>> , @kmatnr int >>>> AS >>>> DECLARE >>>> @_languageID char(2) >>>> , @_kmatnr int; >>>> >>>> SET @_languageID = @Lang; >>>> SET @_kmatnr = @kmatnr; >>>> SELECT dbo.vw_Synergize_Variants.VARIANTID, >>>> dbo.vw_Synergize_Variants.GEN_PRODUCT_NO, >>>> dbo.vw_Synergize_Variants.GEN_EL_NO, >>>> dbo.vw_Synergize_Variants.GEN_EDITION, >>>> vw_Synergize_All_Gen_Chars_11.DESCRIPT AS GEN_EDITION_DESCRIPT, >>>> dbo.vw_Synergize_Variants.GEN_VARIANT_TYPE, >>>> vw_Synergize_All_Gen_Chars_12.DESCRIPT AS GEN_VARIANT_TYPE_DESCRIPT, >>>> dbo.vw_Synergize_Variants.GEN_SPECIAL, >>>> vw_Synergize_All_Gen_Chars_2.DESCRIPT AS GEN_SPECIAL_DESCRIPT, >>>> dbo.vw_Synergize_Variants.GEN_COLOUR_TYPE, >>>> vw_Synergize_All_Gen_Chars_3.DESCRIPT AS GEN_COLOUR_TYPE_DESCRIPT, >>>> dbo.vw_Synergize_Variants.GEN_COLOUR_SURFACE_2, >>>> vw_Synergize_All_Gen_Chars_4.DESCRIPT AS GEN_COLOUR_SURFACE_2_DESCRIPT, >>>> >>>> dbo.vw_Synergize_Variants.GEN_LIGHTSOURCE_SOCKET_BALL_2, >>>> vw_Synergize_All_Gen_Chars_5.DESCRIPT AS >>>> GEN_LIGHTSOURCE_SOCKET_BALL_2_DESCRIPT, >>>> dbo.vw_Synergize_Variants.GEN_ARM_TYPE_2, >>>> vw_Synergize_All_Gen_Chars_6.DESCRIPT AS >>>> GEN_ARM_TYPE_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_BODY, >>>> vw_Synergize_All_Gen_Chars_7.DESCRIPT AS >>>> GEN_BODY_DESCRIPT, dbo.vw_Synergize_Variants.GEN_CABLE_TYPE_2, >>>> vw_Synergize_All_Gen_Chars_8.DESCRIPT AS >>>> GEN_CABLE_TYPE_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_CANOPY, >>>> vw_Synergize_All_Gen_Chars_9.DESCRIPT AS >>>> GEN_CANOPY_DESCRIPT, dbo.vw_Synergize_Variants.GEN_CORNER_ELEMENTS, >>>> vw_Synergize_All_Gen_Chars_10.DESCRIPT AS >>>> GEN_CORNER_ELEMENTS_DESCRIPT, dbo.vw_Synergize_Variants.GEN_POLE_POST, >>>> vw_Synergize_All_Gen_Chars_13.DESCRIPT AS >>>> GEN_POLE_POST_DESCRIPT, dbo.vw_Synergize_Variants.GEN_DIMENSIONS_2, >>>> vw_Synergize_All_Gen_Chars_14.DESCRIPT AS >>>> GEN_DIMENSIONS_2_DESCRIPT, >>>> dbo.vw_Synergize_Variants.GEN_INSULATION_CLASS_2, >>>> vw_Synergize_All_Gen_Chars_15.DESCRIPT AS >>>> GEN_INSULATION_CLASS_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_FILTER, >>>> vw_Synergize_All_Gen_Chars_16.DESCRIPT AS >>>> GEN_FILTER_DESCRIPT, dbo.vw_Synergize_Variants.GEN_FRONT, >>>> vw_Synergize_All_Gen_Chars_17.DESCRIPT AS >>>> GEN_FRONT_DESCRIPT, dbo.vw_Synergize_Variants.GEN_LIGHT_TECHNIQUE, >>>> vw_Synergize_All_Gen_Chars_18.DESCRIPT AS >>>> GEN_LIGHT_TECHNIQUE_DESCRIPT, dbo.vw_Synergize_Variants.GEN_LOUVRE, >>>> vw_Synergize_All_Gen_Chars_19.DESCRIPT AS >>>> GEN_LOUVRE_DESCRIPT, dbo.vw_Synergize_Variants.GEN_MOUNTING_2, >>>> vw_Synergize_All_Gen_Chars_20.DESCRIPT AS >>>> GEN_MOUNTING_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_REFLECTOR, >>>> vw_Synergize_All_Gen_Chars_21.DESCRIPT AS >>>> GEN_REFLECTOR_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SHADE_2, >>>> vw_Synergize_All_Gen_Chars_22.DESCRIPT AS >>>> GEN_SHADE_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SHIELD_DIFFUSOR_2, >>>> vw_Synergize_All_Gen_Chars_23.DESCRIPT AS >>>> GEN_SHIELD_DIFFUSOR_2_DESCRIPT, >>>> dbo.vw_Synergize_Variants.GEN_SOCKET_COVER_2, >>>> vw_Synergize_All_Gen_Chars_24.DESCRIPT AS >>>> GEN_SOCKET_COVER_2_DESCRIPT, dbo.vw_Synergize_Variants.GEN_STEM_BASE, >>>> vw_Synergize_All_Gen_Chars_25.DESCRIPT AS >>>> GEN_STEM_BASE_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SUSPENSION, >>>> vw_Synergize_All_Gen_Chars_26.DESCRIPT AS >>>> GEN_SUSPENSION_DESCRIPT, dbo.vw_Synergize_Variants.GEN_SWITCH, >>>> vw_Synergize_All_Gen_Chars_27.DESCRIPT AS >>>> GEN_SWITCH_DESCRIPT, dbo.vw_Synergize_Variants.GEN_VOLTAGE_FREQUENCY_2, >>>> vw_Synergize_All_Gen_Chars_28.DESCRIPT AS >>>> GEN_VOLTAGE_FREQUENCY_2_DESCRIPT, >>>> dbo.vw_Synergize_Variants.GEN_ADDITIONAL_FEATURES, >>>> vw_Synergize_All_Gen_Chars_29.DESCRIPT AS >>>> GEN_ADDITIONAL_FEATURES_DESCRIPT, >>>> dbo.vw_Synergize_Variants.param >>>> FROM dbo.vw_Synergize_Variants LEFT OUTER JOIN >>>> dbo.vw_Synergize_All_Gen_Chars >>>> vw_Synergize_All_Gen_Chars_29 ON >>>> dbo.vw_Synergize_Variants.GEN_ADDITIONAL_FEATURES >>>> = >>>> vw_Synergize_All_Gen_Chars_29.CHARVALUE AND >>>> vw_Synergize_All_Gen_Chars_29.CHARNAME = >>>> 'GEN_ADDITIONAL_FEATURES' AND >>>> vw_Synergize_All_Gen_Chars_29.[Language] = >>>> @_languageID LEFT OUTER JOIN >>>> dbo.vw_Synergize_All_Gen_Chars >>>> vw_Synergize_All_Gen_Chars_28 ON >>>> dbo.vw_Synergize_Variants.GEN_VOLTAGE_FREQUENCY_2 >>>> = >>>> vw_Synergize_All_Gen_Chars_28.CHARVALUE AND >>>> vw_Synergize_All_Gen_Chars_28.CHARNAME = >>>> 'GEN_VOLTAGE_FREQUENCY_2' AND >>>> vw_Synergize_All_Gen_Chars_28.[Language] = >>>> @_languageID LEFT OUTER JOIN >>>> dbo.vw_Synergize_All_Gen_Chars >>>> vw_Synergize_All_Gen_Chars_27 ON >>>> dbo.vw_Synergize_Variants.GEN_SWITCH = >>>> vw_Synergize_All_Gen_Chars_27.CHARVALUE AND >>>> vw_Synergize_All_Gen_Chars_27.CHARNAME = >>>> 'GEN_SWITCH' >>>> AND vw_Synergize_All_Gen_Chars_27.[Language] = @_languageID LEFT OUTER >>>> JOIN >>>> dbo.vw_Synergize_All_Gen_Chars >>>> vw_Synergize_All_Gen_Chars_26 ON >>>> dbo.vw_Synergize_Variants.GEN_SUSPENSION = >>>> vw_Synergize_All_Gen_Chars_26.CHARVALUE AND >>>> vw_Synergize_All_Gen_Chars_26.CHARNAME = >>>> 'GEN_SUSPENSION' AND vw_Synergize_All_Gen_Chars_26.[Language] = >>>> @_languageID >>>> LEFT OUTER JOIN >>>> dbo.vw_Synergize_All_Gen_Chars >>>> vw_Synergize_All_Gen_Chars_25 ON >>>> dbo.vw_Synergize_Variants.GEN_STEM_BASE = >>>> vw_Synergize_All_Gen_Chars_25.CHARVALUE AND >>>> vw_Syner | |||||||||||||||||||||||
