Quadruple post, finish the thread here :

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=211788

Quadruple post, finish the thread here :

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=211788

Ok, do not use cursors .

Has someone added and index, column whatever to that table while the cursor was running?

Damn I'm gonna be god one day... I pretty much have a gun waiting right beside the server for anyone who wants to come close

You can always use the numbers table instead and recreate the dates on the fly just like I did to populate the table.

If your DBA doesn't want a numbers table...

It's called parameter sniffing. There's a few articles on that subject on this site. Basically when a sp is first used, a plan is created and saved, but...

On behalf on Noeld : HTH .

try that with the new sp :

declare one variable by parameter and transfer the parameters values into those variables, then use those variables in the select. then compare...

well you still have you try what I proposed for the where condition and the join (with indexed calculated column). That's speed up the thing a lot.

Try that...

Hey bulldozer... been a while .

I think my solution is slightly faster that Farrell's .

olap systems are not the same... they have report tables that are refreshed every x minutes... so you don't wait for the query to run.

Can you put the function code...

You have to find a way to make sure they use the datarange. a scan of 1 M rows just can't be fast.

Glad to hear that. Maybe you should drop a line to the other DBAs, maybe that'll ring a bell for something they did in the last x months.

Create a calendar table (all dates of the year for any range of years needed then :

Select Date from dbo.Calendar where Date bewteen @StartRange and @EndRange and datepart(dw, Date)<...

So how exactly are you using this function in the main select (join to it, or use in a subquery)?