Re: SP4 performance Woes.

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

  • Re: SP4 performance Woes.
    ... besides avoiding SP4 like a virus. ... >> Mike Epprecht, Microsoft SQL Server MVP ...
    (microsoft.public.sqlserver.setup)
  • Re: Reinstalling Exchange on new server after disaster
    ... So if I understand you correctly, after a complete rebuild of the OS W2k sp4 ... Microsoft Exchange Support Engineer ... I had to slipstream the installation of 2k server with sp4 ... IIS services to start for me at all. ...
    (microsoft.public.exchange.setup)
  • Re: Potential Problem with Microsoft Security Bulletin MS01-057
    ... Server 5.5 prior to installing SP4 lets MS01-057 install correctly; ... "Actually I ran into the problem on a server running Exchange 5.5 SP4, ...
    (NT-Bugtraq)
  • Very slow network browsing from W2K but not NT
    ... Advanced Server SP4. ... W2K client as it is on the NT4 client. ... Slow Network Performance Occurs When You Select a File on a Share ...
    (microsoft.public.win2000.networking)
  • Re: SBS SP1 Fail Message Pls Help
    ... SharePoint or Monitoring to SP4. ... > services not starting automatically on a server restart? ... I am applying SP1 and was up to the SBS SP1 update and it ended with ...
    (microsoft.public.windows.server.sbs)