StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSelect * faster than select column
text
Body
copied!<pre><code>WITH Categories (child_oid, Level) AS ( SELECT h.child_oid, 0 AS Level FROM Memx_productcatalog.dbo.ME_CatalogHierarchy AS h JOIN dbo.[ME_CatalogProducts] c on h.oid = c.oid WHERE c.CategoryName = 'Root' UNION ALL SELECT h.child_oid, Level + 1 FROM dbo.ME_CatalogHierarchy AS h JOIN Categories AS p ON p.child_oid = h.oid ) --End CTE Recursive SELECT p.oid --problem here FROM dbo.ME_CatalogProducts as p WHERE p.oid IN (SELECT child_oid FROM Categories) </code></pre> <p>I'm writing a recursive CTE SQL statement to pull items from a tree. The query works fine. When I select a specific column(s) the query executes in ~300ms. However when I use <code>select *</code> or <code>p.*</code>, the query executes under 100ms. This is the exact opposite of what I would expect. I have checked indexes, statistics, and both queries seem to generate the same execution plan. I'm stumped on this one.</p> <p><strong>Update</strong></p> <p>I've been running this query all day with consistent results. I've attempted to disable caching by using OPTION (RECOMPILE). I've just been using the "Wait time on server replies" in sql manager to measure the query execution (is that bad?) Here is what happens when I use SET STATISTICS TIME ON.</p> <p>p.oid => SQL Server Execution Times: CPU time = 203 ms, elapsed time = 270 ms. "Wait time" = 195ms</p> <p>p.* => SQL Server Execution Times: CPU time = 469 ms, elapsed time = 1015 ms. "Wait time" = 21ms</p> <p>I have the other stats if they are needed. Is the client wait time time the wrong way to measure these things?</p>

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