How can I speed up "SELECT" Statements from VB to Foxpro 6?

[jmueller]

Anyone, I'm programming in VB 6. I'm retrieving data from A Visual Foxpro 6 .DBC. I have 3 different select statements happening on dbfs with over a million records. 2 of these select statements finish instantaneously but the third one takes several minutes to complete. From what i've been able to gather, the two select stmts that finish quickly have "Where clauses" that matches an Index on the DBF, however, the third select statement which takes several minutes to complete is simply the following:

Select Max(UniqueId) from ldudetail.dbf

Can anyone tell me what I can do speed this select statement up??

Thanks!
Jack.

 

[jimstarr]

Hi Jack,

Index the table on UniqueId. If this is already done, I suspect network problems. The query should be very fast.

Jim

 

[jmueller]

Thanks for responding Jim. Unfortunately I do have an index on UniqueId already... Do you or anyone know of any alternative commands (in place of Max()) I might be able to use? I don't think the max() function is taking advantage of the index. Thanks Again.

 

[jimstarr]

Max() isn't the problem. I've used it and gotten instantaneous results with large files.

Make sure that the index is on UniqueId EXACTLY, not something like UPPER(UniqueId), or VAL(UniqueId).

Jim

 

[BlindPete]

You might try the TOP1 clause instead but you have to use the ORDER BY clause as well. Something like...

SELECT TOP 1 UniqueId FROM ldudetail ORDER BY UniqueId

Although I concur that I have not had these issues with MAX()
-Pete

 

[petervfp]

If you have this field indexed, then skip the SQL and just do this:

SELECT tablename
GO BOTTOM
MaxVal=ldudetail.UniqueId

If it's a DESCENDING index then substitute GO TOP for GO BOTTOM. This should be instantaneous.

Peter

 

[ramani]

HI

GO BOTTOM IN IduDetail
MaxVal=ldudetail.UniqueId

There is no need to use SQL. But the dbf shall have the order set to UniqueId. If not, capture the ORDER , then set it to UniqueId, and after the value obtained, set back the order.

Hope this helps you
ramani :-9
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com
LET KNOW IF THIS HELPED. ENOUGH EXPERTS ARE HERE TO HELP YOU OUT! BEST OF LUCK