Why would an indexed column could take 5 seconds to apply a where clause?

Question

Why would an indexed column could take 5 seconds to apply a where clause?

SSCrazy

Points: 2733

August 15, 2019 at 1:46 pm

I am testing a design and to implement my test, I've created a table with 1 milion rows in it. When running the test I am on 400 eDTUs so the eDTUs is supper big.

I am creating 4 different computed columns from a json column that I have to compare them together like this:

I am creating 4 different computed columns from a json column that I have to compare them together like this:

alter table tenants add ComputedContact as json_value([Json], '$.contact.email')
go

alter table tenants add ComputedContactIndexed as json_value([Json], '$.contact.email')
CREATE NONCLUSTERED INDEX IX_ComputedContactIndexed ON tenants(ComputedContactIndexed); 
go

alter table tenants add computedContactpersisted as json_value([Json], '$.contact.email') PERSISTED

go

alter table tenants add ComputedContactpersistedIndexed as json_value([Json], '$.contact.email') PERSISTED
CREATE NONCLUSTERED INDEX IX_ComputedContactpersistedIndexed ON tenants(ComputedContactpersistedIndexed);

Then I've tried to test the performance by running these queries:

set statistics time on 
SELECT TOP (1000) * FROM [dbo].[Tenants] where ComputedContact = 't10000@gmail.com'
go
SELECT TOP (1000) * FROM [dbo].[Tenants] where ComputedContactIndexed = 't10000@gmail.com'
go
SELECT TOP (1000) * FROM [dbo].[Tenants] where computedContactpersisted = 't10000@gmail.com'
go
SELECT TOP (1000) * FROM [dbo].[Tenants] where ComputedContactpersistedIndexed = 't10000@gmail.com'
go

set statistics time off

I was expecting to see some slow ones and some with only a few miliseconds specially the indexed one with persisted data, but to my surprise all of them took around 5-6 seconds

(1 row affected)

SQL Server Execution Times:
CPU time = 19312 ms, elapsed time = 5079 ms.
SQL Server parse and compile time: 
CPU time = 0 ms, elapsed time = 0 ms.

(1 row affected)

SQL Server Execution Times:
CPU time = 18829 ms, elapsed time = 6011 ms.
SQL Server parse and compile time: 
CPU time = 0 ms, elapsed time = 0 ms.

(1 row affected)

SQL Server Execution Times:
CPU time = 19342 ms, elapsed time = 5499 ms.
SQL Server parse and compile time: 
CPU time = 0 ms, elapsed time = 0 ms.

(1 row affected)

SQL Server Execution Times:
CPU time = 19890 ms, elapsed time = 5827 ms.
SQL Server parse and compile time: 
CPU time = 0 ms, elapsed time = 0 ms.

What am I doing wrong? Why is it so slow?

This topic was modified 4 years, 8 months ago by ashkan sirous.

Best Regards,
Ashkan

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 1

The optimizer may be deciding to ignore the index because of the SELECT * because it may be figuring that it's cheaper to do an index scan rather than do a whole lot of index seeks that also need to do a RID lookup so that it can return all of the columns demanded by the SELECT *.

You'll need to look at the actual execution plan to confirm.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ashkan sirous SSCrazy Points: 2733 More actions · Answer 2

Thanks a lot Jeff for the reply.

I've added a normal column and copied the value in it. I was expecting to see the same result as a persisted computed field on it but to my surprise, it has finished instantly.

alter table tenants add ContactpersistedIndexed nvarchar(100) null
CREATE NONCLUSTERED INDEX IX_ContactpersistedIndexed ON tenants(ContactpersistedIndexed); 
update tenants set ContactpersistedIndexed = json_value([Json], '$.contact.email')

set statistics time on 
SELECT TOP (1000) * FROM [dbo].[Tenants] where ContactpersistedIndexed = 't12345@gmail.com'
set statistics time off
go

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
CPU time = 0 ms, elapsed time = 0 ms.

I've even tried scaling down my db to 10eDTU and the normal column took 30 miliseconds while the computed fields took 300 seconds

This reply was modified 4 years, 8 months ago by ashkan sirous.

Best Regards,
Ashkan

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 3

There's something wrong with the index on the persisted column or the persisted column itself. It should be as fast as the permanent column you just added. Perhaps the statistics on it need to be explicitly rebuilt with a full scan.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)