0

I am trying to execute this query :

SELECT * from vwLstDtaLines d1,vwLStDtafiles d2 where d1.DtaLinePaymentDate='1/1/2000'or d1.DtaLinePaymentDate='1/1/2012'  or d1.DtaLineUserCre='abc' or d1.DtaLineUserMatch='abc' or d2.DtaFileName='Sent'



Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Getting this error again and again

vini
  • 4,657
  • 24
  • 82
  • 170
  • Are you sure you want to be doing a cross-join? – pete Oct 02 '12 at 17:24
  • i want to fetch and compare data from two views . Have to do that :/ – vini Oct 02 '12 at 17:36
  • Do the 2 different views have similar columns. Might you be able to do a `UNION` of those two (each with their own `WHERE` clause) rather than a join? What you are doing, from a SQL standpoint, just seems fundamentally wrong - which is causing your timeout. – MattW Oct 02 '12 at 18:32

2 Answers2

2

Well, just looking at your SQL, it looks to me like you're creating a MUCH larger result set than you intend to.

SELECT * 
from vwLstDtaLines d1,vwLStDtafiles d2 
where d1.DtaLinePaymentDate='1/1/2000' or 
d1.DtaLinePaymentDate='1/1/2012'  or 
d1.DtaLineUserCre='abc' or 
d1.DtaLineUserMatch='abc' or 
d2.DtaFileName='Sent'

This SQL statement has no explicit JOIN between the two views. Consequently, what you're getting is a result set that is probably something like d1r * d2r in size, where d1r is the number of rows in d1 and d2r is the number of rows in d2.

I'd start looking there. Run the following query in SQL server to find out:

SELECT COUNT(*)
from vwLstDtaLines d1,vwLStDtafiles d2 
where d1.DtaLinePaymentDate='1/1/2000' or 
d1.DtaLinePaymentDate='1/1/2012'  or 
d1.DtaLineUserCre='abc' or 
d1.DtaLineUserMatch='abc' or 
d2.DtaFileName='Sent'

If the number of rows is astronomical, you have a join issue.

Mike Hofer
  • 16,477
  • 11
  • 74
  • 110
1

You can increase your Connect Timeout in your string connection

Nota : default value is 15 seconds

Sample of adjusting

<add name="ConnectionString" connectionString="Data Source=;Initial Catalog=;Persist Security Info=True;User ID=;Password=;Connect Timeout=200" providerName="System.Data.SqlClient"/>
</connectionStrings>

Link : http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

Aghilas Yakoub
  • 28,516
  • 5
  • 46
  • 51