SQLRally

Well, I have my flight booked and registered as a speaker. Looking over the speaker provided schedule, there are many sessions to choose from in the Business Intelligence track. My first goal is to get as much SSIS and Data Mart design training as I can over the 2 days of sessions.

I still have not decided about what Pre-Conference session I want to attend. Grant Fritchey’s Query Tuning is number one on the list, but learning BI from the Pragmatic Motley Crew would probably benefit my career as a better choice. Then, there is Kevin Kline teaching Personal Development. Since I will be attending regular BI sessions, I am leaning towards Kevin Kline’s PD, with Grant a close second (you can never get too much performance tuning advice).

This past week at work, I was tasked with adding columns to a Fact table that a consultant started to work on before the end of the contract with their company. I noticed performance problems with the additions to the Inserts and Updates. After investigating, I found out using NOT Null and Default on the Alter Table Add column was best since some of the existing rows were not going to have values. The ETL had to be changed for Daily and Monthly updates.

The Monthly Updates were taking between 4 and 8 hours. After digging a little more, the Monthly Updates where not updating just the current period, but all data. By Adding a WHERE CURRENT_PERIOD = ‘Y"’, the processed reduced to 10 minutes.

Also, a fact table had 6 Non-clustered indexes, no primary key and no clustered index. This is taking a little more work because of the structure of the fact table, but an additional clustered index after removing all non-clustered. Then, adding Non-cluster indexes back after updating their structure to exclude the new non-clustered columns were appropriate, help take the 9-10 hour process down to 3 hours 10 minutes. Also, a big thanks to the Network/Infrastructure group that has enabled us to have a dev/test/prod environment that are almost all equal. That is a blessing not many shop s have.

Unbelievable!!! Whenever I am able to work on something like this, I have a tendency to try and tune more and more and more… never finishing. But it also reinforces all the training and real world experience in action and validate I am on the right track.

There is so much to learn in the Database world, but stopping and concentrating on a few items to become an expert gets harder and harder.

God Bless,

Thomas