1

I'm looking for some advice on reducing the execution time for inserts and deletes on a custom database setup that is used for logging data from a remote location.

Basically I have a custom program that connects to a remote site, pulls out 5,000 records at a time, uploads them to a server on the cloud and then deletes these records from the remote site.

This is only being done on a single table that is pretty simple. The table only has 4 columns (a datetime, a float and 2 integers). No index, no foreign keys, no primary keys, nothing. Each record is unique via a combination of the datetime and 2 integers. There is an external program that writes to the remote database (it is a vendor program and I get zero control over how it works).

Now the problem is that the inserts and deletes are taking too long - about 15 seconds for the insert to the cloud database and 25 seconds for the delete on the remote database. This thing is logging about 10,000 records per minute and so I need to be able to pull off at least double that to keep on top of things. Right now I am barely keeping up and if there is any downtime on the server I can't catch back up.

The custom program creates a large string of inserts and deletes and executes these on mass like this:

INSERT INTO CLOUD_TABLE (COL_DATEANDTIME, COL_INT1, COL_INT2, COL_FLOAT) VALUES ('2000-01-01 00:00:00', 1, 1, 3.14);
INSERT INTO CLOUD_TABLE (COL_DATEANDTIME, COL_INT1, COL_INT2, COL_FLOAT) VALUES ('2000-01-01 00:00:00', 1, 2, 3.14);
INSERT INTO CLOUD_TABLE (COL_DATEANDTIME, COL_INT1, COL_INT2, COL_FLOAT) VALUES ('2000-01-01 00:00:00', 1, 3, 3.14);

DELETE FROM REMOTE_TABLE WHERE COL_DATEANDTIME = '2000-01-01 00:00:00' AND COL_INT1 = 1 AND COL_INT2 = 1;
DELETE FROM REMOTE_TABLE WHERE COL_DATEANDTIME = '2000-01-01 00:00:00' AND COL_INT1 = 1 AND COL_INT2 = 2;
DELETE FROM REMOTE_TABLE WHERE COL_DATEANDTIME = '2000-01-01 00:00:00' AND COL_INT1 = 1 AND COL_INT2 = 3;

I've tried looking online for advice but all of the advice out there is for much more complicated database scenarios. I honestly thought the database would have handled this load easily (I need to transfer about 150 rows per second). Does anyone have any tricks or tips on how I can make this faster?

Some other notes:

  • I can probably add keys to the database table, but I can't change the structure at all
  • Remote computer is basically a PC running SQL Express. It's CPU load is about 10% to 15%. Queries are generally fast (fetching the 5,000 records takes less than a second)
  • Cloud database is an Azure DB (SQL Server)
  • Transfer program is written in C# and uses System.Data.SqlClient to connect to the databases
0

3 Answers 3

3

You can do two easy things to likely help improve your performance meaningfully:

  1. On the remote database that you're selecting and deleting data from, you should create the following index to help improve delete performance: CREATE UNIQUE CLUSTERED INDEX IX_REMOTE_TABLE_COL_DATEANDTIME_COL_INT1_COL_INT2 ON REMOTE_TABLE (COL_DATEANDTIME, COL_INT1, COL_INT2).

Right now, without an index to efficiently locate the rows (and particularly without a clustered index) the data is stored in a random order in a Heap in that table. Every individual DELETE statement you run needs to scan the entire table of randomly sorted data until it finds that row to delete. With the aforementioned index, it should be able to seek directly to the row it needs to delete, much faster. The index persists the data of the table sorted on those columns listed in its definition in an efficient data structure called a B-Tree

  1. Speaking of individual DELETEs, that's your other issue here (same for the individual INSERTs). You should do these operations in batch statements instead, to maximize the rate at which the database can perform them.

For example, you can convert your 5,000 individual INSERT statements into a few batch statements like so:

INSERT INTO CLOUD_TABLE (COL_DATEANDTIME, COL_INT1, COL_INT2, COL_FLOAT)
VALUES
    ('2000-01-01 00:00:00', 1, 1, 3.14),
    ('2000-01-01 00:00:00', 1, 2, 3.14),
    ('2000-01-01 00:00:00', 1, 3, 3.14);

Note there is a limitation on the VALUES clause to a maximum of 1,000 rows of values at a time. So you'd have to create 5 batch statements (1,000 rows per) if you're inserting 5,000 rows at a time. Still significantly better than 5,000 individual INSERT statements.

Similarly you can convert your code to a single batch DELETE statement like so:

DELETE FROM REMOTE_TABLE WHERE COL_DATEANDTIME = '2000-01-01 00:00:00' AND COL_INT1 = 1 AND COL_INT2 IN (1,2,3);

This minimizes the downtime of running each individual statement one at a time, and allows the database engine to generate the most efficient execution plan (such as potentially with parallelization) to execute these queries with.

An added bonus is it also reduces the actual amount of code you need generate, maintain, and debug should you have issues.

0
1

Thanks for your advice, it really helped. I thought I would share what I did to help others in the future.

  1. I combined the inserts as suggested into one big insert with 1,000 records at a time. This dropped the overall insert speed from about 15 seconds to less than 5.

  2. I tried to do the same with the deletes but the performance was far worse and caused an execution timeout (I had it set to 30 seconds, bumped it up to a minute but still triggered it. Previously 5,000 records would take about 25 seconds to execute).

  3. I added a clustered index on the DateTime and 2 integer columns. This really helped speed things up, but it was tricky to implement on a database that had 131 million rows... The maximum database size in SQL express is 10Gb and this was already used up so the extra space required for an index wasn't there. In the end I just dumped 95% of the data so that I could create the index. There were about 5 million records left. I thought it would take a long time but only took a few minutes to create the index.

Now it is transferring around 50,000 records every minute - a 5x improvement.

I'm very pleased, although there will be about a week's worth of data missing in the middle for the future... Oh well, my client will just have to get used to it. But I will know in the future to be a bit more careful when setting this up (to be honest, I didn't think I would be logging so much data - the original brief was 7 data points every 10 seconds, not 150 every second).

1
  • "I tried to do the same with the deletes but the performance was far worse" - This is probably due to a poorly generated execution plan. Would need to see said execution plan to offer advice on how to improve it. Likely re-writing the batched query in a logically equivalent way would fix it. In any case, glad my advice helped. Not required, but feel free to upvote and / or accept the answer so others with similar questions see it was helpful as well.
    – J.D.
    Commented Apr 27 at 13:02
0

The fastest way to add data to SQL Server is to use one of the bulk loading mechanisms.

Since you are using C#, that would be SqlBulkCopy.

You'll get the best speed using the SqlBulkCopyOptions.TableLock option, especially if the target database is using the SIMPLE or BULK_LOGGED recovery model.

With the table lock, you may block other operations on the target table while the bulk load completes, depending on the isolation level used by any concurrent readers.

If this is an issue, and you still need the extra speed:

  1. Bulk load to a separate table with the same columns. Clustered index optional.
  2. Begin a transaction.
  3. Copy the rows across from the new table to the target table in a single statement.
  4. Truncate the separate table, ready for the next data load.
  5. Commit the transaction.

As already noted, you need a unique clustered index or primary key to enforce data integrity and speed up locating records to delete.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.