Query Execution Time - with lesser cardinality

[Sathish J]

Hi   , 

I have query in IQ 15.4 , whose execution time varies based on column’s cardinality   value in where condition. 

SELECT  ****     FROM ******     ,  PACKAGE t10  WHERE  ****     AND 

 

     t10.PORTFOLIO_ID =  2   -- The execution time is less than 2 minutes 

 

The same query in below change in values

  

 t10.PORTFOLIO_ID =  1   -- The execution time is more than 10 minutes  .

  

Cardinality details :

 

 

I tired both HG index and LF index , no improvement in query performance of lesser cardinality. Is there anything more needs to be done to improve the performance?

Thanks in advance 

Regards ,

Sathish J  

[Jeff Younce]

J,

 Been a while since I worked with IQ (I'm a Oracle DBA) now. However, do a Group By on the values because there is a point where if the cardinlality is above a certain number (I forgot what the number is) that the query performance reduces significantly . Albeit there are more 2 in the table than 1?

Sorry I can't be of more help

--
You received this message because you are subscribed to the Google Groups "iqug" group.
To unsubscribe from this group and stop receiving emails from it, send an email to iqug+uns...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Dan Marina]

Hi Sathish,

There is not enough information for any investigation. Can you send the plans for both query executions?

regards,

/dan

--

[Ken Johnson]

Sathish, 

As your performance demonstrates, the higher cardinality is a performance boost in bitmap index based data stores like IQ.  Strategies for improving performance would be to prompt the optimizer / query plan to use the highest cardinality table first to restrict the subsequent query set. Alternatively, breaking up the query with an additional where clause which divides things up into 3 or 4 subqueries has worked for me (this often meant playing with the schema to denormalize things a bit if you have this luxury).   Other tacts I've taken is to create more query complexity - I've sometimes seen applications pulling in a bunch of stuff and then effectively doing additional query restrictions on the bucket of data being returned - delegating as much query complexity as possible actually speeds things up in IQ which was counter intuitive to many developers. 

hth

Ken

On Wed, Nov 25, 2015 at 1:38 AM, Sathish J <sathish...@gmail.com> wrote:

--