Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1168
  • Last Modified:

MYSQL: Fast search method

Hi Everybody, I'm using the TTABLE.Locate(Fieldname,value,[]) method and find it very very very slow in locating a certain record specially when searching  >30,000 records. Do delphi has a searching capability that works fast in locating a certain record? Can you gave me a sample code for this?

I have a TEDIT and DBGrid, everythime the user input a value inside TEDIT (ONCHANGED METHOD) ttable.locate is executed and point the record if it exists, TDBGRID then do the display in pointing the record. This works very very slow when the table has a large record inside.

Thank you very much in advance.
0
ryan_sabarre
Asked:
ryan_sabarre
  • 15
  • 10
  • 6
  • +2
1 Solution
 
ryan_sabarreAuthor Commented:
Thanks in adavance.
0
 
atul_parmarCommented:
You can use Table1.FindKey method, but before using it, the  active index must be set to the field you are searching on.
e.g.
Table1.IndexFieldNames := 'MyField'
If Table1.FindKey([Edit1.Text]) then
  // record found
else
  record not found
0
 
atul_parmarCommented:
Although, by using indexes you can also speed up the search with Locate. :)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ryan_sabarreAuthor Commented:
I don't have an index field created in my MYSQL Table
0
 
ryan_sabarreAuthor Commented:
i don't use or assign an index filed in my table
0
 
ryan_sabarreAuthor Commented:
in short, i don't know how to assign or create an index using mysql :-)

0
 
atul_parmarCommented:
have you tried Table1.IndexFieldNames ?? it does not require an index to be created on table.
0
 
sun4sundayCommented:
Create an index for the table you want to locate.
This will improve the speed of the search

sun4sunday

0
 
sun4sundayCommented:
oops..lots of posts..I didnot refresh the page..

atul_parmar already answerd your question

Table1.Indexname := 'TABLEINDEXNAME';

//Do the serach here using Locate or Findkey

Table1.Indexname := '';  //setback the index, then perfrom the edit/insert/delete  and post/commit etc

sun4sunday
0
 
kretzschmarCommented:
usual i owld recomend a query rather the use a table-component

search can done by modifying the where-clause

meikl ;-)
0
 
sun4sundayCommented:
If the user only need to view the record, then use the query like meikl mentioned.

After locating the record and has to edit on the record, go with TTable Locate/Findkey

sun4sunday
0
 
ryan_sabarreAuthor Commented:
Kretzshmar,

 Does using query is much faster that using the indexname search? My problem when using TQUERY is that i can't use the query.append, query.edit & query.delete methods.

atul_parmar,

 I already tried the Table1.IndexFieldNames but it will return an error. "index table not exists..."

sun4sunday,
 Can you show me on how to create an index field in a table?

Thanks.
0
 
kenpemCommented:
You could try setting the Filter on the TTable.

I would also recommend NOT searching/locating/whatever directly in the OnChange event - if the user is typing, you're trying to do too much work and it's unnecessary. Perhaps fire a half-second timer in the OnChange, and have that timer's OnTimer event do the lookup. That way you won't start the lookup process until the user has finished (or at least paused) typing.


0
 
atul_parmarCommented:
will you tell me what you are assigning to IndexFieldNames ?

It has to be the FieldName on which you want to perform search.

e.g.
Table1.IndexFieldNames := 'Name'; //

0
 
atul_parmarCommented:
To create an index on mySQL tables see http://dev.mysql.com/doc/refman/5.0/en/create-index.html
0
 
kretzschmarCommented:
>TQUERY is that i can't use the query.append, query.edit & query.delete
>methods.

attach a TUpdateSQL as updateObject to the query
(must use chachedupdates-property sets to true)

meikl ;-)
0
 
atul_parmarCommented:
Who said we can't use TQuery for append, edit and  delete  ??????
0
 
ryan_sabarreAuthor Commented:
Kenpem, thanks for the tip.

Should i create a table lookup always whenever i will execute my lookup diaglog?
CREATE TABLE lookup (id INT) ENGINE = MEMORY;
CREATE INDEX id_index USING BTREE ON lookup (id);

can anybody show me a source code that uses the TEDIT & TDBEDIT and using the Onchange event in TEDIT?
0
 
atul_parmarCommented:
NO. e.g. If your table name is customer and you want to create an index on CustomerName field then

CREATE INDEX IdxCustomerName ON customer (CustomerName)
0
 
atul_parmarCommented:
Table1.IndexFieldNames := CustomerName;
......
procedure TForm1.Edit1Change(Sender: TObject);
begin
    Table1.FindKey([Edit1.Text]); // for exact match
    Table1.FindNearest([Edit1.Text]); // for nearest match
end;

To use SQL
procedure TForm1.Edit1Change(Sender: TObject);
begin
  Query1.Close;
  Query1.SQL.Clear;
  Query1.SQL.Add('SELECT * FROM customer WHERE CustomerName like "' + Edit1.Text + '"%');
  Query1.Open;
end;
0
 
kretzschmarCommented:
>Who said we can't use TQuery for append, edit and  delete  ??????
see comment ID 16967095
0
 
ryan_sabarreAuthor Commented:
kretzschmar,

 Hi, i'm really interested to know on how to use the append, edit and delete method using TQUERY. But,  i cant find the topic http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_16967095.html. can you please post the link.

>Who said we can't use TQuery for append, edit and  delete  ??????
see comment ID 16967095

0
 
ryan_sabarreAuthor Commented:
atul_parmar,

An Error is raised
Query1.IndexFieldNames := 'fieldname'; <------------UNDECLARED IDENTIFIER "IndexFieldNames"
0
 
kretzschmarCommented:
hmm,

this was not a questionID, but well,

here is a good description from workshop-alex,
how to configure

http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_11297937.html

here is good description with small code snippets from ashok111

http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_21385663.html

meikl ;-)
0
 
ryan_sabarreAuthor Commented:
atul_parmar,

 Sorry, TQuery don't have a IndexFieldNames property only the TTable has. I am using a TQuery component, so how can i leverage a TQuery component in locating a record more faster.

Note that using this

 Query1.Close;
  Query1.SQL.Clear;
  Query1.SQL.Add('SELECT * FROM customer WHERE CustomerName like "' + Edit1.Text + '"%');
  Query1.Open;

will display only the record that is being search but not the entire record list.
0
 
atul_parmarCommented:
Mate,

If you need the entire list then you should go with TTable.
0
 
ryan_sabarreAuthor Commented:
Which is faster

Using the
Table1.IndexFieldNames := CustomerName;
Table1.FindKey([Edit1.Text]);

or the

Table1.locate(..)


0
 
kretzschmarCommented:
>will display only the record that is being search but not the entire record list.

in this case atul_parmer is right
(i may overlooked this with the recordlist, sorry)

meikl ;-)
0
 
atul_parmarCommented:
FindKey will do better.
0
 
ryan_sabarreAuthor Commented:
Hi kretzschmar,

 I've been in the Q_21385663.html and read the ff: .. What should i place inside

UpdateSQL1.ModifySQL.Add() ?

when i want to modify a certain selected record in the query?


USING TUPDATESQL

TUpdateSQL is not a requirement of cached updates. It is always optional, but it is a convenient way to update data in a cached update dataset. It may be used with any TTable or TQuery in cached updates mode, but is most useful with those components when they are read only or when the RequestLive property is false. TUpdateSQL helps automate the process in such cases and helps you avoid a lot of coding to make updates from a read only dataset.

For the sake of illustration, suppose you are trying to manage a TQuery that returns a dataset which is the result of a join of two tables. In this case, Delphi will almost always force RequestLive to be False and so not allow direct updates to the dataset.

To begin with, let's assume that you are going to update only one of the two tables.

Your basic setup should be:
     TQuery.CachedUpdates := True
     TQuery.RequestLive := {who cares}
     TQuery.UpdateObject := name of TUpdateSQL

And in the TUpdateSQL, you should have the SQL for Modify, Insert, Delete set to what you want. Note that you don't have to put SQL in any of those, but use only one TUpdateSQL for one table.

CachedUpdates should be True before opening the query. It’s best to make this setting at design time, before opening the database.

Believe it or not, you are now ready to run. That is, you have the minimum requirement. To make this work, there are just three steps:

1. Open the query.
2. Make changes to the dataset (Insert, Modify, Delete) in the usual way. You can make changes to many records.
3. Somewhere in your code, in a button click event say, put this code to send the changes to the database.
     MyQuery.ApplyUpdates.

Delphi will handle the updates automatically in this simple case. You don’t have to do anything special with the query, the database or TUpdateSQL.

This should work great if you are in a single-user app and you don't have any special data needs. But, since those things are fairly common, we now go to the next level.

For multi user environments, it's useful to put your changes in a transaction. Here's how (in a button click event, say):

     MyQuery.Database.StartTransaction;
     try
          MyQuery.ApplyUpdates;
          MyQuery.Database.Commit;
          MyQuery.CommitUpdates;
     except
          MyQuery.Database.Rollback;
     end;
0
 
sun4sundayCommented:
>>Which is faster

If you place three four  OR  in your SQL, the time taken to exccute the query will get increase.
We cannot say in one goal that TQuery is more faster or TTable is more faster. All depends on the situations.
You have to choose the right one depends on your application.
If you can explain us what are you going to achive in your application, hopefully we can guide you on the right track.

sun4sunday
0
 
ryan_sabarreAuthor Commented:
I'm making a simple employee record entry. The employee record has a append, edit, remove, post, cancel & <locate> capability. when i want to modify a certain record first i go to the Locate dialog and search for the employee which i want to modify or delete.
The locate dialog is using a TQuery which loads all the employee records and display it in the TDBGrid. when a record has been selected, the application then goes back to the employee record entry and call the TTABLE.locate to locate the record that was selected by the LOCATE DIALOG TQUERY. The process is very slow when the ttable.locate is executed when locating the employee record.
0
 
sun4sundayCommented:
Hope you are using primary key for the employee table.
In that case, once you select the record from the grid  (which is coming from the Tquery)
Locate the record using primary key. Then no need to use the index if you are using the primary key to locate the record in the table. It willbe fast then.

By the way, what database are you using?
The perromance also depends on the Server/PC.

If you don't have a primary key, better use one.

sun4sunday.
0
 
ryan_sabarreAuthor Commented:
How will i use the primary key? using the locate method?
0
 
ryan_sabarreAuthor Commented:
lets say that i have a primary key, what method should i use to search fast a record?
0
 
sun4sundayCommented:
That is already in your question to use it.

TTABLE.Locate(Fieldname,value,[])

TTABLE.Locate(PRIMARYKEYNAME,value,[])
eg:
Table1.Locate('PRIMARYKEY_ID', Query1PRIMARYKEY_ID.Value,[]);

If you are locating the table using primary key, no need to bother the inedx. The primary key field will ack as index filed.
No need to explicity give that.

What DataBase are you using?

From Delphi Help

/////////////////
Using Locate
Locate moves the cursor to the first row matching a specified set of search criteria. In its simplest form, you pass Locate the name of a column to search, a field value to match, and an options flag specifying whether the search is case-insensitive or if it can use partial-key matching. For example, the following code moves the cursor to the first row in the CustTable where the value in the Company column is “Professional Divers, Ltd.”:

var

  LocateSuccess: Boolean;
  SearchOptions: TLocateOptions;
begin
  SearchOptions := [loPartialKey];
  LocateSuccess := CustTable.Locate('Company', 'Professional Divers, Ltd.',
    SearchOptions);
end;

If Locate finds a match, the first record containing the match becomes the current record. Locate returns True if it finds a matching record, False if it does not. If a search fails, the current record does not change.
The real power of Locate comes into play when you want to search on multiple columns and specify multiple values to search for. Search values are variants, which enables you to specify different data types in your search criteria. To specify multiple columns in a search string, separate individual items in the string with semicolons.

Because search values are variants, if you pass multiple values, you must either pass a variant array type as an argument (for example, the return values from the Lookup method), or you must construct the variant array on the fly using the VarArrayOf function. The following code illustrates a search on multiple columns using multiple search values and partial-key matching:

with CustTable do

  Locate('Company;Contact;Phone', VarArrayOf(['Sight Diver','P']), loPartialKey);

Locate uses the fastest possible method to locate matching records. If the columns to search are indexed and the index is compatible with the search options you specify, Locate uses the index.

/////////////////////

Hope this helps you

sun4sunday



 
0
 
ryan_sabarreAuthor Commented:
Thanks a lot sun4sunday.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 15
  • 10
  • 6
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now