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

    Join Date
    Oct 2010
    Posts
    7
    Rep Power
    0

    Large number of fields makes query slow


    i have a table (for customers) that has 60 fields.
    for the customer administration section of my application i want a query to return all of these fields. However query is very slow, even with a good index.

    select * from customers takes over 1 second, which causes a noticable delay in the application. Where as select id from customers takes less than 0.05 seconds, even when returning 3000 or so records.

    why does a large number of fields cause such severe slowdown?

    what is the best practice for number of fields in a table? once you get over about 15 fields, you start to notice the speed slowing down.

    any advice on incresing performace for a table with large number of fields would be apreciated.
  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
    What makes you think it is Firebird causing the slow down and not all the extra data traveling across the network?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2010
    Posts
    7
    Rep Power
    0
    Originally Posted by clivew
    What makes you think it is Firebird causing the slow down and not all the extra data traveling across the network?
    Localhost, no network invloved.
    (although i am using TCP/IP protocol for localhost)

    You can retrive a large number (3000+) records with a few fields really fast. But even when retreiving a few rows with a large number of fields it really slows down.

    I hope i'm making sence. My point is there is not a large amount of data. My index should ensure that only about 250 records need to be scanned.

    Ironically it is much faster in my delphi application compared to running the same query in database workbench. Does DB workbench struggle with large number of fields maybe?
  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
    Ironically it is much faster in my delphi application compared to running the same query in database workbench.
    That has nothing to do with Firebird and everything to do with the client access.

    1. Is the index actually being used?
    2. Does the where clause include any non-indexed fields?
    3. Are statistics on the index up to date?
    4. What is the cardinality of the index?
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2010
    Posts
    7
    Rep Power
    0
    Originally Posted by clivew
    That has nothing to do with Firebird and everything to do with the client access.

    1. Is the index actually being used?
    2. Does the where clause include any non-indexed fields?
    3. Are statistics on the index up to date?
    4. What is the cardinality of the index?
    1. Yes the index is being used.
    2. No the where clause does not include any non-indexed fields.
    3. Not sure, when do they get updated? The database has recently been restored.
    4. How do i find this out? I assume this has something to do with the number of rows?

    Just to clarify the original point. This is a problem with the number of fields in the select clause.

    To give a fairly simple example;

    Example 1 (1 Field in select clause = Really fast)
    SELECT REF FROM CLIENTS WHERE BRANCH_REF = 2

    Prepared in 0.003 secs
    Processed in 0.047 secs
    Returns 390 rows.
    PLAN (CLIENTS INDEX (I_CLIENTS_BRANCH))

    Example 2 (60 fields in select clause = Really slow)
    SELECT * FROM CLIENTS WHERE BRANCH_REF = 2

    Prepared in 0.004 secs
    Processed in 1.034 secs
    Returns 390 rows.
    PLAN (CLIENTS INDEX (I_CLIENTS_BRANCH))

    To give you an idea of how many records are in the table;
    BRANCH_REF = 0 : 12413 records
    BRANCH_REF = 2 : 390 records
    BRANCH_REF = 3 : 359
    BRANCH_REF = 4 : 305 records

    I don't beleive this is a problem with the index, purely the number of fields in the select clause. The more fields you add to the select clause, the slower the query runs.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2010
    Posts
    7
    Rep Power
    0
    I recreated the table in mySQL and imported the same set of data. I get very similar behaviour with mySQL.

    1 field = instant,
    60 fields = about 1 second.

    In fact mySQL was slightly faster.

    I did notice this. I am using Database Workbench to test these queries. If you resize the window so that not many rows are displayed, it can as much halve the time.

    Why do a lot of fields impact performance so much? I think there must be some time consuming process that is looking at each individual field.

    I notice there is a system table called RDB$FIELDS. This has just over 5000 records and there are no indexes on this table.
    Could this be the cause? Would it benefit with indexes on this table, and is that even possible?
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    If you resize the window so that not many rows are displayed, it can as much halve the time.
    Ah Ha!

    The difference is that you are displaying the information as it comes back.
    The difference is in the CPU/OS/Graphics card effort to display all those columns.
    It has nothing to do with retrieving them from the database.

    See what happens if you disconnect the GUI code.

    ALSO: The index is not going to be terribly effective with such a low cardinality. With only four possible values across all the rows it is questionable whether it helps.
    Last edited by clivew; October 27th, 2010 at 03:05 PM.

IMN logo majestic logo threadwatch logo seochat tools logo