Skip to content Skip to sidebar Skip to footer

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"