Post reply

What's wrong with this?

  • October 16, 2009 at 2:35 pm

    #215588

    I just got in a new contract... get a load of this beautiful query :

    SELECT

    BIO_SR_ENVOI_ELEC_MICRO_RST.NoParam

    , Right([Queue_des_echantillons].[Noechantillon],5) AS NoEchantillon

    , BIO_SR_ENVOI_ELEC_MICRO_RST.Valeur, BIO_SR_ENVOI_ELEC_MICRO_RST.NoTest

    FROM ((Queue_des_echantillons

    LEFT JOIN BIO_TB_NoCertificat_VS_NoEchantillon

    ON Queue_des_echantillons.Noechantillon = BIO_TB_NoCertificat_VS_NoEchantillon.NoEchantillon)

    RIGHT JOIN BIO_SR_ENVOI_ELEC_MICRO_RST

    ON Queue_des_echantillons.Noechantillon = BIO_SR_ENVOI_ELEC_MICRO_RST.NoEchantillon)

    LEFT JOIN Queue_des_Parametre

    ON (BIO_SR_ENVOI_ELEC_MICRO_RST.NoParam = Queue_des_Parametre.NoParam)

    AND (BIO_SR_ENVOI_ELEC_MICRO_RST.NoEchantillon = Queue_des_Parametre.Noechantillon)

    WHERE (Left([NoProjet],1) Like 'A' AND Queue_des_Parametre.StatutTraitement Like 'approuv')

    OR ((Left([NoProjet],1) Like 'A') AND Queue_des_Parametre.StatutTraitement Like 'approuv')

    OR (((Left([NoProjet],1)) Like 'A') AND ((Queue_des_Parametre.StatutTraitement) Like 'approuv'))

    OR (((Left([NoProjet],1)) Like 'A') AND ((Queue_des_Parametre.StatutTraitement) Like 'approuv'))

    OR (((Left([NoProjet],1)) Like 'A') AND ((Queue_des_Parametre.StatutTraitement) Like 'approuv'))

    OR (((Left([NoProjet],1)) Like 'A') AND ((Queue_des_Parametre.StatutTraitement) Like 'approuv'))

    OR (((Left([NoProjet],1)) Like 'A') AND ((Queue_des_Parametre.StatutTraitement) Like 'approuv'))

    OR (((Left([NoProjet],1)) Like 'A') AND ((Queue_des_Parametre.StatutTraitement) Like 'approuv'))

    OR (((Left([NoProjet],1)) Like 'A')

    AND ((Queue_des_Parametre.StatutTraitement) Like 'approuv'))

    OR (((Left([NoProjet],1)) Like 'A') AND ((Queue_des_Parametre.StatutTraitement) Like 'approuv'))

    OR (((Left([NoProjet],1)) Like 'A') AND ((Queue_des_Parametre.StatutTraitement) Like 'approuv'))

    OR (((Left([NoProjet],1)) Like 'A') AND ((Queue_des_Parametre.StatutTraitement) Like 'approuv'))

    OR (((Left([NoProjet],1)) Like 'A') AND ((Queue_des_Parametre.StatutTraitement) Like 'approuv'))

    GROUP BY BIO_SR_ENVOI_ELEC_MICRO_RST.NoParam

    , Right([Queue_des_echantillons].[Noechantillon],5)

    , BIO_SR_ENVOI_ELEC_MICRO_RST.Valeur

    , BIO_SR_ENVOI_ELEC_MICRO_RST.NoTest

    , Queue_des_Parametre.StatutTraitement

    HAVING (((BIO_SR_ENVOI_ELEC_MICRO_RST.NoParam) Like '*acctcf01')

    AND ((BIO_SR_ENVOI_ELEC_MICRO_RST.NoTest) Like 700005003)

    AND ((Queue_des_Parametre.StatutTraitement) <> 'annul'))

    OR (((BIO_SR_ENVOI_ELEC_MICRO_RST.NoParam) Like '*acctcf01')

    AND ((BIO_SR_ENVOI_ELEC_MICRO_RST.NoTest) Like 700005162)

    AND ((Queue_des_Parametre.StatutTraitement) <> 'annul'))

    OR (((BIO_SR_ENVOI_ELEC_MICRO_RST.NoParam) Like '*acctcf01')

    AND ((BIO_SR_ENVOI_ELEC_MICRO_RST.NoTest) Like 700005424)

    AND ((Queue_des_Parametre.StatutTraitement) <> 'annul'))

    OR (((BIO_SR_ENVOI_ELEC_MICRO_RST.NoParam) Like '*acctcf01')

    AND ((BIO_SR_ENVOI_ELEC_MICRO_RST.NoTest) Like 500020004)

    AND ((Queue_des_Parametre.StatutTraitement) <> 'annul'))

    OR (((BIO_SR_ENVOI_ELEC_MICRO_RST.NoParam) Like '*acs?--01')

    AND ((BIO_SR_ENVOI_ELEC_MICRO_RST.NoTest) Like 700005007)

    AND ((Queue_des_Parametre.StatutTraitement) <> 'annul'))

    OR (((BIO_SR_ENVOI_ELEC_MICRO_RST.NoParam) Like '*accpha01')

    AND ((BIO_SR_ENVOI_ELEC_MICRO_RST.NoTest) Like 700005425)

    AND ((Queue_des_Parametre.StatutTraitement) <> 'annul'))

    OR (((BIO_SR_ENVOI_ELEC_MICRO_RST.NoParam) Like '*accf--01')

    AND ((BIO_SR_ENVOI_ELEC_MICRO_RST.NoTest) Like 700005424)

    AND ((Queue_des_Parametre.StatutTraitement) <> 'annul'))

    OR (((BIO_SR_ENVOI_ELEC_MICRO_RST.NoParam) Like '*accf--01')

    AND ((BIO_SR_ENVOI_ELEC_MICRO_RST.NoTest) Like 700005161)

    AND ((Queue_des_Parametre.StatutTraitement) <> 'annul'))

    OR (((BIO_SR_ENVOI_ELEC_MICRO_RST.NoParam) Like '*accf--01')

    AND ((BIO_SR_ENVOI_ELEC_MICRO_RST.NoTest) Like 700005162)

    AND ((Queue_des_Parametre.StatutTraitement) <> 'annul'))

    OR (((BIO_SR_ENVOI_ELEC_MICRO_RST.NoParam) Like '*acctec01')

    AND ((BIO_SR_ENVOI_ELEC_MICRO_RST.NoTest) Like 700009050)

    AND ((Queue_des_Parametre.StatutTraitement) <> 'annul'))

    OR (((BIO_SR_ENVOI_ELEC_MICRO_RST.NoParam) Like '*acctec01')

    AND ((BIO_SR_ENVOI_ELEC_MICRO_RST.NoTest) Like 700009051)

    AND ((Queue_des_Parametre.StatutTraitement) <> 'annul'))

    ORDER BY Right([Queue_des_echantillons].[Noechantillon],5)

  • October 16, 2009 at 2:36 pm

    #1067206

    5$ says you might not be able to find everything wrong with it in 1 scan!!!

  • October 16, 2009 at 2:38 pm

    #1067209

    Oh btw, this is only 1 of the queries part of a 6000 lines code snippet, under a single button click.

    They claim this program runs slow (25 minutes to generate 10K worth of text files).

    I wonder where the problem might be :w00t:.

  • October 16, 2009 at 2:43 pm

    #1067213

    Is that T-SQL?

    I ask, because the parentheses at the beginning of the From clause don't seem to encapsulate a derived table.

    Another first-glance issue is all the Like statements that seem to be using an ansterisk instead of one of the T-SQL wildcards (percent symbol, underscore, string in brackets). Also the use of Like with specific numeric values, as opposed to strings.

    Beyond that, assuming the syntax is correct, I can see a huge number of performance-killing concepts, but the odd syntax makes me wonder if those are moot issues, because if it's an engine other than MS-SQL, I can't judge performance issues in it.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • October 16, 2009 at 2:52 pm

    #1067215

    Looks like MS Access. Specially with the comment 'under a single button click'

    Likes without wildcards

    Non-aggregate comparisons in the having clause

    The same two predicates repeated several times with ORs between them. Looks like some may be redundant too.

    Group by, but I don't see any aggregates (might have missed them though)

    Probably lots more.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 16, 2009 at 3:24 pm

    #1067242

    ...a HAVING clause that has nothing to justify filtering AFTER aggregations?

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • October 17, 2009 at 3:19 am

    #1067309

    You got most of them....

    The ones I particulaly like is the mixed use of left and right joins (not a bug by any means, but just odd).

    Group bys without aggregates (hidden distinct), meaning a very poor query, poor data, poor join and / or poor programmer.

    Yes this is comming from access mdb (97) linking to sql 7. The 20+ repeating where conditions are mostly comming from the wizard or gui.

    Now the biggest kicker here is that the local names for the linked table where renamed. So that now that I want to remake the query on sql 7 from query analyser, it totally breaks because of 2 missing tables..

    If it happens to you, just loop through the tabledefs collection and lookup sourcetable property and you'll be able to "fix" this.

    Of course renaming all 200 local tables is totaly out of the questions since there are 1000s of local queries and a couple 100 000 lines of code.

    I laughed so hard when I saw that one that I had to stop working for 15 minutes after showing it to my boss (IT guy with no fault mentality, including performance from a 15 years experience in a major bank).

  • October 21, 2009 at 6:45 am

    #1068819

    That's a fright all right.

    I didn't even read through it all, but at the end I saw this and started laughing

    ORDER BY Right([Queue_des_echantillons].[Noechantillon],5)

    So much for using an index.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 8 posts - 1 through 7 (of 7 total)

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