Обсуждение: ODBC Performance
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...
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
> > 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
---
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
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... :(
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
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