SQL Server Performance

Removing subquery

Discussion in 'T-SQL Performance Tuning for Developers' started by ramkumar.mu, Jul 30, 2006.

  1. ramkumar.mu New Member

    SELECT APPLID
    ,EFFDT
    ,NAME
    FROM PS_APPL_DATA_EFFDT A
    WHERE A.EFFDT = (SELECT MAX(B.EFFDT)
    FROM PS_APPL_DATA_EFFDT B
    WHERE B.EFFDT = A.EFFDT
    AND B.EFFDT <= GETDATE())

    is there anyway that subquery can be avoided???

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  2. ramkumar.mu New Member

    Just now tried...

    SELECT APPLID
    ,EFFDT
    ,NAME
    FROM PS_APPL_DATA_EFFDT A
    WHERE A.EFFDT <= GETDATE()
    GROUP BY EFFDT
    ,APPLID
    ,NAME



    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  3. ramkumar.mu New Member

    Hi Tan,

    Your query gives me the correct result. but when i executed the below given queries in a batch and see the execution plan, it gives me 22%, 7%, 71%. i think the Order by clause in the query you suggested might be consuming the time.

    SELECT APPLID
    ,EFFDT
    ,NAME
    FROM PS_APPL_DATA_EFFDT A
    WHERE A.EFFDT = (SELECT MAX(B.EFFDT)
    FROM PS_APPL_DATA_EFFDT B
    WHERE B.EFFDT = A.EFFDT
    AND B.EFFDT <= GETDATE())


    SELECT APPLID
    ,EFFDT
    ,NAME
    FROM PS_APPL_DATA_EFFDT A
    WHERE A.EFFDT <= GETDATE()
    GROUP BY EFFDT
    ,APPLID
    ,NAME

    SELECT APPLID
    ,EFFDT
    ,NAME
    FROM PS_APPL_DATA_EFFDT A
    WHERE A.EFFDT <= GETDATE()
    ORDER BY A.EFFDT DESC

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  4. FrankKalis Moderator

  5. ramkumar.mu New Member

    I was trying to replace the subquery with some cost-efficient query. As i have subscribed to this topic (earlier not now), Tan's reply reached my mailbox. so i made a reply here and then i saw that Tan's reply is not displayed here, but i have received a mail from Tan as below...


    quote:
    Hello ramkumar.mu

    khtan has replied to a topic on SQL-Server-Performance.Com Forum that you requested notification to. Regarding the subject - Removing subquery.

    You can view the posting athttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=15818
    Here is the text of the message:

    *****************************************

    try this.

    SELECT APPLID, EFFDT, NAME
    FROM PS_APPL_DATA_EFFDT A
    WHERE A.EFFDT <= GETDATE()
    ORDER BY A.EFFDT DESC

    If it does not gives you what you want, post some sample data and the exepcted result.

    *****************************************

    This is an auto-generated email from the Forums, please do not reply to this email.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  6. FrankKalis Moderator

    Aah, I see.
    Well, if Tan's query gives the expected result, why don't you use it? The ORDER BY in the query is not really necessary. It does not have an effect, other than the cosmetical sorting, on the query. You can also sort at the client, which is sometimes much more effective and faster.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
  7. ramkumar.mu New Member

    I guess Tan's query doesnt work without that "Order By desc " clause.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  8. Roji. P. Thomas New Member

    quote:Originally posted by ramkumar.mu

    I guess Tan's query doesnt work without that "Order By desc " clause.

    No. ORDER BY will not affect the selection of rows in this case. (It can have an impact when used with TOP)

    Roji. P. Thomas
    http://toponewithties.blogspot.com
  9. Roji. P. Thomas New Member

    Ramkumar,

    If I understood your requirements right, you want to get the rows from the table where the date matches the last business day.

    If thats the case, the queries you posted subsequently will not yield the same results as the first query.

    Can you please post the DDL + Sample rows + Desired Output ?

    Roji. P. Thomas
    http://toponewithties.blogspot.com
  10. ramkumar.mu New Member

    quote:Originally posted by Roji. P. Thomas


    quote:Originally posted by ramkumar.mu

    I guess Tan's query doesnt work without that "Order By desc " clause.

    No. ORDER BY will not affect the selection of rows in this case. (It can have an impact when used with TOP)

    Roji. P. Thomas
    http://toponewithties.blogspot.com


    Aah! i made a mistake. it will work without order by too.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  11. ramkumar.mu New Member

    quote:Originally posted by Roji. P. Thomas

    Ramkumar,

    If I understood your requirements right, you want to get the rows from the table where the date matches the last business day.

    If thats the case, the queries you posted subsequently will not yield the same results as the first query.

    Can you please post the DDL + Sample rows + Desired Output ?

    Roji. P. Thomas
    http://toponewithties.blogspot.com



    I did some more testing with various dates and everything yielded the same result for the first query and the second one (solution i gave)

    Anyway here are those you asked for...

    -- The below query is in a view
    SELECT APPLID
    ,EFFDT
    ,NAME
    FROM PS_APPL_DATA_EFFDT A
    WHERE A.EFFDT = (SELECT MAX(B.EFFDT)
    FROM PS_APPL_DATA_EFFDT B
    WHERE B.EFFDT = A.EFFDT
    AND B.EFFDT <= GETDATE())

    Sample Rows (after exec the above query)
    -------------------------

    A0529975 2004-08-13 00:00:00.000
    A0552081 2004-09-15 00:00:00.000 Arblaster,David
    A0532496 2004-08-17 00:00:00.000 Diaz Canovaca,Sergio
    A0595189 2004-09-29 00:00:00.000 Edwards,Chloe
    A0534709 2004-08-24 00:00:00.000 O Banahene,Dorin
    A0560671 2004-09-25 00:00:00.000 O Neill,Lorraine
    A0535576 2004-08-23 00:00:00.000 Royal,Charlotte Jane

    Desired output:
    ---------------
    The same resultset but the query needs some tuning so that it is written simpler and executes faster.



    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  12. TheSQLGuru New Member

    I assume you have an index on the EFFDT column? Even so it is going to be an ugly query plan if there are lots of rows in the table. A correlated subquery on a date field can be expensive.

    Also, I don't really get the 'logic' of the query. What is your objective? Is it to get the rows with the max date that is before now? That doesn't jive with the results you presented, which have several different days included.

    I would like to see sample RAW data from the table (with a full table create script), and what you are expecting for output, as well as a textual description of what you are driving at with this query. May be able to help better then.



    SQLGuru
  13. Roji. P. Thomas New Member

    Ramkumar,

    Your query is logically equivalent to Tan's query. You dont need a correlated subquery in this case.

    And if you have an index on EFFDT then Tan's query should be the best perming one.

    Roji. P. Thomas
    http://toponewithties.blogspot.com
  14. ramkumar.mu New Member

    Thanks Roji and all.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."

Share This Page