我有一个 dtsx 包,它将数据从 csv 导入到表中,它会在将数据加载到表中之前截断该表。
问题是,有时由于某种原因,包会失败,因此我最终得到一个空表,我想将工作流包装在某种事务中,并在发生故障时恢复表。
有什么方法可以实现这个吗?
我有一个 dtsx 包,它将数据从 csv 导入到表中,它会在将数据加载到表中之前截断该表。
问题是,有时由于某种原因,包会失败,因此我最终得到一个空表,我想将工作流包装在某种事务中,并在发生故障时恢复表。
有什么方法可以实现这个吗?
我需要能够迭代所有表并发出TRUNCATE
命令,但是我不希望循环因阻塞而停止。本质上,我正在寻找最佳方法来确定SCH-M
在发出截断命令之前是否可以获得锁定。
Windows Server 2016 Datacenter 10.0 上的 Microsoft SQL Server 2019 (RTM-CU26) - 15.0.4365.2 (X64) 标准版(64 位)
有一个表似乎在不断更新。还有一个存储过程定期运行并遍历表,并尝试使用以下命令截断数据分区:
TRUNCATE TABLE [dbo].[Table] WITH (PARTITIONS (10))
循环过程总是锁定在同一个表上,即使在要删除的分区中没有更新数据,不断更新的表也会失败。
调用进程无法更新。除了修改查询之外,是否有其他技巧可以将更新绑定到分区,或者强制不更改分区不被阻止/锁定?
页面锁定会导致分区截断失败吗?例如,两个分区是否可以共享一个页面,而尝试使用分区(2)删除会失败,因为某个进程正在更新分区 1 中的数据,而分区 2 恰好与分区 1 共享一个页面?
CREATE TABLE [dbo].[Table]
(
[TableID] INT NOT NULL
...
, [SourceID] NVARCHAR(4000) NOT NULL
, CONSTRAINT [PK_Table_TableID] PRIMARY KEY NONCLUSTERED([TableTableID] ASC , [SourceID] ASC) ON psSourceID(SourceID)
, CONSTRAINT [CIX_Table_TableID] UNIQUE CLUSTERED ([SourceID] ASC, [TableID] ASC) ON psSourceID(SourceID)
)
ON psSourceID(SourceID)
GO
ALTER TABLE [dbo].[Table] SET (LOCK_ESCALATION = AUTO)
GO
该TRUNCATE
命令被正在对表运行更新的会话阻止。在较低的环境中,经过多次尝试后,阻止过程从未发生过,因此假设只会WITH PARTITION
按分区放置元数据锁,而不是整个表。
MySQL:8.0.35 - 源代码分发
我有长文本(DC2Type:array)列,并且列字符串中的一些字符值不正确,所以我想替换这种数据格式:
a:2:{s:7:"content";s:6:"text 1";s:3:"url";s:19:"https://someurl.com";}
转换为以下数据格式:
a:2:{i:0;s:6:"text 1";i:1;s:19:"https://someurl.com";}
因此将 s:7:"content" 替换为 i:0
并将 s:3:"url" 替换为:i:1
怎么做 ?
问候
我们最近发布了一个新功能版本,该版本向一个大型表(约 3500 万行)添加了大约 25 个新列,现在我们遇到了一些重大的查询性能问题。我认为这与作为这些新列的一部分添加的大量数据有关,但它也可能是基于索引、基于查询或其他我没有想到的东西。
该表包含徽章刷卡信息以及刷卡人员的信息。它位于 AWS RDS 数据库中,我可以完全控制架构,但不能控制 RDS 实例本身。该表的架构如下:
CREATE TABLE [occupancy].[SwipesComplete] (
[PrimaryObjectID] varchar,
[ObjectID] int,
[UserID] varchar,
[Name] varchar,
[PersonnelTypeID] varchar,
[DoorName] varchar,
[SwipetimeUTC] datetime,
[SwipetimeEST] datetime,
[DoorID] int,
[SiteID] int,
[GroupDesc1] varchar,
[GroupDesc2] varchar,
[GroupDesc3] varchar,
[GroupDesc4] varchar,
[GroupDesc5] varchar,
[GroupDesc6] varchar,
[GroupDesc7] varchar,
[GroupDesc8] varchar,
[GroupDesc9] varchar,
[GroupDesc10] varchar,
[GroupDesc11] varchar,
[GroupDesc12] varchar,
[Company] varchar,
[Site] varchar,
[Lab_User] int,
[PersonAssignedBuildingRoomID] varchar,
[GroupName] varchar,
[NeighborhoodAssignedSiteCode] varchar,
[NeighborhoodAssignedSeat] varchar,
[NeighborhoodName] varchar,
[PersonnelType] varchar,
[EmploymentType] varchar,
[PTFriendlyName] varchar,
[PersonAssignedBuildingLocID] int,
[PersonAssignedAreaLocID] int,
[PersonAssignedFloorLocID] int,
[LocationCorrelationSourceID] int,
[SwipeBuildingLocID] int
);
我们有一个基于 Web 的应用程序,允许用户查询这些数据并根据用户选择的时间间隔(每小时、每天、每周、每月、每年)在图表中显示汇总数据。他们几乎可以根据所有这些列进行筛选。
该表上有索引(我没有创建这些,可能需要修改):
我们使用以下存储过程查询这些数据。我们为许多 WHERE 传递一个 JSON 数组,因为用户可以为许多过滤器选择多个值。用户还可以选择将数据分组或不分组,如您在 CASE 语句中看到的那样。我知道这个存储过程不太好:
CREATE PROCEDURE [occupancy].[GetUniqueOccupancyByRange]
(
@StartDate datetime
,@EndDate datetime
,@Interval nvarchar(20)
,@PTFriendlyName VARCHAR(4000)
,@SET VARCHAR(4000)
,@Function VARCHAR(4000)
,@BusinessUnit VARCHAR(4000)
,@AssignedSite VARCHAR(4000) -- assigned site
,@AssignedLocID VARCHAR(4000) -- assigned building
,@NeighborhoodName VARCHAR(4000)
,@SwipeLocID VARCHAR(4000)
,@SiteID INT
,@GroupBy VARCHAR(255)
)
AS
BEGIN
if @Interval = 'Hourly'
BEGIN
WITH MyCTE AS
(
SELECT ObjectID, DATEPART(year, SwipetimeEST) AS year, DATEPART(month, SwipetimeEST) AS month, DATEPART(week, SwipetimeEST) AS week, DATEPART(day, SwipetimeEST) AS day, DATEPART(hour, SwipetimeEST) AS hour,
-- Mapping the @GroupBy options to the respective columns
CASE
WHEN @GroupBy = 'None' THEN NULL
WHEN @GroupBy = 'Personnel Type' THEN PTFriendlyName
WHEN @GroupBy = 'Senior Executive Team' THEN GroupDesc3
WHEN @GroupBy = 'Assigned Building' THEN l.BuildingLocName
WHEN @GroupBy = 'Swipes by Building' THEN loc.BuildingLocName
END AS GroupingClause
FROM [Database_Server].[occupancy].[SwipesComplete] sc
LEFT JOIN [Database_Server].[occupancy].[DoorsComplete] dc
ON (sc.DoorID = dc.DoorID)
LEFT JOIN [Join_Database].[site].[LocationInformation] l
ON (sc.PersonAssignedBuildingLocID = l.LocID)
LEFT JOIN [Join_Database].[site].[LocationInformation] loc
ON (sc.SwipeBuildingLocID = loc.LocID)
WHERE sc.SiteID = @SiteID
AND SwipetimeUTC BETWEEN @StartDate AND @EndDate
AND (sc.Name IS NULL OR sc.Name NOT LIKE '%Visitor%')
AND dc.AssetLocID IS NOT NULL
AND dc.AssetLocID != 5
AND sc.SwipetimeEST IS NOT NULL
AND (@NeighborhoodName IS NULL OR sc.NeighborhoodName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@NeighborhoodName)))
AND (@SET IS NULL OR sc.GroupDesc3 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SET)))
AND (@Function IS NULL OR sc.GroupDesc4 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@Function)))
AND (@BusinessUnit IS NULL OR sc.GroupDesc5 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@BusinessUnit)))
AND (@PTFriendlyName IS NULL OR sc.PTFriendlyName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@PTFriendlyName)))
AND (@AssignedLocID IS NULL OR sc.PersonAssignedBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedLocID)))
AND (@AssignedSite IS NULL OR sc.Site IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedSite)))
AND (@SwipeLocID is NULL OR sc.SwipeBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SwipeLocID)))
)
SELECT COUNT(DISTINCT(ObjectID)) as count, year, month, week, day, hour, GroupingClause AS GroupedBy
FROM MyCTE
GROUP BY year, month, week, day, hour, GroupingClause
END
if @Interval = 'Daily'
BEGIN
WITH MyCTE AS
(
SELECT ObjectID, DATEPART(year, SwipetimeEST) AS year, DATEPART(month, SwipetimeEST) AS month, DATEPART(week, SwipetimeEST) AS week, DATEPART(day, SwipetimeEST) AS day,
CASE
WHEN @GroupBy = 'None' THEN NULL
WHEN @GroupBy = 'Personnel Type' THEN PTFriendlyName
WHEN @GroupBy = 'Senior Executive Team' THEN GroupDesc3
WHEN @GroupBy = 'Assigned Building' THEN l.BuildingLocName
WHEN @GroupBy = 'Swipes by Building' THEN loc.BuildingLocName
END AS GroupingClause
FROM [Database_Server].[occupancy].[SwipesComplete] sc
LEFT JOIN [Database_Server].[occupancy].[DoorsComplete] dc
ON (sc.DoorID = dc.DoorID)
LEFT JOIN [Join_Database].[site].[LocationInformation] l
ON (sc.PersonAssignedBuildingLocID = l.LocID)
LEFT JOIN [Join_Database].[site].[LocationInformation] loc
ON (sc.SwipeBuildingLocID = loc.LocID)
WHERE sc.SiteID = @SiteID
AND SwipetimeUTC BETWEEN @StartDate AND @EndDate
AND (sc.Name IS NULL OR sc.Name NOT LIKE '%Visitor%')
AND dc.AssetLocID IS NOT NULL
AND dc.AssetLocID != 5
AND sc.SwipetimeEST IS NOT NULL
AND (@NeighborhoodName IS NULL OR sc.NeighborhoodName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@NeighborhoodName)))
AND (@SET IS NULL OR sc.GroupDesc3 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SET)))
AND (@Function IS NULL OR sc.GroupDesc4 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@Function)))
AND (@BusinessUnit IS NULL OR sc.GroupDesc5 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@BusinessUnit)))
AND (@PTFriendlyName IS NULL OR sc.PTFriendlyName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@PTFriendlyName)))
AND (@AssignedLocID IS NULL OR sc.PersonAssignedBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedLocID)))
AND (@AssignedSite IS NULL OR sc.Site IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedSite)))
AND (@SwipeLocID is NULL OR sc.SwipeBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SwipeLocID)))
)
SELECT COUNT(DISTINCT(ObjectID)) as count, year, month, week, day, GroupingClause as GroupedBy
FROM MyCTE
GROUP BY year, month, week, day, GroupingClause
END
if @Interval = 'Weekly'
BEGIN
WITH MyCTE AS
(
SELECT ObjectID, DATEPART(year, SwipetimeEST) AS year, DATEPART(week, SwipetimeEST) AS week,
CASE
WHEN @GroupBy = 'None' THEN NULL
WHEN @GroupBy = 'Personnel Type' THEN PTFriendlyName
WHEN @GroupBy = 'Senior Executive Team' THEN GroupDesc3
WHEN @GroupBy = 'Assigned Building' THEN l.BuildingLocName
WHEN @GroupBy = 'Swipes by Building' THEN loc.BuildingLocName
END AS GroupingClause
FROM [Database_Server].[occupancy].[SwipesComplete] sc
LEFT JOIN [Database_Server].[occupancy].[DoorsComplete] dc
ON (sc.DoorID = dc.DoorID)
LEFT JOIN [Join_Database].[site].[LocationInformation] l
ON (sc.PersonAssignedBuildingLocID = l.LocID)
LEFT JOIN [Join_Database].[site].[LocationInformation] loc
ON (sc.SwipeBuildingLocID = loc.LocID)
WHERE sc.SiteID = @SiteID
AND SwipetimeUTC BETWEEN @StartDate AND @EndDate
AND (sc.Name IS NULL OR sc.Name NOT LIKE '%Visitor%')
AND dc.AssetLocID IS NOT NULL
AND dc.AssetLocID != 5
AND sc.SwipetimeEST IS NOT NULL
AND (@NeighborhoodName IS NULL OR sc.NeighborhoodName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@NeighborhoodName)))
AND (@SET IS NULL OR sc.GroupDesc3 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SET)))
AND (@Function IS NULL OR sc.GroupDesc4 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@Function)))
AND (@BusinessUnit IS NULL OR sc.GroupDesc5 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@BusinessUnit)))
AND (@PTFriendlyName IS NULL OR sc.PTFriendlyName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@PTFriendlyName)))
AND (@AssignedLocID IS NULL OR sc.PersonAssignedBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedLocID)))
AND (@AssignedSite IS NULL OR sc.Site IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedSite)))
AND (@SwipeLocID is NULL OR sc.SwipeBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SwipeLocID)))
)
SELECT COUNT(DISTINCT(ObjectID)) as count, year, week, GroupingClause as GroupedBy
FROM MyCTE
GROUP BY year, week, GroupingClause
END
if @Interval = 'Monthly'
BEGIN
WITH MyCTE AS
(
SELECT ObjectID, DATEPART(year, SwipetimeEST) AS year, DATEPART(month, SwipetimeEST) AS month,
CASE
WHEN @GroupBy = 'None' THEN NULL
WHEN @GroupBy = 'Personnel Type' THEN PTFriendlyName
WHEN @GroupBy = 'Senior Executive Team' THEN GroupDesc3
WHEN @GroupBy = 'Assigned Building' THEN l.BuildingLocName
WHEN @GroupBy = 'Swipes by Building' THEN loc.BuildingLocName
END AS GroupingClause
FROM [Database_Server].[occupancy].[SwipesComplete] sc
LEFT JOIN [Database_Server].[occupancy].[DoorsComplete] dc
ON (sc.DoorID = dc.DoorID)
LEFT JOIN [Join_Database].[site].[LocationInformation] l
ON (sc.PersonAssignedBuildingLocID = l.LocID)
LEFT JOIN [Join_Database].[site].[LocationInformation] loc
ON (sc.SwipeBuildingLocID = loc.LocID)
WHERE sc.SiteID = @SiteID
AND SwipetimeUTC BETWEEN @StartDate AND @EndDate
AND (sc.Name IS NULL OR sc.Name NOT LIKE '%Visitor%')
AND dc.AssetLocID IS NOT NULL
AND dc.AssetLocID != 5
AND sc.SwipetimeEST IS NOT NULL
AND (@NeighborhoodName IS NULL OR sc.NeighborhoodName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@NeighborhoodName)))
AND (@SET IS NULL OR sc.GroupDesc3 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SET)))
AND (@Function IS NULL OR sc.GroupDesc4 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@Function)))
AND (@BusinessUnit IS NULL OR sc.GroupDesc5 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@BusinessUnit)))
AND (@PTFriendlyName IS NULL OR sc.PTFriendlyName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@PTFriendlyName)))
AND (@AssignedLocID IS NULL OR sc.PersonAssignedBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedLocID)))
AND (@AssignedSite IS NULL OR sc.Site IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedSite)))
AND (@SwipeLocID is NULL OR sc.SwipeBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SwipeLocID)))
)
SELECT COUNT(DISTINCT(ObjectID)) as count, year, month, GroupingClause AS GroupedBy
FROM MyCTE
GROUP BY year, month, GroupingClause
END
if @Interval = 'Yearly'
BEGIN
WITH MyCTE AS
(
SELECT ObjectID, DATEPART(year, SwipetimeEST) AS year,
CASE
WHEN @GroupBy = 'None' THEN NULL
WHEN @GroupBy = 'Personnel Type' THEN PTFriendlyName
WHEN @GroupBy = 'Senior Executive Team' THEN GroupDesc3
WHEN @GroupBy = 'Assigned Building' THEN l.BuildingLocName
WHEN @GroupBy = 'Swipes by Building' THEN loc.BuildingLocName
END AS GroupingClause
FROM [Database_Server].[occupancy].[SwipesComplete] sc
LEFT JOIN [Database_Server].[occupancy].[DoorsComplete] dc
ON (sc.DoorID = dc.DoorID)
LEFT JOIN [Join_Database].[site].[LocationInformation] l
ON (sc.PersonAssignedBuildingLocID = l.LocID)
LEFT JOIN [Join_Database].[site].[LocationInformation] loc
ON (sc.SwipeBuildingLocID = loc.LocID)
WHERE sc.SiteID = @SiteID
AND SwipetimeUTC BETWEEN @StartDate AND @EndDate
AND (sc.Name IS NULL OR sc.Name NOT LIKE '%Visitor%')
AND dc.AssetLocID IS NOT NULL
AND dc.AssetLocID != 5
AND sc.SwipetimeEST IS NOT NULL
AND (@NeighborhoodName IS NULL OR sc.NeighborhoodName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@NeighborhoodName)))
AND (@SET IS NULL OR sc.GroupDesc3 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SET)))
AND (@Function IS NULL OR sc.GroupDesc4 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@Function)))
AND (@BusinessUnit IS NULL OR sc.GroupDesc5 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@BusinessUnit)))
AND (@PTFriendlyName IS NULL OR sc.PTFriendlyName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@PTFriendlyName)))
AND (@AssignedLocID IS NULL OR sc.PersonAssignedBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedLocID)))
AND (@AssignedSite IS NULL OR sc.Site IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedSite)))
AND (@SwipeLocID is NULL OR sc.SwipeBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SwipeLocID)))
)
SELECT COUNT(DISTINCT(ObjectID)) as count, year, GroupingClause AS GroupedBy
FROM MyCTE
GROUP BY year, GroupingClause
END
END
GO
我们遇到了一些相当严重的性能问题,存储过程需要一分钟以上的时间才能以每周的间隔返回 12 个月的数据,或者需要 15-20 秒的加载时间才能返回 1 个月的每日数据。
以下是我尝试过的一些方法:
通过这些更改,我看到了改进,但这还不够,我不确定下一步该怎么做。对于架构,我添加了年、月、日、周、小时 INT 列,并使用相应的日期部分更新了所有现有数据。正如我提到的,我将数据拆分为两个表(SwipesComplete
(滚动 12 个月)和SwipesCompleteArchive
(> 12 个月前))
以下是更新后的存储过程:
CREATE OR ALTER PROCEDURE [occupancy].[FIDBGetUniqueOccupancyByRange] (
@StartDate datetime,
@EndDate datetime,
@Interval nvarchar(20),
@PTFriendlyName VARCHAR(4000),
@SET VARCHAR(4000),
@Function VARCHAR(4000),
@BusinessUnit VARCHAR(4000),
@AssignedSite VARCHAR(4000), -- assigned site
@AssignedLocID VARCHAR(4000), -- assigned building
@NeighborhoodName VARCHAR(4000),
@SwipeLocID VARCHAR(4000),
@SiteID INT,
@GroupBy VARCHAR(255)
)
AS
BEGIN
CREATE TABLE #TempTable (
ObjectID INT,
year INT,
month INT,
week INT,
day INT,
hour INT,
GroupingClause VARCHAR(4000)
);
INSERT INTO #TempTable (ObjectID, year, month, week, day, hour, GroupingClause)
SELECT
sc.ObjectID,
sc.year,
sc.month,
sc.week,
sc.day,
sc.hour,
CASE
WHEN @GroupBy = 'None' THEN NULL
WHEN @GroupBy = 'Personnel Type' THEN sc.PTFriendlyName
WHEN @GroupBy = 'Senior Executive Team' THEN sc.GroupDesc3
WHEN @GroupBy = 'Assigned Building' THEN l.BuildingLocName
WHEN @GroupBy = 'Swipes by Building' THEN loc.BuildingLocName
END AS GroupingClause
FROM
[Database_Server].[occupancy].[SwipesComplete] sc
LEFT JOIN [Database_Server].[occupancy].[DoorsComplete] dc ON (sc.DoorID = dc.DoorID)
LEFT JOIN [Join_Database].[site].[LocationInformation] l ON (sc.PersonAssignedBuildingLocID = l.LocID)
LEFT JOIN [Join_Database].[site].[LocationInformation] loc ON (sc.SwipeBuildingLocID = loc.LocID)
WHERE
sc.SiteID = @SiteID
AND sc.SwipetimeUTC BETWEEN @StartDate AND @EndDate
AND (sc.Name IS NULL OR sc.Name NOT LIKE '%Visitor%')
AND dc.AssetLocID IS NOT NULL AND dc.AssetLocID != 5
AND sc.SwipetimeEST IS NOT NULL
AND (@NeighborhoodName IS NULL OR sc.NeighborhoodName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@NeighborhoodName)))
AND (@SET IS NULL OR sc.GroupDesc3 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SET)))
AND (@Function IS NULL OR sc.GroupDesc4 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@Function)))
AND (@BusinessUnit IS NULL OR sc.GroupDesc5 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@BusinessUnit)))
AND (@PTFriendlyName IS NULL OR sc.PTFriendlyName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@PTFriendlyName)))
AND (@AssignedLocID IS NULL OR sc.PersonAssignedBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedLocID)))
AND (@AssignedSite IS NULL OR sc.Site IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedSite)))
AND (@SwipeLocID IS NULL OR sc.SwipeBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SwipeLocID)));
IF @StartDate <= DATEADD(year, -1, GETDATE())
BEGIN
INSERT INTO #TempTable (ObjectID, year, month, week, day, hour, GroupingClause)
SELECT
sc.ObjectID,
sc.year,
sc.month,
sc.week,
sc.day,
sc.hour,
CASE
WHEN @GroupBy = 'None' THEN NULL
WHEN @GroupBy = 'Personnel Type' THEN sc.PTFriendlyName
WHEN @GroupBy = 'Senior Executive Team' THEN sc.GroupDesc3
WHEN @GroupBy = 'Assigned Building' THEN l.BuildingLocName
WHEN @GroupBy = 'Swipes by Building' THEN loc.BuildingLocName
END AS GroupingClause
FROM
[Database_Server].[occupancy].[SwipesCompleteArchive] sc
LEFT JOIN [Database_Server].[occupancy].[DoorsComplete] dc ON (sc.DoorID = dc.DoorID)
LEFT JOIN [Join_Database].[site].[LocationInformation] l ON (sc.PersonAssignedBuildingLocID = l.LocID)
LEFT JOIN [Join_Database].[site].[LocationInformation] loc ON (sc.SwipeBuildingLocID = loc.LocID)
WHERE
sc.SiteID = @SiteID
AND sc.SwipetimeUTC BETWEEN @StartDate AND @EndDate
AND (sc.Name IS NULL OR sc.Name NOT LIKE '%Visitor%')
AND dc.AssetLocID IS NOT NULL AND dc.AssetLocID != 5
AND sc.SwipetimeEST IS NOT NULL
AND (@NeighborhoodName IS NULL OR sc.NeighborhoodName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@NeighborhoodName)))
AND (@SET IS NULL OR sc.GroupDesc3 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SET)))
AND (@Function IS NULL OR sc.GroupDesc4 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@Function)))
AND (@BusinessUnit IS NULL OR sc.GroupDesc5 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@BusinessUnit)))
AND (@PTFriendlyName IS NULL OR sc.PTFriendlyName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@PTFriendlyName)))
AND (@AssignedLocID IS NULL OR sc.PersonAssignedBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedLocID)))
AND (@AssignedSite IS NULL OR sc.Site IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedSite)))
AND (@SwipeLocID IS NULL OR sc.SwipeBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SwipeLocID)));
END
IF @Interval = 'Hourly'
BEGIN
SELECT
COUNT(DISTINCT(ObjectID)) AS count,
year,
month,
week,
day,
hour,
GroupingClause AS GroupedBy
FROM #TempTable
GROUP BY year, month, week, day, hour, GroupingClause;
END
ELSE IF @Interval = 'Daily'
BEGIN
SELECT
COUNT(DISTINCT(ObjectID)) AS count,
year,
month,
week,
day,
GroupingClause AS GroupedBy
FROM #TempTable
GROUP BY year, month, week, day, GroupingClause;
END
ELSE IF @Interval = 'Weekly'
BEGIN
SELECT
COUNT(DISTINCT(ObjectID)) AS count,
year,
week,
GroupingClause AS GroupedBy
FROM #TempTable
GROUP BY year, week, GroupingClause;
END
ELSE IF @Interval = 'Monthly'
BEGIN
SELECT
COUNT(DISTINCT(ObjectID)) AS count,
year,
month,
GroupingClause AS GroupedBy
FROM #TempTable
GROUP BY year, month, GroupingClause;
END
ELSE IF @Interval = 'Yearly'
BEGIN
SELECT
COUNT(DISTINCT(ObjectID)) AS count,
year,
GroupingClause AS GroupedBy
FROM #TempTable
GROUP BY year, GroupingClause;
END
DROP TABLE #TempTable;
END
Where do I go from here? In splitting the data into multiple tables actually going to help? Is there a way to query what I want without needing to use a temp table or a CTE? If a user wants to query 12 months of data, it's going to insert ~6 million rows into the temp table if they have no additional filters, which is a ton. I'm at a loss for how to make this performant enough.
I'm willing to change up the schema if I have to, but I'd love to find a way to solve this with some index changes or query changes. At this point anything will help.
大家好,我有这个查询,它返回过去几分钟内发生的具有不同事件 ID 的日志:
SELECT LogEventID, LogMessage, LogTime
FROM Database.dbo.ApplLog
WHERE (LogEventID LIKE 14
OR LogEventID LIKE 20
OR LogEventID LIKE 27
OR LogEventID LIKE 40
OR LogEventID LIKE 41
OR LogEventID LIKE 42)
AND LogTime < CURRENT_TIMESTAMP
AND LogTime > DateADD(mi, -5, CURRENT_TIMESTAMP);
例如结果如下:
日志事件ID | 日志消息 |
---|---|
四十二 | 传输新事件 XYZ 时出错 |
四十二 | 传输新事件 XYZ 时出错 |
20 | 传输更新的事件 XYZ 时出错 |
还有 4 个 EventID。大多数情况下,其他 EventID 不会生成日志。在我们的监控解决方案中,我设置了一个定期执行此查询的传感器。长话短说,我无法正确设置传感器,因为并非所有 EventID 都在过去几分钟内同时生成日志。
我的问题是:如何输出包含所有 LogEventID 但 LogMessages 为空的虚假行,这样我就可以在每个查询结果中都获得过去几分钟内未生成日志的所有 LogEventID?就像这样:
日志事件ID | 日志消息 |
---|---|
四十二 | 传输新事件 XYZ 时出错 |
四十二 | 传输新事件 XYZ 时出错 |
20 | 传输更新的事件 XYZ 时出错 |
14 | |
二十七 | |
40 | |
41 |
我看过这篇文章,但我不明白解决方案,而且它比(感觉)需要的要复杂得多。提前感谢你的帮助。
我正在尝试使用逻辑复制(发布/订阅)将大型 (4TB) Postgresql 数据库从 AWS RDS 移动到 Azure Database for Postgresql Flexible Server。我正在按表逐步执行复制,并逐个同步较大的表以减少主服务器上的 WAL 开销。在同步较大的 (>10GB) 表时,我遇到了挂起和长时间等待。
从同步和更新订阅开始,当我向发布中添加新表并更新订阅时,我看到网络流量和磁盘使用率立即飙升(订阅者的写入吞吐量为 100MB/s)。大约半小时后,流量几乎停止(1MB/s 或更低)。在 1 到 12 小时的某个可变时间段后,流量突然再次增加。此过程重复,直到同步最终完成并且表是最新的。
我检查了双方的数据库日志,没有看到任何错误消息,甚至没有看到与复制过程相关的信息。是什么原因导致这些长时间挂起?我可以做进一步的诊断来找出原因吗?
我进行了服务器迁移,当我将数据库从旧服务器移植到新服务器时,出现了一些数据损坏。例如,IPv6 二进制数据看起来完全相同,但旧服务器的条目通常返回为空。在新服务器上创建的记录很好。数据库结构没有任何变化。
如何从旧服务器获取原始导出数据并从新服务器的数据库中导入新记录?
我所遇到的数据建模问题的简化示例:
我有一个域模型,其中包含一个image
表、一个camera
表、一个machine
表、图像和相机之间的外键约束(taken_by_camera
)以及相机和机器之间的FK(installed_into_machine
)。
image taken_by_camera camera
camera installed_into_machine machine
所有这些都基于我们从客户那里获得的数据。
然而,在我们的应用中,我们几乎总是以图像集的形式使用数据。因此,我们还有一个set
表。每幅图像都属于一个集合,为此我们belongs_to_set
在表上有一个 FK image
。
image belongs_to_set set
我的问题是我想限制仅包含特定机器的图像集,例如
set restricted_to_machine machine
只要这些 FK 到位,我就没有任何约束可以确保通过相机连接到特定机器的所有图像最终也通过该装置连接到同一台机器。
image_1 taken_by_camera camera_1
camera_1 installed_into_machine **machine_1**
image_1 belongs_to_set set_1
set_1 restricted_to_machine **machine_2**
我怎样才能对这样的事物进行建模(从逻辑上讲,但也在 postgres 中)。
PS:只要这些相机安装在同一台机器上,一组中应该可以有不同相机拍摄的图像。
我喜欢使用Export-DbaScript
导出到名为 的文件夹C:\TargetFolder\Database\Schema
。通常,我还没有Database\Schema
文件夹,这将导致Export-DbaScript
无法写入该文件夹。Export-DbaScript
如果文件夹尚不存在,有什么方法可以创建该文件夹?我期待类似-Force
参数的东西,但我在文档中没有找到它。
我正在做
Export-DbaScript `
-InputObject $_ `
-NoPrefix `
-NoClobber `
-FileName "$($MyLocation)\$($_.Database)\$($_.Name).sql
当路径不存在时,我会得到System.IO.DirectonaryNotFoundException
s。我使用的是 2.1.18 版本