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
0 1647 Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Mage |
last post by:
Hello,
I started to write my PostgreSQL layer. I tried pyPgSQL and PyGreSQL. I
made a *very minimal* performance test and comparsion with the same
thing in php. Table "movie" has 129 record and many fields.
I found PyGreSQL / DB-API / fetchall horrible slow (32 sec in my test).
PHP did 13 secs and it gave the result in associative array. Maybe I did
something bad.
|
by: John H |
last post by:
Can anyone help with why our pages are slow to load during peak-use
hours? We run a database site getting 4 million hits a month which
becomes painfully slow during peak hours.
The slowness is not reflected in Coldfusion's debug output; for
example the total run-time of 5 heavy queries is typically 1000 ms,
but during peak times the page does not load for 4-10 seconds.
Processor activity at peak times is "busy, but not flat-out"...
|
by: Joshua D. Drake |
last post by:
Hello,
I think the below just about says it all:
http://www.commandprompt.com/images/mammoth_versus_dolphin_500.jpg
Sincerely,
Joshua Drake
|
by: My Internet |
last post by:
Hello,
I am in the process to define if our product can use PostgreSQL.
Do you know what type of application use PostgreSQL, and also what is the
size of the database for these projects?
Our application has a table with more than 300000 rows. There are complexe
query with many joins. And we must respect some time constraints. Also the
application is running 24 hours per day, so the product must be stable, with
|
by: cefrancke |
last post by:
Are there any ways to speed up a Cumulative sum in a query or perhaps
another faster way to have a cumulative sum column (call a vba
function?).
For example, I want to sum up all values under 'Amount', and also have
a field call 'Cumulative' and have the sum grow with each record. Using
"MyTable", the query results are...
ID or Date Amount Cumulative
--------------- ------ ----------
| |
by: Marc Castrechini |
last post by:
Forgive me on the lack of specifics but I am not a "server" guy.
We just rolled our code out to a 2003 Server from Win2k Server and for some
reason our pages are painfully slow. It appears the pages process fine (by
spinning globe and 100% status bar. But then there is a dead wait time while
it must be processing the request.redirect.
Server 2003
IIS 6.0
Framework 1.1.4322.573
|
by: Jean-Michel POURE |
last post by:
Dear friends,
I am currently testing Ulogd ip traffic logging system with PostgreSQL. It
works in conjunction with GNU/Linux iptables.
The Ulogd project can be found here:
http://gnumonks.org/gnumonks/projects/project_details?p_id=1
(If you try the project, fetch it from CVS as the PostgreSQL code was broken
until today.)
|
by: DEHAINSALA Hondjack |
last post by:
Hi !
sorry for my english ! I am french :-)
I want to test postgreSQL v8 native Windows! Where i can get a version which
allows more than 64 characters (NAMEDATALEN ~=128) to the name of tables and
columns ?
I generate automaticly tables and the length of some of these tables is > to
64.
|
by: Crash |
last post by:
Is there any sort of query engine for use with .NET datasets? I have
an application that has a very small disconnected client database - 2
dozen tables, largest table @100 rows. If loaded into a dataset and
then saved to disk the absolute worst case file wouldn't even be 10
meg...
However, to present data to the user, the application would need to
issue a very complicated select statement; inner & outer joining most
of the tables,...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |