Calling SQL select from C # is thousands of times and takes a lot of time. Is there a better way?

Question

I get a list of IDs and amounts from an excel file (thousands of identifiers and corresponding amounts). Then I need to check the database to see if each identifier exists, and if it checks that the amount in the database is greater than or equal to the sum of the amount from the excel file.

The problem is that this select statement works more than 6,000 times, and the return values ​​are time consuming. Even for 1/2 second, a piece will only take about an hour to make all the samples. (I usually don't get more than 5 max results back)

Is there a faster way to do this?

Is it possible to somehow transfer all IDs at once and just make 1 call and get a massive collection?

I tried using SqlDataReaders and SqlDataAdapters, but they seem to be pretty much the same (too long anyway)

A general idea of ​​how this works below

for (int i = 0; i < ID.Count; i++) { SqlCommand cmd = new SqlCommand("select Amount, Client, Pallet from table where ID = @ID and Amount > 0;", sqlCon); cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = ID[i]; SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dataTable); da.Dispose(); } 

Answer 1

Combine all identifiers together into one big IN clause so it reads like this:

 select Amount, Client, Pallet from table where ID in (1,3,5,7,9,11) and Amount > 0; 

Answer 2

Instead of a long in list (it’s hard to parameterize and has a number of other inefficiencies of execution plans: compilation time, reusing the plan and the plans themselves), you can transfer all the values ​​at once through the table, the evaluated parameter.

See arrays and lists in SQL Server for more information .

Typically, I should provide the table type with a primary key and use option (recompile) to get the most suitable execution plans.

Answer 3

"I tried using SqlDataReaders and SqlDataAdapters"

It looks like you may be open to other APIs. Using Linq2SQL or Linq2Entities:

 var someListIds = new List<int> { 1,5,6,7 }; //imagine you load this from where ever db.MyTable.Where( mt => someListIds.Contains(mt.ID) ); 

This is safe in terms of preventing potential SQL injection vulnerabilities and will generate an in clause. Note, however, that the size of someListIds can be so large that the generated SQL query exceeds the query length limits, but the same applies to any other technique that includes an IN clause. You can easily get around this by breaking the lists into large chunks and still be much better than requesting an ID.

Answer 4

Use Table Parameters

With their help, you can pass C # datatable with your values ​​to the stored procedure as a result set / table that you can join to and make simple:

 SELECT * FROM YourTable WHERE NOT EXISTS (SELECT * FORM InputResultSet WHERE YourConditions) 

Answer 5

Use the in operator. Your problem is very common and it has a name: N + 1 performance issue

Where do you get the identifiers from? If it is from another request, then consider grouping them into one.

Answer 6

Instead of doing a separate request for each identifier that you have, run one query to get the number of each individual identifier you want to check (or if you have too many identifiers to enter one request, and then package them in a batch in a few thousand).

Answer 7

Import data directly to SQL Server. Use the stored procedure to display the data you need.

If you must use it at the application level ... use xml datatype to go into the stored procedure.

Answer 8

You can import data from an excel file into a SQL server as a table (using the data import wizard). Then you can execute one query on the SQL server, where you attach this table to your lookup table by joining the ID field. There are a few more steps to this process, but it is much faster than trying to combine all the identifiers into a much longer request.

I take a certain amount of access privileges to the server, but this is what I would do if I had access, which I usually have. I also assume that this is one task. If not, then importing data to the SQL server can also be done programmatically.

Answer 9

Sentence

IN has limitations, so if you go with this approach, make sure the batch size is used to handle the number of X identifiers at a time, otherwise you will run into another problem.

A @Robertharvey noted that if there are no identifiers and no transactions, then simply pull all the identifiers into memory in a dictionary similar to an object and process them there. Six thousand values ​​are not many, and one choice can return all this in a few seconds.

Just remember that if another process updates the data, your local cached version may be outdated.

Answer 10

There is another way to handle this: creating XML identifiers and passing it to the procedure. Here is the code for the procedure.

 IF OBJECT_ID('GetDataFromDatabase') IS NOT NULL BEGIN DROP PROCEDURE GetDataFromDatabase END GO --Definition CREATE PROCEDURE GetDataFromDatabase @xmlData XML AS BEGIN DECLARE @DocHandle INT DECLARE @idList Table (id INT) EXEC SP_XML_PREPAREDOCUMENT @DocHandle OUTPUT, @xmlData; INSERT INTO @idList (id) SELECT x.id FROM OPENXML(@DocHandle, '//data', 2) WITH ([id] INT) x EXEC SP_XML_removeDOCUMENT @DocHandle ; --SELECT * FROM @idList SELECT t.Amount, t.Client, t.Pallet FROM yourTable t INNER JOIN @idList x ON t.id = x.id and t.Amount > 0; END GO --Uses EXEC GetDataFromDatabase @xmlData = '<root><data><id>1</id></data><data><id>2</id></data></root>' 

You can put any logic in the procedure. You can pass the identifier, the amount also through XML. You can pass a huge list of identifiers through XML.

Answer 11

You can select the entire result set (or join the set of "limited" result sets) and save it all to a DataTable . Then you can make selections and updates (if necessary) directly on the datatable. Then connect the new data back ... Not a super-efficient memory, but often it is a very good (and only) solution when working in bulk and you need it very quickly. Therefore, if you have thousands of records, it may take several minutes to fill out all the records in the DataTable.

then you can search your table as follows:

 string findMatch = "id = value"; DataRow[] rowsFound = dataTable.Select(findMatch); 

Then just a foreach (DataRow dr in rowsFound)

Answer 12

SqlDataAdapter objects are too heavy for this. First, using stored procedures will be faster. Secondly, use the group operation for this pass as a parameter for the list of identifiers on the side of the database, query for these parameters and return the processed result. It will be fast and efficient, since all the data processing logic is on the server side of the database.