[Answers] Performance

[josipradnik]

If you have 1 million records and you have created indexes on the columns that you want to query on, and lets say that only 100 records match the query then lianja will only read 100 records. Once.
You can use the EXPLAIN command in the console to verify that you have correctly created indexes that will be used by the sql query optimizer.

---

Now with some tuning.

set icache to 5000
set dcache to optimum
set dcache on
// now running the same code as stated above

Results are:

Appended 180,000 records in 2.563 secs
Replaced 180,000 records in 2.320 secs
Indexed 180,000 records in 4.580 secs
Replaced 180,000 indexed records in 1.999 secs

So, if its data crunching you want to do you need to play with the DCACHE and ICACHE settings which affect the internal cache sizes for indexes and database tables.

---

clear
use
create table test (name char(20), product char(30), value numeric(10,2))
use test
timeofday(0)
append blank 180000
? "Appended 180,000 records in " + timeofday(4) + " secs"
replace all name with upper(name)
? "Replaced 180,000 records in " + timeofday(4) + " secs"
index on upper(name) tag name
? "Indexed 180,000 records in " + timeofday(4) + " secs"
replace all name with upper(name)
? "Replaced 180,000 indexed records in " + timeofday(4) + " secs"
?

Appended 180,000 records in 2.601 secs
Replaced 180,000 records in 2.087 secs
Indexed 180,000 records in 6.273 secs
Replaced 180,000 indexed records in 2.013 secs

It's worth noting that Lianja creates full balanced B+ tree indexes with no duplicates. These are optimized for multi-user network access.
Note the use of the built-in timeofday( ) function which you can place in your code to find "hotspots" that may be causing issues.
timeofday( 0 ) resets the timer.
timeofday( 4 ) returns the time since the last time it was called with msecs granularity.

---

Lianja uses cursor record buffering so when you move off record A it will automatically be written.
So the answer is, no you can't be editing 2 records from the same table at the same time without having committed the first one.

---

In fact the SQL query optimizer uses the indexes in the same way as rushmore. Its nothing special in that regard.
By using the FOR clause you are telling Lianja to ignore the index and "touch" every record. To get the behavior you want, try using SEEK and COUNT WHILE.

---

using SET TIMELINE OFF will speed this up quite a bit too.

---

Q:
which method provides better performance: case statements or if statements?
A:
The compiler will optimize anyway. There is no difference in performance.
If Your code is merely trying to assign a value based on a condition you should also look at IIF() and ICASE() in the doc wiki. These are faster than using compiled statements for simple values.

---

---

​All topics in [Answers] alphabetically:http://www.lianja.com/community/show...ll=1#post12352