accessing VFP tables from VB.Net

C

cj

I'm getting terrible response times trying to pull data from VFP tables
using .net--like 2 minutes! Can someone help?

f:\arcust01 currently contains 187,728 records and is indexed on CUSTNO
i:\btn currently contains 5,999,657 records and is indexed on BTN


Imports System.Data.OleDb

Public Class Form1
Dim myOleDbConnection As OleDbConnection
Dim myOleDbCommand As OleDbCommand
Dim myOleDbDataAdapter As OleDbDataAdapter

Dim dt As New DataTable

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim starttime As DateTime

myOleDbConnection = New
OleDbConnection("Provider=vfpoledb.1;Data Source=i:\;Collating
Sequence=general")
myOleDbCommand = New OleDbCommand
myOleDbDataAdapter = New OleDbDataAdapter

Dim fields, from As String

fields = "select btn.btn, arcust01.lastpay, arcust01.balance "
from = "from i:\btn, f:\arcust01 where btn.btn =
arcust01.custno and btn.btn = '9128675309'"

myOleDbCommand.CommandText = fields & from
myOleDbCommand.Connection = myOleDbConnection

myOleDbDataAdapter.SelectCommand = myOleDbCommand

starttime = Now()
myOleDbDataAdapter.Fill(dt)

MessageBox.Show("done! " &
Math.Round(Now.Subtract(starttime).TotalSeconds, 4).ToString)

DataGridView1.DataSource = dt
End Sub

End Class
 
A

Anders Altberg

Try
from = "from i:\btn JOIN f:\arcust01 ON btn.btn =
arcust01.custno WHERE btn.btn = '9128675309'"

or
from = "from i:\btn JOIN f:\arcust01 ON btn.btn = arcust01.custno
WHERE btn.btn = '9128675309'"

Make sure it comes out as one line.
-Anders
 
C

cj

I'm pretty sure they do but I don't know. Anyway, I darn sure hope I
can get response time down to a second or two instead of 2 minutes one
way or another.
 
C

cj

Sorry, that didn't help any.

Anders said:
Try
from = "from i:\btn JOIN f:\arcust01 ON btn.btn =
arcust01.custno WHERE btn.btn = '9128675309'"

or
from = "from i:\btn JOIN f:\arcust01 ON btn.btn = arcust01.custno
WHERE btn.btn = '9128675309'"

Make sure it comes out as one line.
-Anders
Click to expand...
 
A

Anders Altberg

That may be but it helps you to write SQL queries with the SQL92 standard,
implemented in VFP since version 5:
Do you know the exact indexing expression? You have to use them as they're
written.
If it's
INDEX ON UPPER(column1) TAG whatever
you have to use WHERE UPPER(column1)= something. That expression is how VFP
finds a suitable index to use. WHERE column1='AAA' will not optimize.

-Anders
 
A

Anders Altberg

That may be but it helps you to write SQL queries with the SQL92 standard,
implemented in VFP since version 5:
Do you know the exact indexing expression? You have to use them as they're
written.
If it's
INDEX ON UPPER(column1) TAG whatever
you have to use WHERE UPPER(column1)= something. That expression is how VFP
finds a suitable index to use. WHERE column1='AAA' will not optimize.

-Anders
 
C

cj

My tags are both simple. As shown in VFP:
BTN &&BTN
CUSTNO &&CUSTNO

But I have heard what your saying and it will come up with some tags in
the future.
 
C

cj

Maybe Jay B was onto something.

Can someone please, please explain why when I switched all my oledb
commands to odbc commands with the connection string

myOdbcConnection = New OdbcConnection("Driver={Microsoft Visual FoxPro
Driver};SourceType=DBF;SourceDB=c:\;Exclusive=No;
Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;")

I now have a return time of

.09 SECONDS!!!!

Why would anyone say oledb is better after seeing what I've seen????

What the heck is going on?????????????

Microsoft please respond to this one too. I want to know why when I
keep hearing OLEDB is supposed to be good and ODBC is old can I get the
results I've seen.
 
D

Dan Freeman

Microsoft please respond to this one too.

This is a peer support group. Microsoft does not officially monitor it.
Why would anyone say oledb is better after seeing what I've seen????
Click to expand...

There are those of us who WON'T and DON'T say that.

Microsoft wants you to believe OLEDB is better for two reasons: 1) they own
it and 2) it ties you to the Microsoft platform. ODBC, on the other hand, is
an open standard originally brought forth by IBM and is platform-agnostic.

The real problem with ODBC and VFP is that the VFP ODBC driver hasn't been
updated since VFP6 so any of the newer data-related features will not be
supported and you may (note I said MAY) effectively be locked out of your
data. If none of the newer features have been used, there's no reason not to
use ODBC vs. OLEDB unless you believe the Microsoft marketing machine.

Dan
 
J

Jeroen van Kalken

Can someone please, please explain why when I switched all my oledb
commands to odbc commands with the connection string

myOdbcConnection = New OdbcConnection("Driver={Microsoft Visual FoxPro
Driver};SourceType=DBF;SourceDB=c:\;Exclusive=No;
Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;")
Click to expand...

I think this is the problem: Collate=Machine;
If the Index is built using a different collate as is currentlyin use,
then the index will not be used. So check the oleDB collate-setting
 
S

swdev2

On that o that -
yer not reading data on the network anymore, right ?
yer pointing yer odbc sources to the C drive -
you had yer oledb sources pointed to network shares I and F, as I recall.

SLAN ! [Bill]
 
P

Paul Pedersen

Dan Freeman said:
Microsoft wants you to believe OLEDB is better for two reasons: 1) they
own it and 2) it ties you to the Microsoft platform. ODBC, on the other
hand, is an open standard originally brought forth by IBM and is
platform-agnostic.
Click to expand...

That's news to me. Now I'm glad I never got around to learning oledb.
 
C

cj

While you are correct that this code sample uses collate=machine I had
tried the oledb driver using collate=machine and general and gotten the
same results. Sorry for the confusion, it's hard to keep my examples
really consistent on here.
 
C

cj

Negative. I tested on C. and while it works faster it took 45 seconds.
I went back to the network drives as I felt it should it should, and
using odbc does, take less than a second from the network drives.
On that o that -
yer not reading data on the network anymore, right ?
yer pointing yer odbc sources to the C drive -
you had yer oledb sources pointed to network shares I and F, as I recall.

SLAN ! [Bill]
--
===================
William Sanders / EFG VFP / mySql / MS-SQL
www.efgroup.net/vfpwebhosting
www.terrafox.net www.viasqlserver.net

cj said:
Maybe Jay B was onto something.

Can someone please, please explain why when I switched all my oledb
commands to odbc commands with the connection string

myOdbcConnection = New OdbcConnection("Driver={Microsoft Visual FoxPro
Driver};SourceType=DBF;SourceDB=c:\;Exclusive=No;
Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;")

I now have a return time of

.09 SECONDS!!!!

Why would anyone say oledb is better after seeing what I've seen????

What the heck is going on?????????????

Microsoft please respond to this one too. I want to know why when I
keep hearing OLEDB is supposed to be good and ODBC is old can I get the
results I've seen.
Click to expand...
Click to expand...
 
C

cj

Thanks Dan, P.S. MS should comment as this is also posted on
microsoft.public.dotnet.languages.vb and I have a MSDN subscription.
I'm not sure how all this works but I see all these post on
microsoft.public.dotnet.languages.vb too. All I know is I email to both
newsgroups.
 
S

swdev2

Alrighty then.
In any event - I'm glad you switched over to odbc.
Good Luck with your project.
Regards [Bill]
--
===================
William Sanders / EFG VFP / mySql / MS-SQL
www.efgroup.net/vfpwebhosting
www.terrafox.net www.viasqlserver.net

cj said:
Negative. I tested on C. and while it works faster it took 45 seconds.
I went back to the network drives as I felt it should it should, and
using odbc does, take less than a second from the network drives.
On that o that -
yer not reading data on the network anymore, right ?
yer pointing yer odbc sources to the C drive -
you had yer oledb sources pointed to network shares I and F, as I recall.

SLAN ! [Bill]
--
===================
William Sanders / EFG VFP / mySql / MS-SQL
www.efgroup.net/vfpwebhosting
www.terrafox.net www.viasqlserver.net

cj said:
Maybe Jay B was onto something.

Can someone please, please explain why when I switched all my oledb
commands to odbc commands with the connection string

myOdbcConnection = New OdbcConnection("Driver={Microsoft Visual FoxPro
Driver};SourceType=DBF;SourceDB=c:\;Exclusive=No;
Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;")

I now have a return time of

.09 SECONDS!!!!

Why would anyone say oledb is better after seeing what I've seen????

What the heck is going on?????????????

Microsoft please respond to this one too. I want to know why when I
keep hearing OLEDB is supposed to be good and ODBC is old can I get the
results I've seen.


cj wrote:
I'm getting terrible response times trying to pull data from VFP tables
using .net--like 2 minutes! Can someone help?

f:\arcust01 currently contains 187,728 records and is indexed on CUSTNO
i:\btn currently contains 5,999,657 records and is indexed on BTN


Imports System.Data.OleDb

Public Class Form1
Dim myOleDbConnection As OleDbConnection
Dim myOleDbCommand As OleDbCommand
Dim myOleDbDataAdapter As OleDbDataAdapter

Dim dt As New DataTable

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim starttime As DateTime

myOleDbConnection = New
OleDbConnection("Provider=vfpoledb.1;Data Source=i:\;Collating
Sequence=general")
myOleDbCommand = New OleDbCommand
myOleDbDataAdapter = New OleDbDataAdapter

Dim fields, from As String

fields = "select btn.btn, arcust01.lastpay, arcust01.balance "
from = "from i:\btn, f:\arcust01 where btn.btn = arcust01.custno
and btn.btn = '9128675309'"

myOleDbCommand.CommandText = fields & from
myOleDbCommand.Connection = myOleDbConnection

myOleDbDataAdapter.SelectCommand = myOleDbCommand

starttime = Now()
myOleDbDataAdapter.Fill(dt)

MessageBox.Show("done! " &
Math.Round(Now.Subtract(starttime).TotalSeconds, 4).ToString)

DataGridView1.DataSource = dt
End Sub

End Class
Click to expand...
Click to expand...
Click to expand...
 
C

cj

Thanks, but this is just the tip of my iceburg. Given the way things
are going I'll be back. Next test is to determine if odbc will write to
the index when it adds a new record to the vfp tables. I'm off till
12/26 now but when I get back I'm going to have to do some testing on
that. I have lots of different queries I'll need to figure out in the
future so I need to be real comfortable with accessing these foxpro
tables from VB. Then I'll need to pull from SQL as well. I doubt I'll
be able to do anything like join a foxpro table to a sql table but
however it needs to be done.


Nothing is off the table. I've been told I should look to use VB or C#
2008 and I'm sure any new features they might have.

Alrighty then.
In any event - I'm glad you switched over to odbc.
Good Luck with your project.
Regards [Bill]
--
===================
William Sanders / EFG VFP / mySql / MS-SQL
www.efgroup.net/vfpwebhosting
www.terrafox.net www.viasqlserver.net

cj said:
Negative. I tested on C. and while it works faster it took 45 seconds.
I went back to the network drives as I felt it should it should, and
using odbc does, take less than a second from the network drives.
On that o that -
yer not reading data on the network anymore, right ?
yer pointing yer odbc sources to the C drive -
you had yer oledb sources pointed to network shares I and F, as I recall.
SLAN ! [Bill]
--
===================
William Sanders / EFG VFP / mySql / MS-SQL
www.efgroup.net/vfpwebhosting
www.terrafox.net www.viasqlserver.net

Maybe Jay B was onto something.

Can someone please, please explain why when I switched all my oledb
commands to odbc commands with the connection string

myOdbcConnection = New OdbcConnection("Driver={Microsoft Visual FoxPro
Driver};SourceType=DBF;SourceDB=c:\;Exclusive=No;
Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;")

I now have a return time of

.09 SECONDS!!!!

Why would anyone say oledb is better after seeing what I've seen????

What the heck is going on?????????????

Microsoft please respond to this one too. I want to know why when I
keep hearing OLEDB is supposed to be good and ODBC is old can I get the
results I've seen.


cj wrote:
I'm getting terrible response times trying to pull data from VFP tables
using .net--like 2 minutes! Can someone help?

f:\arcust01 currently contains 187,728 records and is indexed on CUSTNO
i:\btn currently contains 5,999,657 records and is indexed on BTN


Imports System.Data.OleDb

Public Class Form1
Dim myOleDbConnection As OleDbConnection
Dim myOleDbCommand As OleDbCommand
Dim myOleDbDataAdapter As OleDbDataAdapter

Dim dt As New DataTable

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim starttime As DateTime

myOleDbConnection = New
OleDbConnection("Provider=vfpoledb.1;Data Source=i:\;Collating
Sequence=general")
myOleDbCommand = New OleDbCommand
myOleDbDataAdapter = New OleDbDataAdapter

Dim fields, from As String

fields = "select btn.btn, arcust01.lastpay, arcust01.balance "
from = "from i:\btn, f:\arcust01 where btn.btn = arcust01.custno
and btn.btn = '9128675309'"

myOleDbCommand.CommandText = fields & from
myOleDbCommand.Connection = myOleDbConnection

myOleDbDataAdapter.SelectCommand = myOleDbCommand

starttime = Now()
myOleDbDataAdapter.Fill(dt)

MessageBox.Show("done! " &
Math.Round(Now.Subtract(starttime).TotalSeconds, 4).ToString)

DataGridView1.DataSource = dt
End Sub

End Class
Click to expand...
Click to expand...
Click to expand...
 
S

swdev2

Uhm - ya know -
you COULD get into the VFP v9 IDE as well.
Once you get up to speed, you'll be writing more stuff in less time.

As to joining a sql table to a fox table -
in VFP - there's a coupla ways to do that .
Without VFP - you can also load up a hook to VFP tables inside o SQL
Server -
then use a combination of DTS and parameterized SQL Stored procedures to
return result sets / cursors -
regardless of the front end .

Regards [Bill]

--
===================
William Sanders / EFG VFP / mySql / MS-SQL
www.efgroup.net/vfpwebhosting
www.terrafox.net www.viasqlserver.net

cj said:
Thanks, but this is just the tip of my iceburg. Given the way things
are going I'll be back. Next test is to determine if odbc will write to
the index when it adds a new record to the vfp tables. I'm off till
12/26 now but when I get back I'm going to have to do some testing on
that. I have lots of different queries I'll need to figure out in the
future so I need to be real comfortable with accessing these foxpro
tables from VB. Then I'll need to pull from SQL as well. I doubt I'll
be able to do anything like join a foxpro table to a sql table but
however it needs to be done.


Nothing is off the table. I've been told I should look to use VB or C#
2008 and I'm sure any new features they might have.

Alrighty then.
In any event - I'm glad you switched over to odbc.
Good Luck with your project.
Regards [Bill]
--
===================
William Sanders / EFG VFP / mySql / MS-SQL
www.efgroup.net/vfpwebhosting
www.terrafox.net www.viasqlserver.net

cj said:
Negative. I tested on C. and while it works faster it took 45 seconds.
I went back to the network drives as I felt it should it should, and
using odbc does, take less than a second from the network drives.

swdev2 wrote:
On that o that -
yer not reading data on the network anymore, right ?
yer pointing yer odbc sources to the C drive -
you had yer oledb sources pointed to network shares I and F, as I recall.
SLAN ! [Bill]
--
===================
William Sanders / EFG VFP / mySql / MS-SQL
www.efgroup.net/vfpwebhosting
www.terrafox.net www.viasqlserver.net

Maybe Jay B was onto something.

Can someone please, please explain why when I switched all my oledb
commands to odbc commands with the connection string

myOdbcConnection = New OdbcConnection("Driver={Microsoft Visual FoxPro
Driver};SourceType=DBF;SourceDB=c:\;Exclusive=No;
Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;")

I now have a return time of

.09 SECONDS!!!!

Why would anyone say oledb is better after seeing what I've seen????

What the heck is going on?????????????

Microsoft please respond to this one too. I want to know why when I
keep hearing OLEDB is supposed to be good and ODBC is old can I get the
results I've seen.


cj wrote:
I'm getting terrible response times trying to pull data from VFP tables
using .net--like 2 minutes! Can someone help?

f:\arcust01 currently contains 187,728 records and is indexed on CUSTNO
i:\btn currently contains 5,999,657 records and is indexed on BTN


Imports System.Data.OleDb

Public Class Form1
Dim myOleDbConnection As OleDbConnection
Dim myOleDbCommand As OleDbCommand
Dim myOleDbDataAdapter As OleDbDataAdapter

Dim dt As New DataTable

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim starttime As DateTime

myOleDbConnection = New
OleDbConnection("Provider=vfpoledb.1;Data Source=i:\;Collating
Sequence=general")
myOleDbCommand = New OleDbCommand
myOleDbDataAdapter = New OleDbDataAdapter

Dim fields, from As String

fields = "select btn.btn, arcust01.lastpay, arcust01.balance "
from = "from i:\btn, f:\arcust01 where btn.btn = arcust01.custno
and btn.btn = '9128675309'"

myOleDbCommand.CommandText = fields & from
myOleDbCommand.Connection = myOleDbConnection

myOleDbDataAdapter.SelectCommand = myOleDbCommand

starttime = Now()
myOleDbDataAdapter.Fill(dt)

MessageBox.Show("done! " &
Math.Round(Now.Subtract(starttime).TotalSeconds, 4).ToString)

DataGridView1.DataSource = dt
End Sub

End Class
Click to expand...
Click to expand...
Click to expand...
 
O

Olaf Doschke

Hi cj,

I did not experience the slowness of oledb provider,
as I used it from php and it's win32::blush:le module by
using the adodb.connection and adodb.recordset.

You are working with a connection to a path, which
will be interpreted as a free table directory. Are these
really free tables?

Do you have idx indexes or cdx?

Bye, Olaf.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

need join help 2
Object reference not set to an instance of an object (asp.net, c#) 11

Top