SQL Server performs very well on data warehouse

I personally don't like .net, ms office, windows and all this shit. Maybe because I love open source, Linux, Java, Mac.

In a data warehouse project, we utilized MySql as first option but the database was considerably slow with 90 Gigabytes database (We are using "star schema" for the data warehouse).

In order to solve this problem, the database was switched to SQL Server . After the initial historical loading, the SQL Server database has got 460 Gigabytes of data and I am very impressed with the response time.

Considering the amount of data processed, I have to admit that SQL Server is a great database for huge data warehouse projects.

It is difficult to sit on your hands when the SQL Server performs comfortably well in a 460 GB database.

MySQL command Explain saved my life!

Today I have to thank Paulinho about everything I learned in my Post Graduate APGS (Analisys, Project and Management of Information System - Latu Sensu ). Paulinho is a database specialist and teaches DB subject at PUC-RJ (Pontífica Universidade Católica) in Rio de Janeiro.

I remember I didn 't give it a shit about that subject, used to joke about that and I didn 't believe in that f#&*$ng "explain" command at that time.

I have been involved in a project which uses MySql, JBoss Seam, JBoss Server, JAX-WS and RedHat Linux... Pretty cool stuff!!!

I had to run one query quite big with a couple of joins. I left running for 1 hour and didn 't finish.

This time I remembered Database classes with the teacher Paulinho and decided to "explain" that query.
You don't need to be a DBA to do it, just need to put the word "explain" in front of your query and run it!!! like this:

explain select * from employee inner join department on ...

The most important thing in your result is the column "Type" which tells you how bad is you query.

For the worse to the best ones, here are possibles values of "Type" (in Mysql):

All, index, range, Eq_ref, const, system

If you see "All" in any line of the "type" column means you have your ass in the line (tá fudido).

If you carefully analyze the result you can much improve your query. In my case I just created a few indexes and removed some calculated fields from the clause where.

The original query was running in one hour - The new query ran in less than one minute making inner join in tables with over 1 million lines each!!!

Now we are going to take a look at Ingres database... Let have a go and see it... write about that later...

Leonardo Correa
Java Analyst/Developer, soccer player and beer drinker