Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
7
Erster Beitrag:
vor 5 Jahren
Letzter Beitrag:
vor 5 Jahren
Beteiligte Autoren:
Yogi Yang, DerekT, Piet van Zanten, Tor-Bjarne

Which is faster Navigating in Query or For Each?

Startbeitrag von Yogi Yang am 02.07.2013 13:59

I am experimenting and cannot decide as to which method to use for accessing data.

I have got a file with around 40K records.

I need to know which is faster method of accessing data.

I am currently using:
FOR EACH
//Do something here
END

Or should I use:
SQLFirst()
WHILE NOT SQL.Out
//Do something here
SQLNext()
END

I would like to know which is faster in case of very large data files?

If any one has tried this out and can share their findings it would be great.

TIA

Yogi Yang

Antworten:

Yogi
If you are looking at every record in the file then a FOR EACH filname would be fine.

If you only want a subset of records then define a query to fetch the required data and the do a FOR EACH queryame

WD treats both files and queries the same - these can be interchanged wherever you see the directive file

von DerekT - am 03.07.2013 17:46
DerekT,

On experimenting with SQLFirst-SQLNext and For Each I could not see any speed visible different in speed. But this can probably be because I just have around 40K rows in the DB that I am testing.

I am really interested in knowing as to which one is speedier when the amount of rows goes say for example around half a million.

You see the software that I am currently developing will generate a lot of data and the amount of rows in various files may reach around half million (after filtering) in a years time.

Regards,

Yogi Yang

von Yogi Yang - am 04.07.2013 14:32
Hi,

If I`m not misstaken (please correct me if I am) a "for each" runs on a recordset retrived, while the query runs on a server only sending the resulting recordset, if not all records are retrived.

The difference (I suspect) would be shown when using a Filter only selecting 100 records out of 100 000 in a Client/Server connection.
Where the "for each" would need to retrive all 100 000 records and then do the filtering as opposite to a query that would run on the server and only sending 100 records over the network after the 100 000 records are filtered.

I`f you get into the habbit of using queryes (and if I`m correct) you would preparing your app for a Client/server from start using queries, and if need arises you had only to change the connection from local HF to CS/HF.

I Would not expect any real difference, if you are running local/Classic Hyperfiles.

Cheers
Tor-Bjarne

von Tor-Bjarne - am 05.07.2013 08:05
Yogi
Your best answer is to create a dataset with a million rows and test.
Personally I would have thought that sql would be faster as it is more efficient at filtering.

In the real world however this will depend on the structure of the data, the keys available and the hardware configuration.
Bear in mind that in certain cases (usually complex queries) the DB sql optimiser may elect to do a full table scan - testing and optimising on a representative data set is the only real answer.

What is it that you need to do with half million rows?
There may well be a more efficient way of doing what you need to achieve.

von DerekT - am 05.07.2013 08:06
Hi Yogi,

There's no one-fits-all answer.
On a simple local system a direct read of all records may be faster than a query.
On a client server system there's the bandwidth/server traffic to consider, in which case most of the time queries will be the best choice.
But if you really need to know, just follow Derek's advice: create dummy records and check the time needed for each method.

Regards,
Piet

von Piet van Zanten - am 05.07.2013 11:11
Hello All,

Thanks everyone for your precious advise.

I will have to experiment and as Derek has adivsed I will have to populate my file with half a million records to test.

Regards,

Yogi Yang

von Yogi Yang - am 05.07.2013 15:12
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.