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 
  AND PT.CodeCompany = @codeCompany
  AND PT.Date BETWEEN @begin and @end

For every package there are many bills, and I want to get the details of bills of a company, the result in database it just 20,000 but I have :

System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

the c# code :

using (SqlConnection sqlConn = new SqlConnection(SqlServerMasterConnection))
{
  if (sqlConn.State != ConnectionState.Open) sqlConn.Open();
  using (SqlCommand cmd = new SqlCommand("select DT.* from DetailTable DT, BillTable BT, PackageTable PT where  PT.Id= BT.IdPackage and DT.IdBill= BT.Id and  PT.CodeCompany = @codeCompany and PT.Date between @begin and @end",
                            sqlConn))
  {
    cmd.Parameters.Add(new SqlCeParameter(@begin , beginDate));
    cmd.Parameters.Add(new SqlCeParameter("@end", endDate));
    cmd.Parameters.Add(new SqlCeParameter("@codeCompany", codeCompany));
    using (DbDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
           //work todo
        }
    }
  }
}

I even tried this in SQL Server Management it take 25 seconds! any hint please to fix that.

Update

This is the execution plan :

Update2

There's two problems that I thing for this problem (I want your ideas).

1. PT.Date is a char(8) (it was used by the developer how begin this project) (yyyyMMdd)

2. the detailTable contain 102 columns.

回答1:

Try this query -

SELECT DT.*
FROM dbo.DetailTable DT
WHERE EXISTS(
    SELECT 1
    FROM dbo.BillTable BT
    JOIN (
        SELECT PT.Id
        FROM dbo.PackageTable PT
        WHERE PT.CodeCompany = @codeCompany
            AND PT.Date BETWEEN @begin AND @end 
    ) PT ON PT.Id = BT.IdPackage
    WHERE DT.IdBill = BT.Id 
)

Another way -

CREATE PROCEDURE dbo.usp_Test1
      @codeCompany VARCHAR(50)
    , @begin DATETIME
    , @end DATETIME
AS
BEGIN

    IF OBJECT_ID (N'tempdb.dbo.#temp') IS NOT NULL
    DROP TABLE #temp

    CREATE TABLE #temp (ID BIGINT PRIMARY KEY)

    INSERT INTO #temp (ID)
    SELECT BT.Id
    FROM dbo.BillTable BT
    JOIN dbo.PackageTable PT ON PT.Id = BT.IdPackage
    WHERE PT.CodeCompany = @codeCompany
    AND PT.[Date] BETWEEN @begin AND @end 

    SELECT DT.*
    FROM dbo.DetailTable DT
    WHERE DT.IdBill IN (SELECT Id FROM #temp)

END