Re: Slow queries between VB and VFP
- From: "Ralph" <nt_consulting64@xxxxxxxxx>
- Date: Wed, 10 May 2006 11:18:37 -0500
<jens.kristoffersson@xxxxxxxxx> wrote in message
news:1147271250.745527.40310@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi,
Im building a program that queries a visual fox pro databases (free
table). The largest database has around 80k entries with around 100
fields in each. Also there are several users on this database using
visual fox pro.
Reason I use visual basic 6 is because it was the only development tool
that was availible. My program actually worked very well with DOA on
copies of the databases locally and over the network.
Sadly I had worked with my program some weeks and this problem was
noticed when I installed on the clients (should note, network
connection in my office is 100mbit while the clients in production only
has 10mbit, so thats a problem)
I got an advice to use a OLE DB provider or a ODBC instead of DOA, fair
enough. I downloaded from microsoft a driver with version 6.01.8629.01
and it "works".
The small code below I just use to test the connection (maybe it doesnt
work, since I edited it here some). Anyways, fetching a single post
like this takes 10-30s, now thats on my 100mbit network cable, takes
around 10 times as long on the other clients.
So, any tips? Can it be antivirus problems, routers (I know there is
some problems with the network here), the program itself
How can I limit the network traffic? Any workarounds? I recently
relearned VB so im not that good at it atm, any advice is good.
Does this have to do with free tables and my program fetches the entire
database? (Because no server to run queries on?)
Also I should mention, I can not redesign the database since its used,
this is more or less a "patch" program im working on until the entire
system is changed in 6 months or so.
Also I tried VISDATA which comes with visual basic 6, same slow queries
on that particular database (local or network copies of database works
well).
#####################################################################
Private Sub Command1_Click()
Dim ad As ADODB.Connection
Dim ar As ADODB.Recordset
Set ad = New ADODB.Connection
ad.CursorLocation = adUseClient
Let ad.ConnectionString = "Provider=VFPOLEDB.1;Data
Source=N:\DB;Password=;Collating Sequence=General"
ad.Open
Set ar = New ADODB.Recordset
ar.CursorLocation = ADODB.adUseClient
ar.LockType = ADODB.adLockBatchOptimistic
ar.CursorType = ADODB.adOpenStatic
ar.Open "SELECT CNAME FROM CUSTOMER WHERE CNR='" + Text1.Text + "'", ad
Form1.Caption = ar.Fields(0).Value
End Sub
"Does this have to do with free tables and my program fetches the entire
database? (Because no server to run queries on?)"
Yes.
xBase is just flat files. As such performance is directly linked to your
wire and how the file server is configured. You might check with your
adminstrators to see if 'share' capablities can be improved.
As Jan pointed out indexing will likely help. Also you might consider some
sort of 'replication' scheme where you don't have to go to the server or
'share' quite so often.
Another possiblility is CodeBase (http://www.codebase.com/products/windows/)
which offers a better and much faster 'engine'. May be too pricey for a 6
month solution.
-ralph
.
- References:
- Slow queries between VB and VFP
- From: jens . kristoffersson
- Slow queries between VB and VFP
- Prev by Date: Re: Oracle 9 and 10g and VB6 ADO virtual memory issue
- Next by Date: Re: Slow queries between VB and VFP
- Previous by thread: Re: Slow queries between VB and VFP
- Next by thread: Re: Selecting a Oracle CLOB field
- Index(es):
Relevant Pages
|
