cancel
Showing results for 
Search instead for 
Did you mean: 

Slow performance of simple query on huge table

kimon_moschandreou
Contributor
0 Kudos

Dear all,

We have encountered slow performance on a simple query on a table of 5,8bn rows. It is

select *

from

where col='.....'

there is an HG index on the column mentioned in the where clause and I have attached the query plan. The query runs for about 2min 30 secs. If I ask only the column I have in Where clause, it returns instantly as expected. IQ version 15.4 ESD#2

I see that IQ table has 5.8bn rows and IQ page size is 64KB. Could this be the reason behind the problem?

Other queries where this table participates in joins are as fast as expected. Also there is no real problem while loading the table. sp_iqrowdensity shows density 1 for all FP indexes.

I have run out of ideas on this and any help is welcome.

Thank you in advance

PS I have attached plan.txt, rename it to .html to open the plan in html format

Accepted Solutions (0)

Answers (1)

Answers (1)

c_baker
Employee
Employee
0 Kudos

Remember IQ is a columnar database.  By running a select *, you are opening 51x64K page chains, not just one 64K page.  So IQ still has to put the row back together from all the columns it must access.

Also, the query plan show that this appears to be running single threaded.

Try reducing the query further to only the columns you really need.

Chris

kimon_moschandreou
Contributor
0 Kudos

Hello Chris,

I am aware of your remark. There was not a similar behavior until recently, although it seems such queries were not executing instantly.

Concerning the single-threaded execution, how could I make it use more threads?

Thank you for your kind help.

c_baker
Employee
Employee
0 Kudos

The query plan as sent is only an estimate from before a run.  Can you attach a query plan from an actual run?

What changed recently on the system, if this ran well before?  Different client connection, location, ?

Chris