Please post the DDL and PLAN of the query. Does your query have sarg for some of these columns and joins on other columns or maybe sarg on almost all...

--see the results yourself..Intersect will perform better then other two and if proper indexes will be there the diff will be huge.

Intersect perform badly in Dwain's test because he has...

Do not use the method posted by me.That was wrong..Below one is correct..

Try all 3 methods for your query and see which one is the best.If there is clustred index...

You have to change that query..I just used a table a.Change it as per your requirement... like a will be your table name,id will be ordno and cd will be...

Gullimeel (7/9/2012)

--------------------------------------------------------------------------------

Try something like this.

--change the id with ordno and cd with product code

select * from

(

select id,

max(case when cd = 'A' then 1 else 0 end) +

max(case when...

Try something like this.

select count(ordno) from

(

select OrdNo,

sum(distinct(case when ItemNo = 'A' then 1 when ItemNo = 'B' then 2 when ItemNo = 'C' then 4 end)) as status

from OrdLine

where...

Post the plan of your query and ddl for tables.People might be able to help u on getting your query optimized..

rCTE is a kind of loop.If this loop works with set of data and very few loops(recursion) results are good.but if it does row by row it wont be so...

Try below..Modify it as per your need.

;WITH Abstraction(pYear, pMonth, pValue, jan,feb,mar,april,may,june,july,aug,sep,oct,nov,decb) AS

(

SELECT 1999

,12

,cast (0 as float)

,cast (0 as float)

,cast (null as float)

,cast (null as float)

,cast (null as float)

,cast (null as...

Are you using fillfactor for your...

You have view in the query so it is not possible to create the indexed view.Also, indexes views are dynamic and are refreshed automatically.But it has many more restriction.

For more...

Personally, i think people consider just the avg fragmentation counter to decide whether to rebuild the indexes or not.I think using this counter alone is a bad idea.It mainly impcats...

Can you please let me know if this in actually cause delete/insert for updarte? I couldnt see this behaviour.

Only way to do it is manually.Unless these tables are from same linked server and have some relationship.Even then you might have to check it manually because sometimes joins...

For perforamnce issue you need to give the showplan,ddl and others details.Please see in my signature how to post the perf issue.