How to avoid Query Timeout Expired

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

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
JP. SIVARAJ

Posted 22-Mar-11 18:56pm

JP.SIAVRAJ

Updated 22-Mar-11 19:10pm

v2

Add a Solution

2 solutions

Solution 2

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

Posted 22-Mar-11 19:29pm

Albin Abel

Comments

Sandeep Mewara 23-Mar-11 3:55am

Good answer. My 5!

Albin Abel 23-Mar-11 7:05am

Thanks Sandeep Mewara

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Mahendra.p25 · Accepted Answer · 2011-03-22T19:17:00

Solution 1

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

Posted 22-Mar-11 19:17pm

Mahendra.p25

Comments

smtpserver.in 1-Nov-12 14:29pm

i am using the same with my smtpserver.in 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!