Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Location
    Detroit, MI
    Posts
    7

    Unanswered: Can SELECT be nested as

    I know you can do something like:

    SELECT
    ColumnA,

    (SELECT Columnb FROM Table Where...),

    ColumnC
    ...

    Can you select multiple columns, and how if possible, such as:

    SELECT
    ColumnA,

    (SELECT ColumnB, ColumnC, ColumnD FROM Table Where...),

    ColumnE
    ...

    If this is possible, how would the columns be aliased?

    Thanks in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, you can't do that, sorry

    in the SELECT list, you can have only scalar subqueries, i.e. that return only one value -- one row, one column, one value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and just because your first example CAN be done, doesn't mean it SHOULD be done. TSQL has this little concept called a JOIN...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2003
    Location
    Detroit, MI
    Posts
    7

    Well aware of JOIN ...

    I am well aware of the JOIN, and the sarcasm isn't appreciated. The
    tools included with Enterprise Manager haven't helped identify or
    resolve this issue.

    There are several columns identified in certain tables that cause the
    query time to go from an acceptable 10 seconds to 40 seconds when added
    to the SELECT statement. Each column added increases the time until
    the query time is over 8 minutes which is unacceptable.

    The columns are from a table that is already included in the query. We are not adding another table/join when selecting these columns. The column
    data types are NVARCHAR(40) and are not included in the WHERE clause.

    The only solution found so far is adding the columns as nested SELECT
    statements (all 15 of them) as shown in my original post. This is a
    real mess, but the client doesn't care as long as he gets the
    performance boost.


    Any ideas?

    JRA
    Last edited by James Aiello; 11-25-04 at 07:16. Reason: Critical text left out of original response

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ideas? yes, i would use joins and ensure that the join columns are properly indexed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2003
    Location
    Detroit, MI
    Posts
    7

    Nothing included that should affect the JOINS/indexes.

    I don't think indexes are the issue. Adding a single column from a
    table that's already in the query causes a massive decrease in
    performance. IE:

    SELECT
    TblA.ColumnA,
    TblB.ColumnC
    FROM
    TableA TblA,
    TableB TblB
    WHERE
    TblA.ID = TblB.ID

    The above runs in 10 seconds. The version below runs in 40 seconds.

    SELECT
    TblA.ColumnA,
    TblA.ColumnB,
    TblB.ColumnC
    FROM
    TableA TblA,
    TableB TblB
    WHERE
    TblA.ID = TblB.ID

    If we add another column from TableA the query time junps by 30
    seconds. Nothing is added except a column. Why would indexes affect
    this situation? I don't think they would.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    is the column being added a text field? that would be a significant increase in bandwidth and explain the decrease in performance
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why are people asking for free help these days so sensitive? Lighten up!

    I'm taking my sarcasm (and advice) and going home. Your client is paying you, so YOU figure it out.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, I've run out of sarcasm, so here is a suggestion:

    If you have what is known as a "covered index" on table A that includes both ColumnA and ID, then your first query is never actually accessing the data in Table A, because it can get all the info it needs just from the index alone. Adding in another field requires the server to actually look up that value in table A based upon the index value, and that takes additional time.
    So if this is the case, then its not that your second query is abnormally slow, but rather that SQL Server is able to process your first query extremely efficiently.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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