2 Simple steps to speed up your SQL Server execution

1.Replace "Count (*) " with "exists" when checking for existence

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 by

If 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

Eg:
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 .