Thanks for taking the time.I've inherited an invoicing application. Tons of data. One particular table generally runs about 700,000 records. This data has a one to many relationship with another huge table (about 1,000,000) records.The first structure looks like this:TABLE_ParentID, percentApplied, TABLE_Child1ID, parentID, foreignIDTABLE_Child2ID, parentID, differentForeignID
The parent table will always have a child record in one child table, never in both. This causes any queries which need all of the pertinent data for the parent table to do a left join to both child tables. This combined with the usual join to the other big tables makes my queries, even with indexing, come in at 5 - 8 seconds depending on the amount of data to be returned. This is a production application and is widely used. I've decided that a performance improvement would be to de-normalize the tables (I actually don't think they should have been normalized in the first place) by adding 2 columns to look like this:TABLE_ParentID, percentApplied, Child_1foreignID, Child_2differentForeignID
I will then add triggers to the 2 child tables to update the parent table on insert/update. With this strategy I can begin to improve performance in a targeted manner, addressing the worst performers to no longer use the left joins. Improvements will be able to be implemented through multiple production releases.Thoughts?Flames?ThanksSTGod Bless
Design Question to improve performance
Posted - 2009-05-07 : 11:11:33
