If(Select(count(*) from orders where shipvia=3>0)
The execution Plan shows sqlserver has to read whole rows in orders table ,You can achieve same result byIf exists(select * from orders where shipvia=3>0)
You will see a major speed improvement
2. In sub queries can be replaced with Left outer join
Select * from Customers where customer rid not in(select customer rid from order)
Replace with outer join
Select c.* from Customers c left outer join orders o On o.customerid=c.customerid where o.customerid is null .