CODEWITHSUNDEEP
sqlsql-server
grzes124
grzes124

Reputation: 33

Large number of params in where clause

I have some web application written in c#, mvc and mssql.
There is some form on which user can choose some products.
There are two grids in the form. One grid shows products that user can select for future processing. Other grid shows products already selected. This work like this:
1. User select products from first grid by checkbox
2. Then he click button 'add'
3. After this, grids are refreshed. Second grid shows added products, and First grid shows all products without products from second grid.

Currently there is about 50.000 products in database. The problem is with the grid refreshing, when user select too many products to add. Sql for frist grid looks like:

SELECT ProductId, Name, Description, {other columns} 
FROM Products 
WHERE ProductId NOT IN ({ list of selected ProductId to add })

If { list of selected ProductId to add } has to many elemets (i.e 10.000) sql statement executes too long, or even gets timeout.

I got stuck with this, and don't have any idea how to solve that problem. Any help will be greatly appreciated

Upvotes: 2

Views: 228

Answers (5)

grzes124
grzes124

Reputation: 33

Thanks for your ideas and suggestions.

My current solution is to get all porducts from db (filtered if needed) and exclude selected products directly in c# code (not in sql statement).

I did some test, and it's work in my scenario pretty well. i.e. I can add 100.000 products to second grid in time below 2 sec. I also think about caching sql results. It could give even better performance.

If you know any downsides of this solution, please let me now.

Upvotes: 0

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

I would suggest you to send a TVP (table value parameter) back to the server. After that your query could be done like:

SELECT p.ProductId, Name, Description, {other columns} 
FROM Products p
left join @ExceptedProducts ep on p.ProductId=ep.ProductId
WHERE where ep.ProductId is null

This should be the fastest and cleanest way.

Upvotes: 0

Polity
Polity

Reputation: 15130

You state that users can filter out products by name or by a part of that name. There is your anchor. A user is unlikely to exclude 10 000 different product names but more likely to exclude 10 000 products that share a certain attribute (for example brand in the name).

Rather than sending the product id's back and fort, you can use a more general criteria. This will result in a query like:

select * 
from products
where p.name NOT LIKE 'brand1%'
and p.name NOT LIKE 'specific product'

Note that for even better performance, it can be wise to store these criterias in a second table. Now you can build up a query like:

select p.*
from products p
join criteria c
on p.name NOT LIKE c.name

Upvotes: 0

Jodrell
Jodrell

Reputation: 35726

You could store the product filter in another table. This table could temporary or, if you want to remember the selection you could index it in a permanent table.

So your statement would look like

SELECT
              ProductId
            , Name
            , Description
            ...
    FROM 
            Products 
    WHERE
            ProductId NOT IN 
            (
               SELECT Id FROM #ProductFilter
            )

or,

SELECT
              ProductId
            , Name
            , Description
            ...
    FROM 
            Products 
    WHERE
            ProductId NOT IN 
            (
               SELECT 
                             ProductId 
                   FROM 
                           ProductFilter
                   WHERE
                           FilterId = @filterId
            )

The method of how the temporary table would be created, or, how the filter would be inserted into ProductFilter would vary depending on how the filter is passed to the query.

If you are using SQL Server 2008+, you could use a table valued parameter. Then the query would look like this, obviously.

SELECT
              ProductId
            , Name
            , Description
            ...
    FROM 
            Products 
    WHERE
            ProductId NOT IN 
            (
               SELECT Id FROM @ProductFilter
            )

If your question is about performance tuning, then, I would need more information about your Schema, a representative instance of your database and some time to tweak and test some ideas.

However, the idea of passing a massive exclusion filter like this seems quite wrong, I can't believe that the users is manuallly excluding these products one by one. Even if this is the case, I think, an inclusion filter would lead to simpler query using a inner join to the set.

Upvotes: 1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95731

If I understand you correctly, the main problem seems to be that you're not storing the list of selected products anywhere in the database. If you were, you could execute a query something like this.

select product_id
from products
where product_id not in (select product_id from customer_selected_products);

Now, that's not going to perform as well as many queries, because you're going to be returning tens of thousands of rows from products. (You're returning all the product id numbers that haven't been selected yet.) But it executes with reasonable speed here (21ms with 10,000 rows in customer_selected_products.)

Upvotes: 1

Related Questions