Re: SP4 performance Woes.

Hi

Besides making both 18,2, not much, apart from following Jasper Smith's
post's fix.

The argument is that rather slower results, than wrong ones.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@xxxxxxxxxxxx

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Marcelo" <marcelo.no@xxxxxxxxxxxx> wrote in message
news:exxFyXrnFHA.3300@xxxxxxxxxxxxxxxxxxxxxxx
> Right on the money!
> One column was numeric(18,0) and the other numeric(18,2)
>
> Ok, so this is the reason why it is taking so long. How do you fix it?
> Besides normalizing the database, is there anything on the server level
> that
> I can do? besides avoiding SP4 like a virus.
>
> I understand that SP4 fixes this issue:
> http://support.microsoft.com/?kbid=899976
> But fixing the results by making the query take 10000% times longer is not
> really fixing it!
>
> By the way, thanks for your quick response.
>
> Marcelo
>
>
> "Mike Epprecht (SQL MVP)" <mike@xxxxxxxxxxxx> wrote in message
> news:uq0lZMrnFHA.2904@xxxxxxxxxxxxxxxxxxxxxxx
>> Hi
>>
>> Check that the data types on the joined tables match exactly.
>>
>> There was a change introduced between SP3 and SP4, in one of the
>> hotfixes,
>> when the data type precedence was changed as it resulted in unpredictable
>> result in some cases.
>>
>> e.g. If one column is a decanal(10,2) and the other column is an INT, SQL
>> Server first has to change the INT to decimal(10,2) and then do the
>> comparison.
>>
>> You did not supply any DDL and DML so I am assuming it is something like
>> this.
>>
>> Regards
>> --------------------------------
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>>
>> IM: mike@xxxxxxxxxxxx
>>
>> MVP Program: http://www.microsoft.com/mvp
>>
>> Blog: http://www.msmvps.com/epprecht/
>>
>> "Marcelo" <marcelo.no@xxxxxxxxxxxx> wrote in message
>> news:ObZUD4pnFHA.2580@xxxxxxxxxxxxxxxxxxxxxxx
>> >
>> > Hello,
>> > This SQL Statement which takes 1 second on a server with SP3 takes 24
>> > MINUTES after I upgrade to SP4.
>> >
>> > SELECT
>> > MDL1.[id_entity]
>> > FROM Entity E
>> > INNER JOIN Module_1133821_0 MOD0 ON E.id_entity=MOD0.id_entity
>> > LEFT JOIN Module_1133821_2959 MOD2959 ON
> E.id_entity=MOD2959.id_entity
>> > LEFT JOIN MEMBER_ENTITY_1133821 ME1 ON MOD2959
>> > .[Motivo]=ME1.ID_COUNTER
>> > LEFT JOIN Module_1133821_2995 MDL1 ON MDL1.ID_ENTITY =
>> > ME1.ID_MEMBER_ENTITY
>> > LEFT JOIN MEMBER_ENTITY_1133821 ME2 ON MOD2959
>> > .[AsignadoAtClientes]=ME2.ID_COUNTER
>> > LEFT JOIN Module_1133821_2998 MDL2 ON MDL2.ID_ENTITY =
>> > ME2.ID_MEMBER_ENTITY
>> > WHERE E.TYPE_ENTITY=1125851
>> >
>> > If I select ANY field from ANY table which is being LEFT JOINED, the
> query
>> > takes 24 minutes.
>> >
>> > For now, I have downgraded to SP3.
>> >
>> >
>> > Thanks for your help.
>> >
>> >
>>
>>
>
>


.

Relevant Pages