#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    6

    Performance/design issue


    Hi,

    I am discussing the performance of our Firebird database connected by a Delphi application. Everytime I open the program the program will provide an overview of all the orders of our customer table. Very usefull when you want to check what happened the last day/ hours.

    So my question:

    query A: Select * from table;

    query B: select * from table where column like "searchvalue" limit 0,10;

    Assuming a large table, (100.000) records.
    If 10-50 people on a database perform queries like A, I easily run into performance problems.

    - Can I solve/optimise them by using queries like B, and ask the searchvalues as a input?

    - How to provide a short overview that will update every 10 seconds (like the last 30 records) of a large table and keep performance?

    - Which tools will you use? Which language?

    - What will happen with the performance when you make it web-based, (internet speed not included).

    - Do you know other ways to optimise the search / standard overview?

    I think our programmer makes a serious design flaw. Who agrees?

    Thanks for your attention, your reply will be appreciated.
    And sorry for my bad English.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    Way too many variables in your situation to "know" the solution.

    1. Select * from ... is never good if you do not want all the records.
    If you only want the latest information.
    Try adding a TIMESTAMP field (if you do not have one already) index it and change the query to something like

    Pseudo SQL:

    SELECT ONLY THE FIELDS YOU NEED
    FROM TABLE
    WHERE (CURRENT_TIMESTAMP - TIMESTAMP_FIELD) <= 24 HOURS
    ODER BY TIMESTAMP_FIELD DESC

    Other than that, you need to make sure that the bottleneck is not the server.
    Can it handle 50 concurrent connections, how many processors do you have etc. etc.
    What about your internal network?

    If you want to add a WHERE clause to one of the columns then make sure it is indexed, that it has a high cardinality and that the query can use the index.
    For example.

    WHERE LAST_NAME LIKE 'Will%'
    Can use the index BUT
    WHERE LAST_NAME LIKE '%son'
    Can not.

    HTH - Clive
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    6
    Thank you Clivew.

    About my internal network
    everything is 1000 Mbit, networkcards, cables, switches.

    About my server:
    Quad core, 6 GB, Debian 5, RAID5 - 15.000 rpm, 1000 Mbit/s

    The server does vary between 10 and 60% CPU when everybody is working, but is never touching the max.

    I tried, but could not find a clear reference about the
    maximum concurrent connections.
    How to check this? Is this a SQL thing, linux config or Firebird parameter?
    Does it matter that I run 2.0.4 Super Server in this case?

    Please help, thanks.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    The hardware and even the DBA side of things is not my area of expertise.
    I have to research it myself when I run into an issue. I just know they can be factors.

    My personal expertise (I hope!) is in SQL construction and optimization as
    per the directions I suggested you investigate for the SQL itself.

IMN logo majestic logo threadwatch logo seochat tools logo