Hi,

Can any one help me on the below....

I am using Visual Foxpro as backed an Vb.Net as the front end...

I am using the oleddb connection..... the following is the parameter....

"Provider=vfpoledb.1;Data Source= \\Data; Collating Sequence=general"

I have a Visual Foxpro open table dabase which contains nearly a million record.... When I give

select * from transcation where id='001' and cust='03'

the time taken to purge this record is very high (nearly 5 min).... I have indexed the table on the basis of the above too (id, cust) feild. The purging of record is done thru front end vb.Net OleDB adapter

Can any one tell me how to use the indexes which is creted in foxpro to return the record..... I used Locate or seek it gives the (record pointer or the index number) this is done in VFP , but I need the record to be returned... that to from the front end VB.Net...

1.Is it possible to use (Seek/Locate) to return table value.... using vb.Net code

2.Is it possible to use Indexes created in backed.... for our faster search.... if so how.... using VB.Net code

are there any other method to purge.... one or more data from a million data in foxpro

3. If it can be from a procedure in foxpro or by a class from a foxpro..... give me some idea on this line also

Note: The worst case is that I am not in the situation to migrate to anothere database becase of various problem....

Parameswaran.N

What do you mean by "purging" As far as I know purging is about deleting and I don't see a command there for deletion.

Why do you use collating sequence in your connection. It might be a reason to get slower responses. At least when an index is not done with default "machine" collation then index keys are twice as large.

1) Yes you could use seek() but internally VFP would choose the index(es) for your query if there is one.

2) VFP automatically uses the indexes. Use select/update/insert/delete SQL commands.

3) Yes it can also be done from a stored procedure or a COM dll.

PS: Within a million records table if that query is taking 5 mins. then you should also consider other factors like network hardware.

Thanks

I am sorry... I meant "purging" as selecting/geting one are more records/cursor.... not deleting.....

If I don't use... collating sequence then also it hppens the same.....

My question was

1) how I colud use the Inexedes how can I use the index using VB.NET code....

2) I was asking can we use the index using vb.Net programatical code..... not the VFP use the index(es ) of its own...

3)Can you give me an example.... on store procedure... or a COMdll

I understand the network factor... but that is very much negligble..... If I open that in the runtime of VFP i am able to get the record much faster... but when I am using the VB.net code it takes a very high time.....

Please I need some sample codes to solve this problem..........

Parameswaran.N

I mean do not try to use indexes directly on your own from within your VB.Net code. It's not a trivial task outside VFP if you're not also a seasoned VFP developer. Giving few lines of code samples wouldn't help you much and worse would draw your attention to a wrong path (and yet another reason I can't write VB.Net code but C#).

Stored procedures and COM dll as I said are doable but that doesn't mean I advise you to go that path. I could give you samples but do you know VFP to create stored procedures and COM dll For stored procedures you could check VFP9's sample NorthWind.dbc. It has 5 usefull stored procedure as a sample (and check code using them from .Net in earlier messages - C# code).

If it's slower in .Net only then I think it might be that you're retrieving a result set with a huge record count. If .Net cannot fit result in memory it'd need a disk swap which in turn makes it very slow. Replace your "=" operator in code with "==" operator and retry. Check rows.Count

Here is a test I did way back in .Net 1.1 days:

Select some records and process them to create an XML with string operations only (result was 55K records selected from 1million+ records). VFP did it in 7 secs. C# best result was 18secs. (tried with most effective and hardcoded codes like using ordinates, stringbuilder or direct filestream etc.). Using a dataset is the worst case since it also needs to allocate and preserve a DS structure in memory (and DS usage is common among .Net developers even when it's not needed at all).

Try setting collate to 'Machine' instead of 'General' and see if that makes a difference.

If not, open up VFP and check to make sure there is an index on id and and index on cust and make sure the collation is consistant (either Machine or General) If you are only returning a few records, it's not .net that is slowing you down, it's the indexes as the others have pointed out above.

Greg Gum

In index I have created as follows

Index Name Type Expression Collate

CompDivof Regular id+cust Machine

This is what I have created in VFP

I don't think that is what you want, I think you want one for each:

Index Name Type Expression Collate

ID regular ID GENERAL

CUST regular CUST GENERAL

Thus there are two indexes and VFP will use them both in the query. Also remember to change the collation to GENERAL in your connection string.

Let me know how it goes!

Greg Gum