multiple joins and ugly where … | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

multiple joins and ugly where …

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‘ />
Post the query you used
Madhivanan Failing to plan is Planning to fail
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
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)

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=’:)‘ />
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)

Daily. Import/update from legacy system. This is select part of insert statement.
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.

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

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 />
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.
Unfortunately, SQL doesn’t allow himself to be tricked this easily …
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=’;)‘ />]
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.
Is this function deterministic? If so, you can create calculated column and index on it. This can improve performance significantly. WBR, Vlad A. Scherbinin
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 />
]]>