Web Wiz - Green Windows Web Hosting - Celebrating 25 Years!
Web Wiz Homepage Search Web Wiz

  New Posts New Posts RSS Feed - SQL error when deleting posts
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

SQL error when deleting posts
 Post Reply Post Reply Page  12>
Author
  Topic Search Topic Search  Topic Options Topic Options
Mojo_Risin View Drop Down
Groupie
Groupie


Joined: 23 June 2002
Location: Canada
Status: Offline
Points: 57 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Mojo_Risin Quote  Post ReplyReply Direct Link To This Post Topic: SQL error when deleting posts
    Posted: 27 September 2005 at 12:12am

Can't find a reference to this using the Search function, so hopefully someone can provide an answer. I've upgraded within the last week or so to WWF 7.92 and I, and my moderators, are now getting the following errors when deleting posts.

Microsoft OLE DB Provider for SQL Server error '80040e14'
The query has been canceled because the estimated cost of this query (112) exceeds the configured threshold of 38. Contact the system administrator.
 
and
 
Microsoft OLE DB Provider for SQL Server error '80040e14'
The query has been canceled because the estimated cost of this query (111) exceeds the configured threshold of 38. Contact the system administrator.
 
I'm not sure if this is a bug in the forum software or a problem at the SQL Server end. I tried to get the web host to fix a SQL timeout problem when using the forum's search function (after trying a few things found on this site). I don't know if somehow a setting was changed on SQL Server by the host, or if it's the forum causing the problem as the update and the attempts to fix the timeout problem were basically one after the other (update happened first).
 
In addition, when there is a single post in a new topic, and the post is deleted by the author or the admin or moderator, there seems to be a problem with the topic record not getting deleting, so it continues to appear in a "new topic list" but of course no one can view the topic as no posts exists. I don't know if the two things are related.
 
Anyone else run into these problems, or more importantly, know how to resolve them???
 
Thanks...
 
Dave
Back to Top
JJLatWebWiz View Drop Down
Groupie
Groupie
Avatar

Joined: 02 March 2005
Location: United States
Status: Offline
Points: 136 		Post Options Post Options   Thanks (0) Thanks(0)   Quote JJLatWebWiz Quote  Post ReplyReply Direct Link To This Post Posted: 27 September 2005 at 2:18pm
Your host has set the server-wide Query Governor Cost Limit to a cost of 38.  SQL Server estimates the "cost" of each query based on a secret recipe.  The estimated cost may bear little resemblence to actual cost, but if the estimated cost exceeds the threshold, the query is not allowed to run.  Other than random experimentation, there is little that anyone could do lower the "estimate".
 
It might be possible to build a new stored procedure to perform the actual delete operation and put a "SET QUERY_GOVERNOR_COST_LIMIT 0" in the stored procedure to ignore the limit.  It might also be that the limit is being reached in an existing stored procedure and overriding the limit in the existing stored procedure will fix everything.  You might ask the host to raise the threshold, since it's just an estimate.  A limit of 300 is probably more reasonable.
 
At what line number are these errors reported?
 
Back to Top
Mojo_Risin View Drop Down
Groupie
Groupie


Joined: 23 June 2002
Location: Canada
Status: Offline
Points: 57 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Mojo_Risin Quote  Post ReplyReply Direct Link To This Post Posted: 27 September 2005 at 2:54pm

Thanks for the response.  The web host indicated a cost of 111 was very high. They seemed to have changed something because I never got this error before, unless there's something different in 7.92 (that wasn't in 7.8, which is what I think I had)

/forum/delete_post.asp, line 217

Line 217 is: rsCommon.Open strSQL, adoCon
Dave
Back to Top
JJLatWebWiz View Drop Down
Groupie
Groupie
Avatar

Joined: 02 March 2005
Location: United States
Status: Offline
Points: 136 		Post Options Post Options   Thanks (0) Thanks(0)   Quote JJLatWebWiz Quote  Post ReplyReply Direct Link To This Post Posted: 27 September 2005 at 4:58pm
Line 217 is where this simple query is executed: "SELECT tblThread.Thread_ID, tblThread.Message_Date FROM tblThread WHERE tblThread.Topic_ID =4 ORDER BY tblThread.Message_date ASC;" (using Topic_ID 4 for example only).
 
An estimate of 111 is extremely high for such a simple query.  That particular line seems to be the same at least going back to 7.01 (the version I use).  It would be very difficult to tune that query to lower the estimate.
 
How many records are you dealing with?  I ran the query against a small test WWF forum database running from SQL Server 7 and the estimate was 0.0241 (so was the actual cost).
 
 
Back to Top
Mojo_Risin View Drop Down
Groupie
Groupie


Joined: 23 June 2002
Location: Canada
Status: Offline
Points: 57 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Mojo_Risin Quote  Post ReplyReply Direct Link To This Post Posted: 27 September 2005 at 8:18pm
I get the error when any record is deleted. It's quite annoying not to be able to delete records without generating an error, and it leaves the topic record behind if it's the only post in the thread, so I have to delete topic records manually.  Or, are you asking how many forum records there are in total? If it's the latter, there are over 100,000 posts in the database.
Dave
Back to Top
JJLatWebWiz View Drop Down
Groupie
Groupie
Avatar

Joined: 02 March 2005
Location: United States
Status: Offline
Points: 136 		Post Options Post Options   Thanks (0) Thanks(0)   Quote JJLatWebWiz Quote  Post ReplyReply Direct Link To This Post Posted: 28 September 2005 at 12:30pm
There is something seriously wrong with a SQL server that can't perform such a simple query.  Doing a query against 100,000 records and returning probably less than a 10th of those records and only 2 fields should be nowhere near a cost 38.
 
How do you delete topics manually?  Does the host provide an interface to perform database functions?  Can you run the query manually through your host's interface?
 
I could probably build a stored procedure that does what you need and uses the command to override the global cost limit.  However, a thought just occurred to me... since the Message_Date field isn't indexed, the sort process actually costs the most in that query.  If you change the sort field from Message_Date to Thread_ID, the query cost goes down dramatically.  There's a remote possibility that the ID sort is slightly different than the date sort, but highly unlikely.
 
To do this, go to line 214 and change:
 
strSQL = "SELECT " & strDbTable & "Thread.Thread_ID, " & strDbTable & "Thread.Message_Date FROM " & strDbTable & "Thread WHERE " & strDbTable & "Thread.Topic_ID ="  & lngTopicID & " ORDER BY " & strDbTable & "Thread.Message_date ASC;"
 
to
 
strSQL = "SELECT " & strDbTable & "Thread.Thread_ID, " & strDbTable & "Thread.Message_Date FROM " & strDbTable & "Thread WHERE " & strDbTable & "Thread.Topic_ID ="  & lngTopicID & " ORDER BY " & strDbTable & "Thread.Thread_ID ASC;"
 
That definitely will reduce the cost.  Whether it reduces the cost enough, I can't answer.
Back to Top
Mojo_Risin View Drop Down
Groupie
Groupie


Joined: 23 June 2002
Location: Canada
Status: Offline
Points: 57 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Mojo_Risin Quote  Post ReplyReply Direct Link To This Post Posted: 28 September 2005 at 9:17pm
Originally posted by JJLatWebWiz JJLatWebWiz wrote:

How do you delete topics manually?  Does the host provide an interface to perform database functions?  Can you run the query manually through your host's interface?
 
I delete records manually using Enterprise Manager or Query Analyzer.
 
I will relay your other remarks to the webhost.  This problem is seriously affecting the administration of my forum.
 
Thanks for the feedback.
Dave
Back to Top
Mojo_Risin View Drop Down
Groupie
Groupie


Joined: 23 June 2002
Location: Canada
Status: Offline
Points: 57 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Mojo_Risin Quote  Post ReplyReply Direct Link To This Post Posted: 29 September 2005 at 8:21pm
Well, the webhost says the problem is with the optimization of the tables (which is the default configuration that is installed with WWF). I'm not sure what happened between a couple of weeks ago and a week ago when it started, except the update to 7.92, but JJL indicates the same code is in 7.01.
 
The host has increased the threshold to 60 which does nothing to fix the problem since it is just over half the number required.
 
Apparently WWF is using way too many resources to delete records. I'm baffled about the sudden change. I've been using WWF on SQL Server for 2 years and no problems until now, and it's somehow my fault.  It's an annoying problem.
 
Guess I'll have to work on the table structure or the query structure some more.  I haven't tried the thread_ID suggestion above, yet. BTW, the message_date field was indexed in the table already. I tried changing it from ASC to DESC to see if that made any difference. It didn't.
Dave
Back to Top
 Post Reply Post Reply Page  12>

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.08
Copyright ©2001-2026 Web Wiz Ltd.

Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Notice

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT at 20% unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2026 Web Wiz Ltd. All rights reserved.