SQL Server Performance Forum – Threads Archive

Where clause is decreasing performance

Hi everybody, I have a query that runs normally (3s) without a where filter. Can anyone help me to improve the performance? Any tips? What should I try to do? Here is the query: select distinct m.memoria, mem.nome, [Equipe]=e.nome,
[Acionamentos]= count(distinct pt.DataServidor), [Cancelamentos]=count(distinct pt.dataCancelamento),
[MediaIntervaloAncAc(H)] =
dbo.FnConverteHorarioHHMMSS( isNull(avg(DATEDIFF(second, pt.DataServidor, pt.DataAncoramento)), 0)),
[MediaIntervaloCancAc(H)] =
dbo.FnConverteHorarioHHMMSS( isNull(avg(DATEDIFF(second, pt.DataServidor, pt.DataCancelamento)), 0)),
[MediaIntervaloRetAc(H)] =
dbo.FnConverteHorarioHHMMSS( isNull(avg(DATEDIFF(second, pt.DataServidor, pt.DataRetorno)), 0)),
[_MediaIntervaloAncAc(H)] =
isNull(avg(DATEDIFF(second, pt.DataServidor, pt.DataAncoramento)), 0),
[_MediaIntervaloCancAc(H)]=
isNull(avg(DATEDIFF(second, pt.DataServidor, pt.DataCancelamento)), 0),
[_MediaIntervaloRetAc(H)] =
isNull(avg(DATEDIFF(second, pt.DataServidor, pt.DataRetorno)), 0)
from VMensagemProvidencia mp join VMsgAlarme m
on mp.nomensagem = m.nomensagem join VProvidenciaTomada pt
on mp.notomadaprov = pt.notomadaprov join memoria mem
on m.memoria = mem.memoria join equipe e
on mem.equipeatendimento = e.numequipe
where ((pt.DataCancelamento is not null) or (pt.DataAncoramento is not null))
and e.numEquipe = 1
and dbo.FnTruncDate(pt.DataServidor)>[email protected]
and dbo.FnTruncDate(pt.DataServidor)<[email protected]
group by m.memoria, mem.nome, e.nome ——- This query as is above executes in more than 5 minutes (I stopped the execution without waiting for it to finish). If I remove the "and e.numEquipe = 1" the query executes fastly. The views "VMensagemProvidencia", "VMsgAlarme" and "VProvidenciaTomada" are unions between two tables that contains the same data, one with actual data and the other one with old data, in a kind of "archived data table". So I have to union them to get the results from the complete data. I looked at the execution plans with and without the "e.numEquipe = 1", and they are very different, and that’s why their execution time is completely different. But I can’t figure out how to make they both fast.

---

If you say removing the criteria for one single column makes the query run much faster, then you may have a problem with the indexing regarding this column. However, I would suspect that an even bigger performance difference is when you remove the two UDF calls from your WHERE clause. Note that when you do any calculation on a column in the WHERE clause, then SQL Server can no longer use any index for that column, and it will do the UDF calculation for all rows, before filtering. The FnTruncDate function probably removes the time part of a datetime value, which is not really necessary because you are using a date range. The equivalent would be: and pt.DataServidor >= @DataInicial
and pt.DataServidor < DATEADD(DAY, 1, @DataFinal) … if you prefer, you can of course add the single day to @DataFinal before executing the query.

---

Thanks for your answer. > If you say removing the criteria for one single column makes the query run much faster,
> then you may have a problem with the indexing regarding this column. Well, there are indexes for the column for both tables involved. The column numEquipe is the primary key of the table Equipe. And there is a index for equipeAtendimento, the foreign key that references equipe, from the table Memoria.
Which index would you sugest? > However, I would suspect that an even bigger performance difference is when you remove
> the two UDF calls from your WHERE clause.
> Note that when you do any calculation on a column in the WHERE clause, then SQL
> Server can no longer use any index for that column, and it will do the UDF
> calculation for all rows, before filtering.
> The FnTruncDate function probably removes the time part of a datetime value, which is
> not really necessary because you are using a date range. The equivalent would be: Well, maybe. But you’ve noticed that if just remove the "and e.numEquipe = 1" criteria and mantain the calls to fnTruncDate, the query runs in about 3s.
I’ll test it anyway.
By the way, the fnTruncDate is defined like this: ALTER FUNCTION dbo.FnTRUNCDATE (@pDateHour SmallDateTime)
RETURNS SmallDateTime
AS
BEGIN
if @pDateHour is null return 0
return
CONVERT
(SmallDateTime,
( CONVERT(varchar(2),DATEPART(month,@pDateHour)) + ‘/’ +
CONVERT(varchar(2),DATEPART(day,@pDateHour)) + ‘/’ +
CONVERT(varchar(4),DATEPART(year,@pDateHour)) + ‘ 00:00:00’
)
)
END And the system relies on that truncation, because the system saves the dates with the time information, but the queries need to bring just the information occurred in a period, regardless the time. > and pt.DataServidor >= @DataInicial
> and pt.DataServidor < DATEADD(DAY, 1, @DataFinal)
> … if you prefer, you can of course add the single day to @DataFinal
> before executing the query. Just by curiosity, the system function DateAdd wouldn’t have the same problems as the UDF TruncDate? Or should I do the computation before executing the query, as you suggest?

---

Andrian was asking you change the query to SARG.. read the topic "Is the WHERE Clause Sargable?" from following article…
http://www.sql-server-performance.com/sql_server_performance_audit8.asp What is the size of equipe table and how may rows it resturns for "e.numEquipe = 1"…
If you are getting the subset from equipe table try using the temp as follows.. select * into #e from equipe e where e.numEquipe = 1
seelct * into #v from VProvidenciaTomada pt where
((pt.DataCancelamento is not null) or (pt.DataAncoramento is not null))
and dbo.FnTruncDate(pt.DataServidor)>[email protected]
and dbo.FnTruncDate(pt.DataServidor)<[email protected] select distinct m.memoria, mem.nome, [Equipe]=e.nome,
[Acionamentos]= count(distinct pt.DataServidor), [Cancelamentos]=count(distinct pt.dataCancelamento),
[MediaIntervaloAncAc(H)] =
dbo.FnConverteHorarioHHMMSS( isNull(avg(DATEDIFF(second, pt.DataServidor, pt.DataAncoramento)), 0)),
[MediaIntervaloCancAc(H)] =
dbo.FnConverteHorarioHHMMSS( isNull(avg(DATEDIFF(second, pt.DataServidor, pt.DataCancelamento)), 0)),
[MediaIntervaloRetAc(H)] =
dbo.FnConverteHorarioHHMMSS( isNull(avg(DATEDIFF(second, pt.DataServidor, pt.DataRetorno)), 0)),
[_MediaIntervaloAncAc(H)] =
isNull(avg(DATEDIFF(second, pt.DataServidor, pt.DataAncoramento)), 0),
[_MediaIntervaloCancAc(H)]=
isNull(avg(DATEDIFF(second, pt.DataServidor, pt.DataCancelamento)), 0),
[_MediaIntervaloRetAc(H)] =
isNull(avg(DATEDIFF(second, pt.DataServidor, pt.DataRetorno)), 0)
from VMensagemProvidencia mp join VMsgAlarme m
on mp.nomensagem = m.nomensagem join #v pt
on mp.notomadaprov = pt.notomadaprov join memoria mem
on m.memoria = mem.memoria join #e e
on mem.equipeatendimento = e.numequipe
group by m.memoria, mem.nome, e.nome
Mohammed U.

---

No, almost certainly not. A UDF processes one row at a time and is in this regard pretty much like a cursor. On larger table this can massively decrease performance. Check this article on how to get rid of the time portion in DATETIME values:http://www.sql-server-performance.com/fk_datetime.asp
The advantage is that no procedural approach is needed and any maybe existing index can be used. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

---

in this particular query , u r using almost all Functions. Isnull(),AVG(),Convert(),datediff() etc. Is it using the indexes , to fetch the data if at all any, i don’t think so. There are many joins also. Why don’t u break these query by using #table approach. If u r planning to implement this as a View,why don’t u try it as a SP. After trying all other above mentioned approaches , try in this way also . Madhu

---

]]>