Re: SP4 performance Woes.

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

  • Re: where are serials for xp professional
    ... hopes that only going with sp3 will resolve some issues with Backup Exec ... Everybody I've talked to at Veritas tech support seems to think sp4 ... problems until moving the backup server to Win2K from NT 4. ...
    (comp.programming)
  • Re: SQL 2000 and SP4 performance issue
    ... This seems to be a change in behaviour between SP4 and SP3 as I ... Jasper Smith (SQL Server MVP) ... > We cannot send query without .00 on the end of number becase it doing ...
    (microsoft.public.sqlserver.setup)
  • Re: SP4 Hosed our PC Big Time !!! - Help ???
    ... If you hate MS that much, then perhaps you are dealing with the ... Have you tried just running a clean OS with SP3 and then update to SP4? ... > specifically recommend AGAINST installing SP4???? ...
    (microsoft.public.win2000.security)
  • Re: TS Server hang
    ... The printing bug in SP3 was fixed about a month after the release ... It is of course not present in SP4. ... The number of bugs ...
    (microsoft.public.win2000.termserv.apps)