SQL Server Performance Forum – Threads Archive

Constants in Subquery — Efficiency

Sometimes I have to query a table using a list of constants. In some performance testing I did, the aesthetically pleasing use of a subquery as below turned out to be about 10X much more costly: Select col1, col2, col3 from gl where project in
(‘168795’, ‘168796’, ‘168890’, ‘168977’, ‘169207’, ‘169210’, ‘169317’,
‘169340’); than this ugly non-subquery version: Select col1, col2, col3 from gl where (
project = ‘168792’
project = ‘168795’
project = ‘168796’
project = ‘168890’
project = ‘168977’
project = ‘169207’
project = ‘169210’
project = ‘169317’
project = ‘169340’
); I don’t understand why? Can anyone shed some light? Thanks, Bob Shepherd

---

That query would fail. Let’s assume you added the OR keyword in your actual query. But how did you determine that the IN clause would be "about 10X much more costly"? How do the execution times compare?

---

Yes, I forgot the "or"s in my haste to post. In any case, never mind. As it turns out this has something to do with the fact that the field I’m searching is padded with spaces…I can make both queries perform the same way when I don’t strip the padding from my query.

---

Makes sense. Except for having numeric key values in a CHAR field – if you’re still in the design stages, consider using an appropriate numeric data type instead for improved performance.

---

]]>