Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2009
    Posts
    8

    Unanswered: what is wrong with this quuery?

    Hi, Can some one explain me how to optimise this query?

    select disticnt(csuser.id), csuser.loginName,csuser.displayName,loginSession.l astlogindatetime,loginSession.activesessions,usert ypes.csusertypeid from User csuser join userroleassign csuserroleassign on csuserroleassign.roleid=52 left outer join userloginsession csloginSession on csloginSession.userid=csuser.id left outer join userusertypeassign usertypes on usertypes.userid=csuser.id limit 15;

    If I don't add that distcint keyword, query is returning results in 18 milli seconds; but if I add distcinct it did not return results in two miunutes and finally I have to stop the exection of the query.

    Interestingly, if I say select distinct(user.id) from user; then it is pretty quick and returning in 15 milliseconds.

    Sorry for asking such a basic question but I'm not that good at SQL.

    Thanks and Regards,
    Samba

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    DISTINCT is ~not~ a function

    if you put a column in parentheses after DISTINCT, that does not change what DISTINCT does -- DISTINCT applies to all columns in the SELECT clause

    DISTINCT is implemented by a sort on the result set, sorting on all columns, then looking for duplicate rows, i.e. duplicate in all columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2009
    Posts
    8

    Thanks

    thanks for the clarification

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Did you miss a join condition on the userroleassign csuserroleassign table? Your example shows the only limiting criteria on that table is the role id being a 52. Without join criteria to your other tables this would create a cartesian product, which could explain the length of time it takes to sort all rows. Also, when you say data is returned in 18 milliseconds without the distinct, is that all rows or just a first subset of the rows?
    Dave

  5. #5
    Join Date
    Jun 2009
    Posts
    8
    Hi Dave,
    I meant that the first subset of data is returned in around 18 millisconds if I don't say distinct, which obviously leaves out some records which do not in the subset.

    what I have:

    User*----*Role
    User-----*loginsession
    User----*UserType
    User-------*Profile
    defaultProfile-----*Address
    Address----phone

    here userroleassign and userusertypeassign are the jooin tables for many to many associations between user==>role and user==>usertype respectively.

    Now I want some fields from user,some fields from usertype,some fields from
    loginSession,and phone number from his defaultprofile-->adresss where user has a certain role.

    I could not come up with a query that gives me the above explained data set.

    Can you help build a query that gets the above resultset?

    Thanks and Regards,
    Samba
    Last edited by saasira; 07-24-09 at 13:10.

  6. #6
    Join Date
    Nov 2006
    Posts
    82
    Quote Originally Posted by r937
    DISTINCT is ~not~ a function

    if you put a column in parentheses after DISTINCT, that does not change what DISTINCT does -- DISTINCT applies to all columns in the SELECT clause

    DISTINCT is implemented by a sort on the result set, sorting on all columns, then looking for duplicate rows, i.e. duplicate in all columns
    I'm afraid it is not true. Distinct do not have to aplly to all columns, if you use
    Code:
    dictinct on (..)
    then distinct will apply only to those column you write in parentheses

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rski
    I'm afraid it is not true. Distinct do not have to aplly to all columns, if you use
    Code:
    dictinct on (..)
    then distinct will apply only to those column you write in parentheses
    yes, but....
    Quote Originally Posted by da postgresql manual
    The DISTINCT ON clause is not part of the SQL standard and is sometimes considered bad style because of the potentially indeterminate nature of its results.
    my advice is to stay away from non-standard SQL unless there is a real good reason

    and this aint one of them

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2006
    Posts
    82
    OK, but you can't say that
    if you put a column in parentheses after DISTINCT, that does not change what DISTINCT does -- DISTINCT applies to all columns in the SELECT clause
    cos in postgres it is not true .

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rski
    cos in postgres it is not true .
    it is so true!!!

    you are getting confused

    in postgresql, DISTINCT ON and DISTINCT are two completely separate things

    i was talking about putting an expression into parentheses after the DISTINCT

    DISTINCT ON is completely different semantics
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2006
    Posts
    82
    You are of course right

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •