43088

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.

<strong>Update</strong>

This is the execution plan :

<img src="https://i.stack.imgur.com/OCq0Y.png" alt="enter image description here">

<strong>Update2</strong>

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

<ol> <li>

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

</li> <li>

the detailTable contain 102 columns.

</li> </ol>

Answer1:

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

Recommend

  • Local SQL Server installed by other user account, how to access from new user account
  • How to choose from two connection strings?
  • System.Data.SqlClient Namespace for MySQL?
  • spawn random images in canvas in javascript
  • How to handle exception using Timer (Thread) class
  • Validating embedded document in Mongoid based on embedded attribute
  • OAuth 2.0 - When should an access token be renewed with refresh token?
  • Validate Expired Password in active directory
  • Hive command line Select query time taken incorrect if its not map reduce job in the background
  • Get predicate execution time in seconds
  • Eclipse code fomatting
  • variable scope in AJAX calls
  • ASP.NET 2.0 Session Timeout
  • Thread Synchronization with IntentService
  • How to wait till webViewDidFinishLoad gets completed
  • Spring Integration debounce/deduplicate
  • How can I merge my files when the folder structure has changed using Borland StarTeam?
  • Query timeout expired in django-mssql when executing custom SQL directly
  • Two columns in subquery in where clause
  • UML diagram generator in Visual Studio 2010
  • “A GKScore must specify a leaderboard.”
  • How to make HttpWebRequest async
  • Graceful pod termination
  • Replace last two characters in column
  • Add reference to ASP.NET 5 Class Library from Framework 4.5 Class Library Project
  • Consuming a WCF service in a Java Client using wsHttpBinding
  • Filter strings with regex before casting to numeric
  • Cannot upload to OneDrive using the new SDK
  • Nant, Vault & Windows Integrated Authentication
  • Ajax jQuery multiple calls at the same time - long wait for answer and not able to cancel
  • Hazelcast - OperationTimeoutException
  • Timeout for blocking function call, i.e., how to stop waiting for user input after X seconds?
  • File upload with ng-file-upload throwing error
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • Proper folder structure for lots of source files
  • embed rChart in Markdown
  • How does Linux kernel interrupt the application?
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • How can I use `wmic` in a Windows PE script?
  • Unable to use reactive element in my shiny app