StoredProcedure,Table structure and performance?

Pavel posted on Thursday, May 17, 2007

I have 50 columns in my Employee table and my class Employee has 50 Properties as well.
DataPortal_Update method questions:

1. User can change only 1 property or all 50 of them at once.
So, what would be a  performance result if instead of creating stored procedure that include all 50 columns to update(in this case only 1 column was actually changed), I would generate on-fly SQL query that could contains only 1 column to update.

2. I can create a few tables : EmployeeInfo, EmployeeAddress,EmployeePayHistory and so on (instead of one big table) then based on what property was changed I would run a stored procedure related to that property. I don't know yet how difficult it would be to define procedure based on Property name changed .

Thanks,

Curelom replied on Thursday, May 17, 2007

The performance gains would be so minimal compared to all the work of building and maintaining such a structure, I doubt it would be worth it.  Perhaps in cases where you have a number of huge text/Varchar(Max) columns that you are updating it might be warranted, but even then, I am doubtful.  Using dynamic sql would also negate(or make worse) any performance gains.

[<b>edited by:</b> Curelom at 5:04 PM (GMT -6) on Thu, May 17 2007]
Creating separate tables would be a better way to remove columns that are updated on rare occasions.  Also separating address out and such gives you more flexibility.