Timeout Expired, Optimize Query
I have this query to get all detail of Bills between two dates: SELECT DT.* FROM DetailTable DT, BillTable BT, PackageTable PT WHERE PT.Id = BT.IdPackage AND DT.IdBill= BT.Id
Solution 1:
Try this query -
SELECTDT.*
FROMdbo.DetailTableDTWHEREEXISTS(
SELECT 1
FROM dbo.BillTable BT
JOIN (
SELECT PT.Id
FROM dbo.PackageTable PT
WHERE PT.CodeCompany = @codeCompanyAND PT.Date BETWEEN @beginAND@end
) PT ON PT.Id = BT.IdPackage
WHERE DT.IdBill = BT.Id
)
Another way -
CREATEPROCEDURE dbo.usp_Test1
@codeCompanyVARCHAR(50)
, @begin DATETIME
, @end DATETIME
ASBEGIN
IF OBJECT_ID (N'tempdb.dbo.#temp') ISNOTNULLDROPTABLE #temp
CREATETABLE #temp (ID BIGINTPRIMARY KEY)
INSERTINTO #temp (ID)
SELECT BT.Id
FROM dbo.BillTable BT
JOIN dbo.PackageTable PT ON PT.Id = BT.IdPackage
WHERE PT.CodeCompany =@codeCompanyAND PT.[Date] BETWEEN@beginAND@endSELECT DT.*FROM dbo.DetailTable DT
WHERE DT.IdBill IN (SELECT Id FROM #temp)
END
Post a Comment for "Timeout Expired, Optimize Query"