How to achieve high performance management of a huge list of distinct strings [closed]

Question

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.

Answer 1

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.