From: David Vallner <david@...>
Date: 2006-12-22T06:02:22+09:00
Subject: Re: string of strings...

--------------enig8B737F04AD777652669F493C
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Robert Klemme wrote:
> On 21.12.2006 01:14, David Vallner wrote:
>> Robert Klemme wrote:
>>> A completely different option is to create a temp table (depends on y=
our
>>> DB whether and how that is done), put all the values in that temp tab=
le
>>> and join it against the table you are querying.
>>
>> Talk about serendipity... I was looking for a way to avoid having to d=
o
>> either any sort of query string building (which I hate) or multiple
>> SELECTS (for obvious reasons) for a WHERE ... IN situation for work
>> stuff, and this one sounds quite nifty. Would the performance of that =
be
>> comparable to using a WHERE ... IN?
>=20
> Depends on the number of items in the temp table and probably also on
> indexing.  My guess would be that it's pretty fast for some hundred
> entries.  Note that not all RDBMS allow to have indexes on temp tables.=

>  MS SQL Server also has something called "table variable" which might
> give different results.  Lots of options... :-)
>=20
> Kind regards
>=20
>     robert
>=20

I don't really need an index, it's a throwaway temp table. I just want
to avoid either handcoding parameter escaping or making a parameterised
query using "WHERE foo IN (?,?,?, [...] ?)"  and setting those I have an
unrational fear of looping over integers; argh pure JDBC apps, yay lack
of common sense and using an API designed to do the lowest level of
abstraction for application programming. The query is rather small,
probably in the order of tens of items at best, so a batch insert into
the temp table and then the join shouldn't hurt performance over the
hackish method and still end up better than doing selects one by one
because of the reduced lag. Although my hunch is what's killing that one
is that it involves basically a join on (unindexed) varchar columns, so
I don't expect miracles ;P

Now, if only this weren't Oracle 8i that only supports that weird
"predefined table for temporary data" concept instead of true
transaction-scoped temp tables... *sigh*

David Vallner


--------------enig8B737F04AD777652669F493C
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (MingW32)

iD8DBQFFivZYy6MhrS8astoRAttVAJ40UyH9STtOH47kiZidCG4wd/wGdQCfYr/1
/0mw1TJtPjQm+l2j+I2NxQk=
=KFfc
-----END PGP SIGNATURE-----

--------------enig8B737F04AD777652669F493C--