asked on

java preparedstatement with in clause for lists - SQL server

Hi Experts,

I'm doing a SELECT with a fairly large IN clause. I've seen comments about batching where people tell you to use something like batch sizes of 51, 11, 4, then 1. I know you can't bind a '?' to a list of items, but do I need to create 4 preparedstatements and have the respective number of '?' in my list to properly take advantage of the speed up of a prepared statement, or is it enough to build the list myself without the use of '?' ?

Thanks!
Mike

CEHJ

Yes, afaik you can't get optimization with a dynamic list. It would have to be static. IOW, you might as well use an ordinary Statement with a format string

thready

ASKER

Much better to have 4 preparedstatements no?

CEHJ

Yes. afaik the contents of the list in each case would have to be literals. But i could be wrong ;)

ASKER CERTIFIED SOLUTION

mccarl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CEHJ

Thanks, mccarl for that explanation. I've found in the past that information on this issue is hard to come by. I'm sure you're correct but if you could cite some authoritative sources (say the docs of a major RDBMS) that would be great.

thready

ASKER

Awesome answer mccarl, thank you very much for that! I too would love to have something to back up this stuff so I could prove my arguments to my boss!

mccarl

You're welcome. I guess something like the following explains SQL Servers functionality (I'll admit that I am coming more from an Oracle perspective, but it appears to work in a similar fashion)

https://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx

CEHJ

Great, thanks. And great documentation too. Here's a little gotcha that it would be easy to fall foul of:

The algorithms to match new SQL statements to existing, unused execution plans in the cache require that all object references be fully qualified. For example, the first of these SELECT statements is not matched with an existing plan, and the second is matched:

thready

ASKER

Awesome help! Thank you again! Very much appreciated.

Mike

thready

ASKER

And CEHJ - good point there, but I can't wrap my head around the example they used... So I doubt I'm understanding what this caveat is...

SELECT * FROM Person;

SELECT * FROM Person.Person;

Would you mind explaining this ?

Thanks!
Mike

CEHJ

Would you mind explaining this ?

They are simply saying that only the second statement will be cacheable as it's the only one fully-qualified in object naming terms. Let's hope that doesn't apply to other RDBMSs that i'm more likely to use ;) It's enough to make anyone paranoid enough to use fq-naming all the time

thready

ASKER

Ok I did understand correctly. That's insane!

I can't believe they would transform a query before they store its hash in a map (or whatever they're doing)...

CEHJ

Well i suppose the 'cache' could be global to the RDBMS, meaning that the first form could apply to several databases and hence would not be usable as a key