alternatives to using IN clause

Question

I am running the below query:

SELECT 
    ReceiptVoucherId, 
    VoucherId, 
    ReceiptId,
    rvtransactionAmount, 
    AmountUsed, 
    TransactionTypeId
FROM 
    [Scratch].[dbo].[LoyaltyVoucherTransactionDetails]
WHERE       
    VoucherId IN 
    (2000723,
    2000738,
    2000774,
    2000873,
    2000888,
    2000924,
    2001023,
    2001038,
    2001074,
    2001173)

the aim being to extract the ReceiptVoucherId / VoucherId / ReceiptId / rvtransactionAmount / AmountUsed / TransactionTypeId data for the list of voucherId's that I have.

My problem here is that my list of VoucherID's is 187k long so an IN clause is not possible as it returns the error:

Internal error: An expression services limit has been reached

Can anyone advise on a alternative to doing it this way?

I am using SSMS 2014

If those voucher Ids are present somewhere else, you could use a sub query as well instead of the actual values. But having that stored in a table like @GiorgosBetsos suggested would be a much cleaner approach. — Riaan van Zyl, Jul 04 '16 at 14:48
Where do these 187K values come *from*? They're surely not a fixed list entered by hand? And where does the result data *go*? It's far too much for someone to directly consume these results. It feels like you've maybe broken up what should be a *single* logical query into multiple procedural steps, and this is one of the intermediate steps. If we could understand the entire end-to-end requirements, we may be able to offer a better overall solution. — Damien_The_Unbeliever, Jul 04 '16 at 14:52

score 4 · Answer 1 · answered Jul 04 '16 at 15:05

You can try the approach:

select from mytable where id in (select id from othertable)

or left join:

select from othertable left join mytable using id

not sure what has better performance, also second query could give you empty rows if it is not declared as foreign key.

fly-by-post, feel free to improve it.

score 2 · Accepted Answer · answered Jul 04 '16 at 14:48

2

Just create a table containing all this Vouchers (Hopefully you already have one) and then use IN() selecting from the table :

SELECT 
    ReceiptVoucherId, 
    VoucherId, 
    ReceiptId,
    rvtransactionAmount, 
    AmountUsed, 
    TransactionTypeId
FROM 
    [Scratch].[dbo].[LoyaltyVoucherTransactionDetails]
WHERE       
    VoucherId IN (SELECT VoucherId FROM VourchersTable)

answered Jul 04 '16 at 14:48

sagi

40,026
6
59
84

Depending on the privileges, the creation of a "real" table is not that easy... I guess the tab variable or the cte might be a bit easier, no? – Tyron78 Jul 04 '16 at 14:51
If he doesn't have the privileges, he can just ask his DBA. `CTE` is not a bad idea, but it is for 190k records – sagi Jul 04 '16 at 14:54
haha U R right... but in a Company I used to work for several years ago, the DBA would happily flip the finger at you and tell you to f*** off. ;-) They can get really nasty if it Comes to privileges. :-) BUT you are right - IF you have to analyze something, you should have the privileges. Concerning the number of records: I recently used a CTE with something like 300K and it worked quite fine... but I used a Script in order to spool the query for me. ;-) – Tyron78 Jul 04 '16 at 14:59

score 1 · Answer 3 · answered Jul 04 '16 at 14:57

insert the vouchers to lookup in a seperate table . lets call it Voucher.

Then this query should do the trick. It does not use the IN Clause. but instead it uses Inner join which will be faster.

SELECT 
    L.ReceiptVoucherId, 
    L.VoucherId, 
    L.ReceiptId,
    L.rvtransactionAmount, 
    L.AmountUsed, 
    L.TransactionTypeId
FROM 
    [Scratch].[dbo].[LoyaltyVoucherTransactionDetails] L
INNER JOIN dbo.Vouchers V ON L.VoucherId = V.VoucherId

score 0 · Answer 4 · answered Jul 04 '16 at 14:50

0

Maybe the following works for you:

First of all, declare a variable of type table (or alternatively a temp table) and insert your IDs into it. Modify your Query to

WHERE VoucherID in (SELECT VoucherID FROM @t)

Alternatively (but similar write-intensive for your Hands ;-) ) is the creation of a CTE:

WITH cte AS (SELECT 2000723 UNION ALL SELECT ...)

and again the redesign of your "WHERE... IN..." section.

answered Jul 04 '16 at 14:50

Tyron78

4,117
2
17
32

The creation of a CTE with 187k records? Good luck . – sagi Jul 04 '16 at 14:54

alternatives to using IN clause

4 Answers4