SELECT * Does Not Hurt Performance
People say SELECT * is bad for overhead. They might be right, but the difference is nothing to get excited about. See a side-by-side comparison and learn when not to use SELECT *.
Join the DZone community and get the full member experience.Join For Free
I read all the time how SELECT * hurts performance. I even see where people have said that you just have to supply a column list instead of SELECT * to get a performance improvement. Let’s test it, because I think this is bunkum.
I have here two queries:
SELECT * FROM Warehouse.StockItemTransactions AS sit; --and SELECT sit.StockItemTransactionID, sit.StockItemID, sit.TransactionTypeID, sit.CustomerID, sit.InvoiceID, sit.SupplierID, sit.PurchaseOrderID, sit.TransactionOccurredWhen, sit.Quantity, sit.LastEditedBy, sit.LastEditedWhen FROM Warehouse.StockItemTransactions AS sit;
I’m basically going to run these each a few hundred times from PowerShell. I’ll capture the executions using Extended Events, and we’ll aggregate the results.
I ran the test multiple times because, funnily enough, I kept seeing some disparity in the results. One test would show a clear bias for one method, another test would show the opposite. However, averaging the averages, we see that things broke down as follows:
That’s after about 2000 separate executions of each query. There’s a 2ms bias towards the Column List query, as opposed to the *. That’s an improvement, if you want to call it that, of 1%. It’s hardly worth the bother, assuming that with more testing, this continued to hold true. In multiple tests, the SELECT * ran faster. I just feel honorbound to put up the full results. They show an improvement, but not one I’d get excited about. Oh, and the reads, the execution plan, everything else… identical.
SELECT * Conclusion
Don’t get me wrong, there are lots of reasons to not use SELECT *. Yes, performance is one of the reasons to not use SELECT *. However, when most people suggest that maybe using SELECT * is a bad idea for performance reasons, what they’re saying is you ought to only move the columns you need and the data you are actually using, not everything. I’m not aware of anyone with experience and knowledge suggesting that using the complete column list instead of SELECT * is faster. As we can see in the tests above, it isn’t (or is by so small a margin, so who cares).
Published at DZone with permission of Grant Fritchey, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.