0

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?

  • 1
    Why did you include Company in your script if it's not being used in the SELECT query? – Tara Kizer Aug 30 '16 at 21:44
2

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.

2

Add an index to the name column:

CREATE NONCLUSTERED INDEX ix_Person_Name ON dbo.Person (Name)

It'll have to do a key lookup on the clustered index to get the data for the other columns. If this is a frequent query to be run, consider adding the other columns as INCLUDEs to the above index to avoid the key lookup:

CREATE NONCLUSTERED INDEX ix_Person_Name_includes ON dbo.Person (Name)
INCLUDE (Id, StreetAddress, Height, CompanyId)
0

WHILE in SQL server? that's a bad idea. I think the bottle neck here is the population of the number: If you want to populate numbers, you can read about this in this answer here: https://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table
The answer also gives you estimations.

(Sorry - missed the "A" part. Of course, add an index, as Tara had said)

0

I tested and optimized on my Laptop with I5 Proc, 8GB RAM & SQL Server 2014.

I executed given select statement which took 803ms to completed.

enter image description here

I found 2 ways to optimize it.

1. If your WHERE condition is dynamic (Means instead of one character you can filter on two or more) then create below index.

    CREATE NONCLUSTERED INDEX IDX_Name on Person(Name) WITH FILLFACTOR=90;

After I created the non clustered index, execution time decreased to 620ms.

enter image description here

below select statement will also improve performance after you create above non clustered index

    select *
        from 
        Person p 
        where left(p.Name,1) = 'a'

2. If WHERE condition is fixed (You will use only one character), then use below query. In this query I added a column Name_First_Char as PERSISTED.

    USE optimisationTesting
    GO

    CREATE TABLE Person(Id int not null, Name nvarchar(60), StreetAddress nvarchar(60)  
                      , Height int, CompanyId int**,Name_First_Char AS LEFT(Name,1) PERSISTED** )
    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

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

Then I thought to create a PERSISTED column and it improved performance and query completed in 429ms.

enter image description here

    **CREATE NONCLUSTERED INDEX IDX_Name on Person(Name) WITH FILLFACTOR=90;**


    set statistics time on

    select *
        from 
        Person p 
        where **p.Name_First_Char = 'a'**

    set statistics time off

At last I created a non clustered index on persisted column and it improved query performance a lot. It took only 229 sec.

enter image description here

Thanks

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.