Providing Query statement in JDBC adapter

[Former Member]

HI experts,

Everybody knows we can provide Query to JDBC adapter by two ways...

1st way is by constructing query as XML structure..

2nd way is by giving exact query (Select * from tablename )by SQL_query in access ...

I like to know the performance of these two ways...

Regards,

Sasitharan

[Former Member]

In XI version 3.0 there is only one way to do lookups - manually in the form of an exit in the mapping process. A user exits must be written in Java.

There are a couple of considerations about performance that must be kept in mind besides just the question of “SQL Statement” while implementing a database lookup using a JDBC adapter:

1. The mechanism for database lookup

There are 2 approaches – Direct, Indirect

In Direct approach you poll the external table hence this may lock database for access to other users, also it is an intrusive approach. In indirect approach database table is replicated and the replicated table is accessed. The second approach is recommended for performance reasons.

2. Does the data to be looked up change frequently

If data does not change frequently than pull in data and keep a copy locally – as this will improve performance

2. It is possible to access an external database from within the mapping step of XI utilizing

A. regular JDBC constructs

B. utilization of J2EE JDBC Connector

Approach 2 is recommended as it is more efficient because of Connection pools.

There is no concept or need to XML code here. The Java code that executes the lookup, would call the sql statement, this java code can be referenced from within a user-defined function in the graphical mapper or from within XSLT.

Hence you need SQL statement in J2ee JDBC Connector to get the best performance.

Refer to URL

http://searchsap.techtarget.com/tip/0,289483,sid21_gci1246926,00.html#

to understadn how to configure the JDBC Connector Service to perform database lookups

Let me know if this helps.

[Former Member]

--->what i am thinking is if I give SQL query using SQL_Query in access directly to JDBC adapter instead of giving query as XML structure ...so that jdbc adapter recognises the SQL query easily and it will improve the performance...am I right?If not correct me...

Sasitharan,

I too have the same feeling , basically going for native sql will help in improving the performance ...but not to forget that data type also plays a role in creating the query.

Regards,

[Former Member]

thanks Raj,

I accept your point that giving SQL query directly to JDBC adpater is right idea incase of complex queries...

what i am thinking is if I give SQL query using SQL_Query in access directly to JDBC adapter instead of giving query as XML structure ...so that jdbc adapter recognises the SQL query easily and it will improve the performance...am I right?If not correct me...

Edited by: sasitharan on Mar 8, 2008 1:51 PM

[justin_santhanam]

Sasi,

Yes I agree with you. The complexity will be less and the performance also will be good. I'm telling everything on assumption, coz we don't know what code been written in Adapter module in order of converting the data. What ever it might be, giving direct query will reduce the complexity and increase the performance I believe.

It's my personal opnion and thought!

raj.

[justin_santhanam]

Sasi,

I won't think there could be any performance issue, rather than mapping. Coz if you construct wither way using XML or SQL query, while going out of XI everything goes as SQL Query.

I will prefer to go to SQL Query, coz the complex sql structure can't be achieved using XML Structure.

raj.