Solved

MYSQL: Fast search method

Posted on 2006-06-22
37
1,157 Views
Last Modified: 2010-05-19
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
Comment
Question by:ryan_sabarre
  • 15
  • 10
  • 6
  • +2
37 Comments
 

Author Comment

by:ryan_sabarre
ID: 16966636
Thanks in adavance.
0
 
LVL 10

Expert Comment

by:atul_parmar
ID: 16966711
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
 
LVL 10

Expert Comment

by:atul_parmar
ID: 16966723
Although, by using indexes you can also speed up the search with Locate. :)
0
 

Author Comment

by:ryan_sabarre
ID: 16966735
I don't have an index field created in my MYSQL Table
0
 

Author Comment

by:ryan_sabarre
ID: 16966742
i don't use or assign an index filed in my table
0
 

Author Comment

by:ryan_sabarre
ID: 16966749
in short, i don't know how to assign or create an index using mysql :-)

0
 
LVL 10

Expert Comment

by:atul_parmar
ID: 16966754
have you tried Table1.IndexFieldNames ?? it does not require an index to be created on table.
0
 
LVL 9

Expert Comment

by:sun4sunday
ID: 16966779
Create an index for the table you want to locate.
This will improve the speed of the search

sun4sunday

0
 
LVL 9

Expert Comment

by:sun4sunday
ID: 16966800
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 16966944
usual i owld recomend a query rather the use a table-component

search can done by modifying the where-clause

meikl ;-)
0
 
LVL 9

Expert Comment

by:sun4sunday
ID: 16967086
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
 

Author Comment

by:ryan_sabarre
ID: 16967095
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
 
LVL 4

Expert Comment

by:kenpem
ID: 16967234
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
 
LVL 10

Expert Comment

by:atul_parmar
ID: 16967243
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
 
LVL 10

Expert Comment

by:atul_parmar
ID: 16967249
To create an index on mySQL tables see http://dev.mysql.com/doc/refman/5.0/en/create-index.html
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 16967284
>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
 
LVL 10

Expert Comment

by:atul_parmar
ID: 16967303
Who said we can't use TQuery for append, edit and  delete  ??????
0
 

Author Comment

by:ryan_sabarre
ID: 16967308
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 10

Expert Comment

by:atul_parmar
ID: 16967322
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
 
LVL 10

Expert Comment

by:atul_parmar
ID: 16967338
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
 
LVL 27

Expert Comment

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

Author Comment

by:ryan_sabarre
ID: 16967643
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
 

Author Comment

by:ryan_sabarre
ID: 16967678
atul_parmar,

An Error is raised
Query1.IndexFieldNames := 'fieldname'; <------------UNDECLARED IDENTIFIER "IndexFieldNames"
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 16967681
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
 

Author Comment

by:ryan_sabarre
ID: 16967704
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
 
LVL 10

Expert Comment

by:atul_parmar
ID: 16967788
Mate,

If you need the entire list then you should go with TTable.
0
 

Author Comment

by:ryan_sabarre
ID: 16967803
Which is faster

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

or the

Table1.locate(..)


0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 16967811
>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
 
LVL 10

Expert Comment

by:atul_parmar
ID: 16968073
FindKey will do better.
0
 

Author Comment

by:ryan_sabarre
ID: 16968165
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
 
LVL 9

Expert Comment

by:sun4sunday
ID: 16968254
>>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
 

Author Comment

by:ryan_sabarre
ID: 16968540
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
 
LVL 9

Expert Comment

by:sun4sunday
ID: 16970364
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
 

Author Comment

by:ryan_sabarre
ID: 16970621
How will i use the primary key? using the locate method?
0
 

Author Comment

by:ryan_sabarre
ID: 16970922
lets say that i have a primary key, what method should i use to search fast a record?
0
 
LVL 9

Accepted Solution

by:
sun4sunday earned 125 total points
ID: 16970929
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
 

Author Comment

by:ryan_sabarre
ID: 16977601
Thanks a lot sun4sunday.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now