April 1, 2009 at 10:38 am
Hi,
I am using SQL Server 2005 (9.0.1399). When I use some non-deterministic system functions in the where clause, the execution plan looks not good to me. Here is a example:
First, create a test table
use tempdb;
go
if object_id('test') is not null
drop table test;
go
create table test (UserName varchar(256), a varchar(100));
go
create unique clustered index cidx_uname on test(UserName)
go
insert into test values('Tom', 'test')
insert into test values('Jerry', 'test')
insert into test values('dbo', 'test')
insert into test values('Marry', 'test')
insert into test values('John', 'test')
go
then look at the execution plans of the following two queries:
select * from test where username = user_name()
select * from test where username = convert(varchar(256),user_name())
The plan for the first query is a clustered index scan, whereas the plan for the second query is a clustered index seek. When the tables are large and queries are more complex, the difference is quite significant.
I tried some other system functions. I found object_name() has the same behavior and getdate() is OK.
Could someone tell me why?
Thanks
April 1, 2009 at 12:01 pm
tianyongsun (4/1/2009)
select * from test where username = user_name()select * from test where username = convert(varchar(256),user_name())
The plan for the first query is a clustered index scan, whereas the plan for the second query is a clustered index seek. When the tables are large and queries are more complex, the difference is quite significant.
Could someone tell me why?
It's got nothing to do with deterministic/nondeterministic. It has to do with the data types that the system functions return.
user_name and object_name return nvarchars (can be easily confirmed by a check in books online). I would guess that your test table has username as a varchar column. SQL cannot directly compare a varchar with an nvarchar so one of the columns has to be converted. Since nvarchar has a higher precedence, it's the varchar column that gets converted. So, what's actually running (for your first example) is
select * from test where CAST(username AS NVARCHAR(256)) = user_name()
Since there is a function on the column in the where clause, an index seek is no longer possibel and SQL has to scan
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
April 1, 2009 at 2:00 pm
Thank you very much. This is really helpful.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply