|
Jeepaholic
Starting Member
36 Posts |
 Posted - 2002-09-12 : 02:06:09
|
| Hey there. I've hunted around the forums for some kind of similar situation that correlates to mine, but have had no luck. I'll try to summarize my situation a bit, then will provide you with a link to another forum that has more details.I'm running SQL2k (SP2) on a Win2k box, fully updated. Dual PII 400's, 512MB RAM. I've installed the latest version of Snitz Forums (v3.4.02). This is a newer version of these very forums here at SQLTeam.com. The search.asp page is timing out in 40 seconds for me and showing this error:================Microsoft OLE DB Provider for SQL Server error '80040e31' Timeout expired /citizensband/v34/search.asp, line 347 ===========My query timeout is set to 600 seconds. Setting it to zero does nothing.I'm searching through 110,000 posts, and the result set returns 223 rows. When setting up the forum to run on Access 2000 (identical database contents), it runs and completes in 1:10. After using the Profiler and tracing the SQL statement, I plugged it into the Query Analyzer and it took 3:50 to run! Still, through the ASP page, it times out in 40 seconds.So, I'm a bit confused...and not sure what to do. It appears there are a couple problems:1) The early time-out in SQL2) The fact that Access seems to be out-performing SQL on this particular query by a factor of 3.I've included the query below...and a link to the topic I've created on Snitz Forums regarding this (some more detail there for ya). Any thoughts would be greatly appreciated!QUERY:================SELECT DISTINCT C.CAT_STATUS, C.CAT_SUBSCRIPTION, C.CAT_NAME, C.CAT_ORDER, F.F_ORDER, F.FORUM_ID, F.F_SUBJECT, F.CAT_ID, F.F_SUBSCRIPTION, F.F_STATUS, T.TOPIC_ID, T.T_AUTHOR, T.T_SUBJECT, T.T_STATUS, T.T_LAST_POST, T.T_LAST_POST_AUTHOR, T.T_LAST_POST_REPLY_ID, T.T_REPLIES, T.T_UREPLIES, T.T_VIEW_COUNT, M.MEMBER_ID, M.M_NAME, MEMBERS_1.M_NAME AS LAST_POST_AUTHOR_NAME FROM ((((FORUM_FORUM F LEFT JOIN FORUM_TOPICS T ON F.FORUM_ID = T.FORUM_ID) LEFT JOIN FORUM_REPLY R ON T.TOPIC_ID = R.TOPIC_ID) LEFT JOIN FORUM_MEMBERS M ON T.T_AUTHOR = M.MEMBER_ID) LEFT JOIN FORUM_CATEGORY C ON T.CAT_ID = C.CAT_ID) LEFT JOIN FORUM_MEMBERS MEMBERS_1 ON T.T_LAST_POST_AUTHOR = MEMBERS_1.MEMBER_ID WHERE ( (R.R_MESSAGE LIKE ''%Hello%'' OR T.T_SUBJECT LIKE ''%Hello%'' OR T.T_MESSAGE LIKE ''%Hello%'') ) AND F.F_TYPE = 0 ORDER BY C.CAT_ORDER, C.CAT_NAME, F.F_ORDER, F.F_SUBJECT, T.T_LAST_POST DESC==================Snitz Forums Link to more information:http://forum.snitz.com/forum/topic.asp?TOPIC_ID=34674Thanks again... |
|
40-second OLEDB Timeout Expired Error?