MySQL performance question

Posted on 2008-11-18
Medium Priority
Last Modified: 2013-11-23
I am changing a thirdy party application from Paradox to MySQL using Zeos Components.
There are various TTable components and original application uses FindKey and FindNearest Dataset methods.
What If I change TTable to TZQuery component and FindKey/FindNearest to Locate?
Is Locate method equivalent to a SELECT with WHERE clause adapted to seach criteria indicated in loPartialKey?
Question by:jclobocar
  • 3
LVL 38

Accepted Solution

Geert Gruwez earned 400 total points
ID: 22983399
good for you !!!
FindKey/FindNearest is the slowest possible way of locating a record

Locate is not equivalent to Select/Where if you consider network traffic

Lets say you want 1 record out of 2 million records from 1 table:
with Locate:
Open table -> copy 2 million records from database to pc -> find record in memory
Depends on Memory, NetWork traffic

with Select/Where
Open query -> Database returns 1 record
(Depends on index for faster finding of records)

My guess to your speed gain will be x100 :)

Author Comment

ID: 22983474
So, I have to rewrite all the code for selection using queries, correct?
LVL 38

Expert Comment

by:Geert Gruwez
ID: 22983927
basically that would be the best idea

it's probably a poorly performing application anyway ?
(unless it's only running over max 500 records)
LVL 38

Expert Comment

by:Geert Gruwez
ID: 22992387
you should try and avoid loading the whole table into memory and then using a locate to find 1 record

better would be to have a query with only loading the specific record using "select * from table where id = :id"

also try and use bind variables
and don't create your queries like "select * from table where id = 2"


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Integration Management Part 2
How to fix display issue, screen flickering issue when I plug in power cord to the machine. Before I start explaining the solution lets check out once the issue how it looks like after I connect the power cord. most of you also have faced this…
Suggested Courses
Course of the Month8 days, 1 hour left to enroll

616 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