Glad its moving better,

Looking at you new plan , the issue is now with the amount of rows returned by the securitydata table.

I think this is because its being join...

This link seems appropriate. I quote "Whenever I see a table with repeating column names appended with numbers, I cringe in horror".

http://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/

Hardly "One small step for man"....

Congratulations to "The Thread"

use case in your order by clause

order by case when column =0 then 1 else 0 end asc, column

Ive left for the day now but will try to get a chance to look at this....

Can you post all of the code to ?

Try updating the statistics , on particularly ContractSecurity and SecurityData. The estimates are well out from the actual row counts.

Have you updated your statistics ?

The actual row counts are vastly difference from the estimated.

The only CLR routine that I have written , that wasnt "hello world", is to call and mail a SSRS report.

http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/08/27/direct-emailing-of-ssrs-reports-via-sqlclr.aspx

Just like cursors and udfs , from what i...

Try this link

http://blogs.msdn.com/psssql/archive/2009/12/09/high-cpu-with-reporting-services-2008.aspx

Chris Morris-439714 (12/10/2009)

---

Interesting - from BOL "Character literals and variables are assigned the default collation of the current database". So the default collation of your db is different to that...

More than likely this is a parameter sniffing issue.

Try this link

http://www.databasejournal.com/features/mssql/article.php/3841271/T-SQL-Best-Practices--Parameter-Sniffing.htm

Something like this ? untested , also assumed that the can be independently null

select ProductId ,

ValiFrom,

...

Use a calendar table.

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

lookup "datepart" in BOL