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 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
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: 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.
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: 03 October 2005 at 4:01pm
Originally posted by JJLatWebWiz 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.
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: 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).
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.
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: 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
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: 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:
 
  1. Use a forward-only, read-only cursor if the cursor is server-side.  Or,
  2. 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
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.
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.