Sybase IQ taking unexpectedly long to start query execution

[Former Member]

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

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

Thank you very much for your response. The query I am running is a simple select query on a Union all view (no joins just simple selects on similar tables unioned together).

I have also run queries on individual tables used in that view and they are also slower than expected. A single table takes a couple of seconds in white space/optimization time, while the actual execution time is just a few ms. I am not sure what it could be optimizing that could take that long, as the queries were just a simple select (Ive tried with and without conditions).

It seems that in the Union All View the optimization time is: Optimization time of each table * Number of tables (since that is being done on a single thread). The reason I think there might be a problem is because on some occasions the same queries returned in less than a second for View and just about 400ms for individual tables.

The Sybase IQ version I'm using is 15.

[Gisung · Accepted solution]

Hi,

It might spend a long time to retrieve FP lookup tables for some output columns to the memory.

Try to test with the following option that is saving some time for this issue.

Please let me know whether it's helpful or not in IQ15x.

set temporary option dml_options6 = 16777216;

//To disable DFO tree replacements for delayed projection optimization.

Regards,

Gi-Sung Jang

[Former Member · Accepted solution]

Hi Gi-Sung,

I tried setting this option but unfortunately it did not have any effect on the query.

[Former Member · Accepted solution]

That "delayed projection transform" optimization is the most likely source of the problem. But there are two places that do that optimization- one very yearly in the optimizer (to affect the plan generated) and one late (to catch any extra opportunities after most of the optimizer transforms are done changing the query plan around).

The option suggested option disables the second pass (which I would have also suggested as a first try). To disable the earlier optimization codepath, set dml_options6=8388608

To disable both early and late passes, you can either add them together (dml_options6=25165824) or you can set a different parameter dml_options9=8 Both settings will do the same thing (disable all delayed projection optimizations), so you probably want to only to set one of those.

[c_baker · Accepted solution]

Make sure all tables involved in the union have the same indexing for each table, especially HG indexes.

You mention that this union is also simple selects.  Are you running a 'select *'?  Try narrow down the query to only the columns you need.  As mentioned earlier, the FP lookup into -iqlm cache for each individual table could be taking time to figure out.  Are the FPs flat?  Is -iqlm sized large enough?

Chris

[Former Member · Accepted solution]

David I tried both options, but they had no effect on the query performance. Is there a way to disable all optimizations, just to check if its actually the optmization thats causing the delay or something else?

[markmumy · Accepted solution]

Can you post a post-execution HTML query plan?  One with no options, Then one with each of the other options?

Mark

[Former Member · Accepted solution]

There is no one switch to turn off all optimizations - an interesting idea, but technically a little tough because the optimizer was not originally written to support that. There are probably 200+ individual switches to turn off specific pieces of the optimizer. Most are undocumented dml_options "bits" like the ones suggested, and some are documented - for example, you can turn off the join optimizer with a boolean option "join_optinizer" set to off. Note: I doubt this is a join optimizer issue.

Another less likely possibility is that query tree parallelism is taking long. That can be disabled with dml_options8=8.

As Chris suggested, there could be cache issues (not enough memory in the main cache or -iqlm might cause problems with lookup FP indexes). Another tool Chris suggested is to reduce the number of columns. Many columns can have a big performance effect.

What IQ version is this?

[Former Member · Accepted solution]

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.