8:56 a.m.
Hi,
When querying for a list of objects using ejb-ql, the keyword 'distinct'
is used to de-duplicate the result set and give a correct list of
objects. However I have noticed that everytime distinct is specifed in
the ejb-ql query it also gets specified in the sql query. I am curious
as the reasoning behind this. I understand that distinct is used to
remove duplicates from the SQL result set, but from what I can see, for
the majority of simple use cases the sql distinct is superflous.
Running the hibernate generated query without the distinct returns the
same results. I assume that the distinct is in there for are reason,
are there some more complicated use cases for which the distinct in
necessary?
My main concern is performance, is there anyway of performing an ejb-ql
"select distinct..." without hibernate issuing an sql "select
distinct...".
Regards,
Michael Barker.
9:15 a.m.
The in-memory distincting is actually a temporary solution. There are a
few JIRA cases making up the ultimate solution.
But specifically to your questions, the DISTINCT applied to the query is
intended to limit the data transferred by the JDBC driver from server to
client, especially on large result sets or across poor networks. No,
this is not controllable aside from using a custom query translator.
Michael Barker wrote:
Hi,
When querying for a list of objects using ejb-ql, the keyword
'distinct' is used to de-duplicate the result set and give a correct
list of objects. However I have noticed that everytime distinct is
specifed in the ejb-ql query it also gets specified in the sql query.
I am curious as the reasoning behind this. I understand that distinct
is used to remove duplicates from the SQL result set, but from what I
can see, for the majority of simple use cases the sql distinct is
superflous. Running the hibernate generated query without the
distinct returns the same results. I assume that the distinct is in
there for are reason, are there some more complicated use cases for
which the distinct in necessary?
My main concern is performance, is there anyway of performing an
ejb-ql "select distinct..." without hibernate issuing an sql "select
distinct...".
Regards,
Michael Barker.
_______________________________________________
hibernate-dev mailing list
hibernate-dev(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
9:32 a.m.
Thanks Steve,
Could you point me to the JIRA tasks, I would be interesting in seeing
what the ultimate solution looks like?
The thing that confuses me is that distinct generally doesn't come for
free (generally resuls in a sort by the DB). If the result set is going
to be the same anyway, isn't there an advantage to excluding the
distinct clause, or is it simply not possible to determine which queries
will benefit from it and which won't?
Regards,
Michael Barker.
Steve Ebersole wrote:
The in-memory distincting is actually a temporary solution. There
are
a few JIRA cases making up the ultimate solution.
But specifically to your questions, the DISTINCT applied to the query
is intended to limit the data transferred by the JDBC driver from
server to client, especially on large result sets or across poor
networks. No, this is not controllable aside from using a custom
query translator.
Michael Barker wrote:
> Hi,
>
> When querying for a list of objects using ejb-ql, the keyword
> 'distinct' is used to de-duplicate the result set and give a correct
> list of objects. However I have noticed that everytime distinct is
> specifed in the ejb-ql query it also gets specified in the sql
> query. I am curious as the reasoning behind this. I understand that
> distinct is used to remove duplicates from the SQL result set, but
> from what I can see, for the majority of simple use cases the sql
> distinct is superflous. Running the hibernate generated query
> without the distinct returns the same results. I assume that the
> distinct is in there for are reason, are there some more complicated
> use cases for which the distinct in necessary?
>
> My main concern is performance, is there anyway of performing an
> ejb-ql "select distinct..." without hibernate issuing an sql "select
> distinct...".
>
> Regards,
> Michael Barker.
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev(a)lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>
9:37 a.m.
On Feb 19, 2007, at 3:32 PM, Michael Barker wrote:
>> However I have noticed that everytime distinct is specifed in
the
>> ejb-ql query it also gets specified in the sql query. I am
>> curious as the reasoning behind this.
You are wondering why a DISTINCT you put into your query is actually
send to the database in the generated SQL?
9:43 a.m.
Could you point me to the JIRA tasks, I would be interesting in
seeing
what the ultimate solution looks like?
JIRA is searchable. Look for things marked
as 'query-hql' as the
component... The basic idea is to drop the collection-valued fetches.
The thing that confuses me is that distinct generally doesn't
come for
free (generally resuls in a sort by the DB). If the result set is
going to be the same anyway, isn't there an advantage to excluding the
distinct clause, or is it simply not possible to determine which
queries will benefit from it and which won't?
It depends on the quite a few
things like the particular database, db
server options, the width and depth of the selected dataset, the JDBC
driver, the network, etc. All-in-all, it is not feasible to detect when
to do it and when not to do it.
5:29 p.m.
Hi,
There is a small patch in JIRA (HHH-2440) that allows the user to
specify that the generated SQL should not contain the distinct keyword.
Mike.
Steve Ebersole wrote:
The in-memory distincting is actually a temporary solution. There
are a
few JIRA cases making up the ultimate solution.
But specifically to your questions, the DISTINCT applied to the query is
intended to limit the data transferred by the JDBC driver from server to
client, especially on large result sets or across poor networks. No,
this is not controllable aside from using a custom query translator.
Michael Barker wrote:
> Hi,
>
> When querying for a list of objects using ejb-ql, the keyword
> 'distinct' is used to de-duplicate the result set and give a correct
> list of objects. However I have noticed that everytime distinct is
> specifed in the ejb-ql query it also gets specified in the sql query.
> I am curious as the reasoning behind this. I understand that distinct
> is used to remove duplicates from the SQL result set, but from what I
> can see, for the majority of simple use cases the sql distinct is
> superflous. Running the hibernate generated query without the
> distinct returns the same results. I assume that the distinct is in
> there for are reason, are there some more complicated use cases for
> which the distinct in necessary?
>
> My main concern is performance, is there anyway of performing an
> ejb-ql "select distinct..." without hibernate issuing an sql "select
> distinct...".
>
> Regards,
> Michael Barker.
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev(a)lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>
6271
days inactive
6274
days old
7 comments
3 participants
participants (3)
-
Christian Bauer -
Michael Barker -
Steve Ebersole