August 18, 2005

Data Access Layers

Along with one of my coworkers, I have been looking into different data access layer strategies at work. We have been digging into various code gen tools like LLBLGen and CodeSmith as well as considering a more data centric approach by using the new TableAdapter facilities in VS2005 that allows the developer to drag and drop tables and generate stored procedures with mouse clicks instead of writing T-SQL.

On the initial cut, both strategies seem to have about the same maintenance weight. While the code gen tools will allow you to generate the entire data access layer in just a few minutes, you still must deploy the stored procedures. The TableAdapter tools access the database directly via a Tabular Data Stream therefore for basic crud operations there is no SQL involved at all and the only stored procedures that get generated are for special circumstances.

Performance was another concern. The hypothesis we both had was that the TableAdapter method would be faster since it didn't go through a stored procedure or T-SQL to gets the data, but access the table directly at a lower level. While that may be true about how it accesses the data, the initial testing is not proving it to be anymore speedier of a method.

As you can see from this test app written tonight to test the two methods, the different is only about 200ms for 300 inserts. They were inserting the exact same data in the same table, one after the other. Since 14 seconds seems a bit high to insert 300 rows on a table with no indexes and no constraints and contained 0 rows, there is probably another bottle neck somewhere that might be affecting a true test of these different data access strategies. Also, this was only testing inserts, not updates or selects.

More to follow....