Re: Slow queries between VB and VFP
- From: Jan Hyde <StellaDrinker@xxxxxxxxxxxxxxxxxxx>
- Date: Wed, 10 May 2006 15:40:04 +0100
jens.kristoffersson@xxxxxxxxx's wild thoughts were released
on 10 May 2006 07:27:30 -0700 bearing the following fruit:
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
Is 'CNR' and indexed field?
If your query doesn't utilise an index on that table and
that table contains a lot of records then it will take time.
Jan Hyde (VB MVP)
--
"I'm going to kill Dracula," said Tom painstakingly. (Richard Lederer)
.
- Follow-Ups:
- Re: Slow queries between VB and VFP
- From: jens . kristoffersson
- Re: Slow queries between VB and VFP
- References:
- Slow queries between VB and VFP
- From: jens . kristoffersson
- Slow queries between VB and VFP
- Prev by Date: Slow queries between VB and VFP
- Next by Date: Re: Oracle 9 and 10g and VB6 ADO virtual memory issue
- Previous by thread: Slow queries between VB and VFP
- Next by thread: Re: Slow queries between VB and VFP
- Index(es):
Relevant Pages
|
