报告数据库中丢失的数据 [英] Report missing data in database

问题描述

SQL小提琴

SQL Fiddle

我在Excel中有动态的一长串(> 1000)组件及其各自的资产类型.示例:

I have a dynamic long (>1000) list of components and their respective asset types in Excel. Example:

Component                   Asset Type
0738.D100.L00.55            9211.D108.D07.01_02.02
0738.D100.L00.71            0738.D100.L00.55_04.04
0738.D100.M02.55            0738.D100.M00.60_03.03
0990.OH05.A00.09            0738.D100.M00.60_03.03

其中一些组合可能在SQL数据库中不存在.我想要一个查询来输出这些组合.

Some of these combinations may not exist in the SQL database. I want a query that outputs these combinations.

组件及其各自的资产类型可按以下方式请求

Components and their respective asset type can be requested as follows

Select C.Code, AT.Code From astComponents C 
Join astAssetTypes AT ON AT.Id = C.AssetTypeId

我创建了一种使用临时表进行比较的方法，但是经过1250种组合测试，该方法运行的速度很慢，大约为6.5秒，其中创建表的时间大约为2秒.

I created a method which uses a temp table to compare with, but tested with 1250 combinations, this method runs slow at ~6.5 seconds, of which creating the table is ~2 seconds.

Create Table 
#Temp(Component nvarchar(50) Collate Latin1_General_BIN, AssetType nvarchar(50) Collate Latin1_General_BIN) 
Insert Into #Temp (Component, AssetType) Values ('0738.D100.L00.55','9211.D108.D07.01_02.02');
Insert Into #Temp (Component, AssetType) Values ('0738.D100.L00.71','0738.D100.L00.55_04.04');
Insert Into #Temp (Component, AssetType) Values ('0738.D100.M02.55','0738.D100.M00.60_03.03');
Insert Into #Temp (Component, AssetType) Values ('0990.OH05.A00.09','0738.D100.M00.60_03.03');
--more Inserts left out

Select Distinct 
    Compare.Component,  
    Compare.AssetType 
From astComponents C
    Join astAssetTypes AT ON AT.Id = C.AssetTypeId
    Right Join #Temp Compare ON Compare.AssetType = AT.Code And Compare.Component = C.Code
Where C.Code is null

在小提琴中，如果我们使用上面的示例作为示例，则预期结果如下要检查的数据.这些组件-资产类型组合在fiddle数据库中不存在.

In the Fiddle, the expected outcome is below if we use the top example as our data to check. These component-asset type combinations do not exist in the fiddle database.

Component                   Asset Type
0738.D100.M02.55            0738.D100.M00.60_03.03
0990.OH05.A00.09            0738.D100.M00.60_03.03

我想要一个更快的方法.帮助将不胜感激

I want a faster method. Help would be greatly appreciated

推荐答案

此查询应执行您想要的操作:

This query should do what you want:

select c.*
from #temp c join
     astAssetTypes t 
     on t.AssetType = c.AssetType
where not exists (select 1
                  from components co
                  where co.component = c.code and
                        co.AssetTypeId = t.id
                 )  

对于此查询，您希望在以下位置建立索引:

For this query, you want indexes on:

• astAssetTypes(AssetType, id)
• components(component, AssetTypeId)

• astAssetTypes(AssetType, id)
• components(component, AssetTypeId)

这篇关于报告数据库中丢失的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！