Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Mar 2004
    Posts
    74

    Unanswered: How to fine tune my query

    Good day All,

    if i have following query :-

    SCHEMA :
    Code:
    CREATE TABLE user_tbl
    (
    userid int, 
    primary_usr nvarchar(1),
    secondary_usr nvarchar(1)
    
    
    )
    QUERY
    Code:
    SELECT 
    [red]
             CASE 
                 WHEN (ISNULL(primary_usr,'') != '') THEN (SELECT USERID from user_tbl WHERE primary_usr = 'Y')
                 ELSE (SELECT USERID FROM user_tbl WHERE secondary_usr='Y')
    [/red]                  
    FROM user_tbl
    Basically, i need to get userid if primary_usr contain value of 'Y' else i would like to get seconard_usr.

    my question here is, in such scenario, how can i come out better solution to and which provide better performance.

    beside, what is the proper term for the query with red highlight ? is it call subquery ?

  2. #2
    Join Date
    Apr 2003
    Posts
    2

    Have you tried using Union All?

    SELECT USERID FROM user_tbl WHERE primary_usr='Y'
    UNION ALL
    SELECT secondary_usr FROM user_tbl WHERE (primary_usr <> 'Y' or primary_usr is null)

  3. #3
    Join Date
    Mar 2004
    Posts
    74
    if the data volume is huge , will union give better performance for this case ?

  4. #4
    Join Date
    Apr 2007
    Posts
    183
    Code:
    SELECT	userID
    FROM	userTbl
    WHERE	COALESCE(primaryUsr, secondaryUsr) = 'Y'

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think you need to restate your problem - it does not seem to make sense. You want to return the user id for all rows where either primaryUsr or secondaryUsr = 'y'?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    makes sense to me

    eleventy-seven users in the table, two are primary and five are secondary, plz return only the primary or secondary users
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Fair enough - just me then.

    In that case optimal would be to redesign the table to meet 1NF, new column indicating user type. NCI on that column. If this is 2005 then that is the query covered, otherwise add userid.

    If the design can't change, experiment with NCIs on primary_usr and secondary_usr. SQL:
    Code:
    SELECT    userID
    FROM    userTbl
    WHERE    'Y' IN (primary_usr, secondary_usr)
    and check SQL Server uses index intersection.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    poots, i think you ought to look up the definition of 1NF before you keep flinging it around so carelessly

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

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You don't think "primary user", "secondary user" is a repeating group? You'd add a "tertiary user" column if storing this information became a requirement?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's an old argument, one we've had several times on this forum before (c'mon, you're the archivist, you find them)

    are home_phone and cell_phone a repeating group? what about address_line1 and address_line2?

    1NF talks about non-atomic values -- the classic example is the list of numeric ids stored in a single VARCHAR column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Pretty well all normalisation references describe repeating groups as variations on atomic values.

    Your examples are not the same as this repeating group. These are (effectively) multiple BIT columns indicating a value from a single domain.

    More than likely there are transitive dependencies here too, but 1NF covers it for me.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    home_phone is a BIT column? what you smokin', boy?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Wrong way round Rudeboy.

    There is a strong case that your address and phone columns are not repeating groups as the values are not from the same domain. In these cases, the column name is not data - it describes the domain.

    For the two user columns, the OP has, in effect, two bit columns (in this case NVARCHAR(1), but logically bit columns) both of which flag data from a single domain. The column names are drawn from values in that domain.

    To summarise - you have argued that these are not repeating groups by taking two examples of non-repeating groups, and used these to invalidate my point. I do not believe your examples and this case are the same. In the UK, we call this an Aunt Sally
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Who or what is Aunt Sally?!
    George
    Home | Blog

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I can't even be bothered linking to justf....googleit.
    http://en.wikipedia.org/wiki/Aunt_Sa..._of_Aunt_Sally

    The term 'Aunt Sally' is in limited use as a political idiom, indicating a false adversary or straw man, set up for the sole purpose of attracting negative attention and wasting an opponent's energy.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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