By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,491 Members | 3,132 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,491 IT Pros & Developers. It's quick & easy.

accessing VFP tables from VB.Net

P: n/a
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
Dec 19 '07 #1
Share this Question
Share on Google+
19 Replies


P: n/a
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
"cj" <cj@nospam.nospamwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
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

Dec 19 '07 #2

P: n/a
i dont think the oledbproviders use the foxpro indexes!
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
Dec 19 '07 #3

P: n/a
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.

Jay B wrote:
i dont think the oledbproviders use the foxpro indexes!
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
Dec 19 '07 #4

P: n/a
cj
Sorry, that didn't help any.

Anders Altberg wrote:
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
"cj" <cj@nospam.nospamwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
>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

Dec 19 '07 #5

P: n/a
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
"cj" <cj@nospam.nospamwrote in message
news:eg**************@TK2MSFTNGP02.phx.gbl...
Sorry, that didn't help any.

Anders Altberg wrote:
>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
"cj" <cj@nospam.nospamwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
>>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).TotalSecond s, 4).ToString)

DataGridView1.DataSource = dt
End Sub

End Class
Dec 19 '07 #6

P: n/a
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
"cj" <cj@nospam.nospamwrote in message
news:eg**************@TK2MSFTNGP02.phx.gbl...
Sorry, that didn't help any.

Anders Altberg wrote:
>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
"cj" <cj@nospam.nospamwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
>>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).TotalSecond s, 4).ToString)

DataGridView1.DataSource = dt
End Sub

End Class
Dec 19 '07 #7

P: n/a
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.
Anders Altberg wrote:
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
"cj" <cj@nospam.nospamwrote in message
news:eg**************@TK2MSFTNGP02.phx.gbl...
>Sorry, that didn't help any.

Anders Altberg wrote:
>>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
"cj" <cj@nospam.nospamwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl.. .
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
Dec 19 '07 #8

P: n/a
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.
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
Dec 19 '07 #9

P: n/a
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????
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
cj wrote:
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

Dec 19 '07 #10

P: n/a
On Wed, 19 Dec 2007 15:41:28 -0500, cj <cj@nospam.nospamwrote:
>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=N o;
Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETC H=NO;")
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
>
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
Dec 19 '07 #11

P: n/a
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" <cj@nospam.nospamwrote in message
news:ef**************@TK2MSFTNGP03.phx.gbl...
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

Dec 20 '07 #12

P: n/a

"Dan Freeman" <sp**@microsoft.comwrote in message
news:Op**************@TK2MSFTNGP05.phx.gbl...
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.
That's news to me. Now I'm glad I never got around to learning oledb.


Dec 20 '07 #13

P: n/a
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.
Jeroen van Kalken wrote:
On Wed, 19 Dec 2007 15:41:28 -0500, cj <cj@nospam.nospamwrote:
>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=N o;
Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETC H=NO;")

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
>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).TotalSecond s, 4).ToString)

DataGridView1.DataSource = dt
End Sub

End Class
Dec 20 '07 #14

P: n/a
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.

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

"cj" <cj@nospam.nospamwrote in message
news:ef**************@TK2MSFTNGP03.phx.gbl...
>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=N o;
Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETC H=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).TotalSecond s, 4).ToString)

DataGridView1.DataSource = dt
End Sub

End Class

Dec 20 '07 #15

P: n/a
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.
Dan Freeman wrote:
>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????

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
cj wrote:
>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=N o;
Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETC H=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).TotalSecond s, 4).ToString)

DataGridView1.DataSource = dt
End Sub

End Class

Dec 20 '07 #16

P: n/a
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" <cj@nospam.nospamwrote in message
news:u1**************@TK2MSFTNGP05.phx.gbl...
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

"cj" <cj@nospam.nospamwrote in message
news:ef**************@TK2MSFTNGP03.phx.gbl...
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

Dec 20 '07 #17

P: n/a
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.
swdev2 wrote:
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" <cj@nospam.nospamwrote in message
news:u1**************@TK2MSFTNGP05.phx.gbl...
>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

"cj" <cj@nospam.nospamwrote in message
news:ef**************@TK2MSFTNGP03.phx.gbl...
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=N o;
Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFE TCH=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).TotalSecond s, 4).ToString)
>
DataGridView1.DataSource = dt
End Sub
>
End Class

Dec 21 '07 #18

P: n/a
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" <cj@nospam.nospamwrote in message
news:uj**************@TK2MSFTNGP04.phx.gbl...
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.
swdev2 wrote:
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" <cj@nospam.nospamwrote in message
news:u1**************@TK2MSFTNGP05.phx.gbl...
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

"cj" <cj@nospam.nospamwrote in message
news:ef**************@TK2MSFTNGP03.phx.gbl...
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;BACKGROUNDFET CH=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

Dec 21 '07 #19

P: n/a
Hi cj,

I did not experience the slowness of oledb provider,
as I used it from php and it's win32::ole 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.
Dec 30 '07 #20

This discussion thread is closed

Replies have been disabled for this discussion.