If you are writing SQL...



  • And your paycheck depends on the lines of code you produce, check this new way to skip an OR:

    <font face="Courier New" size="1">insert into _inventoryCAT_DSURT (itemId, packs) 
    select vta_item, packs = sum((isnull(ASU_ARTPED,0)/100) / (ART_FACTCONV/100) ) 
    from dsurt d, dasur a, ResurtRESURTIDO01SC b 
    where sur_user = 'ship_exe' 
    and sur_sucdes=@BodyCAT 
    and SUR_status = 0 
    and ASU_SUCDES = SUR_SUCDES  
    And ASU_deliv = SUR_deliv  
    And ASU_SUCREC = SUR_SUCREC  
    and asu_item = vta_item 
    and asu_sucrec = vta_suc
    group by vta_item 

    union All

    select vta_item, packs = sum((isnull(ASU_ARTPED,0)/100) / (ART_FACTCONV/100) ) 
    from dsurt d, dasur a, ResurtRESURTIDO01SC b   
    where sur_user = 'ship_prc' 
    and sur_sucdes=@BodyCAT 
    and SUR_status = 0 
    and ASU_SUCDES = SUR_SUCDES  
    And ASU_deliv = SUR_deliv  
    And ASU_SUCREC = SUR_SUCREC  
    and asu_item = vta_item 
    and asu_sucrec = vta_suc
    group by vta_item

    SELECT  itemId,  packs = sum( packs )   
    INTO #_InventoryCAT_DSURTAUX  
    FROM _InventoryCAT_DSURT 
    GROUP BY itemId 
     
    TRUNCATE TABLE _InventoryCAT_DSURT 

    INSERT INTO _InventoryCAT_DSURT (itemId, packs) 
    SELECT itemId,  sum( packs ) FROM #_InventoryCAT_DSURTAUX  
    GROUP BY itemId  </font>

    He he, this code can be reduced to: (join detail skipped)

    <font face="Courier New" size="1">INSERT INTO _inventoryCAT_DSURT (itemId, packs) 
    SELECT b.vta_item, packs = sum((isnull(a.ASU_ARTPED,0)/100) / (b.ART_FACTCONV/100) ) 
    FROM dsurt d INNER JOIN dasur a ON ( ... )
        INNER JOIN ResurtRESURTIDO01SC b  ON ( ... )
    WHERE (sur_user = 'ship_exe' OR sur_user = 'ship_prc')
        AND d.sur_sucdes = @BodyCAT 
        AND d.SUR_status = 0
    GROUP BY b.vta_item </font>

    Mike Rod

    PS: The code is anonymized



  • @Mike Rod said:

    And your paycheck depends on the lines of code you produce, check this new way to skip an OR:



    A union can, in some obscure circumstances, be faster than an OR. Maybe your coworker buddy optimizing some slow code?



  • @savar said:

    @Mike Rod said:
    And your paycheck depends on the lines of code you produce, check this new way to skip an OR:



    A union can, in some obscure circumstances, be faster than an OR. Maybe your coworker buddy optimizing some slow code?

    Yeah, I thought of that too.

    The problem seems to be that the union does not group the two result sets.... so, sticking to the plan, this guy saw that the best solution was to group by again, this time into a temp table, truncate the original table and copy the rows from the temp table back, throwing all his "optimization" to the trash with all this operations.

    It has been 7 hours trying to debug this procedure, which turned out to be 5 stored procedures executed in sequential order, using 28 "Temp" tables to exchange information between the SPs. (the first SP drops all the "temp" tables and they are created one by one using INTO clauses) WTF?

    Damn, I will have to rewrite this logic, this thing is just bad; very complex and very badly written.

    So this is yet another case of a simple "enhancement" that ended up to be a complete rewrite.

    I'm tired.   =(

    Mike Rod



  • @savar said:

    A union can, in some obscure circumstances, be
    faster than an OR. Maybe your coworker buddy optimizing some slow code?




    What circumstances would those be?  I know (from personal
    experience) that a UNION can be faster if you're OR'ing together
    sub-selects from different tables, such as: SELECT * FROM a WHERE
    EXISTS (SELECT * FROM b...) OR EXISTS (SELECT * FROM c...), but when
    it's just OR'ing together two discrete values, like here, the mind
    boggles.



  • @Colin McGuigan said:

    @savar said:
    A union can, in some obscure circumstances, be
    faster than an OR. Maybe your coworker buddy optimizing some slow code?




    What circumstances would those be?  I know (from personal
    experience) that a UNION can be faster if you're OR'ing together
    sub-selects from different tables, such as: SELECT * FROM a WHERE
    EXISTS (SELECT * FROM b...) OR EXISTS (SELECT * FROM c...), but when
    it's just OR'ing together two discrete values, like here, the mind
    boggles.

    I can't remember exactly, but it was using sub-selects within an OR.

    The OR version ran for several hours before being terminated. The UNION version ran in twenty seconds.



  • @Mike Rod said:

    Damn, I will have to rewrite this logic, this thing is just bad; very complex and very badly written.

    So this is yet another case of a simple "enhancement" that ended up to be a complete rewrite.

    I'm tired.   =(

    Mike Rod



    Ha, its only a re-write because you care enough to make good things. I'm sure most developers would shoe-horn in just enough hack to get the job done.


  • @savar said:

    @Colin McGuigan said:
    @savar said:
    A union can, in some obscure circumstances, be
    faster than an OR. Maybe your coworker buddy optimizing some slow code?




    What circumstances would those be?  I know (from personal
    experience) that a UNION can be faster if you're OR'ing together
    sub-selects from different tables, such as: SELECT * FROM a WHERE
    EXISTS (SELECT * FROM b...) OR EXISTS (SELECT * FROM c...), but when
    it's just OR'ing together two discrete values, like here, the mind
    boggles.

    I can't remember exactly, but it was using sub-selects within an OR.

    The OR version ran for several hours before being terminated. The UNION version ran in twenty seconds.


    Imagine one big table... no, bigger... no, even bigger. Ok if you query that table with some search conditions that break/ignore/have more fields than the index, it is better to union two queries that do a index search than one that does a full table scan. That is one case

    ... I don't know if I over-simplified this example. Umm... a little help here?

    Mike Rod



  • @savar said:

    @Mike Rod said:

    Damn, I will have to rewrite this logic, this thing is just bad; very complex and very badly written.

    So this is yet another case of a simple "enhancement" that ended up to be a complete rewrite.

    I'm tired.   =(

    Mike Rod



    Ha, its only a re-write because you care enough to make good things. I'm sure most developers would shoe-horn in just enough hack to get the job done.


    Mayhap, but I have learned that if you are gonna make big changes: the earlier, the better.

    If I am the guy that is going to maintain this code from now on, I better change it to make it (at the very least) maintainable, it may take some time, but they don't expect me to "get used" to the code right away (since this is my second day at the project).

    So, if the client expects to get the changes for the system a little slower (due changes on the dev team), maybe it is time to talk to the PM and get support for this rewrite.

    Mike Rod.

Log in to reply