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.

Query

2 posts in General Discussion Last posting was on 2012-07-05 19:38:54.0Z
roburgos Posted on 2012-06-04 12:51:53.0Z
Sender: 4d88.4fcc7be0.1804289383@sybase.com
From: roburgos
Newsgroups: sybase.public.iq
Subject: Query
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4fccaf69.57ae.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 4 Jun 2012 05:51:53 -0700
X-Trace: forums-1-dub 1338814313 172.20.134.41 (4 Jun 2012 05:51:53 -0700)
X-Original-Trace: 4 Jun 2012 05:51:53 -0700, 172.20.134.41
Lines: 18
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4512
Article PK: 246652

I'm executing this query and it takes a long time, specially
the part "count(distinct
DMDECMS_COD_PERSONA||'|'||DMDECMS_COD_OFERTA)".

Could you help me to modify it?.

The select is:

select
DMDECMS_FECHA_DATOS ,DMDECMS_FL_TENENCIA_IPLUS,
count(distinct DMDECMS_COD_PERSONA||'|'||DMDECMS_COD_OFERTA)
from
DM_DE_CARTERA_MENSUAL_SVAS
group by
DMDECMS_FECHA_DATOS ,
DMDECMS_FL_TENENCIA_IPLUS

Thanks


queryplande Posted on 2012-07-05 19:38:54.0Z
Sender: 62eb.4ff5e363.1804289383@sybase.com
From: queryplande
Newsgroups: sybase.public.iq
Subject: Re: Query
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ff5ed4e.6458.1681692777@sybase.com>
References: <4fccaf69.57ae.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 5 Jul 2012 12:38:54 -0700
X-Trace: forums-1-dub 1341517134 172.20.134.41 (5 Jul 2012 12:38:54 -0700)
X-Original-Trace: 5 Jul 2012 12:38:54 -0700, 172.20.134.41
Lines: 37
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4524
Article PK: 246663


> I'm executing this query and it takes a long time,
> specially the part "count(distinct
> DMDECMS_COD_PERSONA||'|'||DMDECMS_COD_OFERTA)".
>
> Could you help me to modify it?.
>
> The select is:
>
> select
> DMDECMS_FECHA_DATOS ,DMDECMS_FL_TENENCIA_IPLUS,
> count(distinct
> DMDECMS_COD_PERSONA||'|'||DMDECMS_COD_OFERTA) from
> DM_DE_CARTERA_MENSUAL_SVAS
> group by
> DMDECMS_FECHA_DATOS ,
> DMDECMS_FL_TENENCIA_IPLUS
>
> Thanks

Hello!
As I do not know your table definition and data I suggest
first to avoid string operations (||).
So you may try grouping of grouped data.
Choose the right indexes may help also. Try LF or HG on each
individual column.
Suggestion statement:
SELECT DMDECMS_FECHA_DATOS, DMDECMS_FL_TENENCIA_IPLUS,
count(*)
FROM (SELECT DMDECMS_FECHA_DATOS ,DMDECMS_FL_TENENCIA_IPLUS,
DMDECMS_COD_PERSONA, DMDECMS_COD_OFERTA
FROM DM_DE_CARTERA_MENSUAL_SVAS
GROUP BY DMDECMS_FECHA_DATOS,
DMDECMS_FL_TENENCIA_IPLUS, DMDECMS_COD_PERSONA,
DMDECMS_COD_OFERTA) a
GROUP BY DMDECMS_FECHA_DATOS, DMDECMS_FL_TENENCIA_IPLUS

Have fun ...