Good day.

Below is the text of the query, it gives the correct results, but it takes too long. Please help optimize it (this is my latest and current version of the query.):

DECLARE @dateFrom datetime = @filter.value('(FilterSet/items[@name="dateFrom"])[1]', 'datetime'); DECLARE @dateTo datetime = @filter.value('(FilterSet/items[@name="dateTo"])[1]', 'datetime'); DECLARE @costTable table (providerName varchar(50), clientName varchar(50),messageId int, [count] int, cost numeric(18,3), [month] date ) INSERT @costTable SELECT p.providerName, c.clientName, m.messageId, [dbo].[func_GetSmsCount] (m.messageId, m.[recipient], p.providerId, m.dt, ms.statusValue), [dbo].[func_GetSmsCost] (m.messageId, m.[recipient], p.providerId, m.dt, ms.statusValue), DATEADD(MONTH, DATEDIFF(MONTH, 0, m.[dt]), 0) as DT FROM [dbo].[Messages] (NOLOCK) m INNER JOIN [dbo].[MessageStatus] (NOLOCK) ms ON m.messageId = ms.messageId INNER JOIN [dbo].[Clients] (NOLOCK) c ON m.clientId = c.clientId INNER JOIN [dbo].[Debtors] (NOLOCK) d ON c.debtorId = d.debtorId INNER JOIN [dbo].[Providers] (NOLOCK) p ON ms.providerId = p.providerId WHERE ms.statusId = 2 AND p.isEmail != 1 AND CAST(m.dt as date) >= @dateFrom AND CAST(m.dt as date) <= @dateTo AND ((ms.[providerId] = 6) OR (ms.[providerId] = 2) OR (ms.[providerId] = 1) ) GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, m.[dt]), 0), p.providerName, c.clientName, m.messageId, m.[recipient], p.providerId, m.dt, [dbo].[func_GetSmsCount] (m.messageId, m.[recipient], p.providerId, m.dt, ms.statusValue), [dbo].[func_GetSmsCost] (m.messageId, m.[recipient], p.providerId, m.dt, ms.statusValue) SELECT [month], providerName, SUM(cost) as [summ], COUNT(cost) as [smsCount], COUNT(1) [TotalCount], COUNT(1) - COUNT(cost) as [ErrorCount], SUM([count]) as partCount FROM @costTable GROUP BY [month], providerName ORDER BY [month] desc, providerName

Thank.

Execution Plan (piece with maximum value): enter image description here

  • By the way, I built an index on those fields, but I still write to create that index. Maybe because a nonclustered index has already been created in the "recipient" field? - Leonard Bertone
  • I would probably start by saying that inside func_GetSmsCount and func_GetSmsCost . It may be possible to combine these two scalar functions into one inline-table. Also, if there are no records in Providers and Clients with the same names, but different Id , then it is better to group them by their Id , rather than by name, and attach the Providers and Clients to the already grouped data. Then, perhaps, look at the missing indexes. - i-one

2 answers 2

Why are you prefetching shove in a table variable? This forces the server to push a bunch of strings into RAM (or in tempdb if the first one ends)

In this case, this action is meaningless and harmful.

As for query optimization, try playing around with the order of the tables in the FROM , assigning the FORCE ORDER option (which causes the optimizer to choose a custom string concatenation order).

And be sure to remove from the grouping function. Your function is performed for each line! They spoil everything to the campaign .. It is enough to add grouping by ms.statusValue, and remove the functions!

It will look something like this:

 DECLARE @dateFrom datetime = @filter.value('(FilterSet/items[@name="dateFrom"])[1]', 'datetime'); DECLARE @dateTo datetime = @filter.value('(FilterSet/items[@name="dateTo"])[1]', 'datetime'); ;WITH costTable AS( SELECT p.providerName, c.clientName, m.messageId, [dbo].[func_GetSmsCount] (m.messageId, m.[recipient], p.providerId, m.dt, ms.statusValue) as [count], [dbo].[func_GetSmsCost] (m.messageId, m.[recipient], p.providerId, m.dt, ms.statusValue) as cost, DATEADD(MONTH, DATEDIFF(MONTH, 0, m.[dt]), 0) as [month] FROM [dbo].[Messages] (NOLOCK) m INNER JOIN [dbo].[MessageStatus] (NOLOCK) ms ON m.messageId = ms.messageId INNER JOIN [dbo].[Clients] (NOLOCK) c ON m.clientId = c.clientId INNER JOIN [dbo].[Debtors] (NOLOCK) d ON c.debtorId = d.debtorId INNER JOIN [dbo].[Providers] (NOLOCK) p ON ms.providerId = p.providerId WHERE ms.statusId = 2 AND p.isEmail != 1 AND CAST(m.dt as date) >= @dateFrom AND CAST(m.dt as date) <= @dateTo AND ((ms.[providerId] = 6) OR (ms.[providerId] = 2) OR (ms.[providerId] = 1) ) GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, m.[dt]), 0), p.providerName, c.clientName, m.messageId, m.[recipient], p.providerId, m.dt,ms.statusValue ) SELECT [month], providerName, SUM(cost) as [summ], COUNT(cost) as [smsCount], COUNT(1) [TotalCount], COUNT(1) - COUNT(cost) as [ErrorCount], SUM([count]) as partCount FROM costTable GROUP BY [month], providerName ORDER BY [month] desc, providerName OPTION(FORCE ORDER)

If the grouping by ms.statusValue not allowed (it depends on your task). Then add the DISTINCT in the CTE costTable (adding there all the fields that were in the grouping in the original request). This will give exactly the same result as your initial request for any data. But something tells me that this DISTINCT is superfluous.

You can still try to play around with LOOP hints; HASH in connections. But it is better to do if you have a certain experience.

    Without the description of the function [dbo]. [Func_GetSmsCount] they will not be able to help you (I am writing as an answer, because I don’t have enough reputation for commenting, if you can still write in person, I think I can help).