SQL error when deleting posts
Printed From: Web Wiz Forums
Category: Web Wiz Web App Support Forums
Forum Name: Web Wiz Forums
Forum Description: Support forum for Web Wiz Forums application.
URL: https://forums.webwiz.net/forum_posts.asp?TID=16705
Printed Date: 15 April 2026 at 11:03pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: SQL error when deleting posts
Posted By: Mojo_Risin
Subject: SQL error when deleting posts
Date 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 |
Replies:
Posted By: JJLatWebWiz
Date 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?
|
Posted By: Mojo_Risin
Date 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 |
Posted By: JJLatWebWiz
Date 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).
|
Posted By: Mojo_Risin
Date 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 |
Posted By: JJLatWebWiz
Date 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.
|
Posted By: Mojo_Risin
Date Posted: 28 September 2005 at 9:17pm
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 |
JJLatWebWiz wrote:Posted By: Mojo_Risin
Date 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 |
Posted By: Mojo_Risin
Date Posted: 29 September 2005 at 8:29pm
|
Thanks for the suggestion about the Thread_ID change, JJL, but it made no difference whatsoever. Off to the drawing board again. ------------- Dave |
Posted By: JJLatWebWiz
Date Posted: 30 September 2005 at 12:54pm
|
This actually sounds like this might be a problem that MS SQL Server has when the index keys correlate closely to the actual tables. In such cases, the optimizing engine may choose to ignore the non-clustered indexes, thus costing much more than it should.
Since you can use the Query Analyzer, I suggest you run the 2 different select statements above using the cost estimator in Query Analyzer to see if the cost and execution plan changes with different index changes. First, add a new multi-column index that includes the Topic_ID, Author_ID, Thread_ID, and Message_Date. Test the performance of the query sorted and unsorted, also sorted on Thread_ID and then on Message_Date. Change the order of the fields in the index too.
I'm building a large test table to evaluate the costs associated with the different indexes. I think a stored procedure that includes a cost limit override will be an effective work-around.
One interesting possibility is to open a new connection instead of using the persistant connection. I wonder if SQL Server is aggragating the costs of all the queries on the persistant connection. So, maybe if you open a new connection, SQL Server will estimate that cost by itself. I have NO idea if that's the case, but it's a quick experiment that can't hurt anything. To do this, open "delete_post.asp" and find line 217. It should say: "rsCommon.Open strSQL, adoCon". Change it to "rsCommon.Open strSQL, strCon", and save and upload it. ------------- p.s. I'm not affiliated with Web Wiz Guide in any way. I'm just an average Web Wiz user repaying my debt for the use of their fine forum by trying to help other Web Wiz Guide users. |
Posted By: Mojo_Risin
Date Posted: 03 October 2005 at 4:01pm
Thanks for the suggestion, unfortunatly it made no difference. I've also noticed that over time the cost is going up. It started at 111 and has slowly worked it's way up to 128. ------------- Dave |
Posted By: JJLatWebWiz
Date Posted: 03 October 2005 at 8:24pm
|
You need to run that query in QA and see if the cost is the same. How about the other index suggestions? In QA, look at the execution plan. Adjust the column order of the covering index and see if the execution plan and cost changes.
The cost governor has a lot to do with server load, multiprocessors, and disk performance.
Have you ever created a stored procedure before? Run this from QA:
That will create a simple stored procedure that does exactly the same thing as the query that gets run at line 217, but it ignores the server-wide Cost Limit. Now, in QA, exec wwfSP_TopicThreadsByDate 4 (where 4 is the Topic_ID, pick one that is very large for a good cost estimate) and compare the cost of the standard query. If the SP runs as it should, the delete_post.asp can then be modified to use the SP instead of the standard query.
I'm mostly interested to see QA estimates the cost for the query. I don't understand why a simple query would be so costly with such a small recordset (big for a WWF forum but small for a SQL server). ------------- p.s. I'm not affiliated with Web Wiz Guide in any way. I'm just an average Web Wiz user repaying my debt for the use of their fine forum by trying to help other Web Wiz Guide users. |
Posted By: Mojo_Risin
Date Posted: 04 October 2005 at 6:29pm
|
The cost in QA is in the area of 0.05 for both the SP and the original code (if I'm doing it right).
I tried to execute the SP instead of the original code from the page, but I get other errors; either the following query doesn't work because the recordset is closed, or the cursor type isn't correct, depending on what kind of statement I use to execute the SP.
No time to debug that problem today, so I will try again later when I get a chance.
The cost of the query has worked it's way up to 131 now. I don't know for sure, but I suspect it's a problem on the server because the query runs almost instantly. According to the technical guru, who knows a heck of a lot more than SQL Server than I do, it's the code. But it strikes me as odd that the cost is gradually incrementing day by day. I wonder what would happen if the SQL server were stopped and restarted? Maybe there's something in memory that's not flushing. ------------- Dave |
Posted By: JJLatWebWiz
Date Posted: 05 October 2005 at 2:14pm
OK. NOW we have something solid to work with. The cost of the actual query is going to be the same in QA and from ASP, the difference is what else happens when the query is run from the different locations. Using the adOpenDynamic with a server-side cursor and a CacheSize of 1 (as the delete_post.asp script is at line 217) , ADO is going to run a number of stored procedures to satisfy that query and multiple round-trips for each record returned by the query. The process also creates a temp tables on the SQL server which is probably part of the additional cost. This is true even for the stroed procedure I described, even though the cost limit override should still allow it to run. There are basically 2 ways to avoid the problem:
The problem with option 1 is that delete_post.asp uses .MoveLast, which counter-intuitively requires a cursor that can use a .MovePrevious so a forward-only cursor won't work.
Option 2 might work simply by adding "adoCon.CursorLocation = 3" immediately above "adoCon.open" in "common.asp". But, I have only tested such a change in a very limited way on my MS SQL test installation. So far, it certainly seems to work. This is actually the option I would recommend from here.
You can't stop the ADO connection from executing those stored procedures that the SQL server apparently uses to maintain the cursor. Now that I think about it, all this extra SQL work is taking place all the time, so for some people, it may actually speed things up by switching to a client-side cursor. The client-side cursor will probably put a drain on the web server, but whether or not it's a net drain or a net boost is anyone's guess.
------------- p.s. I'm not affiliated with Web Wiz Guide in any way. I'm just an average Web Wiz user repaying my debt for the use of their fine forum by trying to help other Web Wiz Guide users. |