gpt4 book ai didi

SQL 查询每隔几天就会变慢

转载 作者:搜寻专家 更新时间:2023-10-30 19:48:26 24 4
gpt4 key购买 nike

我有一些查询多年来一直运行良好,但突然开始变慢了很多。从几乎瞬间到 25 秒。经过一番研究,发现问题出在索引“损坏”上。重新组织和重建查询使用的四个表的所有索引修复了问题。

太好了,我想一切都完成了。但是过了几天又是同样的问题。重建索引修复了它,但随后它开始每隔几天恢复一次。查询变慢 => 重建索引。这些表非常稳定,一周内添加几百个项目,它们有大约 30 万条记录。

这可能是什么原因?

编辑:

关于这个查询,我不知道如何在不破坏东西的情况下提高效率

ALTER PROCEDURE [dbo].[odat_PricesByDealer]
(
@dealer_id int,
@dealergroup_id int,
@startdate datetime,
@pricetype_id int,
@product_groep nvarchar(30)
)
AS
SET NOCOUNT ON

IF @pricetype_id = 0
SET @pricetype_id = NULL

IF @product_groep = '0'
SET @product_groep = NULL

SELECT a.price_ID
, a.make_id
, e.make_code_name
, a.dealergroup_ID
, c.product_ID
, c.product_code
, c.product_department
, c.product_code_name
, c.product_groep
, c.product_subgroep1
, c.product_subgroep2
, c.product_producent
, b.pricerow_lowerbound
, b.pricerow_upperbound
, b.pricerow_value
, aa.startdate AS startdate
, a.price_enddate AS enddate
, d.pricetype_id
, d.pricetype_name
, 3 AS price_level
FROM dbo.tblPrices a WITH (NOLOCK)
JOIN udf_PricesByDealer(@dealer_id, @startdate) aa ON a.price_startdate = aa.startdate
AND a.product_id = aa.product_id
AND a.pricetype_ID = aa.pricetype_ID
AND a.make_ID = aa.make_ID
AND a.dealergroup_ID = aa.dealergroup_ID
AND a.dealer_ID = aa.dealer_ID
JOIN dbo.tblPriceRows b WITH (NOLOCK) ON a.price_ID = b.price_ID
JOIN dbo.tblProducts c WITH (NOLOCK) ON a.product_ID = c.product_ID
JOIN dbo.tblPriceTypes d WITH (NOLOCK) ON a.pricetype_ID = d.pricetype_ID
JOIN dbo.tblMakes e WITH (NOLOCK) ON a.make_ID = e.make_ID
WHERE a.make_ID <> 0
AND a.dealergroup_ID = @dealergroup_id
AND a.dealer_ID = @dealer_id
AND (d.pricetype_id = @pricetype_id OR @pricetype_id IS NULL)
AND (c.product_groep = @product_groep OR @product_groep IS NULL)
AND a.price_authorized = 1
AND c.product_exclude_from_pricelist = 0

并且 JOIN 中的 UDF 看起来像这样

ALTER FUNCTION [dbo].[udf_PricesByDealer] 
(
@dealer_ID numeric,
@startdate datetime
)
RETURNS TABLE
AS
RETURN
(
SELECT MAX(a.price_startdate) AS startdate
, a.make_id
, a.product_id
, a.pricetype_id
, a.dealergroup_id
, a.dealer_id
FROM dbo.tblPrices a WITH (NOLOCK)
JOIN udf_GetMakeIdForDealer(@dealer_id) b ON a.make_ID = b.make_ID
WHERE ( (a.price_startdate <= @startdate AND a.price_enddate >= @startdate)
OR
(a.price_startdate <= @startdate AND a.price_enddate < a.price_startdate) )
AND a.make_ID <> 0
AND a.dealergroup_id <> 0
AND a.dealer_id = @dealer_ID
GROUP BY a.make_id
, a.product_id
, a.pricetype_id
, a.dealergroup_id
, a.dealer_id
)

和另一个加入 UDF

ALTER FUNCTION [dbo].[udf_GetMakeIdForDealer]
(
@dealer_ID numeric
)
RETURNS TABLE
AS
RETURN
(
SELECT m.make_ID
, m.make_code_name
FROM [dbo].[tblMakes] m WITH (NOLOCK)
WHERE (m.make_ID in (SELECT make_ID
FROM tblDealerGroupMakes WITH (NOLOCK)
WHERE dealergroup_ID = (SELECT dealergroup_ID
FROM tblDealers WITH (NOLOCK)
WHERE dealer_ID = @dealer_ID))
OR
m.make_ID IN (SELECT make_ID
FROM tblDealerMakes WITH (NOLOCK)
WHERE dealer_ID = @dealer_ID AND dealermake_exclude = 0))
AND m.make_ID NOT IN (SELECT make_ID
FROM tblDealerMakes WITH (NOLOCK)
WHERE dealer_ID = @dealer_ID AND dealermake_exclude = 1)
)

最佳答案

Punter015 已经发布,你必须调整你的数据库,例如与维护计划。因为你不知道是否有必要(我遇到过同样的问题一次)我做了一些调查,在网上找到了一些样本,最后做了这个程序。

它检查是否需要重建索引,然后执行重建索引。我每晚都使用 cron 任务 (sqlcmd.exe) 运行它。不知道这是否是最好的方法,但对我来说效果很好。

CREATE PROCEDURE [dbo].[proc_ReorganizeIndexes]
AS
BEGIN

DECLARE @table nvarchar(100);
DECLARE @index_name nvarchar(100);
DECLARE @ext_frag float;
DECLARE @int_frag float;
DECLARE @sql nvarchar(max);
DECLARE local_index_cursor CURSOR FOR

SELECT object_name(dt.object_id) Tablename
,si.name IndexName
,dt.avg_fragmentation_in_percent AS ExternalFragmentation
,dt.avg_page_space_used_in_percent AS InternalFragmentation
FROM
(
SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (db_id(DB_NAME()),null,null,null,'DETAILED'
)
WHERE index_id <> 0) AS dt
INNER JOIN sys.indexes si ON si.object_id=dt.object_id
AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10
AND dt.avg_page_space_used_in_percent<75 ORDER BY avg_fragmentation_in_percent DESC

OPEN local_index_cursor
FETCH NEXT FROM local_index_cursor
INTO @table, @index_name, @ext_frag, @int_frag

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT 'Need rebuild: ' + @table+ ' for index ' + @index_name + ' Fragmentation (int/ext): ' + CAST(@int_frag AS varchar(100)) + ' / ' + CAST(@ext_frag AS varchar(100))
SET @sql = 'ALTER INDEX ALL ON ' + @table + ' REBUILD WITH (FILLFACTOR=90)'
EXEC sp_executesql @sql

FETCH NEXT FROM local_index_cursor
INTO @table, @index_name, @ext_frag, @int_frag
END

CLOSE local_index_cursor
DEALLOCATE local_index_cursor

END

关于SQL 查询每隔几天就会变慢,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/21482893/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com