Does each query template call within a repeater re-create the connection?

[Former Member]

Version: 11.5.1.b57

Hi,

How exactly does query template calls from a BLS work? Does each call re-create the connection to the database, or does the connection stay open for awhile?

Specifically, in my situation we are making query template calls to a database within a repeater loop to insert rows and I'm curious whether it is recreating the connection each time. We can't insert all the rows at once because of JDBC limitations, so have to insert row by row. We are reading the data from an xml file and then inserting the data into the database and the performance seems slower than I would have expected (even inserting row by row). Currently it is taking around ~4m to insert ~2600 records into the database.

I thought one of two things would be causing the slow speed.

1. XMII is recreating the DataBase connection for each query template call

2. The repeat reading from the XML document is time-consuming as 11.5 BLS XML manipulation is slow.

Thanks for the help.

Kerby

[Former Member · Accepted solution]

The connection stays open, up to the "Use Count" defined in the data server definition in MII. You can increase that number if you'd like (it was a remnant from earlier days when ODBC and JDBC drivers leaked memory all the time).

Also, part of the problem is simply inserting that many records. That's not entirely unrealistic to have fairly slow performance inserting that many records (800/minute = 15/second is probably about all you can get out of it given the way queries are processed, maybe you could get it up to 30/second or so, but I'd be surprised).

If all the data is from the same database, you'd have a lot better luck writing a stored procedure. If from different databases, and one of them is SQL Server, you can used a "linked server" and a stored procedure. If completely different types of data sources, you might be able to generate a CSV file and use the database's "bulk load" or ETL capabilities to process the entire set of rows at once.

[Former Member]

Kerby,

Since you are reading the data from a XML file, it would be better if you handle this from the database end by writing a stored proceduere to post the data from the XML file.

In case you have created the XML file by extracting the data from SAP using a business logic transaction and if you want to run this peirodically, I would suggest you the following.

Schedule the Business Logic Transaction that stores the result to a XML/CSV file

Schedule a job in the database to run the stored procedure that will process the above XML/CSV and post to the database.

You need to have some mechanism to archive the processed file (that also can be handled from the datbase end).

- John

[Former Member]

Thanks for the responses. I tried Rick's change and I see a very small improvement (the useCount was 256 already by default), but now I know that the time taken is due to the inserting and not due to recreating the connection. This saves me from spending time writing a Custom Action block to keep the connection open when XMII can do that already. The writing into the database is on-demand so scheduled tasks won't really work, but thanks for the ideas and help.

Thanks!

Kerby

[jcgood25]

For clarification, Manisha is WRONG - the jdbc connections in the pool are persistent and do not reconnect every time a query is made.

[Former Member]

Unwind and insert the rows ten or a hundred at a time.

[Former Member]

Hi Kerby,

It will recreate connection eachtime.

You need to use the repeater action block to insert all the rows.If you wont use repeater,it will insert only one row.

2600 records inserting into database means you will get performance issue because eachtime it will recreate the connection.

To avoid the performance issue you need to do the following things:

1) you schedule the transaction in backend.It will insert the records in the database table.

2) If you want to populate the data in front end , write a query using that table and make a grid.

It wont create any performance issue.Always big transactions we need to schedule in backend to avoid the performance issue.

Hope this will help you.

Thanks,

Manisha

[Former Member]

Ummmm...it does not reconnect every time. You need to be more careful with your postings and recommendations, Manesha. Many of them have been erroneous recently.

[Former Member]

Sorry Rick.

I had idea that inside a repeater when we call a query template,it will recreate the connection.But my idea was wrong.Thanks for your clarification.

Thanks,

Manisha