Worst execution times with indexes than without ?!?

Question

Worst execution times with indexes than without ?!?

polytropic2310

Ten Centuries

Points: 1004
More actions
February 28, 2017 at 5:51 am

#319151

Hello,
I get this surprising result with indexes: they don't help to improve performances.
My environment (DEVelopment) :
# 1 VM : MS WS 2012 Datacenter, hosting SQL server 2014
- 16 vCPU @ 2.53Ghz
- RAM: 16 GB
- Disk: 120 GB free
# Database:
* Tables
- 1 table : 21 000 000 rows (size: 2.3 GB)
- 10 satellite tables (star model) : 1 with 700 rows, 9 others < 60 rows
* Indexes
I 've created indexes only on big table on columns involved in joins with the 9 small tables (simple "equal" joins)
. 1 clustered index
. 9 non clustered indexes
I've tested 2 or 3 times :
Without indexes the query answers in about 7 min. 20 sec.
With indexes : 8 min. 15 s. !?!
SELECT tblDE.ANNEE, tblDE_APE.MOD, tblDE_APE.MODLIB, tblDE_A64.MOD, tblDE_A64.MODLIB, tblDE_A38.MOD, tblDE_A38.MODLIB, tblDE_A21.MOD, tblDE_A21.MODLIB, tblDE_A10.MOD, tblDE_A10.MODLIB, tblDE_ART.MOD, tblDE_ART.MODLIB, sum(tblDE.FREQ), SUM(case when tblDE.CHAMP not in ('1','2','4') AND CAST (tblDE.TAILLE AS NUMERIC)>=12 THEN tblDE.FREQ ELSE 0 END), SUM(case when tblDE.CHAMP in ('1') AND CAST(tblDE.TAILLE AS NUMERIC)>=21 THEN tblDE.FREQ ELSE 0 END), SUM(case when tblDE.CHAMP not in ('1','2','4') AND CAST(tblDE.TAILLE AS NUMERIC)>=11 THEN tblDE.FREQ ELSE 0 END), SUM(case when tblDE.CHAMP in ('1') AND CAST(tblDE.TAILLE AS NUMERIC)>=21 THEN tblDE.FREQ ELSE 0 END) FROM tblDE_A10 INNER JOIN tblDE ON (tblDE_A10.MOD=tblDE.A10) INNER JOIN tblDE_A21 ON (tblDE_A21.MOD=tblDE.A21) INNER JOIN tblDE_A38 ON (tblDE_A38.MOD=tblDE.A38) INNER JOIN tblDE_A64 ON (tblDE_A64.MOD=tblDE.A64) INNER JOIN tblDE_APE ON (tblDE_APE.MOD=tblDE.APE) INNER JOIN tblDE_ART ON (tblDE_ART.MOD=tblDE.ART)
GROUP BY tblDE.ANNEE, tblDE_APE.MOD, tblDE_APE.MODLIB, tblDE_A64.MOD, tblDE_A64.MODLIB, tblDE_A38.MOD, tblDE_A38.MODLIB, tblDE_A21.MOD, tblDE_A21.MODLIB, tblDE_A10.MOD, tblDE_A10.MODLIB, tblDE_ART.MOD, tblDE_ART.MODLIB
Please how can I improve this ?
Thanks by advance,
Steve

Viewing 11 posts - 1 through 10 (of 10 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Creating 9 narrow indexes for a single query is a waste of time. In most cases, SQL will only use a single index per table per query, and it prefers wide indexes over narrow ones.

Can you post the table definition, the clustered index definition and the execution plan (actual plan, as a .sqlplan file) *without* the 9 nonclustered indexes?

Are you using Enterprise edition?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

p.s. 16 CPU cores and 16GB memory is a weird combo. My laptop has 16GB memory, a server with 16 cores should have quite a bit more. 64 would be a decent start.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Phil Parkin SSC Guru Points: 246990 More actions · Answer 3

polytropic2310 - Tuesday, February 28, 2017 5:51 AM
Hello,
I get this surprising result with indexes: they don't help to improve performances.
--snip
Please how can I improve this ?
Thanks by advance,
Steve

Using meaningful table aliases would be an improvement, for maintainability.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

-- Give a fast streaming aggregate a chance:

CREATE INDEX ix_Stuff ON tblDE (ANNEE, APE, A64, A38,A21,A10, ART) INCLUDE (FREQ, CHAMP, TAILLE)

-- Rewrite the query in a manner which aggregates as fewcolumns as possible:

SELECT

tblDE.ANNEE,

tblDE_APE.MOD,

tblDE_APE.MODLIB,

tblDE_A64.MOD,

tblDE_A64.MODLIB,

tblDE_A38.MOD,

tblDE_A38.MODLIB,

tblDE_A21.MOD,

tblDE_A21.MODLIB,

tblDE_A10.MOD,

tblDE_A10.MODLIB,

tblDE_ART.MOD,

tblDE_ART.MODLIB,

sum1,sum2,sum3, sum4, sum5

FROM (

SELECT

ANNEE,APE,A64, A38, A21, A10, ART,

sum1=SUM(FREQ),

sum2=SUM(case when CHAMP not in ('1','2','4') AND CAST (TAILLE ASNUMERIC)>=12THEN FREQ ELSE 0 END),

sum3=SUM(case when CHAMP in ('1') AND CAST(TAILLE AS NUMERIC)>=21 THEN FREQELSE 0END),

sum4=SUM(case when CHAMP not in ('1','2','4') AND CAST(TAILLE AS NUMERIC)>=11THENFREQ ELSE 0 END),

sum5=SUM(case when CHAMP in ('1') AND CAST(TAILLE AS NUMERIC)>=21 THEN FREQELSE 0END)

FROMtblDE

GROUPBY ANNEE, APE, A64, A38, A21, A10, ART

) tblDE

INNER JOIN tblDE_A10 ON tblDE_A10.MOD = tblDE.A10

INNER JOIN tblDE_A21 ON tblDE_A21.MOD = tblDE.A21

INNER JOIN tblDE_A38 ON tblDE_A38.MOD = tblDE.A38

INNER JOIN tblDE_A64 ON tblDE_A64.MOD = tblDE.A64

INNER JOIN tblDE_APE ON tblDE_APE.MOD = tblDE.APE

INNER JOIN tblDE_ART ON tblDE_ART.MOD = tblDE.ART

[/code]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grant Fritchey SSC Guru Points: 398911 More actions · Answer 5

Have you looked at the execution plans to understand how the optimizer is choosing to resolve the query? Can you capture the actual plan (although, an estimated plan would work too, just a little more info in the actual plan) and post it?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ScottPletcher SSC Guru Points: 101130 More actions · Answer 6

1 table : 21 000 000 rows

Which table specifically has 21M rows: tblDE_A10 or tblDE?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

polytropic2310 Ten Centuries Points: 1004 More actions · Answer 7

GilaMonster - Tuesday, February 28, 2017 5:56 AM
p.s. 16 CPU cores and 16GB memory is a weird combo. My laptop has 16GB memory, a server with 16 cores should have quite a bit more. 64 would be a decent start.

I do agree with you, this DEVelopment VM is very poor. 🙁
I'll do same tests on UAT VM that has more RAM.
If I well remember, on UAT env. the same query takes about 3 or 4 min.
I'll answer your other questions tomorrow.

Steve.

polytropic2310 Ten Centuries Points: 1004 More actions · Answer 8

ChrisM@Work - Tuesday, February 28, 2017 6:27 AM
-- Give a fast streaming aggregate a chance:
CREATE INDEX ix_Stuff ON tblDE (ANNEE, APE, A64, A38,A21,A10, ART) INCLUDE (FREQ, CHAMP, TAILLE)
-- Rewrite the query in a manner which aggregates as fewcolumns as possible:
SELECT
                tblDE.ANNEE,
                tblDE_APE.MOD,
                tblDE_APE.MODLIB,
                tblDE_A64.MOD,
                tblDE_A64.MODLIB,
                tblDE_A38.MOD,
                tblDE_A38.MODLIB,
                tblDE_A21.MOD,
                tblDE_A21.MODLIB,
                tblDE_A10.MOD,
                tblDE_A10.MODLIB,
                tblDE_ART.MOD,
                tblDE_ART.MODLIB,
                sum1,sum2,sum3, sum4, sum5
FROM (
                SELECT
                                ANNEE,APE,A64, A38, A21, A10, ART,
                                sum1=SUM(FREQ),
                                sum2=SUM(case when CHAMP not in ('1','2','4') AND CAST (TAILLE ASNUMERIC)>=12THEN FREQ ELSE 0 END),
                                sum3=SUM(case when CHAMP in ('1') AND CAST(TAILLE AS NUMERIC)>=21 THEN FREQELSE 0END),
                                sum4=SUM(case when CHAMP not in ('1','2','4') AND CAST(TAILLE AS NUMERIC)>=11THENFREQ ELSE 0 END),
                                sum5=SUM(case when CHAMP in ('1') AND CAST(TAILLE AS NUMERIC)>=21 THEN FREQELSE 0END)
                FROMtblDE
                GROUPBY ANNEE, APE, A64, A38, A21, A10, ART
) tblDE
INNER JOIN tblDE_A10 ON tblDE_A10.MOD = tblDE.A10
INNER JOIN tblDE_A21 ON tblDE_A21.MOD = tblDE.A21
INNER JOIN tblDE_A38 ON tblDE_A38.MOD = tblDE.A38
INNER JOIN tblDE_A64 ON tblDE_A64.MOD = tblDE.A64
INNER JOIN tblDE_APE ON tblDE_APE.MOD = tblDE.APE
INNER JOIN tblDE_ART ON tblDE_ART.MOD = tblDE.ART
[/code]

Hi,

I've to say that these tables are in a small datamart that is querried with SAP/BO BI 4.1 product with adhoc querries built by users.
So it is impossible to have a highly optimized generated SQL.
But I can test your proposal just to see...

Regards,

Steve.

polytropic2310 Ten Centuries Points: 1004 More actions · Answer 9

ScottPletcher - Tuesday, February 28, 2017 9:58 AM
1 table : 21 000 000 rows
Which table specifically has 21M rows: tblDE_A10 or tblDE?

tblDE has 21M rows.
tblDE_A10 < 100 rows

polytropic2310 Ten Centuries Points: 1004 More actions · Answer 10

Grant Fritchey - Tuesday, February 28, 2017 7:12 AM
Have you looked at the execution plans to understand how the optimizer is choosing to resolve the query? Can you capture the actual plan (although, an estimated plan would work too, just a little more info in the actual plan) and post it?

I'll see that tomorrow...