Database access...slow, Log On issue????

[Former Member]

I posted something of this nature a week or 2 back but never got a response. Thought I'd try again and see if anyone else is experiencing this or has any insight.

I'm running Crystal Reports 2008 on WinXP SP3 w/PostgreSQL 8.4 all on the same machine. When I go from preview to design, it seems to take forever to capture ~240 records (certainly longer than I would think it would take...and I've used OpenOffice as a front end, as well). I'm using the Microsoft OLE DB into ODBC for PostgreSQL. Is there a better/faster way to do this that might speed things up? Is there something in the firewall or security area that I need to tweak?

On what may be a related note....I noticed that creating a new connection works fairly quickly. However, once I have the connection made, if I log off then attempt to log back on, the Log Off button never seems to go active, which makes me unsure if I ever got Logged On in the 1st place. Am I missing something here?

Thanks in advance for any help on this.

Cheers...Steph

[former_member11696]

Hi Steph,

Crystal Reports logs off when it's idle or no data is being requested. We keep the connection info but so the database doesn't have multiple open connections we disconnect when not needed.

The slowness could be due to your database indexing. Try to add an index or use one of the fields that are indexed to optimize speed server side.

If you copy the SQL from CR to and ODBC test tool how fast is it?

The likely problem though is with using OLE DB to ODBC is you have 2 separate layers of database connections to deal with. Why not just connect to the data source using ODBC and don't use OLE DB to ODBC driver. Or if PostGres can use OLE DB directly the use it, just not both.

OLE DB will use the DB indexes and ODBC will create it's own indexing.... they may be fighting each other.

Thank you

Don

[Former Member]

Don,

Thanks for the suggestions. Apologies for taking so long to get back to you. Been distracted by a few other high priority projects.

I had set up the OLE DB to ODBC connection because I read on a blog somewhere that CR 2008 couldn't connect to PostgreSQL (or PostgreSQL to CR) directly thru ODBC. Turns out the blog was referring to an older version of CR (XI?).

I went back and created a direct ODBC connection (since I do not have an index established yet in the test table I am using). It's hard to notice any improved speed of access. W/out any changes to the report, the data seems readily available. However, if I change any data access requirements (queries, in particular) the speed advantage goes away.

In my testing, I use a table that has about 200 records. When I go to preview and CR needs to access the db, I can see the records being (re)read counted on the bottom of the report. The number increases ~1 per second until all the records have been read. Is this normal?

You mention about an ODBC test tool. Is this a "test button" when setting up the connection? Is this something that SAP offers as separate s//w? How do I know if I have it as a tool within my version of CR? I did not see anything about this in my help files.

Thanks and cheers...Steph

[Former Member]

Don,

One other thing I neglected to mention in my previous post....

I am also trying out Visual Studio. It seems that getting access to the db is not any faster thru VS than it is thru CR, so maybe it is something to do with the ODBC connection. In that case, the ODBC test tool (whatever that is) would be of use to me.

As a side note: when I originally set up the connection (in LogOn/Off DB), it also seemed to take a long time to "register" the connection and db. Finding a way to speed this up, if possible, would be of great help.

BTW - I tried out a program called "Navicat" and it seem to provide almost instantaneous access to either my PostgreSQL or MySQL database, and it doesn't matter whether it's on the local system, or the Fedora box on the network. Now, Navicat is specifically set up to handle MySQL, PostgreSQL, Oracle and I believe 1 other db, so it has very specific protocol designed in. However.....

Thanks and cheers...Steph

Edited by: stepher on Sep 17, 2010 8:05 PM

[former_member11696]

Hi Steph,

Not sure what Navicat is but if it's designed for that specific DB I doubt it is using ODBC, likely using a native client connection.

Just search for "ODBC test tool" in google and you'll find one. Microsoft makes one for sure. If you can you really should add indexing, if you don't then the nature of DB's is they read sequentially every row of data.

Thanks again

Don