Need help on query performance

Question

Need help on query performance

Viewing 15 posts - 16 through 30 (of 76 total)

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

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

jc85 (5/24/2016)
Yes, both tables do not have non clustered index.

Well there's the cause of your performance problem.

Start with an index on each table, columns used in the where clause first in the index key, then columns used in the group by, and see if that helps. Post the revised execution plan with the new indexes.

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

jc85 Right there with Babe Points: 760 More actions · Answer 2

GilaMonster (5/24/2016)
Start with an index on each table, columns used in the where clause first in the index key, then columns used in the group by

Hope I understood correctly.

Added start_time as Non-clustered index for table ann_events_Tech_Details.

Added report_id, report_item, call_flow_name, source as Non-clustered index for table ann_ReportItem

SQL Exec Plan -jc85_execplan

SSMS suggested adding Source to the index as well so I run another round with it.

SQL Exec Plan - jc85_execplan_wo_index

Without non-clustered index

SQL Exec Plan - jc85_execplan_wo_index.sqlplan

Sergiy SSC Guru Points: 110208 More actions · Answer 3

jc85 (5/24/2016)
Added start_time as Non-clustered index for table ann_events_Tech_Details.

Make it clustered.

Otherwise - drop it, no use of it anyway.

Added report_id, report_item, call_flow_name, source as Non-clustered index for table ann_ReportItem

[Source] must come first.

You do not need report_item in index pages, it will be good enough just being INCLUDEd.

SQL Exec Plan -jc85_execplan
SSMS suggested adding Source to the index as well so I run another round with it.
SQL Exec Plan - jc85_execplan_wo_index
Without non-clustered index
SQL Exec Plan - jc85_execplan_wo_index.sqlplan

You have still DATEADD-DATEDIFF applied to [start_time] in WHERE clause.

If you do not intend to fix that - forget about indexes. They're not gonna help you.

_____________
Code for TallyGenerator

jc85 Right there with Babe Points: 760 More actions · Answer 4

Sergiy (5/24/2016)
jc85 (5/24/2016)
Added start_time as Non-clustered index for table ann_events_Tech_Details.
Make it clustered.
Otherwise - drop it, no use of it anyway.

Both tables already have id set as their clustered index so I will drop [start_time].

jc85 (5/24/2016)
Added report_id, report_item, call_flow_name, source as Non-clustered index for table ann_ReportItem
[Source] must come first.
You do not need report_item in index pages, it will be good enough just being INCLUDEd.

Did as you said.

You have still DATEADD-DATEDIFF applied to [start_time] in WHERE clause.
If you do not intend to fix that - forget about indexes. They're not gonna help you.

Any suggestion how to write SARGable WHERE clause to replace DATEADD-DATEDIFF?

I have temp removed it and ran another round, SQL exec plan is attached. Thanks !

Sergiy SSC Guru Points: 110208 More actions · Answer 5

jc85 (5/24/2016)
Both tables already have id set as their clustered index so I will drop [start_time].

This is exactly the problem.

Make both PK's non-clustered and create a new clustered index on [start_time].

Any suggestion how to write SARGable WHERE clause to replace DATEADD-DATEDIFF?
I have temp removed it and ran another round, SQL exec plan is attached. Thanks !

That's what you've got:

WHERE (dateadd(dd,0, datediff(dd,0,b.start_time))) >= @StartDate

AND (dateadd(dd,0, datediff(dd,0,b.start_time))) <= @EndDate

I assume @StartDate and @EndDate never contain a time portion.

And you intend to select all records with [start_time] during the day indicated by @EndDate.If this is correct then your clause should look like that:

WHERE @StartDate <= b.start_time

AND b.start_time < DATEADD(dd, 1, @EndDate)

_____________
Code for TallyGenerator

jc85 Right there with Babe Points: 760 More actions · Answer 6

Sergiy (5/24/2016)
This is exactly the problem.
Make both PK's non-clustered and create a new clustered index on [start_time].
That's what you've got:
WHERE (dateadd(dd,0, datediff(dd,0,b.start_time))) >= @StartDate
AND (dateadd(dd,0, datediff(dd,0,b.start_time))) <= @EndDate
I assume @StartDate and @EndDate never contain a time portion.
And you intend to select all records with [start_time] during the day indicated by @EndDate.If this is correct then your clause should look like that:
WHERE @StartDate <= b.start_time
AND b.start_time < DATEADD(dd, 1, @EndDate)

Thanks for for advice, both PK's have been changed to non-clustered, [start_time] has been set to clustered index and updated the WHERE clause.

However, the query runs slower now. SQL Plan is attached.

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

jc85 (5/25/2016)
Sergiy (5/24/2016)
This is exactly the problem.
Make both PK's non-clustered and create a new clustered index on [start_time].
That's what you've got:
WHERE (dateadd(dd,0, datediff(dd,0,b.start_time))) >= @StartDate
AND (dateadd(dd,0, datediff(dd,0,b.start_time))) <= @EndDate
I assume @StartDate and @EndDate never contain a time portion.
And you intend to select all records with [start_time] during the day indicated by @EndDate.If this is correct then your clause should look like that:
WHERE @StartDate <= b.start_time
AND b.start_time < DATEADD(dd, 1, @EndDate)
Thanks for for advice, both PK's have been changed to non-clustered, [start_time] has been set to clustered index and updated the WHERE clause.
However, the query runs slower now. SQL Plan is attached.

Try CAST(b.start_time AS DATE)

instead of dateadd (dd, 0, datediff(dd , 0, b.start_time ))

^{“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

jc85 Right there with Babe Points: 760 More actions · Answer 8

Try CAST(b.start_time AS DATE)
instead of dateadd (dd, 0, datediff(dd , 0, b.start_time ))

Just tried it, took longer time to complete.

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

There are a few changes I'd make to your query. Mostly it's about aligning the date filters properly. Try this:

SELECT

[date] = CAST(b.start_time AS DATE),

[report_item] = ( a .source + '-' + a. report_item),

[EN] = SUM(CASE WHEN b.lang = 'EN' AND --b.start_time >= @StartDate AND b.start_time <= @EndDate AND

((b.sel_tel_nodialtone = 1 AND a.report_item_id = 1)

OR (b.sel_tel_noisy = 1 AND a.report_item_id = 2)

OR (b.sel_tech_ckc_disconnect = 1 AND a.report_item_id = 3)

OR (b.sel_tech_ckc_transfer = 1 AND a.report_item_id = 4)

OR (b.sel_tel_csr = 1 AND a.report_item_id = 5)

OR (b.sel_inv_del = 1 AND a.report_item_id = 6)

OR (b.sel_inv_unifi = 1 AND a.report_item_id = 7))

THEN 1 ELSE 0 END),

[BM] = SUM(CASE WHEN b.lang = 'BM' AND --b.start_time >= @StartDate AND b.start_time <= @EndDate AND

((b.sel_tel_nodialtone = 1 AND a.report_item_id = 1)

OR (b.sel_tel_noisy = 1 AND a.report_item_id = 2)

OR (b.sel_tech_ckc_disconnect = 1 AND a.report_item_id = 3)

OR (b.sel_tech_ckc_transfer = 1 AND a.report_item_id = 4)

OR (b.sel_tel_csr = 1 AND a.report_item_id = 5)

OR (b.sel_inv_del = 1 AND a.report_item_id = 6)

OR (b.sel_inv_unifi = 1 AND a.report_item_id = 7))

THEN 1 ELSE 0 END),

[MD] = SUM(CASE WHEN b.lang = 'MD' AND --b.start_time >= @StartDate AND b.start_time <= @EndDate AND

((b.sel_tel_nodialtone = 1 AND a.report_item_id = 1)

OR (b.sel_tel_noisy = 1 AND a.report_item_id = 2)

OR (b.sel_tech_ckc_disconnect = 1 AND a.report_item_id = 3)

OR (b.sel_tech_ckc_transfer = 1 AND a.report_item_id = 4)

OR (b.sel_tel_csr = 1 AND a.report_item_id = 5)

OR (b.sel_inv_del = 1 AND a.report_item_id = 6)

OR (b.sel_inv_unifi = 1 AND a.report_item_id = 7))

THEN 1 ELSE 0 END)

FROM ann_ReportItem a

INNER JOIN ann_events_Tech_Details b

ON a.source = b.source

WHERE CAST(b.start_time AS DATE) >= @StartDate

AND CAST(b.start_time AS DATE) <= @EndDate

AND a.report_id = 8

AND a.call_flow_name = @call_flow_name

GROUP BY

a.source,

a.report_item,

CAST(b.start_time AS DATE)

ORDER BY CAST(b.start_time AS DATE)

^{“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

jc85 Right there with Babe Points: 760 More actions · Answer 10

Thanks ChrisM@Work.

The query took quite some time to complete.

Additional info provided in case you need it.

ann_events_Tech_Details

[start_time] - clustered index

[id] - Unique, non-clustered index (PK)

ann_ReportItem

[source] - non-clustered index

[call_flow_name] - non-clustered index

[report_id] - non-clustered index

[id] - Unique, non-clustered index (PK)

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

Can you display the 21 rows from table ann_ReportItem?

Ideally you would do this by posting a script to create a table then a series of inserts to populate it with data. You don't need to use the same table name, a temp table called say #ann_ReportItem will be fine. Jumping the gun a bit, you might want to do the same with ann_events_Tech_Details.

^{“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

jc85 Right there with Babe Points: 760 More actions · Answer 12

How should i post the table with data on this forum?

How many rows do you need for ann_events_Tech_Details table?

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

-- Try this too.

-- Add [source] to the clustered index, initially AFTER [start_time]

SELECT

[date] = CAST(b.start_time AS DATE),

[report_item] = (b.[source] + '-' + 'a.report_item'),

[EN] = SUM(CASE WHEN b.lang = 'EN' THEN 1 ELSE 0 END),

[BM] = SUM(CASE WHEN b.lang = 'BM' THEN 1 ELSE 0 END),

[MD] = SUM(CASE WHEN b.lang = 'MD' THEN 1 ELSE 0 END)

FROM ann_events_Tech_Details b

WHERE CAST(b.start_time AS DATE) >= @StartDate

AND CAST(b.start_time AS DATE) <= @EndDate

AND 1 IN (b.sel_tel_nodialtone, b.sel_tel_noisy, b.sel_tech_ckc_disconnect,

b.sel_tech_ckc_transfer, b.sel_tel_csr, b.sel_inv_del, b.sel_inv_unifi)

AND EXISTS (

SELECT 1 FROM ann_ReportItem a

WHERE a.report_id = 8

AND a.call_flow_name = @call_flow_name)

GROUP BY

CAST(b.start_time AS DATE),

b.[source]

ORDER BY CAST(b.start_time AS DATE)

^{“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

jc85 Right there with Babe Points: 760 More actions · Answer 14

Isn't 1 clustered index is allowed per table?

The query returned different result.

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

Only one clustered index per table, but the index can contain more than one column.

"Different result" is too vague, please explain.

^{“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