Like Operator.

Question

Like Operator.

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

DonaldW SSCarpal Tunnel Points: 4525 More actions · Answer 1

GilaMonster (12/20/2007)
I'll try it out in 2005, see if the results are different.

Just for the heck of it I tried the following against a table with ~800000 rows and a non-unique, non-clustered index on the ssn column:

SELECT a.cust_id

FROM (

SELECT CUST_ID, LEN(SSN) AS UCI

FROM [CUST]

) AS A

WHERE A.UCI < 9

I got the following stats:

SQL Server Execution Times:

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

Table 'CUST'. Scan count 1, logical reads 2362, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:

CPU time = 230 ms, elapsed time = 230 ms.

(2080 row(s) affected)

I ran dbcc freeproccache first to make sure I wasn't skewing the results.

You and Jeff are WAY more advanced at this stuff than I am but I thought I'd put this out for your feedback.

EDIT: I forgot to clear the data buffers. When I ran dbcc dropcleanbuffers and tried again I got these stats:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times:

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

Table 'CUST'. Scan count 1, logical reads 2362, physical reads 2, read-ahead reads 2368.

SQL Server Execution Times:

CPU time = 321 ms, elapsed time = 1392 ms.

(2080 row(s) affected)

SQL Server Execution Times:

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

Significantly slower than you saw in your test.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

DonaldW (1/30/2008)
Significantly slower than you saw in your test.

Probably because you cleared the cache. I tend to prime the cache before running tests (run the query once so that I know the required results are in the data cache, then run for time)

The second or so difference between the cpu time and the elapsed was most likely the time taken by the IOs

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DonaldW SSCarpal Tunnel Points: 4525 More actions · Answer 3

GilaMonster (1/30/2008)
Probably because you cleared the cache. I tend to prime the cache before running tests (run the query once so that I know the required results are in the data cache, then run for time)

I usually run without clearing it, too, but I was curious to see the impact if there was nothing in the cache. The first set of results was with the data in the cache.