Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    1

    Unanswered: Rows Count too slow

    Our system configuration:
    - Database: Sybase Adaptive Server Anywhere 6.00.04.3779
    - Connection Mode: ODBC
    - OS: Windows XP, Windows 2000

    Problem:
    The following SQL-statement is too slow. It takes about 5 minutes:

    select count(*) from sfordtp;

    In this table are about 400.000 records, which is not that many I believe. We have indexes on this table, but I don't think that matters a lot, because an index is only usefull when you have a where-clause, which we don't. Or am I wrong on this?

    Anyway, in the attached file you can have a look at the table columns and the indexes on it.

    The database is local on my computer, so it's not the network traffic.

    If you could help me, I would be very gratefull.

    Kind regards
    Jelle

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443

    Re: Rows Count too slow

    Actually, an index can be helpful in a count(*) situation even without a where clause.

    In Adaptive Server Enterprise, you can do a

    (a) "select count(colname_whichhasindex) from tablename". OR
    (b) select from the index. like "select count(colnamewhichhasindex) from tablename (index indexname)"

    This way the count will work on the index and is much faster.

    I just browsed the ASA manual, and it looks like the force index doesnt exist.

    I definitely know option (b) works in ASE, but am not sure about option (a) which is :

    Select count(colnamewhichhasindex) from tablename

    Try the above statement (dont use the * but the actual column name which has an index) and let me know if it helps.

    Vishi.

  3. #3
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Most of the time, too, it will scan an index with select count( * ). Take a look at the query plan and see if it is actually doing this. Maybe updating the index statistics (if such a thing exists on ASA) will help it pick a better index.
    Thanks,

    Matt

  4. #4
    Join Date
    Oct 2003
    Posts
    706

    Re: Rows Count too slow

    [i]
    Problem:
    The following SQL-statement is too slow. It takes about 5 minutes:

    select count(*) from sfordtp;

    In this table are about 400.000 records, which is not that many I believe.
    You're quite right: "400,000 rows is nuthin'" and a "count(*)" should take no time at all. My punt-guess is that it might be waiting for locks. Check the query-plan to see what the optimizer is actually telling the engine to do. See if you can monitor the query while it's running to see what it is doing, if it's starting over, and so-on.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •