Web Wiz Support and Community Forums : SQL error when deleting posts

Web Wiz Support and Community Forums : SQL error when deleting posts https://forums.webwiz.net/ Copyright (c) 2006-2013 Web Wiz Forums - All Rights Reserved. Thu, 16 Apr 2026 02:23:13 +0000 Wed, 05 Oct 2005 14:14:21 +0000 http://blogs.law.harvard.edu/tech/rss Web Wiz Forums 12.08 360 https://forums.webwiz.net/RSS_post_feed.asp?TID=16705 <![CDATA[Web Wiz Support and Community Forums]]> https://forums.webwiz.net/forum_images/web_wiz_forums.png https://forums.webwiz.net/ <![CDATA[SQL error when deleting posts : OK. NOW we have something solid...]]> https://forums.webwiz.net/sql-error-when-deleting-posts_topic16705_post91763.html#91763 Author: JJLatWebWiz
Subject: 16705
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:

Use a forward-only, read-only cursor if the cursor is server-side. Or,
User a client-side cursor.

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.

Edited by JJLatWebWiz - 10 October 2005 at 10:53am]]> Wed, 05 Oct 2005 14:14:21 +0000 https://forums.webwiz.net/sql-error-when-deleting-posts_topic16705_post91763.html#91763 <![CDATA[SQL error when deleting posts : The cost in QA is in the area...]]> https://forums.webwiz.net/sql-error-when-deleting-posts_topic16705_post91702.html#91702 Author: Mojo_Risin
Subject: 16705
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.

]]> Tue, 04 Oct 2005 18:29:26 +0000 https://forums.webwiz.net/sql-error-when-deleting-posts_topic16705_post91702.html#91702 <![CDATA[SQL error when deleting posts : You need to run that query inQA...]]> https://forums.webwiz.net/sql-error-when-deleting-posts_topic16705_post91629.html#91629 Author: JJLatWebWiz
Subject: 16705
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:

CREATE PROCEDURE wwfSP_TopicThreadsByDate
@TopicID int=0
AS
SET QUERY_GOVERNOR_COST_LIMIT 0
declare @sql as varchar(200)
set @sql = 'SELECT tblThread.Thread_ID, tblThread.Message_Date FROM tblThread WHERE tblThread.Topic_ID =' + convert(varchar(8),@TopicID) + ' ORDER BY tblThread.Message_date ASC;'
exec (@sql)

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).

]]> Mon, 03 Oct 2005 20:24:46 +0000 https://forums.webwiz.net/sql-error-when-deleting-posts_topic16705_post91629.html#91629 <![CDATA[SQL error when deleting posts : JJLatWebWiz wrote:To do this,...]]> https://forums.webwiz.net/sql-error-when-deleting-posts_topic16705_post91623.html#91623 Author: Mojo_Risin
Subject: 16705
Posted: 03 October 2005 at 4:01pm

JJLatWebWiz wrote:

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.

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.

]]> Mon, 03 Oct 2005 16:01:35 +0000 https://forums.webwiz.net/sql-error-when-deleting-posts_topic16705_post91623.html#91623 <![CDATA[SQL error when deleting posts : This actually sounds like this...]]> https://forums.webwiz.net/sql-error-when-deleting-posts_topic16705_post91480.html#91480 Author: JJLatWebWiz
Subject: 16705
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.

]]> Fri, 30 Sep 2005 12:54:54 +0000 https://forums.webwiz.net/sql-error-when-deleting-posts_topic16705_post91480.html#91480 <![CDATA[SQL error when deleting posts : Thanks for the suggestion about...]]> https://forums.webwiz.net/sql-error-when-deleting-posts_topic16705_post91455.html#91455 Author: Mojo_Risin
Subject: 16705
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.

]]> Thu, 29 Sep 2005 20:29:54 +0000 https://forums.webwiz.net/sql-error-when-deleting-posts_topic16705_post91455.html#91455 <![CDATA[SQL error when deleting posts : Well, the webhost says the problem...]]> https://forums.webwiz.net/sql-error-when-deleting-posts_topic16705_post91454.html#91454 Author: Mojo_Risin
Subject: 16705
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.

]]> Thu, 29 Sep 2005 20:21:42 +0000 https://forums.webwiz.net/sql-error-when-deleting-posts_topic16705_post91454.html#91454 <![CDATA[SQL error when deleting posts : JJLatWebWiz wrote:How do you...]]> https://forums.webwiz.net/sql-error-when-deleting-posts_topic16705_post91362.html#91362 Author: Mojo_Risin
Subject: 16705
Posted: 28 September 2005 at 9:17pm

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.

]]> Wed, 28 Sep 2005 21:17:39 +0000 https://forums.webwiz.net/sql-error-when-deleting-posts_topic16705_post91362.html#91362 <![CDATA[SQL error when deleting posts : There is something seriously wrong...]]> https://forums.webwiz.net/sql-error-when-deleting-posts_topic16705_post91315.html#91315 Author: JJLatWebWiz
Subject: 16705
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;"

That definitely will reduce the cost. Whether it reduces the cost enough, I can't answer.

]]> Wed, 28 Sep 2005 12:30:48 +0000 https://forums.webwiz.net/sql-error-when-deleting-posts_topic16705_post91315.html#91315 <![CDATA[SQL error when deleting posts : I get the error when any record...]]> https://forums.webwiz.net/sql-error-when-deleting-posts_topic16705_post91281.html#91281 Author: Mojo_Risin
Subject: 16705
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.]]> Tue, 27 Sep 2005 20:18:04 +0000 https://forums.webwiz.net/sql-error-when-deleting-posts_topic16705_post91281.html#91281