Re: Running counter
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 12/28/04
- Next message: MichaelK: "#Temp table question"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 28 Dec 2004 10:05:40 +0100
On Mon, 27 Dec 2004 16:08:54 -0800, MichaelK wrote:
>Thanks Hugo.
>Interesting idea and works, but takes very long.
>Unfortunately can't use it, need something faster.
>
>Hey guys any other ideas?
Hi Michael,
Here's another way to write the same query, that might perform quicker
(you'll need to test it on your end to see if it's better for you):
SELECT a.au_id, a.au_lname, a.au_fname,
1 + COUNT(*) AS rank
FROM authors AS a
INNER JOIN authors AS b
ON b.au_lname < a.au_lname
GROUP BY a.au_id, a.au_lname, a.au_fname
ORDER BY rank
Another important thing to consider is that the column you use to base the
ranking on should be indexed. A nonclustered index on only that column
would be best for this query. (Of course, the final choice of indexes
should be based on ALL queries, plus the impact of more indexes on insert,
update and delete performance).
Derrick's suggestion might be useful as well. Probably faster than any
truly relational approach. But you should be aware that multi-row inserts
can be handled in any order - even if you write INSERT INTO ... SELECT ...
ORDER BY ..., SQL Server is still free to choose another ordering when
inserting the rows.
Usually, the rows WILL be inserted (and given identity values) in the
order you specify, but this behaviour is NOT guaranteed.
You'll find more useful ideas on this subject in Aaron's article, which is
here: http://www.aspfaq.com/show.asp?id=2427. Be sure to also follow the
links near the end of this article.
Best, Hugo
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: MichaelK: "#Temp table question"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
