IN with many values or temp table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

IN with many values or temp table?

Author

Topic

sth_Weird
Starting Member

38 Posts

Posted - 2015-03-18 : 05:17:21

hi,

I'm using SQLServer 2012. The queries/data is used in my c# programm.

I have a table A and a table B. The relation is 1:many. Table B has a foreign key entry FK_A for table A.
Now I want to select all entries from table B where the FK_A is one of a bunch of values.
Actually that is the corrent SQL query for what I
m trying to achieve:

SELECT * FROM table_B where FK_TableA IN (1,5,10,....).

The trouble is, there may be 1000s of values within the brakets and I wonder if SQLServer 2012 can handle this and if it does, if there may be performance problems?
So I thought of other ways to get the desired result.
I already HAVE the number values that I need fwithin the IN. However, they are nothing more than the result of a query that I used earlier in my programm, so I could simly requery the values in a subquery...
That would be (I know the syntax for STARTS-WITH is wrong I'll have to look it up again):

SELECT * FROM table_B where FK_TableA IN (SELECT PK_TableA FROM table_A WHERE StringColumn STARTS-WITH('MyPrefix')).

Or I could do a join

SELECT * FROM table_B INNER JOIN table_A ON (table_B.FK_TableA = table_A.PK_TableA) WHERE table_A.StringColumn STARTS-WITH('MyPrefix')

My very last idea was to create a temp table containing only the number values and join this table with my table_B. But I guess that is the least efficient way to handle this?

Can somebody give some advice on which way is best (=fastest). I am running the queries against an existing db but there isn't much data in it yet. Right now there don't seem to be mayor performance differences but the data in the db will grow and so may the performance differences...maybe someone here already knows how the different queries perform when you have a lot of data???

thx for your help in advance
sth_Weird

Kristen
Test

22859 Posts

Posted - 2015-03-18 : 08:45:44

[code]
SELECT *
FROM table_B
WHERE FK_TableA IN
(
SELECT PK_TableA
FROM table_A
WHERE StringColumn STARTS-WITH('MyPrefix')
)
[/code]
is fine. Will the inner SELECT statement produce duplicate values for PK_TableA? If so it will be inefficient.
[code]
SELECT *
FROM table_B
WHERE EXISTS
(
SELECT *
FROM table_A
WHERE StringColumn STARTS-WITH('MyPrefix')
AND PK_TableA = FK_TableA
).
[/code]
will be more efficient because the INNER SELECT only needs to find the first matching value to satisfy the EXISTS statment

quote:

Or I could do a join
[code]
SELECT *
FROM table_B
INNER JOIN table_A
ON (table_B.FK_TableA = table_A.PK_TableA)
WHERE table_A.StringColumn STARTS-WITH('MyPrefix')
[/code]

Again, if the inner select / JOIN will have duplicate values then you will get multiple rows on the outer select. You COULD use a DISTINCT to suppress those, but if anyone here uses that they have to come and explain to me why they did so rather than programming around it! DISTINCT, just to get rid of duplicate values, is much less efficient than programming to avoid them in the first place - SQL has to get all the values, sort them, and then remove the duplicates ...

If you don't need any columns from [table_A] in your final SELECT then no need for the JOIN. OTOH if you DO need some columns then JOIN will be the right answer. (That said, I do use JOIN instead of EXISTS where there is only one matching child record and even though I am not going to use any columns from it - i.e. EXIST would do instead. Reason being when I have to debug it I can change the SELECT statement to include columns from the Child table to help me work out what I did wrong!!! Its a lot safer to modify the SELECT for debugging than change the EXIST to a JOIN (and back again when debugging finished)

I wouldn't bother with a TEMP table ... unless you need to use the same list several times within a single block of code, or perhaps if you need to filter it in multiple steps:

Get a bunch of PK_TableA records from table_A into TEMP table
UPDATE TEMP Table based on some other criteria. Maybe DELETE some rows from TEMP table, or INSERT some more ...
etc etc etc
then JOIN that to get the final query.

Kristen
Test

22859 Posts

Posted - 2015-03-18 : 08:50:52

P.S.


WHERE table_A.StringColumn STARTS-WITH('MyPrefix')

needs to become


WHERE table_A.StringColumn LIKE 'MyPrefix' + '%'

It may be more efficient if you append the "%" to the test string before executing the statement, especially if "'MyPrefix" is actually an @Parameter

i.e.


WHERE table_A.StringColumn LIKE @MyParameter + '%'

is likely to be less effiicent than:


SELECT @MyParameter = @MyParameter + '%'
SELECT ... FROM ...
WHERE table_A.StringColumn LIKE @MyParameter

Either way you need an index on the table_A.StringColumn column for a trailing-wildcard test to be efficient (and IF you have an index them using an expression in the WHERE clause may cause the index to be ignored - depends on how complex the expression is, whether the moon is in Aquarius, and a bunch of other mitigating factors, so this is just Very General Advice And Best Practice

sth_Weird
Starting Member

38 Posts

Posted - 2015-03-19 : 03:38:15

thank you for your reply!
Right now I do not need any columns from table_A so no need to join, but you do have a point saying that it might come handy to include some columns when you're debugging...so I guess I'll go with the join :)
sth_Weird

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources