How to avoid large in clause?

Question

I have 3 tables :

table_product (30 000 row)
---------
ID
label

_

table_period (225 000 row)
---------
ID 
date_start
date_end
default_price
FK_ID_product

and

table_special_offer (10 000 row)
-----
ID
label
date_start,
date_end,
special_offer_price
FK_ID_period

So I need to load data from all these table, so here it's what I do : 1/ load data from "table_product" like this

select *
from table_product
where label like 'gun%'

2/ load data from "table_period" like this

select *
from table_period
where FK_ID_product IN(list of all the ids selected in the 1)

3/ load data from "table_special_offer" like this

select *
from table_special_offer
where FK_ID_period IN(list of all the ids selected in the 2)

As you may think the IN clause in the point 3 can be very very big (like 75 000 big), so I got a lot of chance of getting either a timeout or something like " An expression services limit has been reached".

Have you ever had something like this, and how did you manage to avoid it ?

PS : the context : SQL server 2005, .net 2.0 (please don't tell me my design is bad, or I shouldn't do "select *", I just simplified my problem so it is a little bit simpler than 500 pages describing my business).

Thanks.

Answer 1

In finally have my answer : table variable (a bit like @smirkingman's solution but not with cte) so:

declare @product(id int primary key,label nvarchar(max))
declare @period(id int primary key,date_start datetime,date_end datetime,defaultprice real)
declare @special_offer(id int,date_start datetime,date_end datetime,special_offer_price real)

insert into @product
select * 
from table_product
where label like 'gun%'

insert into @period
select * 
from table_period
where exists(
select * from @product p where p.id = table_period.FK_id_product
)

insert into @special_offer
select * 
from table_special_offer
where exists(
select * from @period p where p.id = table_special_offer.fk_id_period
)

select * from @product
select * from @period
select * from @special_offer

this is for the sql, and with c# I use ExecuteReader, Read, and NextResult of the class sqldatareader

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx

I got all I want : - my datas - i don't have too much data (unlike the solutions with join) - i don't execute twice the same query (like solution with subquery) - i don't have to change my mapping code (1row = 1 business object)

Answer 2

I'd be interested to know if this might make an improvement:

WITH products(prdid) AS (
    SELECT
        ID
    FROM
        table_product
    WHERE
        label like 'gun%'
),
periods(perid) AS (
    SELECT
        ID
    FROM
        table_period
        INNER JOIN products
            ON id = prdid
),
offers(offid) AS (
    SELECT
        ID
    FROM    
        table_special_offer
        INNER JOIN periods
            ON id = perid
)

... just a suggestion...

Answer 3

Don't use explicit list of values in IN clause. Instead, write your query like

... FK_ID_product IN (select ID
from table_product
where label like 'gun%')

Answer 4

A JOIN gives you the same results.

SELECT  so.Col1
        , so.Col2
FROM    table_product pt
        INNER JOIN table_period pd ON pd.FK_ID_product = pt.ID_product
        INNER JOIN table_special_offer so ON so.FK_ID_Period = pd.ID_Period
WHERE   pt.lable LIKE 'gun%'

Answer 5

First some code...

Using JOIN:

SELECT 
  table_product.* --'Explicit table calls just for organisation sake'
, table_period.*
, table_special_offer.*
    FROM 
        table_product
        INNER JOIN table_period
            ON table_product.ID = table_period.FK_ID_product
        INNER JOIN table_special_offer
            ON table_period.ID  = table_special_offer.FK_ID_period
    WHERE 
        tp.label like 'gun%'" 

Using IN :

SELECT 
    * 
FROM 
    table_special_offer 
WHERE FK_ID_period IN 
    (
    SELECT 
        FK_ID_period 
    FROM 
        table_period 
    WHERE FK_ID_product IN
        (
        SELECT 
            FK_ID_product 
        FROM 
            table_product 
        WHERE label like '%gun'
        ) AS ProductSub
    ) AS PeriodSub

Depending on how well your tables get indexed both can be used. Inner Joins as the others have suggested are definitely efficient at doing your query and returning all data for the 3 tables. If you are only needing To use the ID's from table_product and table_period Then using the nested "IN" statements can be good for adapting search criteria on indexed tables (Using IN can be ok if the criteria used are integers like I assume your FK_ID_product is).

An important thing to remember is every database and relational table setup is going to act differently, you wont have the same optimised results in one db to another. Try ALL the possibilities at hand and use the one that is best for you. The query analyser can be incredibly useful in times like these when you need to check performance.

I had this situation when we were trying to join up customer accounts to their appropriate addresses via an ID join and a linked table based condition (we had another table which showed customers with certain equipment which we had to do a string search on.) Strangely enough it was quicker for us to use both methods in the one query:

--The query with the WHERE Desc LIKE '%Equipment%' was "joined" to the client table using the IN clause and then this was joined onto the addresses table:

SELECT 
    Address.*
,   Customers_Filtered.*
FROM
    Address AS Address
INNER JOIN
    (SELECT Customers.* FROM Customers WHERE ID IN (SELECT CustomerID FROM Equipment WHERE Desc LIKE '%Equipment search here%') AS Equipment ) AS Customers_Filtered
ON Address.CustomerID = Customers_Filtered.ID

This style of query (I apologise if my syntax isn't exactly correct) ended up being more efficient and easier to organise after the overall query got more complicated.

Hope this has helped - Follow @AdaTheDev 's article link, definitely a good resource.

Answer 6

SELECT * 
    FROM 
        table_product tp
        INNER JOIN table_period tper
            ON tp.ID = tper.FK_ID_product
        INNER JOIN table_special_offer so
            ON tper.ID  = so.FK_ID_period
    WHERE 
        tp.label like 'gun%'" 

Answer 7

Switch to using joins:

SELECT <FieldList>
FROM Table_Product prod
    JOIN Table_Period per ON prod.Id = per.FK_ID_Product
    JOIN Table_Special_Offer spec ON per.ID = spec.FK_ID_Period
WHERE prod.label LIKE 'gun%'

Something you should be aware of is the difference of IN vs JOIN vs EXISTS - great article here.