Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

IQ 15.2 queries slower after creation of indexes (HG)

2 posts in General Discussion Last posting was on 2011-11-21 14:06:14.0Z
rjlpedrosa Posted on 2011-11-09 05:52:38.0Z
Sender: 195a.4eba12ea.1804289383@sybase.com
From: rjlpedrosa
Newsgroups: sybase.public.iq
Subject: IQ 15.2 queries slower after creation of indexes (HG)
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4eba1526.19ad.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 8 Nov 2011 21:52:38 -0800
X-Trace: forums-1-dub 1320817958 10.22.241.41 (8 Nov 2011 21:52:38 -0800)
X-Original-Trace: 8 Nov 2011 21:52:38 -0800, 10.22.241.41
Lines: 21
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4417
Article PK: 246558

Hello,

I'm doing tests with IQ 15.2 for a possible migration
from a different technology but I getting results that
aren't good.
Yesterday when the tables only had the FP indexes, a
simple query with group by joining a fact table and 2
dimensions took only about 300 secs. During the morning we
have created HG indexes on the ID columns, as recommended by
Index_Advisor but the query doesn't resolve, takes too long.
The fact table has more than 5 billion rows.
Doing the query just on the fact table runs ok.
I have taken the plan from the query and it's doing a
nested-loop pushdown but when I forced hash the times were
better.
I have already increased MAX_HASH_ROWS to 3.000.000.000
as it's a big machine.
Any ideas or recommendations? Should I go to IQ 15.3?
Will it use more cpu's per query?

Many thanks


IQRules Posted on 2011-11-21 14:06:14.0Z
From: IQRules <iqrules@noname.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.24) Gecko/20111103 Thunderbird/3.1.16
MIME-Version: 1.0
Newsgroups: sybase.public.iq
Subject: Re: IQ 15.2 queries slower after creation of indexes (HG)
References: <4eba1526.19ad.1681692777@sybase.com>
In-Reply-To: <4eba1526.19ad.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4eca5ad6@forums-1-dub>
Date: 21 Nov 2011 06:06:14 -0800
X-Trace: forums-1-dub 1321884374 10.22.241.152 (21 Nov 2011 06:06:14 -0800)
X-Original-Trace: 21 Nov 2011 06:06:14 -0800, vip152.sybase.com
Lines: 26
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4422
Article PK: 246561

Is this a many-to-many join (outer join) situation?

On 11/9/2011 12:52 AM, rjlpedrosa wrote:
> Hello,
>
> I'm doing tests with IQ 15.2 for a possible migration
> from a different technology but I getting results that
> aren't good.
> Yesterday when the tables only had the FP indexes, a
> simple query with group by joining a fact table and 2
> dimensions took only about 300 secs. During the morning we
> have created HG indexes on the ID columns, as recommended by
> Index_Advisor but the query doesn't resolve, takes too long.
> The fact table has more than 5 billion rows.
> Doing the query just on the fact table runs ok.
> I have taken the plan from the query and it's doing a
> nested-loop pushdown but when I forced hash the times were
> better.
> I have already increased MAX_HASH_ROWS to 3.000.000.000
> as it's a big machine.
> Any ideas or recommendations? Should I go to IQ 15.3?
> Will it use more cpu's per query?
>
> Many thanks