More performant way to write this where clause

  • ktflash

    Ten Centuries

    Points: 1012

    Ahoi,

    i have the following where clause which was created by my predecessor and i wonder if there was a better way to write this since all these OR are not very efficient:

     where		(
    (
    (
    a.charge_id = c.bewertungsart_id and
    a.bewertungstyp_id != ' ' and
    a.chargenpflicht_Kz = ' '
    )
    or
    (
    c.bewertungsart_id = ' ' and
    a.bewertungstyp_id = ' ' and
    a.chargenpflicht_Kz = 'X'
    )
    or
    (
    a.bewertungstyp_id != ' '
    and a.chargenpflicht_Kz = 'X'
    and a.Bewertungsart_ID = c.Bewertungsart_ID
    )
    )
    and a.Bestand_Id in ('MCHB','MSKA')
    ) OR
    (
    a.Chargenfuehrung_Kz = ' '
    and c.bewertungsart_id = ' '
    and a.Bestand_Id in ('MARD','MSKA')
    )

    The only things i could think of:

    - replacing the strings with a bit fields for these true/false checks

    - i attempted something trying to compare it to hardcoded strings an in (...)

    But it doesnt produce the same result i guess he has some issues with the empty strings, not sure if it would be faster in the first place

    (a.Chargenfuehrung_Kz = ' 'and c.bewertungsart_id = ' ' and a.Bestand_Id in ('MARD','MSKA'))
    a.Chargenfuehrung_Kz+c.bewertungsart_id+a.Bestand_Id in ('  MARD','  MSKA')

    I wonder if there is better way to write all this OR statements

     

     

  • Thom A

    SSC Guru

    Points: 98255

    That WHERE makes it look like your query is suffering from poor alias choices. Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3)

    What you have there seems "ok", but difficult to honestly tell. The clause a.Chargenfuehrung_Kz+c.bewertungsart_id+a.Bestand_Id in (' MARD',' MSKA') is likely to be a bad idea though, as it's likely to lower the SARGability of the query (though the complexity of the OR isn't going to be helping the Data Engine).

    Depending on your goals, you might be better off with a lookup table.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • ktflash

    Ten Centuries

    Points: 1012

    Thom A wrote:

    That WHERE makes it look like your query is suffering from poor alias choices. Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3)

    What you have there seems "ok", but difficult to honestly tell. The clause a.Chargenfuehrung_Kz+c.bewertungsart_id+a.Bestand_Id in (' MARD',' MSKA') is likely to be a bad idea though, as it's likely to lower the SARGability of the query (though the complexity of the OR isn't going to be helping the Data Engine).

    Depending on your goals, you might be better off with a lookup table.

     

    Yeah its hard to tell and i agree, even though the alias choice was not made by me i still catch myself doing this aswell.

    Thanks for the Lookup hint, ill take a look into that.

    This was the format before i rearragend it, in my opinion absolutely unreadable:

     where (((a.charge_id			= c.bewertungsart_id	and a.bewertungstyp_id   != ' '       
    and a.chargenpflicht_Kz = ' ') or
    (c.bewertungsart_id = ' ' and a.bewertungstyp_id = ' '
    and a.chargenpflicht_Kz = 'X') or
    ((a.bewertungstyp_id != ' ' and a.chargenpflicht_Kz = 'X' and
    a.Bewertungsart_ID = c.Bewertungsart_ID)))
    and a.Bestand_Id in ('MCHB','MSKA'))
    OR
    (a.Chargenfuehrung_Kz = ' '
    and c.bewertungsart_id = ' '
    and a.Bestand_Id in ('MARD','MSKA'))
  • ktflash

    Ten Centuries

    Points: 1012

    Ok, i found a to rewrite the query producing the same result:

    --previously:
    (
    a.charge_id = c.bewertungsart_id and
    a.bewertungstyp_id != ' ' and
    a.chargenpflicht_Kz = ' '
    )
    or

    (
    a.bewertungstyp_id != ' '
    and a.chargenpflicht_Kz = 'X'
    and a.Bewertungsart_ID = c.Bewertungsart_ID

    --now:
    (
    a.bewertungstyp_id != ' ' AND
    a.chargenpflicht_Kz in (' ','X') AND
    (
    a.charge_id = c.bewertungsart_id OR
    a.Bewertungsart_ID = c.Bewertungsart_ID
    )

    )

     

    Now i have an additional question:

    Does the ORDER matter if you use in ('...','.....)?

    Example:

    chargenpflicht_Kz in (' ','X')
    --VS
    chargenpflicht_Kz in ('X',' ')

    Knowing there is way more entries with 'X', is there performance boost or will this be corrected by the query optimizer anyway?

    counter

    Attachments:
    You must be logged in to view attached files.
  • ScottPletcher

    SSC Guru

    Points: 98045

    Since an IN gets converted to ... OR ... OR ..., I suppose it might make a difference, in which case you'd want to put the most common values first.

    You have a possible issue in your WHERE clause above, in that you need to verify the specific value of a.chargenpflicht_Kz for each condition.  This will better match your original logic while also pre-screening a.chargenpflicht_Kz:

            (
    a.bewertungstyp_id != ' ' AND
    a.chargenpflicht_Kz in (' ','X') AND
    (
    (a.charge_id = c.bewertungsart_id AND
    a.chargenpflicht_Kz = ' ') OR
    (a.Bewertungsart_ID = c.Bewertungsart_ID AND
    a.chargenpflicht_Kz = 'X')
    )
    )

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply