Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've been tasked with inserting data into a SQL Server table with a geography column. I've found that my times for doing inserts (same data 1.5 million rows) go up increasingly.

I started out with no geography column and it took 6 minutes, then I added a geography column and it took 20 minutes (again same data). Then I added a spatial index and it took 1 hour and 45 minutes.

I'm new at anything spatial, but this seems like really bad performance. Is there anything I can do to help speed this up or is this just the performance I'm going to see when dealing with SQL Server spatial data?

share|improve this question
1  
Did you create the index before or after the load? If before, try doing it after instead. Also, what is your SLA? Is ~30 minutes okay, or do you need it faster than that? –  Jon Seigel Jun 13 '13 at 16:33
    
I'm not sure what SLA stands for? The faster the better. –  webdad3 Jun 13 '13 at 16:37
    
Service level agreement. Obviously faster is always better, but what is the maximum amount of time it can take? You need to consider that you're not going to be able to insert 1.5 million rows in a minute, for example. –  Aaron Bertrand Jun 13 '13 at 16:39
2  
To Jon's point - it is often more beneficial to perform bulk loads into a table without any additional indexes and then apply the indexes afterward. It would also be useful to know exactly how you are structuring your inserts - are there 1.5 million individual INSERT INTO statements? A bunch of INSERT VALUES(1),...,(1000) statements? BULK INSERT / SqlBulkCopy? TVPs? Something else? –  Aaron Bertrand Jun 13 '13 at 16:46
1  
One row at a time? That isn't going to be fast no matter what you do. –  Aaron Bertrand Jun 13 '13 at 16:55
show 12 more comments

1 Answer

up vote 2 down vote accepted

After doing some research, including looking at this question, it appears it isn't directly possible to efficiently bulk load any of the CLR-based types, including geography. 1

You said that adding just the geography column added a significant amount of time to the load process -- this may, in fact, be entirely reasonable, depending on the amount of data that's going into that column.

The most concerning thing is that it's taking so long to load the data without the geography column involved.

When doing large data loads, maximizing throughput is the most important part of optimizing the process, particularly if the client application and database are separated by a network connection. (By the way, I'm going to assume that using a client application to load the data is the best architectural decision for the problem here.)

To maximize throughput means minimizing other types of overhead, such as data preparation on the client, and network roundtrips between servers.

Based on the comments, you have some type of loop happening that reads a row from the source and writes it to the target immediately. Something like this (pseudo-code):

while (more rows to read from source)
{
    read one row from source
    write one row to target
}

This is inefficient primarily because it maximizes network roundtrips and small chunks of I/O on the target, which are both expensive in aggregate.

Probably the easiest way to modify your code to fix this issue is to introduce a delayed-write scheme, where a chunk of rows are read from the source, and flushed to the target all at once. Something like this:

while (more rows to read from source)
{
    read 1..batchSize rows from source
    write 1..batchSize rows to target
}

Even using INSERT ... VALUES with multiple rows at a time (note: there is a 1000-row limit per statement) will significantly improve performance. The test harness I constructed saw a 43x improvement between a batch size of 1 and a batch size of 1,000. 2

A note about parameterization. While this is a best-practice when it comes to application security, for a batch loading process, parameterized statements can cripple performance because of the associated processing time. If you start batching things together, it's still possible to parameterize the whole statement, but the performance gets worse and worse the more parameters you use (there's a limit of something like 2,000 parameters anyway). Assuming the data source values are "safe," I would actually recommend doing your own escaping (replace ' with '') for the string columns and concatenate all values directly into the SQL text as literals. By the way, if you're using .NET, make sure you use a StringBuilder to concatenate strings.

About how to move the geography column around efficiently. Simply batching the INSERTs may be enough. If not, it may be advantageous to attempt loading (or bulk loading) the text definition of the column (I'm assuming you're given a text list of points or something from the data sources) along with the rest of the data into a staging table, then populate the target by doing an INSERT/SELECT to convert the text definition into the CLR type. (Note: I haven't tested this.)

Either way, if you haven't reached network saturation after doing that, and depending on how fast you can read from the data source vs. write to the target, the code could be refactored into a producer/consumer architecture where reads and writes happen asynchronously, and possibly multithreaded on either or both sides. The details of how to do this are out of scope for this site, but I figured it should be mentioned. I don't think you'll have to go to this length to get acceptable performance.

Finally, for completeness of this answer, in terms of indexes, for a one-time load, it's advantageous to create nonclustered indexes after all the data has been loaded into the table. This reduces the number of internal index inserts, much in the same way that batching INSERT statements reduces network roundtrips. It's probably best to create the clustered index, however, before loading, so there isn't a need to rewrite the whole table to give it order (assuming the clustering key is ever-increasing).


1 Well, probably. If you extract Microsoft.SqlServer.Types from the resource database and reference it in an assembly, it should be possible; however, this is wildly unsupported and will probably break the process horribly if Microsoft updates it between versions.

2 This was a single-threaded test inserting constant values of integers and strings, not selecting from a data source; 1,500,000 rows at 201 bytes/row; using INSERT ... VALUES took 771,971ms for a batch size of 1, and 18,120ms for a batch size of 1,000. I also tested the same data with SqlBulkCopy for comparison, and it took 16,576ms for a batch size of 1,000, and 13,829ms for a batch size of 10,000 (this batch size saturated throughput). You can download the source from here -- add the program to a console application; I used .NET 4.0.

share|improve this answer
    
You mentioned this delayed batching process... Are you saying loop through as I'm doing but just build a large insert string and then execute every 1000 rows? Can you eloborate on that some? –  webdad3 Jun 17 '13 at 13:06
    
Do you know what happens if this is a multi-threaded process? –  webdad3 Jun 17 '13 at 13:26
    
@Jeff: I'll post my test harness tonight when I get home, but yes, that's how I did it. Not sure why I didn't do that originally. I didn't try multi-threading the application because I have no idea where the bottleneck is on your system. I can tune the app for my system, but not for yours. –  Jon Seigel Jun 17 '13 at 14:11
1  
@oldmanprogrammer: I added a link to the test harness source code. –  Jon Seigel Jun 17 '13 at 22:12
    
it won't let me log in to the FTP site. –  webdad3 Jun 19 '13 at 18:54
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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