Web Wiz Support and Community Forums : Error: The query has been canceled because https://forums.webwiz.net/ Copyright (c) 2006-2013 Web Wiz Forums - All Rights Reserved. Tue, 07 Apr 2026 09:43:21 +0000 Thu, 05 Jul 2007 11:41:16 +0000 http://blogs.law.harvard.edu/tech/rss Web Wiz Forums 12.08 360 https://forums.webwiz.net/RSS_post_feed.asp?TID=23675 <![CDATA[Web Wiz Support and Community Forums]]> https://forums.webwiz.net/forum_images/web_wiz_forums.png https://forums.webwiz.net/ <![CDATA[Error: The query has been canceled because : Not sure why it wasn&#039;t like...]]> https://forums.webwiz.net/error-the-query-has-been-canceled-because_topic23675_post124339.html#124339 Author: WebWiz-Bruce
Subject: 23675
Posted: 05 July 2007 at 11:41am

Not sure why it wasn't like that in the first place, seems stupid returning all the records then using a client side recordset move, when all we need is the 1 record.]]> Thu, 05 Jul 2007 11:41:16 +0000 https://forums.webwiz.net/error-the-query-has-been-canceled-because_topic23675_post124339.html#124339 <![CDATA[Error: The query has been canceled because : Yes, this seems like a better...]]> https://forums.webwiz.net/error-the-query-has-been-canceled-because_topic23675_post124338.html#124338 Author: Cem Alacayir
Subject: 23675
Posted: 05 July 2007 at 11:30am

Yes, this seems like a better version.
 
I don't know why but it seems "ORDER BY Thread.Message_date" costs too much in SQL Server for some reason but using strDBTop1 should improve it.
 
Thanks Borg.
]]> Thu, 05 Jul 2007 11:30:09 +0000 https://forums.webwiz.net/error-the-query-has-been-canceled-because_topic23675_post124338.html#124338 <![CDATA[Error: The query has been canceled because : The following should have the...]]> https://forums.webwiz.net/error-the-query-has-been-canceled-because_topic23675_post124335.html#124335 Author: WebWiz-Bruce
Subject: 23675
Posted: 05 July 2007 at 9:12am

The following should have the same type of performance improvement, but will ensure compatibility with all databases types used:-

strSQL = "SELECT " & strDBTop1 & " " & strDbTable & "Thread.Thread_ID " & _
    "FROM " & strDbTable & "Thread" & strDBNoLock & " " & _
    "WHERE " & strDbTable & "Thread.Topic_ID = "  & lngTopicID & " " & _
    "ORDER BY " & strDbTable & "Thread.Message_date ASC " & strDBLimit1 & ";"

]]> Thu, 05 Jul 2007 09:12:26 +0000 https://forums.webwiz.net/error-the-query-has-been-canceled-because_topic23675_post124335.html#124335 <![CDATA[Error: The query has been canceled because : I think this page got missed in...]]> https://forums.webwiz.net/error-the-query-has-been-canceled-because_topic23675_post124334.html#124334 Author: WebWiz-Bruce
Subject: 23675
Posted: 05 July 2007 at 9:07am

I think this page got missed in the last optimisation audit, but will certainly look at improving it.

I have a feeling though that for mySQL the MAX method is a bit buggy when you include a WHERE clause.
]]> Thu, 05 Jul 2007 09:07:17 +0000 https://forums.webwiz.net/error-the-query-has-been-canceled-because_topic23675_post124334.html#124334 <![CDATA[Error: The query has been canceled because : Microsoft OLE DB Provider...]]> https://forums.webwiz.net/error-the-query-has-been-canceled-because_topic23675_post124324.html#124324 Author: Cem Alacayir
Subject: 23675
Posted: 04 July 2007 at 9:15pm

Microsoft OLE DB Provider for SQL Server error '80040e14' 
The query has been canceled because the estimated cost 
of this query (432) exceeds the configured threshold of 60. 
Contact the system administrator.

 
I started to get the above error whenever I deleted a single post from a topic. The post was really deleted but the error was causing topic records to be orphaned thus the topics were disappearing from the forums. I found out that the delete query was successfull but another query just before the query that updates topic statistics was stopping the execution in delete_post.asp.
 
The query that was failing is
 
 'Check there are other Posts for the Topic, if not delete the topic as well 
 'Initalise the strSQL variable with an SQL statement to get the Threads from the database
 strSQL = "SELECT " & strDbTable & "Thread.Thread_ID " & _
 "FROM " & strDbTable & "Thread" & strDBNoLock & " " & _
 "WHERE " & strDbTable & "Thread.Topic_ID = "  & lngTopicID & " " & _
 "ORDER BY " & strDbTable & "Thread.Message_date ASC;"
 
 'Query the database
 rsCommon.Open strSQL, adoCon
 
 
 'If there are posts left in the database for this topic get some details for them
 If NOT rsCommon.EOF Then
  
  'Move to the last message in the topic to get the details of the last post
  rsCommon.MoveLast
  
  'Get the post ID of the last post
  lngLastPostID = CLng(rsCommon("Thread_ID"))
 End If
 
 'Close the recordset
 rsCommon.Close
 
It seems strange to me that a simple task like finding the last post id of the thread is determined with an inefficient code like this which costs too much in SQL Server. So I changed the code to a better version:
 
 strSQL = "SELECT MAX(" & strDbTable & "Thread.Thread_ID)" & _
 "FROM " & strDbTable & "Thread"  & strDBNoLock & " " & _
 "WHERE " & strDbTable & "Thread.Topic_ID = "  & lngTopicID
 rsCommon.Open strSQL, adoCon
 If NOT rsCommon.EOF Then
  If (rsCommon(0) <> "") Then lngLastPostID = CLng(rsCommon(0))
 End If
 rsCommon.Close
 
 
I hope Borg optimizes queries like this in version 9.0.


Edited by Cem Alacayir - 04 July 2007 at 9:17pm]]> Wed, 04 Jul 2007 21:15:16 +0000 https://forums.webwiz.net/error-the-query-has-been-canceled-because_topic23675_post124324.html#124324