Open Table

[Francelino]

Hi.

I have a table with 26318 rows and a 20 fields.

And a open command is very slow.

The same table in Ms Access is 50x more fast.

How i can increase this speed time.

I am use Delphi 6.0 and ADO.

Thank's, sorry about my english.

Ass.: Fabio Alves Francelino

 

[rycamor]

See thread699-249658 for a similar discussion.

I am not familiar with Delphi, or ADO, but PostgreSQL is definitely a much faster database than Access, so the problem has to be elsewhere.

1. Is PostgreSQL running on a remote server? What is the speed of your network connection?
2. Is the "open" command a command that returns the whole table to the client machine? That would use a lot of network bandwidth.
3. Is PostgreSQL running on a Linux or Unix machine, or is it running on Windows?
4. Have you tried running any queries on that table from the PostgreSQL console?
5. Do you have any indexes on this table?
6. Have you read any information on optimizing PostgreSQL?

http://www.postgresql.org/idocs/index.php?performance-tips.html

-------------------------------------------

"Now, this might cause some discomfort..."
(

http://www.wired.com/news/politics/0,1283,51274,00.html)

 

[Francelino]

1-)The client is pgadmin2 and is in same machine that the server reside. In network the speed is 100mb.
2-)Yes, is the entire table.
3-)In Windows, but i try in Linux(Server) with pgadmin2 in Windows(Client) over network, and is very slow.
4-)Yes, and the response is very fast, but for scroll i have that hit "return" several times.
5-)Yes, and the query have some JOINS.
6-)Yes, i read about Vacuum and Analyze but it not resolve the Problem.

I read the discussion that you point, but i don't use odbc i use the driver, my connection string is like pgadmin2.

Thank you, but the problem has not been solved yet.

Have you any other solution ?

Ass.: Fabio Alves Francelino

 

[rycamor]

My point, from the other discussion, is the same principle. Retrieving a whole table over the network is never going to be as fast as viewing a table locally, even if the local database is only Access. This is limited by factors such as network speed, driver efficiency, etc... Also, your network itself could be having collision problems, etc... Have you run any diagnostics on your network?

A 100 Mbit network is probably about 50x slower than a local hard drive. Also, when you are viewing a table locally, you don't always "open" the whole table; you only retrieve the parts that you are viewing at the time. But when you view a table or query remotely, you have to wait for the complete dataset to arrive at your client location. That is why it is better to work with queries that generate small result sets. Why would you need to retrieve the complete table into your application anyway? Why have a remote database at all, if you working with a complete dataset locally? The point is to only work with the data you need at any one time.

I just opened PGAdminII on my Windows 2000 workstation, and connected to PostgreSQL on my FreeBSD Unix server, and retrieved a table with 6500 records. It took about 6 seconds to deliver the data. If I make the same query on the server console, I get the results in less than one second. As this table grows larger, the difference in retrieval time will increase drastically.

So, in short, if the results are fast when you query from the server console, then your bottleneck is either in the network, the driver, or your application. I think it's most likely in the network. Try limiting your query results (using &quot;previous <-> next&quot; pages is a good solution), and see what happens. -------------------------------------------

&quot;Now, this might cause some discomfort...&quot;
(

http://www.wired.com/news/politics/0,1283,51274,00.html)

 

[Francelino]

Well.

I understood your reply.

Really i don't need retrieve all data, i can make a page index as you said.

But the application is build and it works well with same DB in Access, and work very slow with DB in PostGreSQL.

In the next application i will make a system that no requery entire tables.

You are right about the network, but the problem is in local machine. If in the local machine the system works fast it will work over network.

The point is:
&quot;The Microsoft Jet 4.0&quot; works better than PostGreSQL Driver.&quot;(Right ?).

I called the support of my country for PostGreSQL and they are helping me about this question.

Thank you.

Ass.: Fabio Alves Francelino

 

[rycamor]

&quot;If in the local machine the system works fast it will work over network.&quot;

No, that's not necessarily true. It depends on your data access methods.

&quot;The Microsoft Jet 4.0&quot; works better than PostGreSQL Driver.&quot;(Right ?).

Again, that is not necessarily the case. The Microsoft Jet driver connecting to a local Access database is far different from a network connection to a remote database.

Also, I am not quite sure I understand your whole setup. Are you running the PostgreSQL server on your Windows computer for local access? If so, that might be your problem. PostgreSQL is very fast on Unix/Linux, but it is not so fast on Windows.

I am curious to hear what PostgreSQL support says. Please post the results here, Thanks ;-). -------------------------------------------

&quot;Now, this might cause some discomfort...&quot;
(

http://www.wired.com/news/politics/0,1283,51274,00.html)

 

[Francelino]

The support is lost in this case.

They stay only testing, and nothing of solution.

They think that problem is with string connection.

My application use the string same string connection of PGAdmin2.

If the View data is slow in PGAdmin2 the Open is slow in my application.

Do you want a schema of my DB ?

I send to you testing, but you populate the tables.

And i use ADO with Delphi. But you can make the test of PGAdmin2.

You have another way to connect with PgSQL without ADO / BDE for Delphi 6.0.

I wait your reply.

Ass.: Fabio Alves Francelino