My 5-year old nephew is very smart

My 5-year old nephew is dreaming of Windows, he likes it very much. But I hope he outgrows it before reaching high school, it's good if he know other operating systems :)


"Real programmers don't write in BASIC. Actually, no programmers write in BASIC after reaching puberty" -- http://www.isaacsoft.com/Humor/RoalProgrammers.html

Let's Just Suck Less

Someone is seeking an advice based on my blog post Why So Fond Of UNION


I don't have the wisdom of a Sage, but here's my piece: Let's just suck less every sprint, every year, it's good enough


The initial step for sucking less is to identify that our code sucks


That advice somehow sucks, but I'll make sure that I’ll be giving more advice and code that sucks less through the passage of time


And let’s not forget: Let's be happy, but not satisfied, my code sucks, your code sucks, suffice to say everyone's code sucks


If we really want to truly build a product that doesn't suck, let's try to build vacuum cleaners ^_^ lol



Happy S... QL writing! ツ

Why so fond of UNION?

I'm wearing my sargchitect hat in our current sprint. Aside from improving the speed of the existing queries by making them sargable and devoid of unintended cartesian-yielding joins, the optimization team need to make sure the queries are properly refactored, easy to maintain, smooth and suave to the eyes, so the developers could have a pleasant time reading and understanding the queries.


I saw some UNION queries that can be made simpler using OR. I don't think it's ok to have a union of the same table, union like that tend to be DRY-violating, tend to have many lines, hence harder to debug and harder to optimize


I'm thinking, why the inordinate fondness for UNION if the query can be made simpler with OR? Last I checked, Microsoft haven't disabled the OR functionality. So let's rejoice to the fact that Microsoft don't have any plans to disable OR functionality for the foreseeable future.




Happy Sarging! Scratch that, Happy SARGing! ツ

Tuple Design Pattern for SQL Server

If your RDBMS doesn't support tuples (just an RDBMS-fancy speak for record), instead of this:

SELECT a,b FROM aTable
WHERE 
    (aTable.a,aTable.b) IN
    
    (SELECT anotherTable.a,anotherTable.b 
    FROM anotherTable
    WHERE anotherTable.IsActive = 1);

You have to do this:

SELECT a,b FROM aTable
WHERE     
    EXISTS
    (
        SELECT *
        FROM anotherTable
        WHERE anotherTable.a = aTable.a AND anotherTable.b = aTable.b
            AND anotherTable.IsActive = 1
    );

I prefer to write it this way though, so as to make the intent clearer:

SELECT a,b FROM aTable
WHERE 
-- (aTable.a,aTable.b) IN -- leave this commented, it makes the intent more clear
    EXISTS
    (
        SELECT anotherTable.a,anotherTable.b -- do not remove this too, perfectly fine for self-documenting code, i.e., tuple presence check
        FROM anotherTable
        WHERE anotherTable.a = aTable.a AND anotherTable.b = aTable.b
        
            AND anotherTable.IsActive = 1 -- put a blank line above, to emphasize that the above condition is filter for the tuple we are looking for
    );

Design patterns are bug reports against your programming language — Peter Norvig

Happy Computing! ツ

Debunking the myth that JOIN is faster than IN

We see now that contrary to the popular opinion, IN / EXISTS queries are not less efficient than a JOIN query in SQL Server. In fact, JOIN queries are less efficient on non-indexed tables, since Semi Join methods allow aggregation and matching against a single hash table, while a JOIN needs to do these two operations in two steps. -- http://explainextended.com/2009/06/16/in-vs-join-vs-exists/

Converted this JOIN (with DISTINCT) … (From 1 minute and 24 seconds) :

INNER JOIN dbo.fn_ssrs_ConstrainID(@LanguageCultureCode,@NeutralLanguageCode,@UserID,@PersonID,@Application,@Module,'PEOPLE','Review') cons
 
ON rpp.PersonID = cons.id

…to IN expression (optimized, down to 43 seconds) :

and rpp.PersonID in
    (select cons.id
    from dbo.fn_ssrs_ConstrainID(@LanguageCultureCode,@NeutralLanguageCode,@UserID,@PersonID,@Application,@Module,'PEOPLE','Review') cons)

Advised the two colleagues to further optimize the query's existence filters by converting all the JOIN+DISTINCT combo to IN. They were able to optimized the original query of 1 minute and 24 seconds down to 1 second.



Using the same technique as above, another colleague and I were able to optimized a stored proc that is very crucial to the web app’s performance, as that stored proc determines the visibility of some buttons on the web page. The original stored proc was 5 seconds slow, on one page there are two buttons that are using the stored proc, that makes the web page to appear only after 10 seconds or so, we were able to tame it down to sub-second response time just by converting some of the JOINs to INs.





Happy Computing! ツ