March 16, 2010 at 2:26 am
Hi,
I have the query below , this query runs fast in some servers (SQL Server 2005 servers) and run very very very slow in other servers.
In the fast servers it runs in 3 or 4 seconds in the other servers it takes 11 hours to complete.
What can this be?
Thank you
/****** Object: StoredProcedure [dbo].[EmissaoCRContagem] Script Date: 03/16/2010 08:21:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[EmissaoCRContagem] @data varchar(20) as
if (SELECT convert(varchar(1), SERVERPROPERTY('productversion')))=8
begin --SQL 2000
-- SELECT COUNT (DISTINCT isnull(NOTA.NUMDU,0)) AS NORECS FROM
-- ((SGTALIQDATA INNER JOIN LIQUIDACAO ON isnull(SGTALIQDATA.CODLIQ,0) = isnull(LIQUIDACAO.CODLIQ,-1))
-- INNER JOIN (SELECT SUM(isnull(IMPTRIB-IMPCOB,0)) SOMA, NUMDU FROM (SGTALIQDATA
-- INNER JOIN LIQUIDACAO ON isnull(SGTALIQDATA.CODLIQ,0) = isnull(LIQUIDACAO.CODLIQ,-1))
-- GROUP BY NUMDU) as NOTA ON isnull(NOTA.NUMDU,'0') = isnull(SGTALIQDATA.NUMDU,'-1'))
--
-- INNER JOIN
--
-- (SELECT NUMDU, DATAEXPIRACAO_FINAL FROM
--
-- EmissaoCR1 AS X
-- INNER JOIN
-- EmissaoCR2 AS Y
--
-- --ON isnull(X.NUMDU_STUF,'0') = isnull(Y.NUMDU_STUF,'-1')) AS ULTIMANOTA
-- ON X.NUMDU_STUF = Y.NUMDU_STUF) AS ULTIMANOTA
--
-- ON isnull(NOTA.NUMDU,'0') = isnull(ULTIMANOTA.NUMDU,'-1') AND LIQUIDACAO.CodCR IS NULL
-- AND SGTALIQDATA.CodCR IS NULL AND (isnull(LIQUIDACAO.IMPTRIB,0) <> isnull(LIQUIDACAO.IMPCOB,0))
-- AND LIQUIDACAO.CodAnula IS NULL AND ULTIMANOTA.DATAEXPIRACAO_FINAL < @data
end
else
begin --SQL 2005
SELECT COUNT (DISTINCT isnull(NOTA.NUMDU,0)) AS NORECS FROM
((SGTALIQDATA INNER JOIN LIQUIDACAO ON isnull(SGTALIQDATA.CODLIQ,0) = isnull(LIQUIDACAO.CODLIQ,-1))
INNER JOIN (SELECT SUM(isnull(IMPTRIB-IMPCOB,0)) SOMA, NUMDU FROM (SGTALIQDATA
INNER JOIN LIQUIDACAO ON isnull(SGTALIQDATA.CODLIQ,0) = isnull(LIQUIDACAO.CODLIQ,-1))
GROUP BY NUMDU) as NOTA ON isnull(NOTA.NUMDU,'0') = isnull(SGTALIQDATA.NUMDU,'-1'))
INNER JOIN
(SELECT NUMDU, DATAEXPIRACAO_FINAL FROM
EmissaoCR1 AS X
INNER JOIN
EmissaoCR2 AS Y
ON isnull(X.NUMDU_STUF,'0') = isnull(Y.NUMDU_STUF,'-1')) AS ULTIMANOTA
--ON X.NUMDU_STUF = Y.NUMDU_STUF) AS ULTIMANOTA
ON isnull(NOTA.NUMDU,'0') = isnull(ULTIMANOTA.NUMDU,'-1') AND LIQUIDACAO.CodCR IS NULL
AND SGTALIQDATA.CodCR IS NULL AND (isnull(LIQUIDACAO.IMPTRIB,0) <> isnull(LIQUIDACAO.IMPCOB,0))
AND LIQUIDACAO.CodAnula IS NULL AND ULTIMANOTA.DATAEXPIRACAO_FINAL < @data
end
March 16, 2010 at 3:20 am
I noticed that the server where this query is very fast as 2 quadcore processors....
The other machines only have a dual processor.
Maybe it's this....
What do you think?
Thanks
P.s - When i run this query in SQL Server 2000 it functions very well (even in the dual processors machine).
Did something change in terms of processors from SQL Server 2000 to SQL Server 2005?
March 16, 2010 at 6:27 am
It's possible. All those ISNULL functions on your columns are going to absolutely kill performance. Have you looked at the execution plan for the query, both on the fast servers and the slow servers. I think you'll see differences there. It's also possible that some servers have more up to date statistics than others. Update the statistics on all servers and run it again. It's also possible that from server to server you might see more index fragmentation, again, causing different performance. As you say, some servers may have varying numbers of CPU's, different memory, different disks... In other words, to answer your question intelligently, a lot more information is needed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply