| Author |
 Topic  |
|
|
pring
Starting Member
USA
9 Posts |
 Posted - 29 July 2009 : 14:14:29
|
I recently migrated our (very bloated - 230MB) access database to MS SQL 2005 Express.
The migration itself seemed to be very clean and the snitz forum is running faster than ever, however, if a member (or guest user) tries to use the search function (search.asp), the following appears:
quote:
Microsoft OLE DB Provider for ODBC Drivers error '80040e31'
[Microsoft][ODBC SQL Server Driver]Timeout expired
/forum/search.asp, line 349
My forum is located: http://www.classicmako.com/forum
if you need to see it for yourself.
I've searched and searched, but most of what I find is either 8+ years old (doesn't pertain to modern OS & SQL) or tells me basically what I've done in migrating.
Does anyone here have any insight, or know of a thread I can read that may help?
Thank you,
Patrick Ring. |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 29 July 2009 : 14:22:01
|
Just checking, but when you migrated did you let the forum setup.asp create the tables and then migrate the data? Or did you let the tool do all the migration?
I ask because I have yet to see a tool properly migrate all the index information, which tends to show as a drop in performance and a general inability to search.
OTOH, the other common problem is a search terms are overly vague/common terms and the individual is trying to search the entire board for them. |
 |
|
|
pring
Starting Member
USA
9 Posts |
Posted - 29 July 2009 : 23:14:14
|
quote:
Originally posted by AnonJr
Just checking, but when you migrated did you let the forum setup.asp create the tables and then migrate the data? Or did you let the tool do all the migration?
I ask because I have yet to see a tool properly migrate all the index information, which tends to show as a drop in performance and a general inability to search.
OTOH, the other common problem is a search terms are overly vague/common terms and the individual is trying to search the entire board for them.
I'm pretty sure I did use the setup.asp on the final attempt. I know in the first attempts, I definitely did, and had the search problem then too. Before going live with the database, I tried both MS SQL and MySQL using a couple of different migration methods on each.
If there is the chance that the indexes didn't get properly created. Is there a way to modify this? Or even optimize the database tables' parameters specifically for the Snitz forum?
When this was running as an Access database a general search term (across all forums)was slow, but it did complete. Here, I've tried a single forum/topic with a very specific term that should have been found in only a few threads. I still got the time-out.
Thank you,
Patrick Ring |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 30 July 2009 : 04:55:18
|
| the sql server probably has a short timeout value set in order to maintain performance (long executing querries will adversely affect performance on other databases) |
|
|
|
pring
Starting Member
USA
9 Posts |
Posted - 30 July 2009 : 11:48:08
|
I tried changing to the OLD DB driver. While the performance was actually better (than the already improved performance), I still get a similar error on search.
========
Microsoft OLE DB Provider for SQL Server error '80040e31'
Timeout expired
/forum/search.asp, line 349
========
I'll try getting the modified search.asp shortly.
I'm currently looking to see how to increase the timeout period of the database queries.
If anyone has any other advise, I'm all ears.
Thank you,
Patrick Ring |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 30 July 2009 : 12:07:59
|
You will need to change the CommandTimeout property of the connection object. Something like:
my_conn.CommandTimeout = x
The default timeout is 30 secs. Don't think you really should make it that much big. Using 0 for the timeout, means the client will wait indefinitely for the query.
I would advise trying the alternate search page. It improves search time in a meaningful manner. If it still doesn't solve the problem, then either the database is too big or the server is rather underpowered. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
pring
Starting Member
USA
9 Posts |
Posted - 30 July 2009 : 12:23:18
|
quote:
Originally posted by ruirib
You will need to change the CommandTimeout property of the connection object. Something like:
my_conn.CommandTimeout = x
The default timeout is 30 secs. Don't think you really should make it that much big. Using 0 for the timeout, means the client will wait indefinitely for the query.
I would advise trying the alternate search page. It improves search time in a meaningful manner. If it still doesn't solve the problem, then either the database is too big or the server is rather underpowered.
I guess the reason I feel the search is a bit crippled is it worked on a older (much slower) server with the Access database. Even large searches (whole forum.. general string) would return result. Unfortunately, some members don't know exactly where they might need to look in the topics, so a broad search suits them. And, in the social engineering of the situation, it's difficult to get people to look one topic at a time.
So I guess I'm looking to make the search timeout maybe a Minute from the default 30 seconds. I'll definitely try the alternate search page before I do that.
The database is definitely big (Access db was 230+MB...which is why we moved to a better server and SQL engine). Currently the forum shows 27343 topic posts with 185396 replies, and 6076 members. Archiving (older threads) has been tossed around, but that idea has been batted down (in the important topics) by the ownership for now since the information is generally useful no matter what the age.
I'll definitely start with the new search.asp, and move from there. I'll post my result.
Thank you,
Patrick Ring.
|
|
|
| |
Topic |
|
Topic Locked
