From: Sam Smoot <ssmoot@...>
Date: 2006-12-22T09:35:10+09:00
Subject: Re: string of strings...

Keep in mind that temp-tables in MSSQL are still written to disk in
TempDB. So as a general rule, if you're concerned about performance, if
you don't *need* a temp-table, don't use it.

Not that performance should always be a #1 concern of course. Just that
I've seen hundreds if not thousands of stored-procedures that use
temp-tables as a matter of course just because the developer wasn't
comfortable with sub-selects, grouping, etc. I'm sure you won't fall
into that trap though. :-)

There are also in-memory tables, but I don't remember the caveats. I
think perhaps they might have a global scope or something inconvienent
like that, but don't quote me on that.

-Sam

David Vallner wrote:
> 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 your
> >>> DB whether and how that is done), put all the values in that temp table
> >>> and join it against the table you are querying.
> >>
> >> Talk about serendipity... I was looking for a way to avoid having to do
> >> 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?
> >
> > 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... :-)
> >
> > Kind regards
> >
> >     robert
> >
>
> 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
> Content-Disposition: inline;
> 	filename="signature.asc"
> Content-Description: OpenPGP digital signature
> X-Google-AttachSize: 188