Data Architecture Chapter restarted

 

Here is a link to @SqlSoldier blog on the restarting of the PASS Data Architecture Virtual Chapter.

I was involved with the Performance Chapter for about 6 months, until I changed positions at Amedisys.

http://www.sqlsoldier.com/wp/sqlserver/darcvcreborn

If you would like to speak, please email us from the Virtual Chapter site

PASS Data Architecture Virtual Chapter

This weekend is time for some spiritual food listening to speakers talk about the walk with God. It will be nice to take a break from life, and work on the spirit.

God Bless,

Thomas

Visit the site: http://dataarch.sqlpass.org/
Follow us on Twitter: @DArchVC
Like us on Facebook: Data Architecture VC

Next Meeting Announced

Subject:
Database Normalization

Start Time:
Thursday, April 21, 2011 12:00 PM US Central Time (6:00 PM GMT)

End Time:
Thursday, April 21, 2011 1:00 PM US Central Time (7:00 PM GMT)

Presenter:
Thomas LeBlanc (blog|@TheSmilingDBA)

Live Meeting Link:
https://www.livemeeting.com/cc/usergroups/join?id=28NR92&role=attend&pw=PTSs%26%5E%282Z

Database Standards SOP
Join Normalization nut Thomas LeBlanc for a review of a standard operating procedure used among DBAs at an employer. See the changes he made after joining the BI group at this employer. The session will go through naming conventions, check list for creating a table, formatting in stored procedures, and more. A brief preview of the SQLRally talk 3rd Normal Form: That’s Crazy Talk!!! Will be given about Lookup tables. This discussion comes from 21+ years of developing databases for application developers. The use of identity columns for primary keys, and the need for a unique constraint on transaction tables that do resort to ID columns will be covered.

Add to your calendar

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