|
Home > Archive > MS SQL Server > February 2006 > any reason why a query takes longer to run on 2005 ?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
any reason why a query takes longer to run on 2005 ?
|
|
|
| any reason why a query takes longer to run on 2005 ?
sql 2000 query takes 7 seconds
same query pasted into sql 2005 takes 56 seconds
sql 2005 server is pretty much 10x higher specification
query :-
SELECT Personal.ID, Personal.Surname1, Lead.DateLeadReceived,
Personal.Postcode, Lead.Reference, Lead.LeadLoggedBy, RTB.RTBToClient,
RTB. RTBReturnedFromClien
t, Live.RTBToCouncil,
ToLive.TransferToLive, Closed.DateClosed, NoValidTel.ID
FROM Personal LEFT OUTER JOIN
Diary ON Personal.ID = Diary.MainID LEFT OUTER JOIN
Lead ON Personal.ID = Lead.ID LEFT OUTER JOIN
Closed ON Personal.ID = Closed.ID LEFT OUTER JOIN
ToLive ON Personal.ID = ToLive.ID LEFT OUTER JOIN
NoValidTel ON Personal.ID = NoValidTel.ID LEFT OUTER
JOIN
RTB ON Personal.ID = RTB.ID LEFT OUTER JOIN
Live ON Personal.ID = Live.ID
GROUP BY Personal.ID, Personal.Surname1, Lead.DateLeadReceived,
Personal.Postcode, Lead.Reference, Lead.LeadLoggedBy, RTB.RTBToClient,
RTB. RTBReturnedFromClien
t, Live.RTBToCouncil,
ToLive.TransferToLive, Closed.DateClosed, NoValidTel.ID
HAVING (NOT (Personal.ID IN
(SELECT mainid
FROM diary
WHERE valid = 'true'))) AND
(RTB.RTBToClient IS NULL OR
RTB.RTBToClient = CONVERT(DATETIME, '1900-01-01
00:00:00', 102)) AND (RTB. RTBReturnedFromClien
t IS NULL OR
RTB. RTBReturnedFromClien
t = CONVERT(DATETIME,
'1900-01-01 00:00:00', 102)) AND (Live.RTBToCouncil IS NULL OR
Live.RTBToCouncil = CONVERT(DATETIME, '1900-01-01
00:00:00', 102)) AND (ToLive.TransferToLive IS NULL OR
ToLive.TransferToLive = CONVERT(DATETIME, '1900-01-01
00:00:00', 102)) AND (Closed.DateClosed IS NULL OR
Closed.DateClosed = CONVERT(DATETIME, '1900-01-01
00:00:00', 102)) AND (Lead.DateLeadReceived > CONVERT(DATETIME, '2005-01-01
00:00:00',
102)) AND (NoValidTel.ID IS NULL)
ORDER BY Lead.DateLeadReceived
| |
|
| nm, fixed it, had to tune the databases, it was from a fresh backup restore
"luna" <luna@themoon.com> wrote in message
news:DQZGf.35862$494.13699@newsfe2-gui.ntli.net...
> any reason why a query takes longer to run on 2005 ?
>
> sql 2000 query takes 7 seconds
>
> same query pasted into sql 2005 takes 56 seconds
>
> sql 2005 server is pretty much 10x higher specification
>
> query :-
>
>
> SELECT Personal.ID, Personal.Surname1, Lead.DateLeadReceived,
> Personal.Postcode, Lead.Reference, Lead.LeadLoggedBy, RTB.RTBToClient,
> RTB. RTBReturnedFromClien
t, Live.RTBToCouncil,
> ToLive.TransferToLive, Closed.DateClosed, NoValidTel.ID
> FROM Personal LEFT OUTER JOIN
> Diary ON Personal.ID = Diary.MainID LEFT OUTER JOIN
> Lead ON Personal.ID = Lead.ID LEFT OUTER JOIN
> Closed ON Personal.ID = Closed.ID LEFT OUTER JOIN
> ToLive ON Personal.ID = ToLive.ID LEFT OUTER JOIN
> NoValidTel ON Personal.ID = NoValidTel.ID LEFT OUTER
> JOIN
> RTB ON Personal.ID = RTB.ID LEFT OUTER JOIN
> Live ON Personal.ID = Live.ID
> GROUP BY Personal.ID, Personal.Surname1, Lead.DateLeadReceived,
> Personal.Postcode, Lead.Reference, Lead.LeadLoggedBy, RTB.RTBToClient,
> RTB. RTBReturnedFromClien
t, Live.RTBToCouncil,
> ToLive.TransferToLive, Closed.DateClosed, NoValidTel.ID
> HAVING (NOT (Personal.ID IN
> (SELECT mainid
> FROM diary
> WHERE valid = 'true'))) AND
> (RTB.RTBToClient IS NULL OR
> RTB.RTBToClient = CONVERT(DATETIME, '1900-01-01
> 00:00:00', 102)) AND (RTB. RTBReturnedFromClien
t IS NULL OR
> RTB. RTBReturnedFromClien
t = CONVERT(DATETIME,
> '1900-01-01 00:00:00', 102)) AND (Live.RTBToCouncil IS NULL OR
> Live.RTBToCouncil = CONVERT(DATETIME, '1900-01-01
> 00:00:00', 102)) AND (ToLive.TransferToLive IS NULL OR
> ToLive.TransferToLive = CONVERT(DATETIME, '1900-01-01
> 00:00:00', 102)) AND (Closed.DateClosed IS NULL OR
> Closed.DateClosed = CONVERT(DATETIME, '1900-01-01
> 00:00:00', 102)) AND (Lead.DateLeadReceived > CONVERT(DATETIME,
> '2005-01-01 00:00:00',
> 102)) AND (NoValidTel.ID IS NULL)
> ORDER BY Lead.DateLeadReceived
>
| |
| Amos Soma 2006-02-10, 11:23 am |
| How did you 'tune' the databases?
"luna" <luna@themoon.com> wrote in message
news:N6_Gf.86038$zt1.2377@newsfe5-gui.ntli.net...
> nm, fixed it, had to tune the databases, it was from a fresh backup
> restore
>
>
>
> "luna" <luna@themoon.com> wrote in message
> news:DQZGf.35862$494.13699@newsfe2-gui.ntli.net...
>
>
| |
|
|
"Amos Soma" <amos_j_soma@yahoo.com> wrote in message
news:uC7ektlLGHA.2036@TK2MSFTNGP14.phx.gbl...
> How did you 'tune' the databases?
>
theres some tuning wizard as part of the install, was 65% improved running
it
|
|
|
|
|