MySQL performance question

Posted on 2008-11-18
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
    LVL 36

    Accepted Solution

    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 :)
    LVL 1

    Author Comment

    So, I have to rewrite all the code for selection using queries, correct?
    LVL 36

    Expert Comment

    by:Geert Gruwez
    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 36

    Expert Comment

    by:Geert Gruwez
    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    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…
    In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    745 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

    14 Experts available now in Live!

    Get 1:1 Help Now