Speed retrieving one record from large table

miguelenguica · Post by **miguelenguica** » Sat 16 Apr 2011 16:14

Hi. I have a large MySQL table (5.000.000 records). My application must retrieve individual (sometimes 2 or 3) records intensively. With a TMyQuery using the correct indexes in a sequence

MyQuery.Params[];
MyQuery.Open;
while not MyQuery.eof do
(...)
MyQuery.Close;

It just takes a lot of time (4 or 5 seconds).

On the second case I use a TMyTable or a TMyQuery with no params and opened at startup, with the right index. The I just use TMyTable or TMyQuery.Locate and the results are instant. i can perform hundreds of locates under 1 second. The only problem is that it just takes a lot of time at startup.

The second case works, but it just cant't be thread safe. Is there anyway I can have the same locate speed with just runtime select? What would be the best way to implement this?

Thanks,
Miguel

AndreyZ · Post by **AndreyZ** » Mon 18 Apr 2011 11:42

Hello,

The fastest way to get records from a big table without obtaining it on a client computer is to use a query like:

Code: Select all

MyQuery.SQL.Clear;
MyQuery.SQL.Add('select field1, field2 from tablename'); // select only needed data
MyQuery.SQL.Add('where field3 = :value1, field4 = :value2'); // you should have an index that includes field3 and field4
MyQuery.Prepare; // to increase performance

Also please read the "Increasing Performance" topic of the MyDAC documentation. There you will find several suggestions for speeding up your application.

miguelenguica · Post by **miguelenguica** » Mon 18 Apr 2011 18:34

Thank you AndreyZ.

I've tried that respecting the indexes. But I still can't get the same performance from 100 processes of query open / select rather than TQuery or TTable Locate and LocateEX. Any other option?

Miguel

AndreyZ · Post by **AndreyZ** » Tue 19 Apr 2011 11:11

Please try using the USE INDEX statement. You can find more information about it here: http://dev.mysql.com/doc/refman/5.0/en/index-hints.html
Execution of a query one hundred times is always slower than performing the Locate function one hundred times on a client machine, because requests to the server require time (connection establishment, network speed).

miguelenguica · Post by **miguelenguica** » Wed 20 Apr 2011 13:52

Thanks. That's what I thought. This project's nature requires the high number of queries. No way around it. So I'll stick with Locate and LocateEX. They work fine. The only problem is the startup time, but in a server application the damage is minimal.

Best,
Miguel