Wednesday, September 09, 2009

#Firebird , #Mysql and Not NULLs + Stored Procedures

Last night/morning i have read again some rants against the NULLs in one of the CJ Date book SQL and the Relational theory and in practice it's is true that I found one table with something like 20% of it filled with nulls (not normalized) and let's say that count(*) is in the range of millions , Now you have a lot of scans for data that is not there So is better to split that table and normalize it also the columns should be NOT NULL by default for example EMPLOYEE_ID

Now comes the good part you don't need to pollute the queries with NOT NULLS anymore and you get more speed , it depends how many nulls you had before (of course there are opimizer tricks to do some shortcuts)
and save the plannet for overheating

On mysql i eliminated not null check from one query it didn't improved too much the speed (~0.2s ) but at least it's easier to read
on firebird side it improved performance but again not so much (~0.2 s)

I started to create a selectable stored procedure and at least in firebird part it was faster 0.2s vs the query without selectable sp

In the end i got 0.4 seconds only by eliminating NULL from the query and creating a selectable procedure

No comments: