DOH!! Coffee clearly hadn't kicked in when I posted! At least I offered up a few improvements ... 🙂

Hardy21 (5/29/2012)

---

Lynn Pettis - you are right.

I have just given one option to change the query.

It will calculate and store filtered records in temp table so during delete, it will...

Brian Souder (5/16/2012)

---

I have a client with a Point of Sale system that the vendor of the PoS has left on a woefully undersized server. The vendor's recommendation was...

SQL Kiwi (5/27/2012)

---

htiteuf (5/23/2012)

---

With PDW AU3, I know stored procedures are supported but some functions in the SMP SQL Server product have not been implemented in SQL Server PDW, Transact-SQL...

Welsh Corgi (5/24/2012)

---

SQLKnowItAll (5/15/2012)

---

Assuming it is indexed properly, 10 million records should not be an issue. Let's say the join condition is called ID... Just do a select from...

Jayanth_Kurup (5/25/2012)

---

While i am enjoying this dicussion I wanted to point out there is a more obvious benefit to partioning than just the elimantion of unwanted partitions. Lock escalation occurs...

SOOOO many things could be at play here - you likely need to get a professional involved for a few hours to identify what the root cause is. Here...

Sean Lange (5/24/2012)

---

TheSQLGuru (5/24/2012)

---

Sean Lange (5/24/2012)

---

TheSQLGuru (5/24/2012)

---

Actually Grant I don't see even the slightest hint of a question from the OP - it is just a bunch of code. ...

Sean Lange (5/24/2012)

---

TheSQLGuru (5/24/2012)

---

Actually Grant I don't see even the slightest hint of a question from the OP - it is just a bunch of code. No request about...

I updated my post to include the connect bug link.

Here are my findings:

select MAX(mycol) from mytable_unpart where id<=10

NC INDEX scan

Table 'mytable_unpart'. Scan count 1, logical reads 283

select MAX(mycol)...

Gullimeel (5/24/2012)

---

I had put a real time example...

chandan_jha18 (5/24/2012)

---

TheSQLGuru (5/24/2012)

---

The left join on the MIN is going to suck in any case, but these are the real killers here I bet:

WHERE DsLocationCode = CAST(term.TerminalID AS VARCHAR)

AND...

The left join on the MIN is going to suck in any case, but these are the real killers here I bet:

WHERE DsLocationCode = CAST(term.TerminalID AS VARCHAR)

AND (sup.SupplierCode LIKE...

Actually Grant I don't...

I would have (probably clustered) index on your datetimestamp column. then do something like this:

declare @error int, @rowcount int

set @rowcount = 9999

while @rowcount > 0

begin

begin tran

...