I am trying to achieve high performance management of a huge list of distinct strings in SQL Server (but I'm open to suggestions of alternate platforms that might be more performant). My specific use case is this:

  • I have to maintain a master list of approximately 200 million rows of distinct Varchar(150) fields (average 15 characters long). The list must allow high performance substring queries (e.g. "LIKE") for ad-hoc reports. So I decided to make the Varchar itself the clustered primary key, as that is the fastest possible schema for queries. There is no identity column.
  • Every night, my automated process downloads a bunch of "child" lists which must "freshen" the master list by inserting or deleting strings. Each child list contains the latest list of all strings with a certain suffix.
  • Any child list strings not present in the master list are inserted there.
  • Any strings in the master list that match the suffix of the child list, but are not present in the child list, are deleted from the master list.
  • Relatively few of the strings in the lists change each night, probably less than 0.1%.
  • A download of a child list can fail (they are large and subject to communication errors.) When that happens, we don't want to proceed as if the child list was empty, because that would just delete all strings from the master list that match the child suffix. Instead we want to keep the strings in the master list that match the child suffix -- slightly stale data is better than nothing! For the same reason, I cannot just replace the entire master list every night.
  • The child lists are unsorted and can contain duplicates. They are too large to sort and remove duplicates in memory (e.g. over 2 GB compressed), so I use a stored procedure with a table-valued data type to insert the child list into a temporary staging table, with a NOT EXISTS clause to filter out duplicates. The staging table is also clustered on the single Varchar field as its primary key. (The staging table is truncated before each child list is inserted.)
  • I use another stored procedure to INSERT/DELETE from the staging table into the master table. Both tables are in a separate database with Simple recovery model to reduce transaction log burden. In the stored procedure, I use a loop to process the INSERTS and DELETES with a TOP(@ChunkSize) technique that keeps a pointer to the current max string index that I have processed. This was necessary to avoid overwhelming the transaction log with massive INSERTs and DELETEs.

I'll try to give a simplified example of what I mean:

Master list (maintained permanently and indexed for high speed queries):

  1. apple-red
  2. banana-yellow
  3. berry-blue
  4. berry-red
  5. mango-yellow
  6. pear-green

Sample "-red"-suffix Child List (downloaded each night):

  1. apple-red
  2. papaya-red

This "-red" child list is compared with all "-red" strings in the master list. The result is that I would need to keep "apple-red", delete "berry-red", and insert a new "papaya-red". Then the master will be said to be up to date with respect to the "-red" child list.

The process is repeated for each child list, which has a different suffix.

I have a working solution, but in testing on my admittedly slow development database, performance has been abysmal. Just the initial insert into the staging table takes about 1 minute per 1 million rows. A similar amount of time is needed to freshen the master table from there. That wouldn't be so bad (approx 400 minutes per night, and probably faster in my Production database) but the problem is that after I reach about 30 or 40 million rows, the inserts and updates start to really slow down.

So I'm wondering if there are any other tips I can use to make SQL Server faster for this specific use case. For example, I could make the tables nonclustered and use an identity column. I'm sure that would make inserts faster due to less page splits. But I need them both to eventually be unique and indexed because of the INSERT/DELETE freshening operations. So I feel like I would eventually have to pay the same penalty.

Or, do you think I should be looking at MongoDB or some other NoSQL solution? I have never tried NoSQL so I'm totally ignorant about its indexing, set operations, and duplicate culling abilities. I imagine it is highly dependent on the platform you choose, and there are so many to choose from.

P.S. Hopefully this question doesn't get closed as a solicitation of a software recommendation. I think it's a bit broader than that, although if SQL Server is not the right tool for the job, then suggesting a more suitable platform could be an important part of the answer.

closed as too broad by mustaccio, James Anderson, Philᵀᴹ, Julien Vavasseur, Andriy M Jun 24 '16 at 13:53

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • You haven't explained why "the inserts and updates start to really slow down."? – Martin Smith Jun 17 '16 at 20:30
  • @MartinSmith I think it must have been the indexing on the staging table. At 30~40 million rows maybe it stopped being able to fit into cache space? When I took the indexes off, the inserts sped up by a factor of 2 (see comments to Greg's answer) and they didn't seem to slow down much at 30~40 million rows. Probably because they were just spooling out to disk at more or less full speed. But that speed is still not good enough, so I am going to have to test my data set on my Production DB, which has better hardware. – Jordan Rieger Jun 17 '16 at 20:44
  • 2
    In fact the more I look at this, the more I think that we need to see the actual table and index definitions. – RBarryYoung Jun 17 '16 at 20:53
  • @RBarryYoung The table definition for the the staging table, which is currently slow to insert into, is literally just one field as I said in the question -- just the Varchar(150) string field. Full Text search is not relevant here because I am not storing text, but rather abstract strings, and there are no spaces or tokens inside the strings for a Full Text index to make use of (other than the suffix which I already parse off before it gets to the database.) – Jordan Rieger Jun 17 '16 at 21:55
  • 2
    Sorry, there's just too much hand-waving going on here to provide a meaningful answer. In principle, and SQL Server, even a slow one should be to do this in seconds, not minutes. But with the relevant details of the strings structures and the actual content, procedures and processes, there's not much we can tell you. – RBarryYoung Jun 17 '16 at 22:49

Have you considered:

  • Ensuring your database isn't auto-growing. The fact that it is slowing down after 30-40 million rows could be caused by auto-grow.
  • For loading, try bcp in/out instead, and set database to bulk-logged recovery model. Just be aware of the pitfalls with this recovery model, in the case something bad happens to the db during this time.
  • For indexes, consider dropping all the indexes, and re-create them afterwards. Of course, test this first.
  • The table variable - depending on the size - can be notoriously slow. So if it gets too large, you will have perf issues. Depending on what you are doing, have you considered temp table instead? Temp tables maintain stats, and you can create indexes (if needed).

Other than that, not sure what your dev environment looks like, but your IO could be serious bottleneck, it would be interesting to know what disks sec/read and write are, and queue length. Unless the dev environment is a direct replica of your prod environment, I rarely trust performance numbers in a dev environment.

  • I have the database configured to Simple recovery model, which I think is even faster than Bulk Logged. I'm not concerned about disaster recovery as we can get fresh data from the downloads each night. I know it's not auto-growing because I set the initial size to 10 GB and the tables are still under 6 GB total (plus no growth events are present in the trace log.) As for table variables, I'm not using them for anything other than passing the data to the SP that inserts into the staging table (i.e. no filtered queries against the table-valued parameter.) – Jordan Rieger Jun 16 '16 at 21:08
  • I think for my next attempt, I will try inserting without indexes, and then creating the index afterward while filtering duplicates. – Jordan Rieger Jun 16 '16 at 21:08
  • If you are disk bound, I don't think there is much more you can do. Can you tell us your disk Io numbers? – Greg Jun 17 '16 at 5:19
  • Yes, I'm starting to think the same thing. I did the initial staged insert without any indexes, and it still took 50 minutes to insert 130 million rows. The total space used for the table was about 3 GB, meaning it was only working at about 1 MB/s. (But I will try to get more exact numbers from performance counters.) The Dev database I'm using is on an older VM and serves some other applications, so maybe it's heavily I/O bound. I'm going to have to setup a test on my faster Production DB to really tell whether SQL Server will cut it or if I should adopt some NoSQL tool. – Jordan Rieger Jun 17 '16 at 15:02
  • 1
    I am not sure why you would think nosql would solve your issue. – Greg Jun 17 '16 at 15:37

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