SQL Server Performance

multiple joins and ugly where ...

Discussion in 'T-SQL Performance Tuning for Developers' started by Niktu, Jul 22, 2005.

  1. Niktu New Member

    I have query selecting from 5 tables inner-joined together.<br />One of the tables is temporary.<br />There are indexes on join fields etc.<br />Query works quite fast (couple seconds), result count in range of hundred.<br /><br />But i needed to refine result set by adding check on one of the columns, it's a user function, used thruout my sp's.<br />Now all gets totally wrong ... SQL decides that whery condition should be run before joins, an runs it against 1,5M rows table. . Now query doesn't end before i loose my patience (my longest wait was 20m <img src='/community/emoticons/emotion-1.gif' alt=':)' />.<br />How do convince sql to do joins first and filtering later? (i've seen in execution plans it sometimes can do that ...)<br />I would really want to avoid making another intermediate temp table, similar behavior repeats along quite large sp, so amount of code would increase significantly, and creation of tens temp tables wouldn't be that good for server load either.<br /><br />PS. madly running statisctics update on everything on sight doesn't help either <img src='/community/emoticons/emotion-4.gif' alt=':p' />
  2. Madhivanan Moderator

    Post the query you used


    Madhivanan

    Failing to plan is Planning to fail
  3. Niktu New Member

    select
    dbo.TS_PhoneCleanup(CFT.Telefon, 'n'),
    dbo.TS_PhoneCleanup(CFT.TelKier, 'k'),
    dbo.TS_PhoneCleanup(CFT.TelWew, 'w'),
    UK.KlientID,
    CFT.HDataOD,
    UmowaKlientaID

    from #TabShortCRU_K as TMP
    join TA_UMOWAKLIENTA as UK on UK.Numer = TMP.NumerUmowy
    join CSO..CR_ZRODLODOCH as CZD on CZD.UmowaKredytID = TMP.UmowaKredytID
    join CSO..CR_FIRMASZCZEG as CFS on CFS.FirmaSzczegID = CZD.FirmaSzczegID
    join CSO..CR_FIRMA_TELEFON as CFT on CFS.TelefonID = CFT.TelefonID

    WHERE dbo.TS_PhoneCleanup(CFT.Telefon, 'n') is not null
  4. FrankKalis Moderator

    The use of the UDF in the WHERE doesn't allow SQL Server to use an index. What are you doing in this UDF?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  5. Niktu New Member

    UDF reformats field and does some checks on its validity (in case of corrupted or nonsense values it returns null)<br />it'a around 150 lines of sql code. <br />Around 5% of records are invalid, ... all I want with that where is to filter them out (it's import from legacy system).<br /><br />Is there any way to force SQL Server to leave that low-gaining filtering AFTER the joins?<br />(without temp tables or table variables, that is ...<img src='/community/emoticons/emotion-1.gif' alt=':)' />
  6. FrankKalis Moderator

    quote:
    (it's import from legacy system).
    This is a one-time action or are you doing this regularly?


    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  7. Niktu New Member

    Daily. Import/update from legacy system. This is select part of insert statement.
  8. RGKN New Member



    SELECT Telefon, TelKier, TelWew, KlientID, HDataOD, UmowaKlientaID FROM (select
    dbo.TS_PhoneCleanup(CFT.Telefon, 'n')AS Telefon,
    dbo.TS_PhoneCleanup(CFT.TelKier, 'k')AS TelKier,
    dbo.TS_PhoneCleanup(CFT.TelWew, 'w')AS TelWew,
    UK.KlientID,
    CFT.HDataOD,
    UmowaKlientaID

    from #TabShortCRU_K as TMP
    join TA_UMOWAKLIENTA as UK on UK.Numer = TMP.NumerUmowy
    join CSO..CR_ZRODLODOCH as CZD on CZD.UmowaKredytID = TMP.UmowaKredytID
    join CSO..CR_FIRMASZCZEG as CFS on CFS.FirmaSzczegID = CZD.FirmaSzczegID
    join CSO..CR_FIRMA_TELEFON as CFT on CFS.TelefonID = CFT.TelefonID ) tmp2
    WHERE Telefon is not null

    Doesn't get you round creating a new temp table except you don't have to add the DDL to your procedure, as you are only returning a hundred or so records I would have thought it wouldn't be too slow or have too bad an effect on tempdb. However it is not really what you want. If you are only returning hundreds of records why are you so concerned about a new temp table?

    Regards,

    Robert.


  9. mmarovic Active Member

    Try this:
    select *
    from(
    select
    dbo.TS_PhoneCleanup(CFT.Telefon, 'n') as n,
    dbo.TS_PhoneCleanup(CFT.TelKier, 'k') as k,
    dbo.TS_PhoneCleanup(CFT.TelWew, 'w') as w,
    UK.KlientID,
    CFT.HDataOD,
    UmowaKlientaID

    from #TabShortCRU_K as TMP
    join TA_UMOWAKLIENTA as UK on UK.Numer = TMP.NumerUmowy
    join CSO..CR_ZRODLODOCH as CZD on CZD.UmowaKredytID = TMP.UmowaKredytID
    join CSO..CR_FIRMASZCZEG as CFS on CFS.FirmaSzczegID = CZD.FirmaSzczegID
    join CSO..CR_FIRMA_TELEFON as CFT on CFS.TelefonID = CFT.TelefonID
    ) as t
    WHERE t.n is not null
  10. Niktu New Member

    Hehe, i've already tried such query derived tables ...<br />unfortunately SQL Server keeps doing full scan of CSO..CR_FIRMA_TELEFON as CFT neverless<br /><br />(I supose optimalizer moves execution of where condition into query derived table ... at<br />least execution plan shows it this way, in the result sql everytime filters whole big CFT <br />through my innocent UDF, even though after joins there would be<br />over 1000 times less data to filter ...)<br /><br />Catch is that most limiting factor is join with #TabShortCRU_K, not where condition ...<br />hot to tell that to sql server? It is VERY persistant on assuming otherwise ... <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />
  11. RGKN New Member

    Could you try applying the function in the outer query i.e.

    SELECT dbo.TS_PhoneCleanup(Telefon, 'n'),
    dbo.TS_PhoneCleanup(TelKier, 'k'),
    dbo.TS_PhoneCleanup(TelWew, 'w'),
    KlientID, HDataOD, UmowaKlientaID FROM
    (select
    CFT.Telefon, CFT.TelKier, CFT.TelWew,
    UK.KlientID,
    CFT.HDataOD,
    UmowaKlientaID

    from #TabShortCRU_K as TMP
    join TA_UMOWAKLIENTA as UK on UK.Numer = TMP.NumerUmowy
    join CSO..CR_ZRODLODOCH as CZD on CZD.UmowaKredytID = TMP.UmowaKredytID
    join CSO..CR_FIRMASZCZEG as CFS on CFS.FirmaSzczegID = CZD.FirmaSzczegID
    join CSO..CR_FIRMA_TELEFON as CFT on CFS.TelefonID = CFT.TelefonID ) tmp2
    WHERE dbo.TS_PhoneCleanup(Telefon, 'n') is not null

    Just something you could try to dupe the optimiser.

    Robert.

    PS Sorry haven't tried it here as I don't have a suitable function etc.

  12. Niktu New Member

    Unfortunately, SQL doesn't allow himself to be tricked this easily ...
  13. mmarovic Active Member

    Ok, then:<pre id="code"><font face="courier" size="2" id="code">declare @t table(....)<br />insert into @t (...)<br />select<br />dbo.TS_PhoneCleanup(CFT.Telefon, 'n'),<br />dbo.TS_PhoneCleanup(CFT.TelKier, 'k'),<br />dbo.TS_PhoneCleanup(CFT.TelWew, 'w'),<br />UK.KlientID,<br />CFT.HDataOD,<br />UmowaKlientaID<br /><br />from #TabShortCRU_K as TMP <br />join TA_UMOWAKLIENTA as UK on UK.Numer = TMP.NumerUmowy<br />join CSO..CR_ZRODLODOCH as CZD on CZD.UmowaKredytID = TMP.UmowaKredytID<br />join CSO..CR_FIRMASZCZEG as CFS on CFS.FirmaSzczegID = CZD.FirmaSzczegID<br />join CSO..CR_FIRMA_TELEFON as CFT on CFS.TelefonID = CFT.TelefonID<br />--<br />select *<br />from @t<br />where n is not null</font id="code"></pre id="code">When it doesn't work try bigger hammer [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]
  14. TheReverend New Member

    why not cut it into 2 parts?
    do the select into a new temp table and then do


    select * from #temp2
    WHERE dbo.TS_PhoneCleanup(Telefon, 'n') is not null

    this way you work the function only on the small output.


    YGEsoft, Retail Engineering.
  15. Ray D New Member

    Is this function deterministic? If so, you can create calculated column and index on it. This can improve performance significantly.

    WBR, Vlad A. Scherbinin
  16. Niktu New Member

    Actually calculated column with index wouldn't help much ... condition on index pointing into 95% of table would still result in table scan ...<br /><br />Looks like i will have to skip to last resort and put many temp tables/table variables in all such situations ...<br />(I have quite a lot of them already ... tried to avoid adding another when they weren't absolutely necessary ... i've already swap-slughtered sevrver once <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />... looks like making optimizer do our bidding has limitis which are biting me now <img src='/community/emoticons/emotion-4.gif' alt=':p' /><br />

Share This Page