Using a UniQuery to connect to a sqlite database with about 6 Million unique sha1 values in a column, the column is fully indexed
I am using
if(ShaHash->Locate("sha1", sha1, TLocateOptions()))
{
//do something
}
to determine whether various values are in the table.
The first time through the loop Locate takes an age to execute (minutes) on repeat calls the function returns immediately. Is this normal?
Locate speed
6M records is far too many to be bringing to the client and performing that sort of search with any kind of data access mechanism. To do that it may have to bring the entire 6M records to the client before finding the one you're interested in.
Can you not simply set your query to:
and examine rows of interest individually that way?
i.e. Let the server do what its best at i.e. returning as few rows as possible to the client that match some criteria you provide.
Regards, Paul.
Can you not simply set your query to:
Code: Select all
select * from table where SHA1 = SomeValue
i.e. Let the server do what its best at i.e. returning as few rows as possible to the client that match some criteria you provide.
Regards, Paul.
I think that sounds about right. Selecting and returning 1 record takes a matter of milliseconds when the main table is indexed correctly.
When you say 'ultimately 16 million'; is that number of records in the main table or number of lookups?
IIRC, you said that the DB was local to the client.
Using a UniQuery where UniDirectional is False (needed to allow locate to work by moving forwards and backwards through the data):
Also, you could leave the index on the main table to save time rebuilding and prevent duplicates, etc.
Do you have to modify the records that exist or do you do something else if they exist?
Regards, Paul.
When you say 'ultimately 16 million'; is that number of records in the main table or number of lookups?
IIRC, you said that the DB was local to the client.
Using a UniQuery where UniDirectional is False (needed to allow locate to work by moving forwards and backwards through the data):
- The DB must first assemble all the records to fill the query (requires lots of memory for DB).
Then the UniQuery gets populated (another chunk of memory to hold all the records again).
Your Locate has to search all these records for your value.
- DB uses its index/keys (very optimal) and returns 1 record very quickly with not much memory used.
UniQuery gets the one record (again hardly any memory and fast).
No further searching/locating required (you have answered your question that the record exists) and you can modify and post changes as required.
Also, you could leave the index on the main table to save time rebuilding and prevent duplicates, etc.
Do you have to modify the records that exist or do you do something else if they exist?
Regards, Paul.
Re: Locate speed
The point is that the FetchAll property is set to False by default. In this case when you call Locate for the first time UniDAC fetches all unfetched records and just after this begins searching. To solve the problem set the FetchAll option to True, like this:sandy771 wrote:The first time through the loop Locate takes an age to execute (minutes) on repeat calls the function returns immediately. Is this normal?
Code: Select all
UniQuery.SpecificOptions.Values['FetchAll'] := 'True';