Today we are continuing with tips for high volume transaction processing in an OLTP database. Yesterday we talked about preferring INSERT transactions over UPDATE or DELETE transactions. Today I want to talk about preferring SETS over multiple calls with single records. Sets are not always practical, or may not meet the business requirements. When sets are a valid options, then you can gain additional performance using set techniques.
There are a few different ways you can implement set logic. The options depend on the capabilities of your data engine. One implementation that works for all engines is to use a staging table. In this scenario, data is inserted into a staging table, and once populated, a process handles the sets of data in the staging table, creating normalized records. This two stage implementation requires a little additional work. But, you only retain locks on the transactional tables for a short period of time.
With SQL Server there are some additional options available to you. A very powerful option is BULK INSERT. You can bulk insert into your transactional tables. However, because you are often inserting into multiple tables with relationships based on IDENTITY columns, you can combine the BULK INSERT and staging table techniques.
Another option is to create a temporary table for saving data to transactional tables. This works much the same as a staging table with one key difference. Each process has its own instance of the staging table. You don’t have to maintain any sort of separation of data in staging records from those of other user’s activities. Each upload is autonomous. You can also combine a temp table with using BULK INSERT. This combination can really increase your application performance.
Another implementation available in SQL Server is user defined table types. Using user defined tables you can pass in sets of data to a stored procedure. This takes the place of a staging table, or temp table. It cannot be combined with BULK INSERT. But, it does already have good performance. Since the user defined table parameters are not persisted they don’t require transactions. The performance you gain from BULK INSERT is that the transactions maintained from BULK actions are reduced, hence the performance gain. With user defined table parameters being passed to stored procedures, the table parameters are read only, and don’t require transactional capabilities. Because no transactions are needed on the input data, you gain additional performance.
SQL engines are optimized to perform set activity. So, if you can use any of these techniques, or others you may have found, often times you can get a performance boost by taking advantage of set actions.
Next time we’ll take a look at some syntax techniques helping you optimize your set processing queries.
Cheers,
Ben