StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POfilter first or join first?
text
Body
copied!<p>I originally just wrote a query to find out annual total order number per customer larger than 1. In 1.query, I filtered the result set and join it with another result set which found out customer name. Curiously, I guess filter first would produce better performance since less results needed to join. So I wrote second query to join first and then filter, which looks neater than first query. Result is the same as I expect since all time in the result is lower. But I am not sure which time is most important? Or this case is just a coincident? How to think about performance? </p> <pre><code>use [AdventureWorks2012] set statistics time on; --1.filter first,join second select tempC.*,tempP.FirstName,tempP.LastName from (select Year(OrderDate) As OrderYear,CustomerID,count(CustomerID) As CustomerOrderAmt from Sales.SalesOrderHeader group by Year(OrderDate),CustomerID having count(CustomerID) >1 ) as tempC join( select p.FirstName,p.LastName,c.CustomerID from Person.Person as p join Sales.Customer as c on c.PersonID=p.BusinessEntityID ) as tempP on tempC.CustomerID=tempP.CustomerID order by tempC.OrderYear,tempC.CustomerID GO --2.join first,filter second select Year(so.OrderDate) As Orderdate,so.CustomerID,count(so.CustomerID) As CustomerOrderAmt,p.FirstName,p.LastName from Sales.SalesOrderHeader as so join Sales.Customer as C on so.CustomerID=c.CustomerID join Person.Person as p on c.PersonID=p.BusinessEntityID group by Year(so.OrderDate),so.CustomerID,p.FirstName,p.LastName having count(so.CustomerID)>1 go </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload