SQL查询需要很长时间才能执行

USE Pooja
GO
----Create TestTable
CREATE TABLE TestTable(RtJobCode VARCHAR(20), RtProfCode smallint,RtTestCode smallint,ProfCode smallint,TestCode smallint)
----INSERT INTO TestTable using SELECT
INSERT INTO TestTable (RtJobCode, RtProfCode,RtTestCode,ProfCode,TestCode)
SELECT RtJobCode,RtTestCode,TestCode,RtProfCode,ProfCode
FROM dbo.ResultTest,dbo.Test,dbo.Profiles
WHERE RtTestCode=ANY(Select TestCode from dbo.Test)

----Verify that Data in TestTable
SELECT *
FROM TestTable

GO

The above code tries to take out entries from a table called resutltest and profiles and test,

上面的代码试图从名为resutltest和profiles和test的表中取出条目，

The problem was during creation of a cube i was encountering some data which was not consistent in all the tables, So i tried a join on the tables but as the tables contained a huge number of columns it was'nt feasible so tried making this code which just keeps on executing without stopping and not displaying any data

问题是在创建一个多维数据集期间我遇到了一些在所有表中都不一致的数据，所以我尝试了对表的连接，但是由于表包含大量的列，所以它是不可行的，所以尝试制作这段代码只是不停地执行而不停止显示任何数据

Resulttest's Rttestcode is foreign key from testcode

Resulttest的Rttestcode是testcode的外键

2 个解决方案

#1

Your query is very slow because it is making a cartesian product between ResultTest, Test and Profiles. you need to provide "join" conditions to link the tables together.

您的查询非常慢，因为它在ResultTest，Test和Profiles之间制作了一个笛卡尔积。您需要提供“加入”条件以将表链接在一起。

SELECT RtJobCode
     , RtTestCode
     , TestCode
     , RtProfCode 
     , ProfCode
FROM dbo.ResultTest r 
JOIN dbo.Test t
  ON r.RtTestCode = t.TestCode
JOIN dbo.Profiles p
  ON r.RtProfCode = p.ProfCode

I speculate that this is the query you are looking for. Note the conditions that link ResultTest and Test together and the condition that links ResultTest and Profiles together.

我推测这是您正在寻找的查询。请注意将ResultTest和Test链接在一起的条件以及将ResultTest和Profiles链接在一起的条件。

#2

USE Pooja
GO
----Create TestTable
CREATE TABLE TestTable(RtJobCode VARCHAR(20), RtProfCode smallint,RtTestCode smallint,RtCenCode smallint,LabNo int,ProfCode smallint,ProfRate money,ProfName varchar(100),TestCode smallint,TestRate money,TestName varchar(100),TestCategory varchar(50),Cost money)
----INSERT INTO TestTable using SELECT
INSERT INTO TestTable (RtJobCode, RtProfCode,RtTestCode,RtCenCode,LabNo,ProfCode,ProfRate,ProfName,TestCode,TestRate,TestName,TestCategory,Cost)
SELECT RtJobCode
     , RtProfCode
     , RtTestCode
     , RtCenCode
     , LabNo
     , ProfCode
     , ProfRate
     , ProfName
     , TestCode
     , TestRate
     , TestName
     , TestCategory
     , Cost
FROM dbo.ResultTest

JOIN dbo.Test 
  ON ResultTest.RtTestCode = Test.TestCode
JOIN dbo.Profiles 
  ON ResultTest.RtProfCode = Profiles.ProfCode

#1