you can just convert the whole thing and strip out the characters. milliseconds is 3 digits so you'll have to truncate the result from 17 to 16 digits.

select substring(...

for the last time: the 150s query is not a problem! it is not optimized (it's actually our day/week query) but it already runs faster than the quarterly...

Peso (2/5/2008)

---

antonio.collins (2/5/2008)

---

the query that produces our 22K row ranked result set takes 150s. i was looking for some top N wizardry to cajole the query engine to skip...

Peso (2/5/2008)

---

My suggested algorithm fetches only the top 10 records based on the column that ranking is calculated on.

I then calculate the ranking for only these 10 records.

Then I eventually...

the query that produces our 22K row ranked result set takes 150s. i was looking for some top N wizardry to cajole the query engine to skip producing most...

peso: you're trying to speed up the selection of items from the cte/temp table and that's not the issue. even in my original temp table approach, it took...

Matt Miller (2/5/2008)

---

Notice that my latest calls your Temp table solution, the CTE solution.

You really should give some of this a whirl. Running RANK() to the whole...

peso: that's the CTE version that was discussed earlier. it performs the same as the temp table version if the expression is only referred to once.

as i...

cdun2 (2/4/2008)

---

with the CASE nested in the SUM, I got this:

Msg 130, Level 15, State 1, Line 5

Cannot perform an aggregate function on an expression containing an aggregate or a...

... using the Rank() function against the TOP(N) with TIES subquery will return your results faster than the RANK() function against the whole set, since the top(N) is an ordering...

You're missing the point I'm trying to make: the two are related. By not giving it something to work with that it "likes" (i.e. appropriate indexing, etc...), and by requiring...

SELECT top 1000

ds.AcctCode,

ds.TxnDate,

SUM(isnull(ds.FuelFee,0)) + SUM(isnull(ds.CashFee,0)) + SUM(isnull(ds.ScFee,0)) AS TotalDailyRevenue,

--"MTD" = ?,

--"YDT" = ?,

ps.TC,

CASE

WHEN ps.Proj = 100 THEN 'New Account'

WHEN ps.Proj = 200 THEN 'Current Account'

END AS ProjStatus,

ps.FSR,

ps.SubmitRep1

FROM

TxnRptg.dbo.tbl_DailySummary ds

INNER JOIN...

Matt Miller (2/4/2008)

---

First - have you looked at using the WITH TIES syntax?

Second - the fact that you're using single-column indexes to do this points to why everything is slow.

The...

Matt Miller (2/4/2008)

---

What index are you using? Are you sure the compiler even used it?

All of the items to posted had the Top(N) syntax in it...

Without the RIGHT index...

In this particular query, top N is showing no efficiency benefits at all. Here are representative timings from 2 runs of each technique.

Top N:

set statistics time on

go

select top (10)

itemId,...