Thread: odbc parameters

Hello,

I use MS Access for data import. Access imports csv file, make some
calculation and transffers data to PostgreSQL.
Unfortunately, it takes a lot of time to transfer data to PostgreSQL.
My odbc settings are following:

[ODBC]
DRIVER=PostgreSQL Unicode
UID=postgres
XaOpt=1
LowerCaseIdentifier=0
UseServerSidePrepare=1
ByteaAsLongVarBinary=0
BI=0
TrueIsMinus1=0
DisallowPremature=1
UpdatableCursors=1
LFConversion=1
ExtraSysTablePrefixes=dd_
CancelAsFreeStmt=0
Parse=1
BoolsAsChar=0
UnknownsAsLongVarchar=0
TextAsLongVarchar=1
UseDeclareFetch=0
Ksqo=1
Optimizer=1
CommLog=0
Debug=0
MaxLongVarcharSize=8190
MaxVarcharSize=255
UnknownSizes=0
Socket=4096
Fetch=100
ConnSettings=
ShowSystemTables=0
RowVersioning=1
ShowOidColumn=0
FakeOidIndex=0
Protocol=7.4-1
ReadOnly=0
SSLmode=allow
PORT=5432
SERVER=localhost
DATABASE=PLANINGZ

Could you suggest what parameters values would yield best performance for
batch import to PostgreSQL?

Thanks.

Hello,

> I use MS Access for data import. Access imports csv file, make some
> calculation and transffers data to PostgreSQL.
> Unfortunately, it takes a lot of time to transfer data to PostgreSQL.

I don't know how you transfer the data. So it's hard to answer.

> UpdatableCursors=1

Do you need this?

> LFConversion=1

Are you sure you want CR/LF conversion?

> Parse=1

Do you need parse statements? I think you don't need one of
UpdatableCursors or Parse at least.

> Fetch=100

You don't need specify this while you have UseDeclareFetch = 0

> RowVersioning=1

Do you need RowVersioning?

> Could you suggest what parameters values would yield best performance for
> batch import to PostgreSQL?

Maybe this maybe someone suggest anothers. Let's experiment a little bit
yourself ;o)

Luf

Could you post what you are doing in Access? Are you just copy/pasting
from a local table to a linked odbc table? Or are you executing insert
statements in a VBA for loop, if so please post the VBA code?


There are some settings in the ODBC driver that can effect performance,
but the types of SQL statements you are executing often have a much
greater effect on overall performance.

Also if you could turn on the logging in the ODBC driver, often Access
is executing queries on your behalf that you may be unaware of it.

Zlatko Matic wrote:
> Hello,
>
> I use MS Access for data import. Access imports csv file, make some
> calculation and transffers data to PostgreSQL.
> Unfortunately, it takes a lot of time to transfer data to PostgreSQL.
> My odbc settings are following:
>
>


--
David Gardner