3

On multiple questions here and on stack overflow i saw people saying in the comments and answers that select * from table is almost always an antipattern without any explaination why. Althrough I can sort of deduce why it is an antipattern. I may be looking over a detail that someone else with better understanding of the issue noticed.

So here is my question, why people say that select * is an antipattern.

  • 3
    In most cases you do NOT need all fields. Again in most cases the time needed to transfer the output to a client via network overpassed the time needed to produce this output on the server. Removing unneeded fields from output decreases the output size and so decreases the time needed to transfer it to client. Ever if both server and client are running on the same OS instance unneeded fields in output increases the memory size needed to store it on the server side and to accept it on the client side. So in most cases the asterisk instead of needed fields list wastes time and resources. – Akina Oct 16 '18 at 10:08
5

The two reasons that I find the most compelling not to use SELECT * in SQL Server are

  1. Memory Grants
  2. Index usage

Memory Grants

When queries need to Sort, Hash, or go Parallel, they ask for memory for those operations. The size of the memory grant is based on the size of the data, both row and column wise.

String data especially has an impact on this, since the optimizer guesses half of the defined length as the 'fullness' of the column. So for a VARCHAR 100, it's 50 bytes * the number of rows.

Using Stack Overflow as an example, if I run these queries against the Users table:

SELECT TOP 1000 DisplayName
FROM dbo.Users AS u
ORDER BY u.Reputation;

SELECT TOP 1000 DisplayName, u.Location
FROM dbo.Users AS u
ORDER BY u.Reputation;

DisplayName is NVARCHAR 40, and Location is NVARCHAR 100.

Without an index on Reputation, SQL Server needs to sort the data on its own.

NUTS

But the memory it nearly doubles.

DisplayName:

NUTS

DisplayName, Location:

NUTS

This gets much worse with SELECT *, asking for 8.2 GB of memory:

NUTS

It does this to cope with the larger amount of data it needs to pass through the Sort operator, including the AboutMe column, which has a MAX length.

NUTS

Index Usage

If I have this index on the Users table:

CREATE NONCLUSTERED INDEX ix_Users
    ON dbo.Users
(
    CreationDate ASC,
    Reputation ASC,
    Id ASC 
 );

And I have this query, with a WHERE clause that matches the index, but doesn't cover/include all the columns the query is selecting...

SELECT u.*, 
       p.Id AS [PostId]
FROM   dbo.Users AS u
JOIN   dbo.Posts AS p
ON p.OwnerUserId = u.Id
WHERE  u.CreationDate > '20171001'
       AND u.Reputation > 100
       AND p.PostTypeId = 1
ORDER BY u.Id

The optimizer may choose not to use the narrow index with a key lookup, in favor of just scanning the clustered index.

NUTS

You would either have to create a very wide index, or experiment with rewrites to get the narrow index chosen, even though using the narrow index results in a much faster query.

NUTS

CX:

 SQL Server Execution Times:
   CPU time = 6374 ms,  elapsed time = 4165 ms.

NC:

 SQL Server Execution Times:
   CPU time = 1623 ms,  elapsed time = 875 ms.
  • While this answer is very detailed, I wonder in my naivety if problems you have put through are SQL server implementation centric. – Thomas E. Oct 16 '18 at 12:28
  • 1
    @ThomasE. At least the part with key lookups or table scans will be valid for a lot of RDBMS – Tom V - Team Monica Oct 16 '18 at 12:30
  • @ThomasE. I noted in the beginning of my answer that they are. I can't answer for other platforms. If you don't find my answer helpful, I'll delete it. – Erik Darling Oct 16 '18 at 12:38
  • @sp_BlitzEri I certainly found it helpful. I asked my question because I wondered if I could pull out some general ideas about other databases from it :) TomV 's comment gave me some pointers to what to think about from this answer. If I'll have the time I'll do some tests on database systems i'm more familiar with maybe I'll find out something useful. – Thomas E. Oct 16 '18 at 12:48
0

It is a question of control.

Using 'select * from...' leaves the number of returned columns and ordering of the returned columns undefined.

Many programmatic interfaces to the database depend on the number and order of returned values.

-2

Two main reasons:

Firstly, your database has to go and work out which columns are available on the table[s] that you're querying. That's an additional overhead and, in [older versions of] older DBMSs, that could be a [very] significant performance bottleneck if there was lots of contention around the System Catalog tables holding this information.

Secondly, it assumes that you know everything about these tables and that you always will. Database are inherently shared resources and will, over time, have many people working on them, changing the data structures to keep up with changing requirements. Today, you could create a table with two columns (say, "username" and "password_hash") and use that as the core of your Application's login screen. Everything works well for ages ... until someone [else] comes along and shoehorns into your tidy, little table a couple of dozen extra, "blob" fields, containing gigantic data or photos or whatever. Suddenly, your login screen slows to a crawl because the query is pulling back all ("*") those extra, gigantic, network traffic-bloating fields in which it has no interest at all!

Also, you can write code that depends on the order in which fields are returned (you shouldn't, but you can). This is most common in reporting circles, particularly if data is "handed off" to a User's spreadsheet program of choice. When the table structure changes, fields can "move around" and your (their?) data "suddenly" starts arriving in the "wrong" order.

Always ask for the columns that you need, in the order in which you need them.

And, just for completeness, remember the "order by " clause - rows in [Relational] tables have no intrinsic order.

  • 4
    I don't think the first paragraph is correct. Even if you specify all columns explicitly, the database has to look them up to validate that they exist, their data types and access privileges. Expanding a * to a list of values is most probably the smallest part of that – a_horse_with_no_name Oct 16 '18 at 11:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.