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