Skip to Content
avatar image
Former Member

Sybase IQ taking unexpectedly long to start query execution

Hello All,

I have a situation where Sybase IQ takes too long to start the query execution. I have looked at the execution plans, the actual execution times are just a couple of seconds but the white space or "thinking time" takes over a couple of minutes which results in extremely slow queries. I have seen a couple of runs where the queries took no time at all, and "thinking time" in those cases was only about 200ms.

I'm trying to figure out why there would be soo much "thinking time" before Sybase actually starts the query and how it can be improved. There doesnt seem to be much documentation on sybase iq website about what this "thinking time" actually is.

Cheers,

NeM

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    May 08, 2016 at 06:00 PM

    White space near the start of a query plan is normally time spent in the IQ optimizer

    In some cases highly connected joins with many tables can take significant time in the join optimizer. Sometimes a query can take while to do some complex optimizer transforms, usually in queries with many-arm unions (and usually not in minutes).

    The optimizer opens some indexes to gather metadata and in some cases (SP10 and later) sample data for GROUP BY statistics. If there are disk problems there might be delays in the optimizer, but I would also expect other delays in the query from disk problems.

    Finally, if it is a table with RLV enabled, in some cases there were performance problems collecting metadata in the optimizer from the RLV store. I believe most of those have been fixed in latest updates.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      dml_options8=8 did not effect either. Im using version 15.4. And Im only selecting the required columns. Ive tried with a single table with just a couple of columns it still takes 2 seconds for optimization time on a single table.

      I tried this on 16 and the query was much faster so it seems there is an issue with 15.4.