Most likely, you will be better off with idea # 2 (i.e. sending all 15-20 objects to the database in one shot using several TVPs and processing a whole set of up to 2000 messages).
Finding application-level caching master data and translating it before sending it to the database sounds great, but misses something:
- You will have to hit the DB to get the original list anyway
- You will have to hit the database to insert new records anyway
- Finding values in a dictionary to replace with identifiers is exactly what the database does (suppose there is a non-clustered pointer for each of these search queries by name)
- Often requested values will be cached in the buffer pool (which is the memory cache).
Why duplicate at the application level what is already provided for and is happening right now at the database level, especially considering:
- 15 to 20 objects can have up to 20 thousand records (which is a relatively small number, especially considering that for a non-clustered index only two fields are required:
Name and ID , which can pack many rows on a single data page when using 100% - fill factor). - Not all 20k entries are “active” or “current”, so you don’t have to worry about caching all of them. Thus, any current values will be easily identified as those requested, and those data pages (which may include some inactive records, but not many there) will be those that will be cached in the buffer pool.
Therefore, you don’t have to worry about aging old records or forcing keys to expire or reload due to a possible change in values (i.e. an updated Name for a specific ID ), as this is handled naturally.
Yes, in-memory caching is a wonderful technology and significantly speeds up work with websites, but these scenarios / use cases are when processes without a database request the same data again and again in pure read-only mode. But this particular scenario is one in which data is combined, and the list of search values can change frequently (moreso because of new records than because of updated records).
With everything said, Option No. 2 is the way to go. I have done this technique several times with great success, although not with 15 TVPs. Perhaps some optimizations / adjustments need to be configured to tune this particular situation, but what I found to work fine:
- Receive data via TVP. I prefer this over
SqlBulkCopy because:- it makes for an easily autonomous stored procedure
- it fits very well in the application code to completely flush the collection into the database without having to copy the collection to the
DataTable first, which duplicates the collection that wastes CPU and memory.This requires you to create a method for each collection that returns IEnumerable<SqlDataRecord> , takes the collection as input and uses yield return; to send each entry in a for or foreach .
- TVPs are not very good for statistics and, therefore, are not suitable for JOINing (although this can be mitigated using
TOP (@RecordCount) in queries), but you do not need to worry about this, since they are only used to populate real tables with any missing values Step 1: Insert the missing names for each object. Remember that for each object there must be a NonClustered index in the [Name] field, and assuming the identifier is a clustered index, this value will naturally be part of the index, so [Name] will provide a coverage index in addition to help in next operation. And also remember that any previous executions for this client (i.e., approximately the same entity values) will cause data pages for these indexes to remain cached in the buffer pool (i.e., in memory).
;WITH cte AS ( SELECT DISTINCT tmp.[Name] FROM @EntityNumeroUno tmp ) INSERT INTO EntityNumeroUno ([Name]) SELECT cte.[Name] FROM cte WHERE NOT EXISTS( SELECT * FROM EntityNumeroUno tab WHERE tab.[Name] = cte.[Name] )
Step 2: INSERT all the “messages” into a simple INSERT...SELECT , where the data pages for lookup tables (ie, “entities”) are already cached in the buffer pool because of step 1
Finally, keep in mind that hypotheses / assumptions / educated guesses do not replace testing. You need to try several methods to see what works best for your specific situation, as there may be additional details that have not been separated that may affect what is considered “ideal” here.
I will say that if only inserts are inserted, then Vlad’s idea can be faster. The method that I describe here I used in situations that were more complex and required complete synchronization (updates and deletions), as well as additional checks and the creation of relevant operational data (not search values). Using SqlBulkCopy can be faster on direct inserts (although for only 2000 entries I doubt there is a big difference, if any), but this assumes that you load directly into the destination tables (messages and search queries), and not into the intermediate / intermediate tables (and I believe that Vlad’s idea is SqlBulkCopy directly in the destination tables). However, as stated above, using an external cache (i.e., not a buffer pool) is also more error prone due to the problem of updating search values. This can lead to more code than the external cache is invalidated, especially if using the external cache is only slightly faster. You must consider the additional risk / service in which the method as a whole is best suited to your needs.
UPDATE
Based on the information provided in the comments, we now know:
- There are several suppliers
- There are several products offered by each supplier.
- Products are not unique to the Supplier; Products are sold by 1 or more suppliers.
- Product properties are the only ones
- Price information has properties that can have multiple entries.
- Pricing information is INSERT only (i.e. time history)
- Unique product defined by SKU (or similar field)
- After creating a product that comes with an existing SKU, but with other properties (for example, category, manufacturer, etc.), it will be considered the same product; differences will be ignored.
With all of this in mind, I still recommend TVP, but rethink the approach and make it vendor-oriented rather than product-oriented. The provider is supposed to send files each time. So when you get the file, import it. The only search you do well in advance is the provider. Here is the basic layout:
- It seems reasonable to assume that you already have VendorID at the moment, because why should the system import the file from an unknown source?
- You can import into packages
- Create a
SendRows method that:- accepts a filestream or something that allows you to move through a file
- accepts something like
int BatchSize - returns
IEnumerable<SqlDataRecord> - creates an
SqlDataRecord to match the TVP structure - for loops though FileStream until BatchSize or more entries in the file are encountered
- perform any necessary data checks
- match data with
SqlDataRecord - call
yield return;
- Open file
- While the file has data
- stored procedure call
- go to VendorID
- go to
SendRows(FileStream, BatchSize) for TVP
- Close the file
- Experiment with:
- opening SqlConnection before looping around FileStream and closing it after looping
- Opening SqlConnection, executing a stored procedure, and closing SqlConnection inside a FileStream loop
- Experiment with different BatchSize values. Start with 100, then 200, 500, etc.
- The stored procedure will process the insertion of new products
Using this type of structure, you will send product properties that are not used (i.e. only SKUs are used to search for existing products). BUT, it scales very well, as there are no restrictions on file size. If the Seller ships 50 Products, a penalty. If they send 50 thousand Products, a fine. If they send 4 million products (this is the system I was working on, and it was processing product information updates that were different for any of its features!), Then that's fine. There is no increase in memory at the application level or database level for processing even 10 million products. The time spent on imports should increase with the number of products shipped.
UPDATE 2
New source data information:
- comes from an Azure EventHub
- comes as C # objects (no files)
- Product details are part of the OP system API
- going in a separate queue (just insert the data from the insert into the database)
If the data source is C # objects, then I would most definitely use TVP, since you can send it through the method described in my first update (i.e. the method that returns IEnumerable<SqlDataRecord> ). Send one or more TVPs for price / offer for each supplier information, but regularly enter parameters for specific property attributes. For instance:
CREATE PROCEDURE dbo.ImportProduct ( @SKU VARCHAR(50), @ProductName NVARCHAR(100), @Manufacturer NVARCHAR(100), @Category NVARCHAR(300), @VendorPrices dbo.VendorPrices READONLY, @DiscountCoupons dbo.DiscountCoupons READONLY ) SET NOCOUNT ON;