Postgres Pro
Downloads
Home   >   mailing lists

Thread: PostgreSQL, JDBC and BObj

PostgreSQL, JDBC and BObj

From
Keith Fisher
Date:

Hi,

 

I am trying to use a JDBC connection (driver version PostgreSQL 9.1 JDBC4 (build 903) to connect to a PostgreSQL 9 database. I am using SAP BusinessObjects 4.1 SP2 to build a universe over the database.

 

The connection is working ok but is really slow.

 

I don’t have any direct access to the database, but the developer who is supporting me says the sql run directly against the database is quick and has no issues.

 

The main table that seems to cause issues has 250+ columns, but only 52,400 rows.

 

Has anyone got experience of running BOBJ and PostgreSQL, and of tuning the connection for speed? I can add Java memory to the universe designer application and to the services that run SQL but so far this doesn’t seem to have helped.

 

Keith Fisher

 

Re: PostgreSQL, JDBC and BObj

From
Szymon Lipiński
Date:


On 10 September 2015 at 15:22, Keith Fisher <keith.fisher@edenhousesolutions.co.uk> wrote:

Hi,

 

I am trying to use a JDBC connection (driver version PostgreSQL 9.1 JDBC4 (build 903) to connect to a PostgreSQL 9 database. I am using SAP BusinessObjects 4.1 SP2 to build a universe over the database.

 

The connection is working ok but is really slow.

 

I don’t have any direct access to the database, but the developer who is supporting me says the sql run directly against the database is quick and has no issues.

 

The main table that seems to cause issues has 250+ columns, but only 52,400 rows.

 

Has anyone got experience of running BOBJ and PostgreSQL, and of tuning the connection for speed? I can add Java memory to the universe designer application and to the services that run SQL but so far this doesn’t seem to have helped.

 

Keith Fisher

 


Hi

What was run directly at the database server? The same query? Just explain? Or maybe explain analyze? If there was the same query, was it downloading exactly the same data from the database server, or there were just limits added?

Try running explain analyze to see what is the plan for executing the query.

Another problem could be that the query is rather fast, however downloading the data is slow. If each column has on average about 20B of data, then the whole table has about 250MB. Downloading all the data can be much slower than downloading just a couple of rows.


--
    regards Szymon Lipiński

Re: PostgreSQL, JDBC and BObj

From
James Keener
Date:
Can you connect directly to the database using something like pgadmin or another database viewer (preferably from the same computer or at least network as the server).

How many rows are you trying to return? Does a limit 1 on the query speed it up? Did your colleague mean the same query runs fast, or just queries in general? Can you extract the raw SQL from what the application is producing and do an explain analyze on it?

Jim

On September 10, 2015 8:22:13 AM CDT, Keith Fisher <keith.fisher@edenhousesolutions.co.uk> wrote:

Hi,

 

I am trying to use a JDBC connection (driver version PostgreSQL 9.1 JDBC4 (build 903) to connect to a PostgreSQL 9 database. I am using SAP BusinessObjects 4.1 SP2 to build a universe over the database.

 

The connection is working ok but is really slow.

 

I don’t have any direct access to the database, but the developer who is supporting me says the sql run directly against the database is quick and has no issues.

 

The main table that seems to cause issues has 250+ columns, but only 52,400 rows.

 

Has anyone got experience of running BOBJ and PostgreSQL, and of tuning the connection for speed? I can add Java memory to the universe designer application and to the services that run SQL but so far this doesn’t seem to have helped.

 

Keith Fisher

 


--
Sent from my Android device with K-9 Mail. Please excuse my brevity.