Which one is faster, cursors, queries, local views, other?

Hello,

I'm using VFP 7 on a very old machine. I'm working with very large tables and I need to create a lot of queries, but my tables are so large it takes forever to load the results.

I've been creating cursors on the run from SELECT statements but it's still very slow....

What can I do to optimize my queries/views so they are much faster... I'm new to this, so I don't know much... I just need to be able to run all this queries without freezing my pc..

Are cursors a good option should I change my indexes

Thank you so much

Answer this question

Which one is faster, cursors, queries, local views, other?

Meghana
Whether Xbase code is faster than SQL code depends on the specific situation.

For sure, your code would be faster is you replaced the macros (&) with name expressions. Also, it's not a good idea to refer to workareas by letter; use the alias instead, so you don't have to worry about specific workareas.

Tamar
AdrianGodong

For me i will use this command instead of using SQL commands

i will crearte a temp tables

tempinv = "c"+substr(time(),4,2)+substr(time(),7,2)

sele a

use invoice excl && let said this is the large files

index on invoice_no to invoice.idx

set index to invoice.idx

copy stru to &tempinv

sele b

use &tempinv shared

index on invoice_no to invoice2.idx

set index to invoice2.idx

sele a

set index to invoice.idx

go top

do while !eof()

do condition

or seek the invoice or maybe other criteria tat u wan to temporary table

its more faster than SQL commands

seek its a powerful searching functions compare to scan or locate.

sele a

skip

enddo

hope i can help u.
DracoWiz

Hi

The general rule, when setting up indexes for tables is index the primary key field(s), any fields that are used directly in the WHERE clause and to add an index on DELETED() (especially if there are no deleted records in the tables).

However, if your tables are very large (sevarl millions of records) even with good indexes queries can take a long time because VFP operates on the local machine and so has to bring the data over the network to the local machine before it can do anything else. Obviously this can slow things down.

Another option is, therefore, to run the queries on a local copy of the system data (unless you actually require real-time data) - especially if this is historical analysis - where you can get away with refreshing the data occasionally (depends on how uoften the data changes of course).
ahmed1979

yes tamar i agree wit u.

select workareas onli limited from A until J.
Mark Remkiewcz
Try using SET KEY TO command, you will get the results instantly !!!

SELECT 0
USE myTable
SET ORDER TO TAG myIndex
SET KEY TO lcSearchKey

SELECT * FROM myTable WHERE myKey = lcSearchKey
Marc Selis
Large tables - how large are they

Check your indexes, design of tables, when using views where parameters and fetching etc. Depending on what you're doing you might not need SQL select cursors,views and seek, follwed by xbase commands might work much better.
SunHunter
400K records in a networked env is not that bad. We have large databases with large row sizes (from bad design in the first place) and queries against 1 mill recs takes very little time, certainly not 2 minutes. Make sure that your indexes are correct and make sure that they are not corrupted.
Bruce L-C MVP

But how abt in network environment i have 400,000 records its takes 2 mins to process my output.

Thanks in advance.
matchoo

Yes, Start by reviewing your indexes, joins and where criteria.

Dave
Which one is faster, cursors, queries, local views, other?

Answer this question