SQL Server Performance

Where clause is decreasing performance

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by pvmilreu, Jan 12, 2007.

  1. pvmilreu New Member

    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)>=@DataInicial
    and dbo.FnTruncDate(pt.DataServidor)<=@DataFinal
    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.
  2. Adriaan New Member

    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.
  3. pvmilreu New Member

    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?
  4. MohammedU New Member

    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)>=@DataInicial
    and dbo.FnTruncDate(pt.DataServidor)<=@DataFinal

    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.
  5. FrankKalis Moderator

    quote:Originally posted by pvmilreu
    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?
    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
  6. madhuottapalam New Member

    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

Share This Page