Connection Time Out - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Connection Time Out

Author

Topic

prince500
Starting Member

21 Posts

Posted - 2007-05-22 : 11:56:52

Hi,

Can someone help me on this

When I run simple select query "Select * from Aadata" it works but when I filter with date it doesn't work please see below error:

Error Source : .Net SqlClient data provider

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

I fixed connection properties:

Connection timeout = 30000

Execution timeout = 30000

Thanks in advance

AA

AA

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-22 : 12:00:43

Could be a variety of things. (1) How many rows does the SELECT statement return? (2) How long does the query take to return results in Query Analyzer? (3) You can change the timeout property in the connection string. (4) Do you have an index on the date column in the table?

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

prince500
Starting Member

21 Posts

Posted - 2007-05-22 : 12:06:10

1) Rows are 20,868,007
2) I don't know exect time but it should take 15 - 20 mins
3) I have changed the connection timeout as I mentioned (Or is this something else?)
4) No I don't have index

AA

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-22 : 12:10:54

Does your front end really need all the 20 mil rows? Perhaps you can return the top 10-20? The normal timeout is 30 secs I believe. Even if you change the timeout to 20 minutes can your front end handle all the data? would your users wait for 20 minutes to see 20 mil rows? Adding an index on that column can help reduce the time. So (1)you need an index on the column (2) a better strategy.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-22 : 12:11:55

Tried same query in SSMS? It will be slow without index.

prince500
Starting Member

21 Posts

Posted - 2007-05-22 : 12:22:35

this is history table and I need to make decision making report from this table, I would take only few colums between two dates which will return me around couple of hundred (200 - 300 rows).

I am not using any front end all I need to transfer (200- 300) rows in access.

Is there any way to make it possible?

Thanks

AA

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-22 : 12:26:48

so are you applying the filter at the application level, while returning all the data to the application?

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-22 : 12:27:40

Possible to add index on the table?

prince500
Starting Member

21 Posts

Posted - 2007-05-22 : 12:29:23

After opening the table I am using below query in SQL Pane:

SELECT entry_date
FROM HistoryLog
WHERE (entry_date = CONVERT(DATETIME, '2007-05-04 00:00:00', 102))

AA

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-22 : 12:47:09

Try adding an index as "rmiao" suggested.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

prince500
Starting Member

21 Posts

Posted - 2007-05-22 : 13:08:08

No! it's not working not creating index and replying with same error.

Is this not strange:) SQL Server should be able to perform simple queruies without time limit

what else should I do?
Thanks

AA

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-22 : 13:09:51

what is the scritp you used to create the index and what is the error you got?

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

prince500
Starting Member

21 Posts

Posted - 2007-05-22 : 13:19:19

CREATE INDEX AaDate ON historylog(entry_date)

AA

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-22 : 13:21:20

and the error msg?

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

prince500
Starting Member

21 Posts

Posted - 2007-05-22 : 13:27:19

Error Source : .Net SqlClient data provider

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

The statemnt has been termintated

AA

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-22 : 13:36:59

I meant, did you get any error when you created the index? Also, do an

UPDATE STATISTICS historylog WITH FULLSCAN

after you run the Index script. then run the query and see if it performs any better..

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

prince500
Starting Member

21 Posts

Posted - 2007-05-22 : 13:44:29

I am sorry I forget to tell you one thing I got error before running Index query "The CREATE INDEX SQL construct or statement is not supported."

AA

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-22 : 13:48:49

Do you use sql server?

prince500
Starting Member

21 Posts

Posted - 2007-05-22 : 13:50:47

Yes 2005 Standard

AA