developers-list Digest, Vol 2, Issue 7

developers-list-request-d/

2012-10-19 10:00:01 UTC

This message was forwarded from developers-list-d/***@public.gmane.org The MonetDB
mailing lists have moved to monetdb.org. Please subscribe to
developers-list-d/***@public.gmane.org, and unsubscribe from this list.
See: http://mail.monetdb.org/mailman/listinfo/developers-list

Send developers-list mailing list submissions to
developers-list-d/***@public.gmane.org

To subscribe or unsubscribe via the World Wide Web, visit
http://mail.monetdb.org/mailman/listinfo/developers-list
or, via email, send a message with subject or body 'help' to
developers-list-request-d/***@public.gmane.org

You can reach the person managing the list at
developers-list-owner-d/***@public.gmane.org

When replying, please edit your Subject line so it is more specific
than "Re: Contents of developers-list digest..."

Today's Topics:

1. Re: ANSI SQL 2003 Extension OVER() appears to be missing in
MonetDB Click to flag this post (Niels Nes)
2. Re: ANSI SQL 2003 Extension OVER() appears to be missing in
MonetDB Click to flag this post (James Becker III)
3. datetime extract function performance (metin d)

----------------------------------------------------------------------

Message: 1
Date: Thu, 18 Oct 2012 22:10:06 +0200
From: Niels Nes <Niels.Nes-rh8NL+***@public.gmane.org>
To: "Communication channel for developers of the MonetDB suite."
<developers-list-d/***@public.gmane.org>
Subject: Re: ANSI SQL 2003 Extension OVER() appears to be missing in
MonetDB Click to flag this post
Message-ID: <20121018201006.GA17059-+7+HFsgkf2oxr/***@public.gmane.org>
Content-Type: text/plain; charset="utf-8"

Well OVER isn't implemented as function more as a language feature. So
no you won't find the 'over' functions in the functions table.

Before I inundate you fine people with my need to understand, is there any
documentation for Monet's OVER implementation? This would save me from
prematurely posting potentially na?ve questions like this to this list.
Here is a sample of the code in vertica that needs to be ported. There are
more complex examples, but for the spike, I simply need a fair sample
query set to take Monet into a full POC phase of testing.
SELECT
col1
,col2
, FIRST_VALUE(school_name) over (partition by school_loc_code order by
academic_year_code::integer desc)
as school_name
,blah?
-- large array of nested subqueries
-- then the dense_rank()over(
, dense_rank()over(
order by account_code
, school_group_inst_code
, school_loc_code
, grade_code
, subject_code
, course_code
, staff_sid
, section_sid
, student_sid
, attribute_category_code
, attribute_value_code) entity_num
I have attached the entire query in it's unmodified vertica form for
deeper analysis, if you are willing to give it a gander. It's kind of
hairy, but the OVER() bits are rather discreet.

The FIRST_VALUE isn't implemented, but shouldn't be all that difficult
to add. The dense rank is and should be directly usable.

Currently indeed the OVER isn't fully described in the documenation.

Niels

Thanks again.
~jj

Hello MonetDB Community.
My name is James Becker, my friends call me Jaimi, and I am new to
posting messages here, so I hope you will bear with me.
I am conducting a POC on MonetDB to see if we can use it to replace
Vertica for a large scale read mostly educational reporting
application. So far things are showing promise, but the legacy reports
lean heavily on OVER(), which doesn't appear to be part of the 11.11.11
release. Am I missing something, or is this the case?
If anyone who has run into a similar limitations has any insights as to
where I have made a wrong turn, or how to either implement OVER() in
MonetDB using either MAL/C, or any suggestions for query work-arounds
that would yield similar results, this would be greatly appreciated.
Thx.
~jj

James
OVER is supported, but no all of its functionality. Could specify which
functions you would like to use with over? Also which windowing
limitations would be required?
We sofar focused on the row_number and rank functions.
Niels

_______________________________________________
developers-list mailing list
http://mail.monetdb.org/mailman/listinfo/developers-list

--
Niels Nes, Centrum Wiskunde & Informatica (CWI)
Science Park 123, 1098 XG Amsterdam, The Netherlands

_______________________________________________
developers-list mailing list
http://mail.monetdb.org/mailman/listinfo/developers-list

--
Niels Nes, Centrum Wiskunde & Informatica (CWI)
Science Park 123, 1098 XG Amsterdam, The Netherlands
room L3.14, phone ++31 20 592-4098 sip:4098-3SA25f8um+Dz+***@public.gmane.org
url: http://www.cwi.nl/~niels e-mail: Niels.Nes-rh8NL+***@public.gmane.org
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 198 bytes
Desc: not available
URL: <http://mail.monetdb.org/pipermail/developers-list/attachments/20121018/ac458a80/attachment.sig>

------------------------------

Message: 2
Date: Thu, 18 Oct 2012 21:32:45 +0000
From: James Becker III <jbecker-***@public.gmane.org>
To: Communication channel for developers of the MonetDB suite.
<developers-list-d/***@public.gmane.org>
Subject: Re: ANSI SQL 2003 Extension OVER() appears to be missing in
MonetDB Click to flag this post
Message-ID: <CCA5E8CD.2F7A%jbecker-***@public.gmane.org>
Content-Type: text/plain; charset="utf-8"

Niels?

The FIRST_VALUE isn't implemented, but shouldn't be all that difficult
to add. The dense rank is and should be directly usable.

Yes, I have tweaked that out of what I am working with currently.

Currently indeed the OVER isn't fully described in the documenation.

Is how this is implemented in Monet documented at all? I can't find any
reference to it.

I have been testing things that work against other vendors DB's in Monet
to get a feel for it with mixed results.

If not, in which section of the ANSI documents might I find this bit of
information?

Thx.

~jj

Well OVER isn't implemented as function more as a language feature. So
no you won't find the 'over' functions in the functions table.

Before I inundate you fine people with my need to understand, is there
any
documentation for Monet's OVER implementation? This would save me from
prematurely posting potentially na?ve questions like this to this list.
Here is a sample of the code in vertica that needs to be ported. There
are
more complex examples, but for the spike, I simply need a fair sample
query set to take Monet into a full POC phase of testing.
SELECT
col1
,col2
, FIRST_VALUE(school_name) over (partition by school_loc_code order by
academic_year_code::integer desc)
as school_name
,blah?
-- large array of nested subqueries
-- then the dense_rank()over(
, dense_rank()over(
order by account_code
, school_group_inst_code
, school_loc_code
, grade_code
, subject_code
, course_code
, staff_sid
, section_sid
, student_sid
, attribute_category_code
, attribute_value_code) entity_num
I have attached the entire query in it's unmodified vertica form for
deeper analysis, if you are willing to give it a gander. It's kind of
hairy, but the OVER() bits are rather discreet.

The FIRST_VALUE isn't implemented, but shouldn't be all that difficult
to add. The dense rank is and should be directly usable.
Currently indeed the OVER isn't fully described in the documenation.
Niels

Thanks again.
~jj

Hello MonetDB Community.
My name is James Becker, my friends call me Jaimi, and I am new to
posting messages here, so I hope you will bear with me.
I am conducting a POC on MonetDB to see if we can use it to replace
Vertica for a large scale read mostly educational reporting
application. So far things are showing promise, but the legacy reports
lean heavily on OVER(), which doesn't appear to be part of the
11.11.11
release. Am I missing something, or is this the case?
If anyone who has run into a similar limitations has any insights as
to
where I have made a wrong turn, or how to either implement OVER() in
MonetDB using either MAL/C, or any suggestions for query work-arounds
that would yield similar results, this would be greatly appreciated.
Thx.
~jj

_______________________________________________
developers-list mailing list
http://mail.monetdb.org/mailman/listinfo/developers-list

--
Niels Nes, Centrum Wiskunde & Informatica (CWI)
Science Park 123, 1098 XG Amsterdam, The Netherlands

_______________________________________________
developers-list mailing list
http://mail.monetdb.org/mailman/listinfo/developers-list

--
Niels Nes, Centrum Wiskunde & Informatica (CWI)
Science Park 123, 1098 XG Amsterdam, The Netherlands

------------------------------

Message: 3
Date: Fri, 19 Oct 2012 02:34:02 -0700 (PDT)
From: metin d <metdos-/***@public.gmane.org>
To: "developers-list-d/***@public.gmane.org" <developers-list-d/***@public.gmane.org>
Subject: datetime extract function performance
Message-ID:
<1350639242.20556.YahooMailNeo-+7zY+oFt7UyvrfWm4H71L5EhsgyP+***@public.gmane.org>
Content-Type: text/plain; charset="iso-8859-1"

Hey,

I'm running a small performance test on MonetDB and Postgres.?According?to my results "extract" function in monetDB (http://www.monetdb.org/Documentation/SQLreference/Temporal) is nearly 10x slower then Postgres.?

Do you know the reason, or do you have any idea which can make "extract" function faster in MonetDB ?

Thanks,
Metin
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.monetdb.org/pipermail/developers-list/attachments/20121019/688d4db5/attachment.html>

------------------------------

_______________________________________________
developers-list mailing list
developers-list-d/***@public.gmane.org
http://mail.monetdb.org/mailman/listinfo/developers-list

End of developers-list Digest, Vol 2, Issue 7
*********************************************