Click here to Skip to main content
14,733,448 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Dear Sir,

We use MS-SQLServer2008 R2. We work a database contains more tables. Some of tables contains 175323+ rows.
When the user fetchs this particular table it loads and take more time. Finally, one message box display an error saying QUERY TIMEOUT EXPIRED. For that, we have to disconnect the sql server and start again.

Please find out the solution for this error and advice us at the earliest.

The front end designed VB6.0

Thanks in advance

With regards
Updated 22-Mar-11 20:10pm

Do you need to fetch that much records in a single query?

The optimation steps would be

1) Check if the table contains redundancy, if so normalize it

2) Use Index for speed search.

3) Use pagination to display. So that no need to fetch that much record in a single query

4) If your business logic depends on that much records (some calculation of something), use a stored procedure to do that or use appropriate aggregate queries.

5) Repository pattern with identity mapper is a good way to reuse the previously obtained records.

Hope this helps
Sandeep Mewara 23-Mar-11 3:55am
Good answer. My 5!
Albin Abel 23-Mar-11 7:05am
Thanks Sandeep Mewara
either you can make your query go faster (return less data, improve the database design, find and apply indices that make your queries execute faster), or then increase the timeout you allow the query to run before a timeout is thrown.

Those are your two options - take your pick.

UPDATE: a little googling reveals:

Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
cmd.CommandTimeout = 120 ' number of seconds
Comments 1-Nov-12 14:29pm
i am using the same with my relay server to communicate and now i have more then 2crore records and i need to export this data into segments or whole to some csv files but i am unable to export even 100000 records and getting time out.
as a front end i am using c# .net 4.0 with visual studio 2010.
Member 9704955 21-Dec-12 20:32pm
Thanks dude, your method of setting commandtimeout works! and it made my day!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900