10

I'm just curious.

Say you have a table of 1 million records/ rows.

select order_value from store.orders

Does it make a difference whether that table has 1 field, 2 fields, or 100 fields, in actual query time? I mean all fields other than "order_value."

Right now I'm pushing data to a data warehouse. Sometimes I dump fields into the table that "may be used in the future, someday" - but they aren't being queried right now, by anything. Would these 'extraneous' fields affect select statements that do no include them, directly or indirectly (no * I mean)?

  • There is tons of information on this available on the web. The key is getting the most recent info as technology changes. What you are asking is so dependent on your particular setup that it is not possible to give a very good answer. A key point to remember is that as we move to SSD, many things that were once very important to performance is no longer the case. – Joe Oct 6 '17 at 14:50
10

This really depends on indexes and data types.

Using the Stack Overflow database as an example, this is what the Users table looks like:

NUTS

It has a PK/CX on the Id column. So it's the entirety of the table data sorted by Id.

With that as the only index, SQL has to read that whole thing (sans the LOB columns) into memory if it's not already there.

DBCC DROPCLEANBUFFERS-- Don't run this anywhere near prod.

SET STATISTICS TIME, IO ON 

SELECT u.Id
INTO  #crap1
FROM dbo.Users AS u

The stats time and io profile looks like this:

Table 'Users'. Scan count 7, logical reads 80846, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 2406 ms,  elapsed time = 446 ms.

If I add an additional nonclustered index on just Id

CREATE INDEX ix_whatever ON dbo.Users (Id)

I now have a much smaller index that satisfies my query.

DBCC DROPCLEANBUFFERS-- Don't run this anywhere near prod.

SELECT u.Id
INTO  #crap2
FROM dbo.Users AS u

The profile here:

Table 'Users'. Scan count 7, logical reads 6587, physical reads 0, read-ahead reads 6549, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 2344 ms,  elapsed time = 384 ms.

We're able to do far fewer reads and save a little CPU time.

Without more information about your table definition, I can't really try to reproduce what you're trying to measure any better.

But you're saying that unless there is a specific index on that lone column, the other columns/ fields will also be scanned? Is this just a drawback inherent to the design of rowstore tables? Why would irrelevant fields be scanned?

Yes, this is specific to rowstore tables. Data is stored by the row on data pages. Even if other data on the page is irrelevant to your query, that whole row > page > index needs to be read into memory. I wouldn't say that the other columns are "scanned" so much as the pages they exist on are scanned to retrieve the single value on them relevant to the query.

Using the ol' phonebook example: even if you're just reading phone numbers, when you turn the page, you're turning last name, first name, address, etc along with the phone number.

  • @jpmc26 It can get worse than that, because if the requested columns are all part of an index, the query can be served merely by looking at the index. If the columns are not indexed, they can cause the primary record to be loaded, and even secondary records for non-custered table/column types. – Christopher Schultz Oct 6 '17 at 17:53
12

It depends, on the table structure and the available indexes.

  • Case A: Common (rowstore) table, no index on (order_value).

    The only possible execution plan is to read the whole table (which is of course much different when it's 2 vs 200 columns, so a few vs a few thousand bytes wide).

  • Case B: Common table, there is an index on (order_value) or some other indexes that include that column.

    There is a better plan now, scan the whole index (one of them) - which is of course much more narrow than the whole table, just a few bytes. Which makes irrelevant if the table has 2 or 200 columns. Only the index is scanned.

  • Case C: It's a columnstore table.

    As the name implies, the structure of these tables are column-wise oriented, not row-wise. There is no need for any index, the table design itself is suited for reading entire columns.

  • My knowledge is a bit green on this issue. It's most conventional (say typical SQL Server database) to have rowstore tables, correct? Why would the whole table be scanned if only one column/ field needs to be returned? Is this just inherent to the design of rowstore tables? – user45867 Oct 5 '17 at 22:28
  • @user45867 yes, the data are stored in rows (except some very big columns that are stored outside). When SQL Server reads from the disk, it reads in whole blocks, it can't read only the part that has one column. – ypercubeᵀᴹ Oct 5 '17 at 22:31

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.