hai All
I am very new to PostgreSql.
I have given the following code to VB 6. and it takes around 2 to 3 minutes to exicute with PostgreSQL back end. However, the same code work in a fraction of a second with MS Access back end . Kindly advise me on how to improve its perfomance with PostgrSQL 8.4.2
Option Explicit
Dim dbill As New ADODB.Connectio n
Dim mtlmast As New ADODB.Recordset
Dim rs As New ADODB.Recordset
Dim rss As New ADODB.Recordset
Dim cmd, mstr As String
Private Sub Form_Load()
cmd = "PROVIDER=Postg reSQL OLE DB Provider;" & _
"DATA SOURCE=192.168. 1.31;" & _
"LOCATION=maste r;" & _
"USER ID=Admin;" & _
"PASSWORD=pass; "
With dbill
.ConnectionStri ng = cmd
.Open
.CursorLocation = adUseClient
End With
List1.Visible = False
End Sub
Private Sub Text1_KeyDown(K eyCode As Integer, Shift As Integer)
If KeyCode = 114 Then
mstr = "select * from mtls_mast where mtls_mast.matco de LIKE '" & Trim(Text1.Text ) + "%'" & " order by 1;"
If rs.State <> adStateOpen Then
Else
rs.Close
End If
rs.Open mstr, dbill, adOpenKeyset, adLockOptimisti c
If rs.RecordCount > 0 Then
rs.MoveFirst
List1.Clear
Do While Not rs.EOF
List1.AddItem Left(rs.Fields( "matcode") + " ", 7) + Left(rs.Fields( "title") + Space(35), 35)
rs.MoveNext
Loop
List1.Visible = True
List1.SetFocus
End If
rs.Close
End If
End Sub
The table mtls_mast has 1809 records. when press F3 simply in a blank text box it take around 2 to 3 Minute. I use Windows XP SP 3. when using the select command from psql it works very well.
Expalin analyse select * from mtls_mast order by 1;
gives the follwing
index scan using mtls_mast_pkey on mtls_mast (cost =0.00..110.39 rows=1809 width=179) actual time =0.030..0.977 rows=1809 loops=1)
total run time=1.371 ms
The above code is part of programme deveped with MS Access 2000 as back end. Now I like to change the back end to postgresql 8.4.2 server. while inserting the data into postgresql it was fast enough. the table has a primary key index on feild matcode. this field is char (6) not null type.no other index.no update/ delete done on the table. However, have droped the table many time and recreated it. I assume that I have furnished all the information I know. Any further shall be given if asked for.
Please Help
Thanks in advance
Shajimon
I am very new to PostgreSql.
I have given the following code to VB 6. and it takes around 2 to 3 minutes to exicute with PostgreSQL back end. However, the same code work in a fraction of a second with MS Access back end . Kindly advise me on how to improve its perfomance with PostgrSQL 8.4.2
Option Explicit
Dim dbill As New ADODB.Connectio n
Dim mtlmast As New ADODB.Recordset
Dim rs As New ADODB.Recordset
Dim rss As New ADODB.Recordset
Dim cmd, mstr As String
Private Sub Form_Load()
cmd = "PROVIDER=Postg reSQL OLE DB Provider;" & _
"DATA SOURCE=192.168. 1.31;" & _
"LOCATION=maste r;" & _
"USER ID=Admin;" & _
"PASSWORD=pass; "
With dbill
.ConnectionStri ng = cmd
.Open
.CursorLocation = adUseClient
End With
List1.Visible = False
End Sub
Private Sub Text1_KeyDown(K eyCode As Integer, Shift As Integer)
If KeyCode = 114 Then
mstr = "select * from mtls_mast where mtls_mast.matco de LIKE '" & Trim(Text1.Text ) + "%'" & " order by 1;"
If rs.State <> adStateOpen Then
Else
rs.Close
End If
rs.Open mstr, dbill, adOpenKeyset, adLockOptimisti c
If rs.RecordCount > 0 Then
rs.MoveFirst
List1.Clear
Do While Not rs.EOF
List1.AddItem Left(rs.Fields( "matcode") + " ", 7) + Left(rs.Fields( "title") + Space(35), 35)
rs.MoveNext
Loop
List1.Visible = True
List1.SetFocus
End If
rs.Close
End If
End Sub
The table mtls_mast has 1809 records. when press F3 simply in a blank text box it take around 2 to 3 Minute. I use Windows XP SP 3. when using the select command from psql it works very well.
Expalin analyse select * from mtls_mast order by 1;
gives the follwing
index scan using mtls_mast_pkey on mtls_mast (cost =0.00..110.39 rows=1809 width=179) actual time =0.030..0.977 rows=1809 loops=1)
total run time=1.371 ms
The above code is part of programme deveped with MS Access 2000 as back end. Now I like to change the back end to postgresql 8.4.2 server. while inserting the data into postgresql it was fast enough. the table has a primary key index on feild matcode. this field is char (6) not null type.no other index.no update/ delete done on the table. However, have droped the table many time and recreated it. I assume that I have furnished all the information I know. Any further shall be given if asked for.
Please Help
Thanks in advance
Shajimon