|
Hi!
Indeed, trying to simplify und generalize things, I
simplified my query until the grouping was useless. I am using it in a stored
procedure to visualize where users are coming from:
create dbproc sp_GetOnlPos
RETURNS CURSOR
AS
$CURSOR = 'ONLPOS_CURSOR'; DECLARE :$CURSOR CURSOR
FOR
select x,y,cnt=count(x) from dba.users u inner join dba.zipcodes z on
u.zip=z.zip and u.country=z.country_id where country_id='D' and
(PanLastLogin>=PanLastLogout OR (PanLastLogout is null and PanLastLogin is
not null))
group by y,x
order by y,x;
I need to get the distinct x,y positions, which are
stored in my "zipcodes" table, and the number of results for each
position.
As I pointed out, this operation seems to be quite
costly with sapdb. I don't think any 'view' will help by that, will
it?
Thanks!
(Gerald)
----- Original Message -----
Sent: Tuesday, March 25, 2003 9:36
AM
Subject: RE: Performance on grouping
clause
Gerald Nowitzky wrote:
>
> Hello!
> I have
got a question about the performance of the "group by"
> clause. I
am
> running SAPDB 7.4.3.14 on W2K.
> my query
>
>
select x,y from zipcodes where country_id='D' group by y,x
> order by
y,x
>
> with SAPDB runs about 1.100 msec on a given hardware, and
> just 160 msec with
> MS-SQL.
> the problem seems to be
the grouping of the result. without
> the grouping
> SAPDB is 50
msec.
>
> There is an Index on Y,X on table zipcodes. This helps
a lot
> for sorting,
> but not for the grouping.
>
>
Any hints how to speed this up??
> Thanks a lot!
>
(Gerald)
In your case the easiest way is to throw the group-by-clause
away
because it does nothing.
A little bit more earnest:
if grouping
and ordering has to be done with the same values,
the extra order-by-clause
can be thrown away.
It is as you said, index on x,y will not help for
grouping.
No, there is no 'how-to-speed-up-this' in general.
Elke
SAP Labs
Berlin
_______________________________________________
sapdb.general
mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
|
