Home All Groups Group Topic Archive Search About

Help me performance optimize

microsoft.public.sqlserver.programming
Author
12 Sep 2006 9:30 AM
Rasmus Lynggaard
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

Author
12 Sep 2006 9:50 AM
Chris Lim
Ha ha, good luck.
Author
12 Sep 2006 10:48 AM
ML
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/
Author
12 Sep 2006 12:49 PM
Tracy McKibben
Rasmus Lynggaard wrote:
Show quoteHide quote
> 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

Egad...  Without knowing table schemas, indexes, the definition of the
views, it's impossible for us to make suggestions.  Have you at least
looked at the execution plan to see where the bottleneck is?


--
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Author
12 Sep 2006 1:11 PM
Roy Harvey
On Tue, 12 Sep 2006 02:30:02 -0700, Rasmus Lynggaard <Rasmus
Lyngga***@discussions.microsoft.com> wrote:

>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?

Not from the information provided.  Query optimization requires
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
Author
12 Sep 2006 1:59 PM
Dan Guzman
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 quoteHide 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
Author
12 Sep 2006 2:25 PM
Greg Linwood
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 quoteHide 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
>
>
Author
12 Sep 2006 2:32 PM
Tracy McKibben
Greg Linwood wrote:
> I'll do a dance with my pants down at PASS if the Index Tuning Wizard can
> fix this baby (c:
>

Egad!  I'm on a roll today, I've used that word twice!


--
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Author
13 Sep 2006 12:06 PM
Dan Guzman
> 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

Show quoteHide 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
>>
>>
>
>
Author
13 Sep 2006 2:37 PM
Greg Linwood
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 quoteHide 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_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
>>>
>>>
>>
>>
>
>
Author
12 Sep 2006 3:18 PM
Roy Harvey
On Tue, 12 Sep 2006 08:59:26 -0500, "Dan Guzman"
<guzmanda@nospam-online.sbcglobal.net> wrote:

>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

I don't suppose you are suggesting an index on the view?  An indexed
view (or two) might actually address the performance problem on this
monster, but considering what the query looks like it seems more than
reasonable to be concerned about the complexity of the underlying
views.  I half expect the "LEFT" view, vw_Synergize_Variants, to be
based on another table where each attribute is a row.

Roy Harvey
Beacon Falls, CT
Author
13 Sep 2006 11:56 AM
Dan Guzman
> I don't suppose you are suggesting an index on the view?

I was thinking about the underlying tables but I agree that an index on the
view is worth pursuing.

> I half expect the "LEFT" view, vw_Synergize_Variants, to be
> based on another table where each attribute is a row.

I suspect this too.  I like to use this sort of design when I need the worst
performance possible :-)

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quoteHide quote
"Roy Harvey" <roy_har***@snet.net> wrote in message
news:8ijdg2poho4empnl43sh30v96tmnb5q335@4ax.com...
> On Tue, 12 Sep 2006 08:59:26 -0500, "Dan Guzman"
> <guzmanda@nospam-online.sbcglobal.net> wrote:
>
>>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
>
> I don't suppose you are suggesting an index on the view?  An indexed
> view (or two) might actually address the performance problem on this
> monster, but considering what the query looks like it seems more than
> reasonable to be concerned about the complexity of the underlying
> views.  I half expect the "LEFT" view, vw_Synergize_Variants, to be
> based on another table where each attribute is a row.
>
> Roy Harvey
> Beacon Falls, CT
Author
12 Sep 2006 2:33 PM
Jim Underwood
OK, you need to provide more information, see belwo for details:
http://www.aspfaq.com/etiquette.asp?id=5006

In particular, explain what you are doing here, so we dont have to guess.
Give us your DDL, and tell us what your indexes are.  In this case, we have
to know the text of the view DBO.VW_SYNERGIZE_ALL_GEN_CHARS and the indexes
on the underlying tables, as well as why you are using a view here rather
than the tables themselves.  MAYBE this view only hits a single table, but
we don't know.  Give us the table create scripts and the indexes for all of
the underlying tables (and the text of underlying views if applicable)

Here is what I do see...

You have one view that is outer joined 30 times to another view, to select
descriptions for codes stored in 30 different columns.  Whether this
repeated view is a single table with all of your lookup values, or multiple
tables joined and filtered we do not know.  These are important details for
performance tuning.

How many rows are in this lookup table/view?

Also, the first thing you need to do when tunign a query, is format it so it
is readable.  Use aliases to make it easier to read, and make good use of
whitespace (carriage returns and indenting) to make the flow of the code
obvious.  The following site can help somewhat with the latter.
http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl

Here is a version that is slightly easier to follow, and thus more likely to
get you assistance from folks here.  Unfortunately, this is probably still
going to wrap since the view and column names are so long, but there is
little we can do about that.

SELECT VAR.VARIANTID,
       VAR.GEN_PRODUCT_NO,
       VAR.GEN_EL_NO,
       VAR.GEN_EDITION,
       CHARS_11.DESCRIPT AS GEN_EDITION_DESCRIPT,
       VAR.GEN_VARIANT_TYPE,
       CHARS_12.DESCRIPT AS GEN_VARIANT_TYPE_DESCRIPT,
       VAR.GEN_SPECIAL,
       CHARS_2.DESCRIPT AS GEN_SPECIAL_DESCRIPT,
       VAR.GEN_COLOUR_TYPE,
       CHARS_3.DESCRIPT AS GEN_COLOUR_TYPE_DESCRIPT,
       VAR.GEN_COLOUR_SURFACE_2,
       CHARS_4.DESCRIPT AS GEN_COLOUR_SURFACE_2_DESCRIPT,
       VAR.GEN_LIGHTSOURCE_SOCKET_BALL_2,
       CHARS_5.DESCRIPT AS GEN_LIGHTSOURCE_SOCKET_BALL_2_DESCRIPT,
       VAR.GEN_ARM_TYPE_2,
       CHARS_6.DESCRIPT AS GEN_ARM_TYPE_2_DESCRIPT,
       VAR.GEN_BODY,
       CHARS_7.DESCRIPT AS GEN_BODY_DESCRIPT,
       VAR.GEN_CABLE_TYPE_2,
       CHARS_8.DESCRIPT AS GEN_CABLE_TYPE_2_DESCRIPT,
       VAR.GEN_CANOPY,
       CHARS_9.DESCRIPT AS GEN_CANOPY_DESCRIPT,
       VAR.GEN_CORNER_ELEMENTS,
       CHARS_10.DESCRIPT AS GEN_CORNER_ELEMENTS_DESCRIPT,
       VAR.GEN_POLE_POST,
       CHARS_13.DESCRIPT AS GEN_POLE_POST_DESCRIPT,
       VAR.GEN_DIMENSIONS_2,
       CHARS_14.DESCRIPT AS GEN_DIMENSIONS_2_DESCRIPT,
       VAR.GEN_INSULATION_CLASS_2,
       CHARS_15.DESCRIPT AS GEN_INSULATION_CLASS_2_DESCRIPT,
       VAR.GEN_FILTER,
       CHARS_16.DESCRIPT AS GEN_FILTER_DESCRIPT,
       VAR.GEN_FRONT,
       CHARS_17.DESCRIPT AS GEN_FRONT_DESCRIPT,
       VAR.GEN_LIGHT_TECHNIQUE,
       CHARS_18.DESCRIPT AS GEN_LIGHT_TECHNIQUE_DESCRIPT,
       VAR.GEN_LOUVRE,
       CHARS_19.DESCRIPT AS GEN_LOUVRE_DESCRIPT,
       VAR.GEN_MOUNTING_2,
       CHARS_20.DESCRIPT AS GEN_MOUNTING_2_DESCRIPT,
       VAR.GEN_REFLECTOR,
       CHARS_21.DESCRIPT AS GEN_REFLECTOR_DESCRIPT,
       VAR.GEN_SHADE_2,
       CHARS_22.DESCRIPT AS GEN_SHADE_2_DESCRIPT,
       VAR.GEN_SHIELD_DIFFUSOR_2,
       CHARS_23.DESCRIPT AS GEN_SHIELD_DIFFUSOR_2_DESCRIPT,
       VAR.GEN_SOCKET_COVER_2,
       CHARS_24.DESCRIPT AS GEN_SOCKET_COVER_2_DESCRIPT,
       VAR.GEN_STEM_BASE,
       CHARS_25.DESCRIPT AS GEN_STEM_BASE_DESCRIPT,
       VAR.GEN_SUSPENSION,
       CHARS_26.DESCRIPT AS GEN_SUSPENSION_DESCRIPT,
       VAR.GEN_SWITCH,
       CHARS_27.DESCRIPT AS GEN_SWITCH_DESCRIPT,
       VAR.GEN_VOLTAGE_FREQUENCY_2,
       CHARS_28.DESCRIPT AS GEN_VOLTAGE_FREQUENCY_2_DESCRIPT,
       VAR.GEN_ADDITIONAL_FEATURES,
       CHARS_29.DESCRIPT AS GEN_ADDITIONAL_FEATURES_DESCRIPT,
       VAR.PARAM
FROM   DBO.VW_SYNERGIZE_VARIANTS as VAR
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS as CHARS_29
         ON VAR.GEN_ADDITIONAL_FEATURES = CHARS_29.CHARVALUE
            AND CHARS_29.CHARNAME = 'GEN_ADDITIONAL_FEATURES'
            AND CHARS_29.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_28
         ON VAR.GEN_VOLTAGE_FREQUENCY_2 = CHARS_28.CHARVALUE
            AND CHARS_28.CHARNAME = 'GEN_VOLTAGE_FREQUENCY_2'
            AND CHARS_28.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_27
         ON VAR.GEN_SWITCH = CHARS_27.CHARVALUE
            AND CHARS_27.CHARNAME = 'GEN_SWITCH'
            AND CHARS_27.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_26
         ON VAR.GEN_SUSPENSION = CHARS_26.CHARVALUE
            AND CHARS_26.CHARNAME = 'GEN_SUSPENSION'
            AND CHARS_26.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_25
         ON VAR.GEN_STEM_BASE = CHARS_25.CHARVALUE
            AND CHARS_25.CHARNAME = 'GEN_STEM_BASE'
            AND CHARS_25.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_24
         ON VAR.GEN_SOCKET_COVER_2 = CHARS_24.CHARVALUE
            AND CHARS_24.CHARNAME = 'GEN_SOCKET_COVER_2'
            AND CHARS_24.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_23
         ON VAR.GEN_SHIELD_DIFFUSOR_2 = CHARS_23.CHARVALUE
            AND CHARS_23.CHARNAME = 'GEN_SHIELD_DIFFUSOR_2'
            AND CHARS_23.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_22
         ON VAR.GEN_SHADE_2 = CHARS_22.CHARVALUE
            AND CHARS_22.CHARNAME = 'GEN_SHADE_2'
            AND CHARS_22.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_21
         ON VAR.GEN_REFLECTOR = CHARS_21.CHARVALUE
            AND CHARS_21.CHARNAME = 'GEN_REFLECTOR'
            AND CHARS_21.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_20
         ON VAR.GEN_MOUNTING_2 = CHARS_20.CHARVALUE
            AND CHARS_20.CHARNAME = 'GEN_MOUNTING_2'
            AND CHARS_20.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_19
         ON VAR.GEN_LOUVRE = CHARS_19.CHARVALUE
            AND CHARS_19.CHARNAME = 'GEN_LOUVRE'
            AND CHARS_19.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_18
         ON VAR.GEN_LIGHT_TECHNIQUE = CHARS_18.CHARVALUE
            AND CHARS_18.CHARNAME = 'GEN_LIGHT_TECHNIQUE'
            AND CHARS_18.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_17
         ON VAR.GEN_FRONT = CHARS_17.CHARVALUE
            AND CHARS_17.CHARNAME = 'GEN_FRONT'
            AND CHARS_17.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_16
         ON VAR.GEN_FILTER = CHARS_16.CHARVALUE
            AND CHARS_16.CHARNAME = 'GEN_FILTER'
            AND CHARS_16.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_15
         ON VAR.GEN_INSULATION_CLASS_2 = CHARS_15.CHARVALUE
            AND CHARS_15.CHARNAME = 'GEN_INSULATION_CLASS_2'
            AND CHARS_15.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_14
         ON VAR.GEN_DIMENSIONS_2 = CHARS_14.CHARVALUE
            AND CHARS_14.CHARNAME = 'GEN_DIMENSIONS_2'
            AND CHARS_14.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_12
         ON VAR.GEN_VARIANT_TYPE = CHARS_12.CHARVALUE
            AND CHARS_12.CHARNAME = 'GEN_VARIANT_TYPE'
            AND CHARS_12.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_9
         ON VAR.GEN_CANOPY = CHARS_9.CHARVALUE
            AND CHARS_9.CHARNAME = 'GEN_CANOPY'
            AND CHARS_9.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_13
         ON VAR.GEN_POLE_POST = CHARS_13.CHARVALUE
            AND CHARS_13.CHARNAME = 'GEN_POLE_POST'
            AND CHARS_13.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_11
         ON VAR.GEN_EDITION = CHARS_11.CHARVALUE
            AND CHARS_11.CHARNAME = 'GEN_EDITION'
            AND CHARS_11.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_10
         ON VAR.GEN_CORNER_ELEMENTS = CHARS_10.CHARVALUE
            AND CHARS_10.CHARNAME = 'GEN_CORNER_ELEMENTS'
            AND CHARS_10.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_8
         ON VAR.GEN_CABLE_TYPE_2 = CHARS_8.CHARVALUE
            AND CHARS_8.CHARNAME = 'GEN_CABLE_TYPE_2'
            AND CHARS_8.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_7
         ON VAR.GEN_BODY = CHARS_7.CHARVALUE
            AND CHARS_7.CHARNAME = 'GEN_BODY'
            AND CHARS_7.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_6
         ON VAR.GEN_ARM_TYPE_2 = CHARS_6.CHARVALUE
            AND CHARS_6.CHARNAME = 'GEN_ARM_TYPE_2'
            AND CHARS_6.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_5
         ON VAR.GEN_LIGHTSOURCE_SOCKET_BALL_2 = CHARS_5.CHARVALUE
            AND CHARS_5.CHARNAME = 'GEN_LIGHTSOURCE_SOCKET_BALL_2'
            AND CHARS_5.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_4
         ON VAR.GEN_COLOUR_SURFACE_2 = CHARS_4.CHARVALUE
            AND CHARS_4.CHARNAME = 'GEN_COLOUR_SURFACE_2'
            AND CHARS_4.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_3
         ON VAR.GEN_COLOUR_TYPE = CHARS_3.CHARVALUE
            AND CHARS_3.CHARNAME = 'GEN_COLOUR_TYPE'
            AND CHARS_3.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_2
         ON VAR.GEN_SPECIAL = CHARS_2.CHARVALUE
            AND CHARS_2.CHARNAME = 'GEN_SPECIAL'
            AND CHARS_2.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS CHARS_1
         ON VAR.GEN_VARIANT_TYPE = CHARS_1.CHARVALUE
            AND CHARS_1.CHARNAME = 'GEN_VARIANT_TYPE'
            AND CHARS_1.[LANGUAGE] = @_LANGUAGEID
       LEFT OUTER JOIN DBO.VW_SYNERGIZE_ALL_GEN_CHARS as CHARS
         ON VAR.GEN_EDITION = CHARS.CHARVALUE
            AND CHARS.CHARNAME = 'GEN_EDITION'
            AND CHARS.[LANGUAGE] = @_LANGUAGEID
WHERE  (VAR.GEN_PRODUCT_NO = @_KMATNR)
       AND (VAR.GEN_VARIANT_TYPE = 1)



Show quoteHide 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?
> ...



Post Other interesting topics
Strange result
Programatically Accessing Maintenance Plans through SMO
Cannot see system views?
How to calculate a 5 minute running average?
Tree table
Script for importing Windows active directory domain user accounts?
Need CheckSum Algorithm
How do I run a DTS package from vb.net
Data Manipulation
Server: Msg 8623 - The query processor could not produce a query p