查询太慢了 [英] Query too slow
问题描述
请提前告诉我我的小英语。
我有这个存储过程
************************************************ ** ************************** *
**********
声明@Azienda为varchar（3），@ Utente为varchar（20），
@DataDa为datetime，@ DataA为datetime，
@AreaDa为varchar（3），@ AreaA为varchar（3），
@LineaDa为varchar（3），@ Random为varchar（3），
@ TipoDa as varchar（3），@ TipoA as varchar（3），
@FamigliaDa as varchar（3），@ .FamigliaA as varchar（3），
@ProdottoDa as varchar（20），@ ProtottoA as varchar（20），
@AgenteDa as varchar（4），@ agenteA as varchar（4），
@NazioneDa as varchar（ 50），@ NasioA as varchar（50），
@ZonaDa as Varchar（3），@ ZonaA as Varchar（3），
@ProvinciaDa as varchar（2） ，@ ProvinciaA as varchar（2），
@ClienteDa as Varchar（12），@ ClienteA as Varchar（12），
@DestinDa as varchar （5），@ DestinA as varchar（5），
@TipoDestinDa as varchar（1），@ TipoDestinA as varchar（1），
@FlagProdNoTarget as varchar（5 ），
@GrAcqDa为varchar（10），@ GroAcqA为varchar（10），
@TipoCliDa为varchar（3），@ TivolioCliA为varchar（3），
@SettMercDa为varchar（3），@ SETMercA为varchar（3）
设置@Azienda =''900''
设置@ Utente =''Eugenio''
Set @DataDa =''2004-01-01''
Set @DataA =''2004-01-10''
设置@AreaDa =''UNI''
设置@AreaA =''UNI''
设置@LineaDa =''''
设置@LineaA =''ZZZ''
设置@TipoDa =''''
设置@TipoA =''ZZZ''
设置@FamigliaDa =''''
设置@FamigliaA =''ZZZ''
设置@ProdottoDa =''''
设置@ProdottoA =''ZZZZZZZZZZZZZZZZZZZZ''
设置@AgenteDa =''''
设置@AgenteA = '' ZZZZ ''
组@NazioneDa = '' ''
组@NazioneA = '' ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZ ''
组@ ZonaDa =''''
设置@ZonaA =''ZZZ''
设置@ProvinciaDa =''''
设置@ProvinciaA =''ZZ''
设置@ClienteDa =''''
设置@ClienteA =''ZZZZZZZZZZZZ''
设置@DestinDa =''''
设置@DestinA =''ZZZZZ''
设置@TipoDestinDa =''''
设置@TipoDestinA = ''Z''
设置@FlagProdNoTarget =''Vero''
设置@GrAcqDa =''''
设置@GrAcqA = ''ZZZZZZZZZZ''
设置@TipoCliDa =''''
设置@TipoCliA =''ZZZ''
设置@SettMercDa = ''''
设置@SettMercA =''ZZZ''
选择WSDFR.AreaCommerciale，
WSDFR.Agente，
WSDFR.NazDestin，
WSDFR.ZonaDestin，
WSDFR.ProvDestin，
WSDFR.Cliente，
WSDFR.DescrCliente，
WSDFR.GruppoAcq，
WSDFR.TipoCli，
WSDFR.SettMerc，
WSDFR.CDestin，
WSDFR.DescrDestin，
WSDFR.TipoDestin，
WSDFR.EsclStatis，
WSDFR.EsclTarget，
WSDFR。 ValoreNetto，
WSDFR.TpDocum，
WSDFR.VCambioITL，
WSDFR.VCambioEUR，
WSDFR.MeseFatt，
WSDFR.Posizione
来自W_St_DocFatt_Righe WSDFR
内部加入UniP_Prodotti UPP on
WSDFR.prodotto = UPP.Cod
@DataDa和@DataA之间的WSDFR.Dtdocum和@AreaDa与@AreaA之间的WSDFR.AreaCommercia和
WSDFR @LineaDa和@LineaA和@TipoDa和@TipoA和@FamigliaDa和@FamigliaA之间
WSDFR.FamigliaProdotto之间
WSDFR.TipoProdotto之间.LineaProdotto b $ b @DrodottoDa之间的WSDFR.Prodotto和@ProdottoA和
@AgenteDa和@AgenteA之间的WSDFR.Agente
********************* ***************************** ********************* *****
************************
" ; W_St_DocFatt_Righe"是一个视图。
此查询在我的SQL7服务器上运行，大约需要10秒。
此查询存在于另一台SQL7服务器上，直到上周它花了大约
10秒。
两台服务器的配置相同。只有硬件不同。
现在，在第二台服务器上，这个查询需要大约30分钟来提取s
ame详细信息，但是任何人都改变了详情。
如果我在没有Where的情况下执行此查询，它会在7
秒内显示详细信息。
如果在哪里，那么查询仍然需要大约相同的时间
@DataDa和@DataA之间的WSDFR.Dtdocum以及@AreaDa和@AreaA之间的
WSDFR.AreaCommerciale和
WSDFR.LineaProdotto @LineaDa和@LineaA之间和
- WSDFR.TipoProdotto @TipoDa和@TipoA之间和
- WSDFR.FamigliaProdotto @ FamigliaDa和@FamigliaA和
WSDFR.Prodotto @ProdottoDa和@ProdottoA之间和
WSDFR.Agente @AgenteDa和@AgenteA
或
@DataDa和@DataA之间的WSDFR.Dtdocum和@AreaDa和@AreaA之间的
WSDFR.AreaCommercia，
--WSDFR.LineaProdot到@LineaDa和@LineaA和@TipoDa和@TipoA和@FamigliaDa和@FamigliaA和
WSDFR.FamigliaProdotto之间
--WSDFR.TipoProdotto之间/>
WSDFR.Prodotto @ProdottoDa和@ProdottoA和
WSDFR.Agente @AgenteDa和@AgenteA
或
@DataDa和@DataA之间的WSDFR.Dtdocum和@AreaDa和@AreaA之间的
WSDFR.AreaCommerciale和
- WSDFR.LineaProdotto @ @TamoDa和@TipoA之间的LineaDa和@LineaA和
- WSDFR.TipoProdotto和@FamigliaDa和@FamigliaA之间的
- WSDFR.FamigliaProdotto和
- @DrodottoDa和@ProdottoA之间的WSDFR.Prodotto和@AgenteDa与@AgenteA之间的
WSDFR.Agente
这是一个真正的难题！
会发生什么？
有没有人遇到过这样的问题并找到合适的解决方案？
谢谢提前。
再见
Eu genio
Excuse me in advance fo my little English.
I''ve got this stored procedure
************************************************** **************************
**********
declare @Azienda as varchar(3), @Utente as varchar(20),
@DataDa as datetime, @DataA as datetime,
@AreaDa as varchar(3), @AreaA as varchar(3),
@LineaDa as varchar(3), @LineaA as varchar(3),
@TipoDa as varchar(3), @TipoA as varchar(3),
@FamigliaDa as varchar(3), @FamigliaA as varchar(3),
@ProdottoDa as varchar(20), @ProdottoA as varchar(20),
@AgenteDa as varchar(4), @AgenteA as varchar(4),
@NazioneDa as varchar(50), @NazioneA as varchar(50),
@ZonaDa as Varchar(3), @ZonaA as Varchar(3),
@ProvinciaDa as varchar(2), @ProvinciaA as varchar(2),
@ClienteDa as Varchar(12), @ClienteA as Varchar(12),
@DestinDa as varchar (5), @DestinA as varchar (5),
@TipoDestinDa as varchar(1), @TipoDestinA as varchar(1),
@FlagProdNoTarget as varchar(5),
@GrAcqDa as varchar(10), @GrAcqA as varchar(10),
@TipoCliDa as varchar(3), @TipoCliA as varchar(3),
@SettMercDa as varchar(3), @SettMercA as varchar(3)
Set @Azienda = ''900''
Set @Utente = ''Eugenio''
Set @DataDa = ''2004-01-01''
Set @DataA = ''2004-01-10''
Set @AreaDa = ''UNI''
Set @AreaA = ''UNI''
Set @LineaDa = ''''
Set @LineaA = ''ZZZ''
Set @TipoDa = ''''
Set @TipoA = ''ZZZ''
Set @FamigliaDa = ''''
Set @FamigliaA = ''ZZZ''
Set @ProdottoDa = ''''
Set @ProdottoA = ''ZZZZZZZZZZZZZZZZZZZZ''
Set @AgenteDa = ''''
Set @AgenteA = ''ZZZZ''
Set @NazioneDa = ''''
Set @NazioneA = ''ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZ''
Set @ZonaDa = ''''
Set @ZonaA = ''ZZZ''
Set @ProvinciaDa = ''''
Set @ProvinciaA = ''ZZ''
Set @ClienteDa = ''''
Set @ClienteA = ''ZZZZZZZZZZZZ''
Set @DestinDa = ''''
Set @DestinA = ''ZZZZZ''
Set @TipoDestinDa = ''''
Set @TipoDestinA = ''Z''
Set @FlagProdNoTarget = ''Vero''
Set @GrAcqDa = ''''
Set @GrAcqA = ''ZZZZZZZZZZ''
Set @TipoCliDa = ''''
Set @TipoCliA = ''ZZZ''
Set @SettMercDa = ''''
Set @SettMercA = ''ZZZ''
Select WSDFR.AreaCommerciale,
WSDFR.Agente,
WSDFR.NazDestin,
WSDFR.ZonaDestin,
WSDFR.ProvDestin,
WSDFR.Cliente,
WSDFR.DescrCliente,
WSDFR.GruppoAcq,
WSDFR.TipoCli,
WSDFR.SettMerc,
WSDFR.CDestin,
WSDFR.DescrDestin,
WSDFR.TipoDestin,
WSDFR.EsclStatis,
WSDFR.EsclTarget,
WSDFR.ValoreNetto,
WSDFR.TpDocum,
WSDFR.VCambioITL,
WSDFR.VCambioEUR,
WSDFR.MeseFatt,
WSDFR.Posizione
From W_St_DocFatt_Righe WSDFR
inner join UniP_Prodotti UPP on
WSDFR.prodotto=UPP.Cod
Where WSDFR.Dtdocum between @DataDa and @DataA and
WSDFR.AreaCommerciale between @AreaDa and @AreaA and
WSDFR.LineaProdotto between @LineaDa and @LineaA and
WSDFR.TipoProdotto between @TipoDa and @TipoA and
WSDFR.FamigliaProdotto between @FamigliaDa and @FamigliaA and
WSDFR.Prodotto between @ProdottoDa and @ProdottoA and
WSDFR.Agente between @AgenteDa and @AgenteA
************************************************** **************************
************************
"W_St_DocFatt_Righe" is a view.
This query run on my SQL7 server and it takes about 10 seconds.
This query exists on another SQL7 server and until last week it took about
10 seconds.
The configuration of both servers are same. Only the hardware is different.
Now, on the second server this query takes about 30 minutes to extract the s
ame details, but anybody has changed any details.
If I execute this query without Where, it''ll show me the details in 7
seconds.
This query still takes about same time if Where is
Where WSDFR.Dtdocum between @DataDa and @DataA and
WSDFR.AreaCommerciale between @AreaDa and @AreaA and
WSDFR.LineaProdotto between @LineaDa and @LineaA and
--WSDFR.TipoProdotto between @TipoDa and @TipoA and
--WSDFR.FamigliaProdotto between @FamigliaDa and @FamigliaA and
WSDFR.Prodotto between @ProdottoDa and @ProdottoA and
WSDFR.Agente between @AgenteDa and @AgenteA
or
Where WSDFR.Dtdocum between @DataDa and @DataA and
WSDFR.AreaCommerciale between @AreaDa and @AreaA and
--WSDFR.LineaProdotto between @LineaDa and @LineaA and
--WSDFR.TipoProdotto between @TipoDa and @TipoA and
WSDFR.FamigliaProdotto between @FamigliaDa and @FamigliaA and
WSDFR.Prodotto between @ProdottoDa and @ProdottoA and
WSDFR.Agente between @AgenteDa and @AgenteA
or
Where WSDFR.Dtdocum between @DataDa and @DataA and
WSDFR.AreaCommerciale between @AreaDa and @AreaA and
--WSDFR.LineaProdotto between @LineaDa and @LineaA and
--WSDFR.TipoProdotto between @TipoDa and @TipoA and
--WSDFR.FamigliaProdotto between @FamigliaDa and @FamigliaA and
--WSDFR.Prodotto between @ProdottoDa and @ProdottoA and
WSDFR.Agente between @AgenteDa and @AgenteA
It is a real puzzle!
What happen?
Is there someone that had such as problems and have the right solution?
Thanks in advance.
Bye
Eugenio
推荐答案
Eugenio（Ci**@Eugenio.it）写道：
Eugenio (Ci**@Eugenio.it) writes:
此查询运行于我的SQL7服务器大约需要10秒钟。
这个查询存在于另一台SQL7服务器上，直到上周花了大约10秒钟。
两台服务器的配置相同。只有硬件不同。
现在，在第二台服务器上，这个查询大约需要30分钟来提取详细信息，但是任何人都改变了任何细节。 />
如果我在没有Where的情况下执行此查询，它将在7
秒内显示详细信息。
如果Where is
This query run on my SQL7 server and it takes about 10 seconds.
This query exists on another SQL7 server and until last week it took about
10 seconds.
The configuration of both servers are same. Only the hardware is
different.
Now, on the second server this query takes about 30 minutes to extract
the s ame details, but anybody has changed any details.
If I execute this query without Where, it''ll show me the details in 7
seconds.
This query still takes about same time if Where is
在了解视图中的基础表及其
索引的情况下，我只能回答一般性问题。
SQL Server使用基于成本的优化器。此优化程序评估可能的查询计划的数量
，并估计哪个计划将提供最佳的b
性能。作为其决策的基础，它使用
表的统计数据来保存各列中的数据分布。
默认情况下，这些统计信息会自动更新，通常在
与SQL Server一起查询表格。
优化器有很多可能出错。例如
如果统计数据有些偏差，在计算某个连接订单的成本时，第一个表中的一个小错误
可能是一个很大的错误
在最后，并错误导致错误的计划。还有
系统错误;优化器不知道
列之间的相关性，例如OrderDate和InvoiceDate倾向于跟随每个
其他。
因为对此，很可能会发生一个昨天执行得很好的查询突然执行得慢得多，因为你已经超出了一些
的阈值，导致SQL Server选择一个糟糕的计划。
你应该做的第一件事是验证所涉及的表在两个服务器中都有相同的索引集。您还可以在有问题的服务器上尝试UPDATE STATISTICS
WITH FULLSCAN，这可能会消除
统计数据中的不准确性。你还应该在表格上使用DBCC SHOWCONTIG，并且
观察碎片。使用DBCC DBREIDNEX对表进行碎片整理。
-
Erland Sommarskog，SQL Server MVP， so **** @ algonet.se
SQL Server SP3联机丛书
http://www.microsoft.com/sql/techinf...2000/books.asp
With out knowledge about the underlying tables in the view and their
indexes, I can only answer in general terms.
SQL Server uses a cost-based optimizer. This optimizer evaluates a number
of possible query plans, and estimates which plan will give the best
performance. As basis for its decisions it uses statistics about the
table which holds the distribution of the data in the various columns.
By default these statistics are updated automatically, usually in
conjunction with SQL Server querying the tables.
There are plenty of possibilities for optimizer to go wrong. For instance
if the statistics are somewhat skewed, a small error in the first table
when computing the cost of a certain join order, may be a large error
in the last, and incorrectly lead to the wrong plan. There are also
systematic errors; the optimizer does not know about correlation between
columns, for instance that OrderDate and InvoiceDate tend follow each
other.
Because of this, it can well happen that a query that executed well
yesterday, suddenly executes much slower, because you''ve execeeded some
threshold which causes SQL Server to pick a bad plan.
The first you should do is to verify that the involved tables have the
same set of indexes in both servers. You could also try UPDATE STATISTICS
WITH FULLSCAN on the problematic server, this may remove inaccuracies in
the statistics. You should also use DBCC SHOWCONTIG on the tables and
observe fragmentation. Use DBCC DBREIDNEX to defragment the tables.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
" Erland Sommarskog" <所以**** @ algonet.se> ha scritto nel messaggio
news：Xn ********************** @ 127.0.0.1 ...
"Erland Sommarskog" <so****@algonet.se> ha scritto nel messaggio
news:Xn**********************@127.0.0.1...
Eugenio（Ci**@Eugenio.it）写道：
Eugenio (Ci**@Eugenio.it) writes:
此查询在我的SQL7服务器上运行，大约需要10秒钟。
此查询存在于另一台SQL7服务器上，直到最后一周花了大约10秒的
。
两台服务器的配置是一样的。只有硬件不同。
现在，在第二台服务器上，这个查询大约需要30分钟来提取详细信息，但是任何人都改变了任何细节。 />
如果我在没有Where的情况下执行此查询，它将在7
秒内显示详细信息。
如果Where is
This query run on my SQL7 server and it takes about 10 seconds.
This query exists on another SQL7 server and until last week it took about 10 seconds.
The configuration of both servers are same. Only the hardware is
different.
Now, on the second server this query takes about 30 minutes to extract
the s ame details, but anybody has changed any details.
If I execute this query without Where, it''ll show me the details in 7
seconds.
This query still takes about same time if Where is
SQL Server使用基于成本的优化器。此优化程序评估可能的查询计划的数量，并估计哪个计划将提供最佳的性能。作为其决策的基础，它使用
表的统计信息来保存各列中的数据分布。
默认情况下，这些统计信息会自动更新，通常与SQL Server查询结合使用表格。
优化器有很多可能出错。例如
如果统计数据有些偏差，第一张表中的一个小错误在计算某个连接订单的成本时，可能是最后一个很大的错误，并且导致错误错误的计划。还存在系统性错误;优化器不知道
列之间的相关性，例如OrderDate和InvoiceDate倾向于跟随每个
因此，很可能发生执行的查询好吧
昨天，突然执行得慢得多，因为你已经超过了一些阈值，导致SQL Server选择了一个糟糕的计划。
你要做的第一件事就是验证所涉及的表在两个服务器中都具有相同的索引集。您还可以在有问题的服务器上尝试UPDATE STATISTICS
WITH FULLSCAN，这可能会消除统计数据中的不准确之处。您还应该在表上使用DBCC SHOWCONTIG并观察碎片。使用DBCC DBREIDNEX对表进行碎片整理。
- Erland Sommarskog，SQL Server MVP，所以** **@algonet.se
SQL Server SP3的在线书籍
http://www.microsoft.com/sql/techinf...2000/books.asp
谢谢Erland
我会马上试试。
再见
Eugenio
Thanks Erland
I''ll try it immediatly.
Bye
Eugenio
>
在了解视图中的基础表及其
索引的情况下，我只能回答一般性的问题。
SQL服务器使用基于成本的优化器。此优化程序评估可能的查询计划的数量，并估计哪个计划将提供最佳的性能。作为其决策的基础，它使用
表的统计信息来保存各列中的数据分布。
默认情况下，这些统计信息会自动更新，通常与SQL Server查询结合使用表格。
优化器有很多可能出错。例如
如果统计数据有些偏差，第一张表中的一个小错误在计算某个连接订单的成本时，可能是最后一个很大的错误，并且导致错误错误的计划。还存在系统性错误;优化器不知道
列之间的相关性，例如OrderDate和InvoiceDate倾向于跟随每个
因此，很可能发生执行的查询好吧
昨天，突然执行得慢得多，因为你已经超过了一些阈值，导致SQL Server选择了一个糟糕的计划。
你要做的第一件事就是验证所涉及的表在两个服务器中都具有相同的索引集。您还可以在有问题的服务器上尝试UPDATE STATISTICS
WITH FULLSCAN，这可能会消除统计数据中的不准确之处。您还应该在表上使用DBCC SHOWCONTIG并观察碎片。使用DBCC DBREIDNEX对表进行碎片整理。
- Erland Sommarskog，SQL Server MVP，所以** **@algonet.se
SQL Server SP3的在线书籍
http://www.microsoft.com/sql/techinf...2000/books.asp
With out knowledge about the underlying tables in the view and their
indexes, I can only answer in general terms.
SQL Server uses a cost-based optimizer. This optimizer evaluates a number
of possible query plans, and estimates which plan will give the best
performance. As basis for its decisions it uses statistics about the
table which holds the distribution of the data in the various columns.
By default these statistics are updated automatically, usually in
conjunction with SQL Server querying the tables.
There are plenty of possibilities for optimizer to go wrong. For instance
if the statistics are somewhat skewed, a small error in the first table
when computing the cost of a certain join order, may be a large error
in the last, and incorrectly lead to the wrong plan. There are also
systematic errors; the optimizer does not know about correlation between
columns, for instance that OrderDate and InvoiceDate tend follow each
other.
Because of this, it can well happen that a query that executed well
yesterday, suddenly executes much slower, because you''ve execeeded some
threshold which causes SQL Server to pick a bad plan.
The first you should do is to verify that the involved tables have the
same set of indexes in both servers. You could also try UPDATE STATISTICS
WITH FULLSCAN on the problematic server, this may remove inaccuracies in
the statistics. You should also use DBCC SHOWCONTIG on the tables and
observe fragmentation. Use DBCC DBREIDNEX to defragment the tables.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
我做到了，但它什么都没改变。
你能否给我其他人的建议。
非常感谢/>
Eugenio
I did that, but it''s changed nothing.
Can you give me others suggestion about this.
Thanks a lot
Eugenio
这篇关于查询太慢了的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
