Seek command

[abdulmojeb]

Hi,

I've been searching SEEK COMMAND both in lianja and vfp but I've found nothing about range of date.

Is it possible to use SEEK COMMAND in range of date (Ex. 11/1/2015 - 11/30/2015)? If so then how may I write it in SEEK and SCAN COMMAND to optimize my query?

SEEK ...
SCAN REST WHILE ...

ENDSCAN

thanks in advance for your help.

[barrymavin]

The NoSQL-style SEEK command searches for a key. The key has a distinct value that may occur many times in an index.

If you want to select from a set of records where the keys are within a range then use SQL SELECT not NoSQL-style SEEK.

Now saying that....

If you know what range of keys you want to search for e.g. a date range for a quarter 1/1/2015 to 3/31/2015 you can create what is called a filtered index using a FOR condition.

Then when you select this index and seek to a date you will only scan the records where the date is within the date range as specified in the INDEX...FOR condition. This reduces disk I/O significantly but is only useful if the date range is known beforehand.

[abdulmojeb]

hi barry,

I was using SQL SELECT before but the query is slow even though the table is already indexed. It doesn't work so fast as the NoSQL-style do.

It seems that scanning of the entire table is being performed unlike the seek/scan rest while...

thanks anyway..

[barrymavin]

We've been over this already,

Use the EXPLAIN command to see what is going on.

In original forum post it was explained that you had a lot of duplicated which was causing the sequential scanning.

You are now talking about another query, Every query is handled differently by the SQL optimizer...

[phabio]

hello Abdulmajid,
when you work with a two-tier system, you have a server with data and a machine where they are processed (your client).
if the data extracted by the query are many, the time for the reading will be long .. regardless of the speed of the server, because all the records are to be extracted which are transmitted by the network.

If you have a table with 1,000,000 records and yiu read only one, will be sent only that record. if you read 5,000 records .. time will be proportionally longer.
How many records returns you select?

[phabio]

my post is true if you have a SQL server.. (lianja, MS, mySql, Postgres, etc).

or you have only stored the lianja DB on a file server?
in this case, if you have a 1.000.000 of records, or you work only with a seek, or your clint CPU need to read all the records stored on a server..

[abdulmojeb]

hi phabio,

I believe my DB has a SQL server which is the lianja.

I'm catching 300-500 records from the server and takes 1-2min to load all the records. Is that reasonable?

I know that should be the case. The more records you catch the long it will take to load. But when I try to query the same number of records but in different methods (SQL SELECT and NoSQL style) the speed of querying records are different. It took longer if I'm using the SQL SELECT. So those 200-500 records will only took less than a min using NoSQL style.

I know it may sound crazy but I'm just giving you what I observed as a beginner.

Thanks a lot phabio. I think that 1-2min would be reasonable for 500 records.

[abdulmojeb]

I'm always using the EXPLAIN COMMAND to test all my query but everything seems so fine.

[barrymavin]

No I think 1-2 minutes is ridiculous. Never seen that other than when a sequential scan is being made across a LAN with no indexes being used.

Rather than just try and guess what you are doing, zip up your database and submit a ticket with it attached and an example of a SQL query that you say is taking 2-3 minutes.

I expect most queries to complete in msecs not minutes.

[abdulmojeb]

When I'm running a SQL SELECT using the lianja console or the project on the server PC to query records, it's just actually a matter of msec.

But using the deployed project on the client PC, that's the moment that it will take more time. Note that is only observed in SQL SELECT but not in NoSQL style command.

I'll zip up my database together with the project for you to test.

thanks