Wednesday, July 27, 2011

Refactoring data access layer

I refactored a highly used data access layer method and achieved 50% reduction in lines of code, 20% reduction in execution time, and increased readability and error checking.

Optimizations:
1. Combined several separate SQL statements into one long statement with multiple joins. This reduces the number of trips to the SQL Server.

2. Former code was using multiple separate try catch blocks to test for null values in the output. I removed all of these and replaced them with ISNULL in the sql statement.

3. Moved SQL statement to a stored proc instead of building a big statement in the code. This reduces the amount of data traveling over the network, because I'm only passing in the stored proc's name, and reduces execution time because stored procs are compiled and reused, as opposed to rebuilding a command text string every execution

4. Instead of doing "if (statement) boolval = true else boolval = false; I did boolval = statement instead. This is a readability issue more than anything.

No comments:

Post a Comment

There was an error in this gadget