I use SET TRANSACTION ISOLATION LEVEL in all my reporting where the data does not have to be up to the minute (which is most reports most of the time, if you think about it. You could also use WITH (NOLOCK) query hints on the individual tables, which amounts to the same thing, but can be more granular -- in other words you could apply it to some tables but not all in your query.

The most important thing, however, for rapid query performace is good indexing and using joins that employ valid Search Arguments ("SARGs"). There is absolutely no substitute for these. I have been able to get up to 400% performance gains from queries involving millions of records from index and WHERE clause tweaking.

See esp. Chapters 15-17 of Inside SQL Server 2000 by Kalen Delaney.

Avoid curors like the plague, if at all possible, use a WHILE loop, if you can.