string of strings...

[Josselin]

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

 

[Martin DeMello]

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]

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]

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]

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

A completely different option is to create a temp table (depends on you= r
DB whether and how that is done), put all the values in that temp table=

and join it against the table you are querying.
=20

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


--------------enig444FE6AAB5C57281FEBB55CA
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)

iD8DBQFFidHIy6MhrS8astoRAk8oAJ9qwvHQxVaWgcymheNIOk6NrfzLAgCeNxSw
+CEEUDZNQmquHQGbzzz7wL4=
=YMhA
-----END PGP SIGNATURE-----

--------------enig444FE6AAB5C57281FEBB55CA--

 

[Robert Klemme]

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]

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

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

 

[Sam Smoot]

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

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]

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

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

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

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

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


--------------enig27338423BFEC1776E36B192C
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)

iD8DBQFFi86dy6MhrS8astoRAofJAJ9ofmJKo7HWDKTDZ12WA0XllhC3JACfYXzo
f8KwpbzksJJBoMPiRqpHZqk=
=jqst
-----END PGP SIGNATURE-----

--------------enig27338423BFEC1776E36B192C--

 

[Robert Klemme]

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]

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

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