Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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
Edited 22-Mar-11 19:10pm
v2
Rate this: bad
good
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
  Permalink  
Comments
Sandeep Mewara at 23-Mar-11 3:55am
   
Good answer. My 5!
AlbinAbel at 23-Mar-11 7:05am
   
Thanks Sandeep Mewara
Rate this: bad
good
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
  Permalink  
Comments
smtpserver.in at 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 at 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
0 CPallini 375
1 Sergey Alexandrovich Kryukov 370
2 BillWoodruff 314
3 George Jonsson 274
4 OriginalGriff 257
0 OriginalGriff 5,030
1 CPallini 4,115
2 Sergey Alexandrovich Kryukov 3,554
3 George Jonsson 2,826
4 Gihan Liyanage 2,386


Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 23 Mar 2011
Copyright © CodeProject, 1999-2014
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