Page 2 of 2
Query?
Posted: Tue Aug 08, 2006 7:15 pm
by Pureabc
Michael,
I have used cheetah in a few projects and thought it is quite good.
In your code, I see you are looping through the entire record set, and it can be slow. Cheetah has the "query commands" that are designed to do some of these search/Match.
You might give this a try and see if it makes any differences. Here is an example code
Code: Select all
;The query will find all records where the Name contain Paul
; We will sort the query results by Name in ascending order.
xdbQueryCondition(Query1, 0, "Name", #CONTAINS, "Paul", "")
xdbQuerySort(Query1, "Name", #SORT_ASCEND)
;the query is created when you call xdbQueryExecute
xdbQueryExecute(Query1)
msg="Record Count: " + Str(xdbRecordCount(Query1))
MessageRequester (Title, msg, 0)
You could also use "Index files", and that should speed things up as well.
Re: Query?
Posted: Tue Aug 08, 2006 8:16 pm
by Michael Vogel
Pureabc wrote:You might give this a try and see if it makes any differences. Here is an example code ...
Thanks, Pureabc - before I'll give it a second try I have one additional question concerning the query command? Is it possible to access other fields of the found records easily?
Maybe its easier to understand what I mean giving an example: let's say, I have one database with receipts where I have to find all entries of a special article, I have to make a sum of all prices for this article, then I have to take the id of the customer to get the customer name from another database, etc.
I think, cheetah is very powerful - on the other hand (also because I only need to read from databases) I can also write some missing procedures for my requirements... ...now I have to think, what's the better way to go
Posted: Tue Aug 08, 2006 8:57 pm
by Pureabc
Yes, you can access any data in the found records quite easily.
from the manual:
Code: Select all
Once the query has been generated you can process the results in the same manner that you would process any other records in a database or index.
For example, the following code shows you how to traverse through the query result set.
'queries do not require the index parameter - it would be ignored anyway.
CALL xdbMoveFirst(Query1&, 0)
DO UNTIL xdbEOF&(Query1&) 'process the record here using functions such as xdbFieldValue$
Manuf$ = xdbFieldValue$(Query1&, "MANUF", 0)
'if you need to know the record number in the database then call 'the xdbRecordNumber function with the Query handle, not the 'database handle.
RecNum& = xdbRecordNumber&(Query1&)
CALL xdbMoveNext(Query1&, 0)
LOOP
You can also use xdbGetRecord to specify the "i-th" record in a query result. In this case you would use the query handle instead of the database handle and pass the position in the query result instead of the record number. For example, to retrieve the 5th record in the query result you would use:
CALL xdbGetRecord(Query1&, 5)
You can also include mulitple conditions in the query.
From the Manual:
Code: Select all
Example: To create a condition such as trying to find all customers with a customer ID between 1000 and 2000, and who have purchases of between $5000 and $10000, you would use the following conditions. (Note, the first condition should always have a join phrase equal to ZERO).
CALL xdbQueryCondition(Query1&, 0, "CUSTID", %BETWEEN, "1000", "2000")
CALL xdbQueryCondition(Query1&, %QUERY_AND, "PURCH", %BETWEEN, "5000", "10000")
Example: Create a condition to find all customer names containing the words "Bill" or "William". (Note, the first condition should always have a join phrase equal to ZERO).
CALL xdbQueryCondition(Query1&, 0, "CUSTNAME", %CONTAINS, "BILL", "")
CALL xdbQueryCondition(Query1&, %QUERY_OR, "CUSTNAME", %CONTAINS, "WILLIAM","")
For my projects, I was going to write my own database procedures, and I am glad I tried Cheetah before I did any of the procedures. It saved me a lot of time and energy.
Posted: Wed Aug 09, 2006 2:53 am
by blueb
I also noticed that you did not create/use an index. I would index on the field that makes the most sense to you. In fact, you can combine multiple fields to create your index.
I tried your code on a 52,000 record DBF file and it returned a unique search in less than 1/10 second. With a proper index and/or query, I'm sure this could be much faster.
--blueb
Posted: Sun Aug 13, 2006 3:39 am
by PaulSquires
The Cheetah Database query functions use a buffer to read the database so the process is faster than reading one record at a time especially if you must process every record in your database.
There is one problem with the freeware version of Cheetah: If your database has any memo fields then Cheetah will load the associated memo from the memo file (*.dbt) each time a record is read. This will slow down your record processing considerably. Most of the time this behaviour is of little concern but if you are reading a 50 meg database that has associated memo entries then you will notice a slowdown.
For the Open Source version, the Cheetah Database System has been completely re-written from scratch. You can find the open source version at the following links:
http://cheetahdatabase.sourceforge.net
https://sourceforge.net/projects/cheetahdatabase
There currently is no PureBasic translation of the header file but it should be easy for experienced PureBasic programmers to handle.
Hope this helps.