Yes, pulling just the data you need is really important.

I was asked the other day why a customer was having performance issues on a table. A simple SELECT that only returns 1134 rows was taking over a minute!

I did some looking because that sounds really odd. I mean that’s a LOT of time for just over a thousand rows. Ok, so I check the server speed, drive speed, anything blocking, wait stats etc. All the usual suspects. No luck. Then I checked the table size:

exec sp_spaceused [TableName];

That’s an pretty big table for the number of rows. Particularly given that it’s all data and almost no index. Ok, let’s look at the time/io breakdown:

SET STATISTICS TIME, IO OFF
SELECT [Col1], [Col2], [Col3], [Col4], [Col5]
      ,[Col6], [Col7], [Col8], [Col9], [Col10]
  FROM [dbo].[TableName]
GO

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

(1134 rows affected)

Table ‘TableName’. Scan count 1, logical reads 182, physical reads 0, read-ahead reads 0, lob logical reads 53742, lob physical reads 18, lob read-ahead reads 27384.

SQL Server Execution Times:

CPU time = 172 ms, elapsed time = 69110 ms.

Hmm, 182 logical reads. Not a whole lot. But wait, lob logical reads 53742, lob physical reads 18, lob read-ahead reads 27384. Or a total of 81144 reads. That’s a fair amount. In case you weren’t aware lob stands for large object. Lob objects are things like varchar(max), nvarchar(max), filestream, xml and varbinary. Not all of the data will be in an lob page. For example my understanding is if you use a varchar(max) to store 15 bytes it will still be stored in the normal data page. It’s only when it no longer fits it gets pushed to an lob page. Either way I looked over the data types for the table. There were some INTs, VARCHARs, a couple of DATETIMEs and an XML.

An XML. Ok, let’s try this differently.

SET STATISTICS TIME, IO OFF
SELECT [Col1], [Col2], [Col3], [Col4], [Col5]
      ,[Col6], [Col7], [Col8], [Col9] /*, [Col10] */  FROM [dbo].[TableName]
GO

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

(1134 rows affected)

Table ‘TableName’. Scan count 1, logical reads 181, 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 = 0 ms, elapsed time = 129 ms.

Well look at that. One less logical read, no lob reads and it took all of 129 ms. That’s 537 times faster.

This prompted a quick conversation with the client.

Me: Do you really need to pull that XML column every time you do the query?
Client: Well we need to keep it. We need that data.
Me: I get that. But when you do the query to pull all the rows do you need the XML for all of them at the same time? Or do you look at the basic info in a grid, scroll through it and then bring up a single detail page with the XML for just that single row.
Client: Oh yea. That’s exactly what we do.

So yes, not pulling a single column, that wasn’t needed most of the time, on a tiny, tiny table made a huge difference. Only pull the data you need people. * is a really bad habit, and even in this case where they were pulling the columns by name they still need to pay attention and only pull the columns they were actually going to use.