SQL Server Performance Forum – Threads Archive

Removing subquery

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…"

---

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…"

---

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…"

---

I might be a bit slow on a Monday morning, but can you explain what you are trying to do? —
Frank Kalis
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

---

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…

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

---

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

---

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…"

---

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

---

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

---

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…"

---

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…"

---

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

---

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

---

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

---

]]>