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

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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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

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

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

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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> 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
>>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> 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
>>

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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>>> news:%(E-Mail Removed)...
>>>> 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
>

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

> 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