«

Lew wrote:
> Arne Vajhøj wrote:
>> Lew wrote:
>>> Vince wrote:
>>>> I'm not sure if I'll be capable to prepare a preparedStatement for
>>>> each type of product that goes through this adapter. One requirement
>>>> of the adapter is that whenever a new product is introduced, the
>>>> application must react dynamically. Basically no change to the code
>>>> (thus no new release)is allowed.
>>>
>>> A new product should be only a data change, therefore no need to
>>> change code. The same PreparedStatement should work for any
>>> products, new, former, current, whatever - the data structure should
>>> not change with the new products

Not true in my case, I've got 1600 possible attributes. A product
(financial transactions like options, swaps, futures etc) will only use
a subset of the 1600 possible attributes. Thus the data structure will
change for each product (~400 different product available). The point is
that I don't get a package of product X first and then Y and then Z. The
products will come through MQ fully mixed up.

--
Posted via a free Usenet account from http://www.teranews.com

Vince <> wrote:
>I must say at this point that every message has a variable length and
>thus variable amount of data/fields that must be returned to the DB.
>
>During a workshop I got an argue with another developer because he said
>we should use PrepareStatements instead of normal Statements, they've
>got a better performance - compiling once on DB Server.

He's right. The other MAJOR advantage you get is automatic quoting. If
you're executing statments via string concatenation, you have to deal with crap
like quotes and SQL reserved words in the data.

>We basically better concatenate the Insert Statement once for
>each row of data and then use a batch and commit it to the DB...
....
>My question now: How would you solve it? Would the PrepareStatement
>still improve the performance, considering though the variable amount of
>columns?

I'd still plan on using as few distinct statements, even if it means you have
a number of messages that are explicitly inserting nulls as opposed to
generating a custom insert that ignores irrelevant columns (which still
inserts nulls). Even if you generate them by concatenation, you can make use
of caching - Oracle and some others will only have to do a soft-parse rather
than a full parse if the text of the statement is identical to one in
the cache. C3p0 or other pooling system will do client-side caching based
on string comparisons.
--
Mark Rafn <http://www.dagon.net/>

Vince wrote:
> Lew wrote:
>> Arne Vajhøj wrote:
>>> Lew wrote:
>>>> Vince wrote:
>>>>> I'm not sure if I'll be capable to prepare a preparedStatement for
>>>>> each type of product that goes through this adapter. One
>>>>> requirement of the adapter is that whenever a new product is
>>>>> introduced, the application must react dynamically. Basically no
>>>>> change to the code (thus no new release)is allowed.
>>>>
>>>> A new product should be only a data change, therefore no need to
>>>> change code. The same PreparedStatement should work for any
>>>> products, new, former, current, whatever - the data structure should
>>>> not change with the new products
>
> Not true in my case, I've got 1600 possible attributes. A product
> (financial transactions like options, swaps, futures etc) will only use
> a subset of the 1600 possible attributes. Thus the data structure will
> change for each product (~400 different product available). The point is
> that I don't get a package of product X first and then Y and then Z. The
> products will come through MQ fully mixed up.
>
We never said you needed a PreparedStatement per product - just one per
table which gets re-used for each message that's stored in the table it
accesses.

We never mentioned any dependence on message/product ordering either.

Maybe some pseudo code will make things a bit clearer:

INITIALIZE
FOR EACH TABLE
CREATE PreparedStatement
LABEL with the associated product code(s) for this table
END-FOR
SET AUTO-COMMIT off
END-INITIALIZE

FOR EACH MESSAGE
SELECT PreparedStatement USING product code
PARSE message
FOR EACH FIELD
ATTACH it to the prepared statement column
END-PARSE
FOR EACH UNUSED COLUMN
SET prepared statement column to null
END-FOR
INSERT ROW
COMMIT
END-FOR


--
martin@ | Martin Gregorie
gregorie. | Essex, UK
org |