Click here to Skip to main content
13,095,218 members (84,335 online)
Rate this:
Please Sign up or sign in to vote.
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
Posted 22-Mar-11 18:56pm
Updated 22-Mar-11 19:10pm
Rate this: bad
Please Sign up or sign in to vote.

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
Sandeep Mewara 23-Mar-11 3:55am
Good answer. My 5!
AlbinAbel 23-Mar-11 7:05am
Thanks Sandeep Mewara
Rate this: bad
Please Sign up or sign in to vote.

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
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web03 | 2.8.170813.1 | Last Updated 23 Mar 2011
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100