Wednesday, November 12, 2008 4:12 AM
I work on a team that has been using SQL Server exclusively for database storage and access. To accomplish this, we've been using the Sqlxxx classes for creating connections, commands, etc.
Recently, we found the need to support a customer that used Oracle for its database management system. We changed our base classes to use the DBFactory class to get a DBConnection based on the provider specified and carry on from there.
The result was an 8-10% decrease in performance (even with existing SQL Server databases), and the main lag seems to be in the creation of the connection. We deal in massive amounts of data, and that lag is completely unacceptable.
We ran eight different tests using the Sql classes and the DB classes retrieved from the DBFactory. The first two tested our base classes using Sql and DB classes where the connection was being created every time before the test stored procedure was called. This was executed 10,000 times, and the Sql classes were the clear winner.
Then we ran a test where the connection was created before the loop and then the connection object was reused 10,000 times within the loop. The difference between the Sql and the DB classes were nearly identical, with the Sql classes just beating out the DB classes.
Finally to narrow down the source of the lag, we ran the same four tests, but left out the call to the stored procedure. The test that created the connection and then modified the parameters 10,000 times (without calling the procedure) was an astounding win for the DB classes. The result for creating and disposing the connection 10,000 times was absolutely dismal for the DB objects by a factor of 10.
Has anyone else experienced a lag using the DBFactory class? Would it be wiser to instantiate the abstract DBConnection object using the Activator class instead? The lag definitely seems to be on the connection, not the actual call to the database.
Wednesday, November 12, 2008 10:00 PM
It seems you are hitting one of two possible issues:
- On introducing the DBFactory, you lost Connection Pooling. If so, it is relatively easy to enable it explicitly on your DbFactory, so your SqlConnections are coming from a pool and not degrading so much
- Or your DbFactory code is doing a bunch of work in the loop. If so, can you get the DbFactory out of the loop? and for the full system, can you create a single instance of the factory? Even if you have to introduce some locking to access the shared DbFactory, it shouldn't degrade after the extra initialization costs are accounted.
Please follow up your results with me
Wednesday, November 12, 2008 10:53 PM
Thanks for the ideas.
We have the DbFactory (buried in base classes) being created each time in the loop because we were testing the performance of those base classes compared to the base classes we normally use and distribute. One thing you hit on that could very well be the problem is the recreation of the DbFactory 10,000 times per test. If it was only created once, perhaps that would drop the lag down considerably.
However, we seem to have worked around the problem by avoiding the factory all together. The performance is outstanding by using interface fields (e.g. IDbConnection) and constructing the object based on a provider variable passed into the base classes.