pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedODBC Performance
3+ messages / 3 participants
[nested] [flat]
* ODBC Performance
@ 1999-07-13 13:17 Ming-Hung Eng <mheng@hachette-multimedia.fr>
0 siblings, 1 reply; 3+ messages in thread
From: Ming-Hung Eng @ 1999-07-13 13:17 UTC (permalink / raw)
To: pgsql-interfaces
Hi
I want to copy a content of a Access table to a Postgres attached table via
ODBC.
There are 104000 entries.
The problem is the operation take more than 3 hours !!!
Do someone know why and what's wrong for me.
Thanks!!
^ permalink raw reply [nested|flat] 3+ messages in thread
* RE: [INTERFACES] ODBC Performance
@ 1999-07-13 13:33 Ansley, Michael <Michael.Ansley@intec.co.za>
0 siblings, 0 replies; 3+ messages in thread
From: Ansley, Michael @ 1999-07-13 13:33 UTC (permalink / raw)
To: 'Ming-Hung Eng' <mheng@hachette-multimedia.fr>; +Cc: pgsql-interfaces
I found that performance was increased quite a lot with 6.5 (ODBC driver
6.40.0006), and that the driver options also helped. I had a table with 210
000 rows (also in Access), and this took about an hour or so, once I'd
managed to clean the data, and set the options as best I could. I think
that my settings were:
Disable Genetic Optimizer: on
KSQO: on
Recognize Unique Indexes: on
Use Declare/Fetch: on
I had the logging turned off.
Something that I did find was that using something like DAO's .Add method to
perform mass inserts was tremendously slow, and I suspect that that is how
Access is doing it (kind of). When programming in VB, using ADO, I
constructed INSERT statements, and executed them, in order to insert
information, and this was a lot faster. However, there is no way that you
can tell what Access is doing, and how to speed it up on the client side.
Anyway, try the ODBC settings above (if you haven't already), it should
speed things up somewhat.
MikeA
>> I want to copy a content of a Access table to a Postgres
>> attached table via
>> ODBC.
>> There are 104000 entries.
>>
>> The problem is the operation take more than 3 hours !!!
>>
>> Do someone know why and what's wrong for me.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [INTERFACES] ODBC Performance
@ 1999-07-13 18:46 Brett W. McCoy <bmccoy@lan2wan.com>
parent: Ming-Hung Eng <mheng@hachette-multimedia.fr>
0 siblings, 0 replies; 3+ messages in thread
From: Brett W. McCoy @ 1999-07-13 18:46 UTC (permalink / raw)
To: Ming-Hung Eng <mheng@hachette-multimedia.fr>; +Cc: pgsql-interfaces
On Tue, 13 Jul 1999, Ming-Hung Eng wrote:
> I want to copy a content of a Access table to a Postgres attached table via
> ODBC.
> There are 104000 entries.
>
> The problem is the operation take more than 3 hours !!!
>
> Do someone know why and what's wrong for me.
I've found for bulk copying like that, going through ODBC is terribly
efficient. You might be better off dumong your Access table to some sort
of text-delimited file and using the COPY command in pgsql. It is far
faster.
Als, check to see if you have the fsync option in use. For bulk copying,
you will want to turn off the fsync funciton, since it slows down disk
operations. Turn it back on, though, if you want to do transactional
work or you could lose data if your server crashes.
Brett W. McCoy
http://www.lan2wan.com/~bmccoy
-----------------------------------------------------------------------
If you've seen one redwood, you've seen them all.
-- Ronald Reagan
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1999-07-13 18:46 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-13 13:17 ODBC Performance Ming-Hung Eng <mheng@hachette-multimedia.fr>
1999-07-13 18:46 ` Brett W. McCoy <bmccoy@lan2wan.com>
1999-07-13 13:33 RE: [INTERFACES] ODBC Performance Ansley, Michael <Michael.Ansley@intec.co.za>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox