PostgreSQL with Delphi 2010 really slow?

[RobNorthcott]

We're planning to redevelop our ageing Delphi 5/BDE/Paradox application and looking into the options.

PostgreSQL is looking good for the database, and we've tried Java/NetBeans, MS Visual Studio and Delphi 2010.

So far, Delphi is looking the most promising, but it seems pathetically slow at accessing the Postgre data. It's OK with very small result sets, but with a query returning even 1000 records it takes ages. That's using an ODBC connection - with the Postgre ADO/OLEDB it runs out of memory and crashes with anything more than a few records.

Is anybody out there using Postgre with Delphi - if so, is it really that slow or is there something we need to know to get it to work properly? The Postgre installation itself is fine - the Java and Visual Studio tests we did run much faster.

Any ideas?

Rob

 

[whosrdaddy]

I have no experience with PostgreSQL,

I have a lot of field expierence with MySQL/MsSQL and msAccess though. What I learned through the years is that optimization can be very important in database applications and every setting counts (on the application side and database side).

some remarks:

if you are going the ODBC route, I can advise you to use the dbGo (ADO) components: TADOConnection, TADOQuery to name the most important ones. Fiddling with options of the ODBC driver can be very important (like caching and stuff)

if you want to go native, use zeoslib (

http://sourceforge.net/projects/zeoslib/).

Show use some code so we can give some more advice.

Cheers,
/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[RobNorthcott]

Thanks. There's not really any code to show you yet. We're just evaluating various methods to make sure they do what we need before we commit ourselves. I'm drawn to Delphi again because it seems a shame to waste my 25-odd years experience with Pascal/Delphi, but we really want to use a "proper" client-server database engine this time for various reasons.

All we've done so far in Delphi 2010 is set up a connection to a database using TADOConnection and TADOQuery with the relevant connection string. We've tried an ODBC connection an the Postgre OLE DB connection, which both perform equally poorly. All we're doing at the moment is running a very simple "select * from <table> where <simple filter>" query and displaying the results in a TDBGrid. Seems to take about five to ten times as long in Delphi as it does with the same data in Visual Studio (which I hate) and Java/NetBeans (so it's not the Postgre engine or the drivers that are pants). We've used Postgre a lot for other projects and like it. I think it was developed by the MySQL people after Sun took over MySQL - but ironically Sun have nabbed Postgre as well now!

We'll have another play with it tomorrow and let you know how we get on.

Rob

 

[smays]

Rob,

For whatever it is worth, I have had good success with MySQL Direct Access Components from

http://www.microolap.com.

On the code side, the interface is extremely similar to the BDE, so there should be some familiarity with the product. The support from microolap.com has been good with hot fixes made available almost over night. I am able to pull thousands of records with a fairly complicated query with multiple JOINs in less than a second and make it available to the user. I do nothing to optimize the connection or caching.

whosrdaddy, how does the interface to the zeoslib components compare to the BDE interface?

Steve.

 

[RobNorthcott]

Thanks for that Steve - we'll have a look at those while we're at it. We're not utterly decided on Postgre - we'd use another SQL server like MySQL if it performed better. It's just that we've used it a few times before (mostly for web apps) and it's always seemed good, and has the added bonus of no license fees to pay.

Rob

 

[whosrdaddy]

how does the interface to the zeoslib components compare to the BDE interface?

don't know as I have never used the BDE (there was always a better alternative )

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[RobNorthcott]

Steve: Just gave those microolap components a try. They do a PostgreSQL version, and it's absolutely superb. Easy to use and goes like stink. Thanks for the tip-off. Looks like we could be sticking with Delphi after all

Just need to get used to Rave Reports now... I was so used to QuickReports it all looks a but weird, but I'm sure we'll get used to it (or just install QR!)

Rob

 

[DjangMan]

Rob - for reports you can also look at Fast Report

http://fast-report.com/en/