Sql-server – How to optimise this query

optimizationperformancequery-performancesql server

Script to create Tables Person and Company

USE optimisationTesting

CREATE TABLE Person(Id int not null, Name nvarchar(60), StreetAddress nvarchar(60)  
                  , Height int, CompanyId int)
CREATE TABLE Company(Id int not null, Name nvarchar(60))
GO

ALTER TABLE [dbo].[Person]  ADD CONSTRAINT [PK_Person]  PRIMARY KEY CLUSTERED ([Id] ASC)
ALTER TABLE [dbo].[Company] ADD CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED ([Id] ASC)
GO

DECLARE @i int = 0

WHILE @i < 100000 BEGIN

    INSERT INTO Person(Id, Name, StreetAddress, Height, CompanyId) 
    VALUES (
        @i, 
        CONVERT(nvarchar(60), newid()), 
        CONVERT(nvarchar(60), newid()), 
        140+rand()*60, 
        rand()*100) 

    SET @i = @i + 1
END

SET @i=0
WHILE @i < 100 BEGIN

    INSERT INTO Company(Id, Name) values (@i, convert(nvarchar(60), newid()))

    SET @i = @i + 1
END

-------------------------------------------------------------------------------

set statistics time on

select *
    from 
    Person p 
    where p.Name like 'a%'

set statistics time off

The average elapsed time after three execution was 363ms.
How can this query be further optimized to find out the names of people that starts with A?

Best Answer

Misread your post and wrote up a post telling you how to optimize the inserts, which seemed like an odd activity to do. The real query to optimize is this:

select *
from 
Person p 
where p.Name like 'a%';

That query gets back around 6100 rows out of 100000 rows for me. Even if you create a simple index on the name column it is unlikely that the query optimizer will use it.

create index Person__name ON Person (name);

select *
from 
Person p  WITH (INDEX (Person__name)) 
where p.Name like 'a%';

Disclaimer that I'm only using that hint for testing purposes. You don't want to use something like that in production unless you really know what you're doing. That hint forces the optimizer to use the index but I get worse results than not using it. If you truly need that query to be faster my only ideas are that you'll need to create a covering index, you'll need to change the structure of the table, or maybe there are some small gains to be had by writing the filter condition in a different way.

Here is what I mean by a covering index:

create index Person__name ON Person (name)
INCLUDE (StreetAddress, Height, CompanyId)

That index avoids the key lookups that you get with a simple index because every necessary column is included in it. However, the size of that index will be similar in size to the table. Any DML on the table will be slower because the index needs to be maintained.

You could change the clustering index from ID to name. That would have significant impacts on DML performance and on other queries, but it would make your query faster.

Perhaps instead of LIKE there is another string operator that you can use. I doubt that such changes would have a significant impact on your query's runtime.

Related Question