Oracle Odbc Connectivity Very Very Slow Follow

0
Avatar
Legacy Poster

Greetings,

 

I am running the following:

 

Windows 7 Enterprise x86, SP1

BarTender Pro 10.1 SR4

Oracle Instant Client 12.1.0.1.0

Oracle ODBC Driver 12.1.0.1.0

 

I am trying to pull data from a VERY large Oracle R12 ERP system to populate fields in a packing label.  My problem is everything from configuring the connectivity to running the SQL query is taking 4 - 5 minutes to complete.

 

My first step was creating the Windows ODBC connection using the Oracle ODBC driver (listed above) and testing it with a simple query tool.  My custom SQL statement returned the needed records in 0.05 seconds. 

 

Things get very painful when I try to configure connectivity inside of Bartender. Here are the steps I use:

 

File -> New -> Blank Template

File -> Database Connection Setup -> Add -> ODBC Data Source -> System Data Source -> (My DSN)

 

At this point it sits for 4 - 5 minutes getting the list of tables. Once it completes I select the 3 tables I need add them to the list and then wait another 4 - 5 minutes for that to complete.

 

Trying to add criteria is impossible.  I select the necessary field and when I click the criteria box it'll sit there for another 4 - 5 minutes pulling records until it fails with an "Out of Memory" error and the entire Database Connection Setup window closes.  This forces me to start all over again which is VERY FRUSTRATING.

 

To get around this I have resorted to hand-coded SQL. The query is very simple but it takes BarTender 4 - 5 minutes to actually get the data.  This is very puzzeling as I can copy and paste the SQL code into a simple ODBC query tool and it returns the results in 0.05 seconds.  (Yes, I timed it)

 

What the crap am I doing wrong? 

 

Best regards,

The Dude

 

 

 

2 comments

0
Avatar
Shotaro Ito
Moderator
Comment actions Permalink

Hi Dude,

I recon OLEDB / ODBC connection might get very slow when you connect to very big table - don't know why.

Try Oracle setting in BarTender database connection setup (that's OLEDB.)

Try Options > [Use client cursor] if haven't tried.

 

Usually when you have complex query in Database connection and that makes database connection slow,

create a equivalent view in database and connect to the view from BarTender.

Also, I suppose you're using Query prompt already, but if not - use query prompt to filter records, demonstrated in below.

http://www.seagullscientific.com/label-software/training-video-(selecting-records-from-a-database-at-print-time).aspx

0
Avatar
Legacy Poster
Comment actions Permalink

Sorry to resurect an old thread, however, I also have a similar problem AND a issue with how BarTender is pulling in all the tables from Oracle.

I am only using a few tables and clearly defined join conditions (though, for one of the joins BarTender calls it a circular reference when it clearly is not). I am finding that it looks to be pulling in ALL records prior to running any filters on the query?

 

I decided to commit to a custom query instead of the query derived via the tables/joins/filters as it was putting in certian fields twice into the query. (e.g. ORDER_NUMBER, ATTRIBUTE1, HEADER_ID and then reprating this again - ORDER_NUMBER, ATTRIBUTE1, HEADER_ID) yet are only used once on the template.

 

Also, I have to actually bring in one table at a time, make the join, close the template and then repeat this for each table used.

This is for Oracle EBS R12 as the datasource. So I am unsure why there is such a huge performance hit when BarTender links to Oracle? We did create materialized views to try to increase the performance but to no avail. Performance is exactly the same as the SQL Query (and in some cases, worse). we have tuned the main query to create the view, have the correct indexes and the like. This is operating in an enterprise realm and the performance is not on par with what we would like.

 

It is almost like it is trying to bring back the top 1000 records and then performing any filter related data. Any insight would be most appreciated. Other than this issue with Oracle connectivity, the rest of what we use the application for is nothing short of amazing!

Please sign in to leave a comment.