Sander A. (2/21/2010)

---

There is a big change we will do that in the near future. My boss wants every query to run within several seconds...... and I have no idea...

A much more efficient query plan is produced from this code:

DECLARE...

I would need to sit down with you for several hours to give that query and plan the discussion it deserves 😉

Nevertheless:

1. In your original post, you said the...

The ROW_NUMBER() method will often be the fastest available, and a very consistent performer, but there are cases where a query based on an APPLY might be faster still. ...

You could also look into why the SQL Server is generating so much log activity. There might be a quick win there.

In an ideal world, the DBA would just...

SELECT TOP (10)

MIN[QuestionID],

[Title],

...

The question of why there was a delay can be answered by considering the internal structure of log files.

Books Online has an excellent description of it:

Transaction Log Physical Architecture

Transaction Log...

It does seem like an unusual way to copy stuff around - replication springs to mind as an alternative.

Nevertheless, given that the code exists, and you just need a way...

That's nice code Chris. It represents a nice compromise between the benefits of an indexed view, and the drawbacks related to the maintenance of aggregates for that view. ...

Hello again,

It's a lot more difficult for people like me, who know nothing about your data, to understand what you mean, even with that longer explanation.

Maybe I'm just not very...

chris.nilsson (2/21/2010)

---

Thanks Paul:

It is ad-hoc--it comes in as a query passed in from the web front end, rather than as a stored proc or the like.

I will try...

ChrisM@home (2/21/2010)

---

Blimey, what was I thinking? :blush: Of course it works like this, it would be inconsistent for it to work any other way. I guess I got "confined" by...

No worries! It's a small thing, but helps a lot, yes.

BSavoie (2/21/2010)

---

So below is...

None of the replies so far explain the increase in worktable reads.

Personally, I doubt the query is disk-bound; it is more likely that the rewrite introduced a spool of some...

Do we need one? If so, ([Location], [PartNumber]) seems like a candidate.