Prev: select statement ,sum with calculation
Next: Return a specific string based on positions of Y/N data
From: Erland Sommarskog on 6 Dec 2009 12:49
Gert-Jan Strik (sorrytoomuchspamalready(a)xs4all.nl) writes:
> That is so unusual to see a new phenomenon when it has been around for
> decades. With your repro script I even get this virtually unbounded
> index seek on SQL Server 7.0. It goes to show how infrequently I use
> sp_executesql (which definitely seems to be a prerequisite), especially
> in combination with unicode.

sp_executesql is not a prerequisite, but the fact that it is a parameter
is likely to matter. My first attempt to use the fragments table I
introduce in my chapter was:

CREATE PROCEDURE map_search_one @wild varchar(80) AS
SELECT p.person_id, p.first_name, p.last_name, p.birth_date, p.email
FROM persons p
WHERE p.email LIKE '%' + @wild + '%'
AND EXISTS (SELECT *
FROM fragments_persons fp
JOIN wordfragments(@wild) w ON fp.fragment = w.frag
WHERE fp.person_id = p.person_id)

And SQL Server went for the index on email. Guess if I liked that. :-(

> As to the effectiveness of such a seek: it does not seem very effective,
> at least not for this situation. I just finished reading your chapter 17
> yesterday, and your setup gives a bit more data to play around, so I
> tried it on this 1,000,000 row persons table. The index "seek" does
> exactly the same amount of logical and physical reads as the index scan.
> So in this case, the seek doesn't add any value. My guess is, that these
> LikeRange functions only add value when the leading character is a
> regular character.

Well, let's say I have:

exec sp_executesql
N'select count(*) from bludder WHERE CustID_CS LIKE @s',
N'@s nvarchar(20)', '%W%'

There is no use with this Index "Seek" for this input, but let's that
the next call has @s = '%W', the plan is more efficient than a scan.
This makes sense, but both in my repro the SQL code includes an
explicit initial wild card, so the whole idea is useless. Apparently
the optimizer fails to consider that.

It was very interesting to hear that this is already in SQL 7. I know
they did some changes to help up LIKE searches with statistics in SQL
2005, so I assumed that this was new to SQL 2005

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

First  |  Prev  | 
Pages: 1 2 3 4 5
Prev: select statement ,sum with calculation
Next: Return a specific string based on positions of Y/N data