Postgres Pro
Downloads
Home   >   mailing lists

Thread: ODBC Performance

ODBC Performance

From
Fábio Sato
Date:
I'm using the ODBC Driver version 07_01_0007 with ADO and the performance
is very disappointing... I have a procedure that update 868 rows and it is
taking more than 5 minutes to complete.

After some search on the mailing lists I've found similar problems but
didn't find any answer to them. Can someone help me?

Thanks!

PS: I'm not using any logging option that could cause a bottleneck in the
ODBC driver...

Re: ODBC Performance

From
Joel Burton
Date:
On Thu, 18 Oct 2001, [iso-8859-15] F�bio Sato wrote:

> I'm using the ODBC Driver version 07_01_0007 with ADO and the performance
> is very disappointing... I have a procedure that update 868 rows and it is
> taking more than 5 minutes to complete.
>
> After some search on the mailing lists I've found similar problems but
> didn't find any answer to them. Can someone help me?

Are you committing after each operation, or is the ODBC driver forcing
this?

By "procedure", do you mean as PG-backend proc (in plpgsql or something
like that), or do you mean client-side procedure that calls PG 868 times?

--

Joel BURTON  |  joel@joelburton.com  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant

Re: ODBC Performance

From
Fábio Sato
Date:
> > I'm using the ODBC Driver version 07_01_0007 with ADO and the performance
> > is very disappointing... I have a procedure that update 868 rows and it is
> > taking more than 5 minutes to complete.
> >
> > After some search on the mailing lists I've found similar problems but
> > didn't find any answer to them. Can someone help me?
>
> Are you committing after each operation, or is the ODBC driver forcing
> this?
>
> By "procedure", do you mean as PG-backend proc (in plpgsql or something
> like that), or do you mean client-side procedure that calls PG 868 times?
>
> --
>
> Joel BURTON  |  joel@joelburton.com  |  joelburton.com  |  aim: wjoelburton
> Independent Knowledge Management Consultant

I think that the source code may be a better answer to your questions,
so this is basically what I'm doing:

---
Dim conn As ADODB.Connection
Dim codes() As Long
Dim array1() As Double
Dim array2() As Double
Dim array3() As Double
.
.
.
.
Dim array20() As Double
Dim size As Long

' The arrays have always the same size (868): Here they are resized and
filled with data
.
.
.

' Then I open the connection and do the updates

conn.Open "DSN=mydb;uid=username;pwd=mypassword"

For I = 0 To size - 1
  sql = "update mytable set value1 = " & array1(I) & ", value2 = " &
array2(I) & _
        ", value3 = " & array3(I) & ... & ", value20 = " & array20(I) &
-
        " where code = " & codes(I)
  conn.Execute(sql)
Next I

---

Re: ODBC Performance

From
Joel Burton
Date:
On Thu, 18 Oct 2001, [iso-8859-1] F�bio Sato wrote:

> > > is very disappointing... I have a procedure that update 868 rows and it is
> > > taking more than 5 minutes to complete.
>
> I think that the source code may be a better answer to your questions,
> so this is basically what I'm doing:
>
> conn.Open "DSN=mydb;uid=username;pwd=mypassword"

conn.Execute("begin")

> For I = 0 To size - 1
>   sql = "update mytable set value1 = " & array1(I) & ", value2 = " &
> array2(I) & _
>         ", value3 = " & array3(I) & ... & ", value20 = " & array20(I) &
> -
>         " where code = " & codes(I)
>   conn.Execute(sql)
> Next I

conn.Execute("commit")


Does this help?

--

Joel BURTON  |  joel@joelburton.com  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant

Re: ODBC Performance

From
Fábio Sato
Date:
Joel Burton wrote:
>
> On Thu, 18 Oct 2001, [iso-8859-1] Fábio Sato wrote:
>
> > > > is very disappointing... I have a procedure that update 868 rows and it is
> > > > taking more than 5 minutes to complete.
> >
> > I think that the source code may be a better answer to your questions,
> > so this is basically what I'm doing:
> >
> > conn.Open "DSN=mydb;uid=username;pwd=mypassword"
>
> conn.Execute("begin")
>
> > For I = 0 To size - 1
> >   sql = "update mytable set value1 = " & array1(I) & ", value2 = " &
> > array2(I) & _
> >         ", value3 = " & array3(I) & ... & ", value20 = " & array20(I) &
> > -
> >         " where code = " & codes(I)
> >   conn.Execute(sql)
> > Next I
>
> conn.Execute("commit")
>
> Does this help?

No. I didn't change even the seconds... :(

Re: ODBC Performance

From
Hiroshi Inoue
Date:
F畸io Sato wrote:
>
> > > I'm using the ODBC Driver version 07_01_0007 with ADO and the performance
> > > is very disappointing... I have a procedure that update 868 rows and it is
> > > taking more than 5 minutes to complete.
> > >
> > > After some search on the mailing lists I've found similar problems but
> > > didn't find any answer to them. Can someone help me?
> >
> > Are you committing after each operation, or is the ODBC driver forcing
> > this?
> >
> > By "procedure", do you mean as PG-backend proc (in plpgsql or something
> > like that), or do you mean client-side procedure that calls PG 868 times?
> >
> > --
> >
> > Joel BURTON  |  joel@joelburton.com  |  joelburton.com  |  aim: wjoelburton
> > Independent Knowledge Management Consultant
>
> I think that the source code may be a better answer to your questions,
> so this is basically what I'm doing:
>

[snip]

>
> ' Then I open the connection and do the updates
>
> conn.Open "DSN=mydb;uid=username;pwd=mypassword"
>
> For I = 0 To size - 1
>   sql = "update mytable set value1 = " & array1(I) & ", value2 = " &
> array2(I) & _
>         ", value3 = " & array3(I) & ... & ", value20 = " & array20(I) &
> -
>         " where code = " & codes(I)
>   conn.Execute(sql)
> Next I
>

First aren't you turning on ODBC trace ?
Second, how does
  explain update mytable set value1 = ..,
                             value20 = ..
          where code = ..;
show using psql ?

regards,
Hiroshi Inoue

Re: ODBC Performance

From
"Andrea Aime"
Date:
Fábio Sato wrote:
>
> Joel Burton wrote:
> >
> > On Thu, 18 Oct 2001, [iso-8859-1] Fábio Sato wrote:
> >
> > > > > is very disappointing... I have a procedure that update 868 rows and it is
> > > > > taking more than 5 minutes to complete.
> > >
> > > I think that the source code may be a better answer to your questions,
> > > so this is basically what I'm doing:
> > >
> > > conn.Open "DSN=mydb;uid=username;pwd=mypassword"
> >
> > conn.Execute("begin")
> >
> > > For I = 0 To size - 1
> > >   sql = "update mytable set value1 = " & array1(I) & ", value2 = " &
> > > array2(I) & _
> > >         ", value3 = " & array3(I) & ... & ", value20 = " & array20(I) &
> > > -
> > >         " where code = " & codes(I)
> > >   conn.Execute(sql)
> > > Next I
> >
> > conn.Execute("commit")
> >
> > Does this help?
>
> No. I didn't change even the seconds... :(
>

Strange, I got much better timing on a similiar situation
with transactions... can you try this?

 Dim conn As New ADODB.Connection
  Dim com As New ADODB.Command
  Dim i As Long

  conn.Open "DSN=mydb;uid=username;pwd=mypassword"
  Set com.ActiveConnection = com

  conn.BeginTrans
  For i = 0 To Size - 1
    sql = "update mytable set value1 = " & array1(I) & ", value2 = " &
array2(I) & _
          ", value3 = " & array3(I) & ... & ", value20 = " & array20(I)
& _
          " where code = " & codes(I)
    com.CommandText = sql
    com.Execute adExecuteNoRecords + adCmdText
  Next i
  conn.CommitTrans

Also, have you updated to the latest ADO version (I'm using ADO 2.6)?
Is all logging disabled?
Hope this helps
Best regards
Andrea Aime