Sybase IQ 16.1 issue

[former_member199543]

Hello

We have IQ 16.1 on AIX and hundreds of tables in it, since this is POC we use default settings of IQ, with no specific parameter switches.

I have noticed that few SQL with the same structure runs much slower compared to other ones, 3 minutes and more compared to 6-10 seconds. There are default HG indexes on Join columns and nBIT of rest of them

The performance leak, at least I think so, is because of one table, because if we use this table, then performance goes down. So, I dropped it and created/loaded with data once again and suddenly no performance issues - all queries runs perfectly fine.

I have seen this now and then, and always i recreate the table and get this resolved, but it should not be like this.

What could be wrong with this picture?

[former_member552099]

Mike,

Quote from SAP "Row fragmentation occurs when rows are deleted. SAP IQ only free the page when all rows are deleted from the page."

You can run simple select to see the difference between count and last row id:
select count(*), max(rowid(t)) from owner.tablename t;

Also, regardless of POC approach - did you use recommended RAM settings?
~30% for each of the memory part (-iqlm, -iqmc, -iqtc) and leave minimum 10% and maximum 50-100G of RAM to your OS

What about your storage? RAW devices, I assume? is it from dedicated or shared storage?
Can it be thin provisioned? (it takes extra time to expand with every new data chunk load)

-Albert

[former_member199543]

Hello, Albert!

These are transaction (non aggregates) data from a retailer , no partitions etc. I try to avoid update/deletes, only bulk inserts into DW, Structure of these large fact tables does not change at all, at least I do not remember that, views are used if more specific calculations are needed.

More technical: i was testing the object with sp_iqindexfragmentation and

sp_iqrowdensity, but did not face any issue there, everything was per SAP IQ best practices. Index fragmentaiton was more that 93%

Mike

[former_member552099]

How about fragmentation of this table?

Is it under heavy update/delete usage?