Thread: ODBC performance with ADO
Hi, I'm trying to access PostgreSQL table from Visual Basic using ADO with ODBC Provider. I am using the Postgres ODBC driver version 7.01.00.05. The problem is that to obtain a readonly recordset of 5000 records it takes around 15 seconds. disable the SQL logging feature in the ODBC control panel does not change performance. Regards, Enrico Enrico Gava mailto:enrico.gava@tiscalinet.it __________________________________________________________________ Abbonati a Tiscali! Con VoceViva puoi anche ascoltare ed inviare email al telefono. Chiama VoceViva allo 0143 434343 http://voceviva.tiscali.it
Do you get similar performance from MS Access? If not, it might tell you where the bottleneck is. -Robby -----Original Message----- From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Enrico Gava Sent: Thursday, July 19, 2001 6:48 AM To: pgsql-odbc@postgresql.org Subject: [ODBC] ODBC performance with ADO Hi, I'm trying to access PostgreSQL table from Visual Basic using ADO with ODBC Provider. I am using the Postgres ODBC driver version 7.01.00.05. The problem is that to obtain a readonly recordset of 5000 records it takes around 15 seconds. disable the SQL logging feature in the ODBC control panel does not change performance. Regards, Enrico Enrico Gava mailto:enrico.gava@tiscalinet.it __________________________________________________________________ Abbonati a Tiscali! Con VoceViva puoi anche ascoltare ed inviare email al telefono. Chiama VoceViva allo 0143 434343 http://voceviva.tiscali.it ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
The problem seems to be ADO but only with PostgreSQL, with the same odbc provider, accessing MYSQL is very fast (less than 1 second) -- Messaggio Originale -- >Do you get similar performance from MS Access? If not, it might tell >you where the bottleneck is. >-Robby > >-----Original Message----- >From: pgsql-odbc-owner@postgresql.org >[mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Enrico Gava >Sent: Thursday, July 19, 2001 6:48 AM >To: pgsql-odbc@postgresql.org >Subject: [ODBC] ODBC performance with ADO > > >Hi, >I'm trying to access PostgreSQL table from Visual Basic using ADO with ODBC >Provider. I am using the Postgres ODBC driver version 7.01.00.05. >The problem is that to obtain a readonly recordset of 5000 records it takes >around 15 seconds. >disable the SQL logging feature in the ODBC control panel does not change >performance. > >Regards, >Enrico > >Enrico Gava >mailto:enrico.gava@tiscalinet.it > > > >__________________________________________________________________ >Abbonati a Tiscali! >Con VoceViva puoi anche ascoltare ed inviare email al telefono. >Chiama VoceViva allo 0143 434343 >http://voceviva.tiscali.it > > > > > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to majordomo@postgresql.org so that your >message can get through to the mailing list cleanly > Enrico Gava mailto:enrico.gava@tiscalinet.it __________________________________________________________________ Abbonati a Tiscali! Con VoceViva puoi anche ascoltare ed inviare email al telefono. Chiama VoceViva allo 0143 434343 http://voceviva.tiscali.it
> -----Original Message----- > From: Enrico Gava > > Hi, > I'm trying to access PostgreSQL table from Visual Basic using ADO > with ODBC > Provider. I am using the Postgres ODBC driver version 7.01.00.05. > The problem is that to obtain a readonly recordset of 5000 What command are you issuing ? > records it takes > around 15 seconds. Could you show me the table definiton ? It takes 1~3 seconds to obtain 5000 or more records using ADO. regards, Hiroshi Inoue
>> Hi, >> I'm trying to access PostgreSQL table from Visual Basic using ADO >> with ODBC >> Provider. I am using the Postgres ODBC driver version 7.01.00.05. >> The problem is that to obtain a readonly recordset of 5000 > >What command are you issuing ? > recordset.open "select * from table_name" the cursor is ForwardOnly, the lock is ReadOnly it's the same with Connection.execute "select * from table_name" >> records it takes >> around 15 seconds. > >Could you show me the table definiton ? >It takes 1~3 seconds to obtain 5000 or more records using ADO. it's a simple test table: ID int4 LastName varchar(30) FirstName varchar(30) regards Enrico Gava Enrico Gava mailto:enrico.gava@tiscalinet.it __________________________________________________________________ Abbonati a Tiscali! Con VoceViva puoi anche ascoltare ed inviare email al telefono. Chiama VoceViva allo 0143 434343 http://voceviva.tiscali.it
> -----Original Message----- > From: Enrico Gava [mailto:enrico.gava@tiscalinet.it] > > >> Hi, > >> I'm trying to access PostgreSQL table from Visual Basic using ADO > >> with ODBC > >> Provider. I am using the Postgres ODBC driver version 7.01.00.05. > >> The problem is that to obtain a readonly recordset of 5000 > > > >What command are you issuing ? > > > recordset.open "select * from table_name" > the cursor is ForwardOnly, the lock is ReadOnly > it's the same with Connection.execute "select * from table_name" > > >> records it takes > >> around 15 seconds. > > > >Could you show me the table definiton ? > >It takes 1~3 seconds to obtain 5000 or more records using ADO. > > it's a simple test table: > ID int4 > LastName varchar(30) > FirstName varchar(30) > Hmm it doesn't take that long even on a table with 30 columns here. Aren't you turning on mylog debug ? i.e Aren't there mylog_xxxx.log files under C:\ ? If so Ensure that the ODBC driver is not in Debug mode (i.e. set HKEY_LOCAL_MACHINE\Software\ODBC\ODBCINST.INI\PostgreSQL\Debug = 0 using regedit). regards, Hiroshi Inoue