Asp Forum - string of strings...

Josselin

12/19/2006 8:22:00 AM

I got an array of id's :

ids = [22443, 22468, 22371, 22218, 22472, 22377, 22245]

I would like to get a string of strings (to be used in SQL WHERE..IN clause

" '22443' , ' 22468' , ' 22371' , ' 22218' , '22472' , '22377' , '22245' "

If a use ids.join(','), I get
"22443,22468,22371,22218,22472,22377,22245" which is obviously not
useful for SQL....

is there any shorcuts or should I concatenate each element ?

thanks

joss

10 Answers

Martin DeMello

12/19/2006 8:30:00 AM

On 12/19/06, Josselin <josselin@wanadoo.fr> wrote:
> I got an array of id's :
>
> ids = [22443, 22468, 22371, 22218, 22472, 22377, 22245]
>
> I would like to get a string of strings (to be used in SQL WHERE..IN clause
>
> " '22443' , ' 22468' , ' 22371' , ' 22218' , '22472' , '22377' , '22245' "
>
> If a use ids.join(','), I get
> "22443,22468,22371,22218,22472,22377,22245" which is obviously not
> useful for SQL....

ids.map {|i| "'#{i}'"}.join(" ,")

martin

Josselin

12/19/2006 9:23:00 AM

On 2006-12-19 09:29:38 +0100, "Martin DeMello" <martindemello@gmail.com> said:

> On 12/19/06, Josselin <josselin@wanadoo.fr> wrote:
>> I got an array of id's :
>>
>> ids = [22443, 22468, 22371, 22218, 22472, 22377, 22245]
>>
>> I would like to get a string of strings (to be used in SQL WHERE..IN clause
>>
>> " '22443' , ' 22468' , ' 22371' , ' 22218' , '22472' , '22377' , '22245' "
>>
>> If a use ids.join(','), I get
>> "22443,22468,22371,22218,22472,22377,22245" which is obviously not
>> useful for SQL....
>
> ids.map {|i| "'#{i}'"}.join(" ,")
>
> martin

thanks Martin... I realize that the error I got was not due to
integers list , "22443, 22468, 22371, 22218, 22472, 22377, 22245" is
accepted... so the join(',') is ok..
but the list is too long ... for a select statement.. (I can have 50
numbers or more.....) there is a characters limit to the select

joss

Robert Klemme

12/19/2006 11:59:00 AM

On 19.12.2006 10:23, Josselin wrote:
> On 2006-12-19 09:29:38 +0100, "Martin DeMello" <martindemello@gmail.com>
> said:
>
>> On 12/19/06, Josselin <josselin@wanadoo.fr> wrote:
>>> I got an array of id's :
>>>
>>> ids = [22443, 22468, 22371, 22218, 22472, 22377, 22245]
>>>
>>> I would like to get a string of strings (to be used in SQL WHERE..IN
>>> clause
>>>
>>> " '22443' , ' 22468' , ' 22371' , ' 22218' , '22472' , '22377' ,
>>> '22245' "
>>>
>>> If a use ids.join(','), I get
>>> "22443,22468,22371,22218,22472,22377,22245" which is obviously not
>>> useful for SQL....
>>
>> ids.map {|i| "'#{i}'"}.join(" ,")
>>
>> martin
>
> thanks Martin... I realize that the error I got was not due to integers
> list , "22443, 22468, 22371, 22218, 22472, 22377, 22245" is accepted...
> so the join(',') is ok..
> but the list is too long ... for a select statement.. (I can have 50
> numbers or more.....) there is a characters limit to the select

Maybe you can generate ranges for adjacent values. IIRC we had a thread
here at some point in the past - maybe it's even a quiz.

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.

Kind regards

robert

David Vallner

12/21/2006 12:14:00 AM

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?

David Vallner

Robert Klemme

12/21/2006 8:25:00 PM

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

David Vallner

12/21/2006 9:02:00 PM

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

Sam Smoot

12/22/2006 12:35:00 AM

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

David Vallner

12/22/2006 12:25:00 PM

Sam Smoot wrote:
> 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.
>

Well, the Powers That Be said "it's probably the query lag". So this
should clear it. My opinion is that it's the varchar column that could
use a unique index constraint, and since it's a read-only rather small
table (in the order of tens of records, maybe hundreds at most) from the
POV of the client app I'm working on, I'd prefer to just prefetch it on
startup and stop fooling around.

> 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. :-)
>

Erm. Using a temp table to store data that -already is- in the DB? Eugh.
I presume that's the same kind of developer that's not comfortable with
nesting function calls and gets into 9 levels of indentation and umpty
local variables. And if I have the necessary privileges on a DB, any and
all subselects I see are very good candidates to be axed and put into a
view.

> 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.
>

This is Oracle 8i, working in Mysterious Ways (tm), and the only temp
tables you get are globally scoped with locally scoped data. Luckily I
think developers have create table rights, so it should be possible to
sneak this in.

Right, thanks for the hints!

David Vallner

Robert Klemme

12/22/2006 4:26:00 PM

On 22.12.2006 01:34, Sam Smoot wrote:
> 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.

The fact that they reside in tempdb does not necessarily mean they are
written to disk or that they are slow. Small temp tables will easily
fit into the page cache. And since they are created directly before
usage likelihood of finding those pages in the cache is pretty high.
Also, tempdb has recovery model simply which reduces burden on the disk
somewhat. Also, I am not sure whether operations on temp tables are
logged at all.

> 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. :-)

Yeah, that's what I heard also: often people use temp tables because
they do not know SQL and the capabilities of their RDBMS very well.

> 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.

They are called "table variables". Scope is not an issue, they are
scoped like local variables (unless maybe if they are returned from a
procedure). IIRC limitation is that they do not allow indexes and
constraints.

Kind regards

robert

PS: Please don't top post.

Sam Smoot

12/22/2006 8:57:00 PM

> The fact that they reside in tempdb does not necessarily mean they are
> written to disk or that they are slow. Small temp tables will easily
> fit into the page cache. And since they are created directly before
> usage likelihood of finding those pages in the cache is pretty high.
> Also, tempdb has recovery model simply which reduces burden on the disk
> somewhat. Also, I am not sure whether operations on temp tables are
> logged at all.

I think you've got it right about whether a temp-table _must_ be
written to disk during it's lifetime. Looking it up just now I found
conflicting accounts, so I can't authoritatively say either way. :-/

> > 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.
>
> They are called "table variables". Scope is not an issue, they are
> scoped like local variables (unless maybe if they are returned from a
> procedure). IIRC limitation is that they do not allow indexes and
> constraints.

It looks like I was actually thinking about global temp-tables using
the ##table syntax. Shows how often I've found a use for them I
suppose. :-)

> PS: Please don't top post.

Sorry about that. :-o

comp.lang.ruby

string of strings...

Josselin

Martin DeMello

Josselin

Robert Klemme

David Vallner

Robert Klemme

David Vallner

Sam Smoot

David Vallner

Robert Klemme

Sam Smoot

x Login to ForumsZone