Exists almost always outperforms IN with the same result set. A loop with individual selects will almost always suck n-1 times more than IN or Exists (where n = number of loops).
Adam On Mon, Feb 22, 2010 at 5:18 PM, Matt C <[email protected]> wrote: > Ahh sorry for the lack of information. It's a MySQL database. Here's > an example of what I'm trying to accomplish. Let's say a user has a > given list, and they want to know which of their items are present in > my database. I want my CF page to receive a list as a parameter, > check which items are in the database, and return a list of the valid > items (along with some additional info). It would then be up to the > caller to parse out the returned list to determine which of the items > in its own list are present in my table. I figured this would be far > more efficient than having the caller run my CF page for every item > individually. > > - Caller sends list ('A','B','C','D') > - Page queries for any records matching that list > - Returns "results=A:123,C:789" > > It sounds like I'll just have to try some good ole fashioned speed > tests. Thanks for the quick replies. > > > On Feb 22, 4:44 pm, Alan Holden <[email protected]> wrote: > > What's the database? > > > > While most db's will not allow multiple selects in simple jdbc requests > > (i.e. CFQUERY tag), some will allow multiple selects (and return > > multiple result sets, way fast) when compiled into a stored procedure. > > This raises the possibility of one humongous SP into which you pass ~100 > > parameters. > > > > It's hard without knowing your specific app, but there are times when > > you need to gauge which technology is best suited to perform the work. > > In my case: I know ColdFusion better than anything else, which means > > that I may use it to solve a problem - that the database could have > > handled far more efficiently given a good SP. I can write them, It's > > just not fun for me; so the application suffers for purely human reasons. > > > > Sorry for not specifically answering your question. Perhaps some debug > > output or verbose logging on the db side could answer your connection > > theory? > > > > Al Holden > > -- > Open BlueDragon Public Mailing List > http://www.openbluedragon.org/ http://twitter.com/OpenBlueDragon > mailing list - http://groups.google.com/group/openbd?hl=en > > !! save a network - please trim replies before posting !! > -- Open BlueDragon Public Mailing List http://www.openbluedragon.org/ http://twitter.com/OpenBlueDragon mailing list - http://groups.google.com/group/openbd?hl=en !! save a network - please trim replies before posting !!
