How to improve query performance with about 2

deokmo
2013-07-30
2014-01-19
  • deokmo

    deokmo - 2013-07-30

    How to improve query performance with about 2 million on cached table?
    The desktop is pretty powerful(from memory 4GB memory, 4 core processor, SSD) running hsql server.

       - Sql Execution response times: About 50 ~ 70 seconds.

    In the query below,
    SELECT COUNT(E.ID)
    FROM FM_EVENTLOG E, CM_NODE N
                    WHERE E.OCCURRENCETIMELONG BETWEEN 63507974400000 AND 63510605999000
                        AND E.NODEID = N.ID
                        AND N.ISDELETED = 0

    This FM_EVENTLOG has OCCURRENCETIMELONG index.

     
  • Fred Toussi

    Fred Toussi - 2013-07-30

    Your index looks correct for the query. Use

    EXPLAIN PLAN FOR SELECT COUNT(E.ID) …

    to see an index is used for both tables.

    If COUNT is returning values that are not very large, the query should complete quickly.

     
  • deokmo

    deokmo - 2013-07-30

    So, It's use the COUNT function in case of how many data?

     
  • Fred Toussi

    Fred Toussi - 2013-07-30

    It is not possible to say exactly. It depends on the indexes and what percentage of rows in the second tables have ISDELETE = 0.

    In the best case scenario, it should take a fraction of a second.

     

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks