February 28, 2017 at 5:51 am
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
February 28, 2017 at 5:55 am
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
February 28, 2017 at 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.
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
February 28, 2017 at 6:03 am
polytropic2310 - Tuesday, February 28, 2017 5:51 AMHello,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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 28, 2017 at 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]
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
February 28, 2017 at 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?
"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
February 28, 2017 at 9:58 am
1 table : 21 000 000 rows
Which table specifically has 21M rows: tblDE_A10 or tblDE?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 28, 2017 at 1:06 pm
GilaMonster - Tuesday, February 28, 2017 5:56 AMp.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.
February 28, 2017 at 1:12 pm
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.
February 28, 2017 at 1:15 pm
ScottPletcher - Tuesday, February 28, 2017 9:58 AM1 table : 21 000 000 rows
Which table specifically has 21M rows: tblDE_A10 or tblDE?
tblDE has 21M rows.
tblDE_A10 < 100 rows
February 28, 2017 at 1:16 pm
Grant Fritchey - Tuesday, February 28, 2017 7:12 AMHave 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...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply