Member Avatar for nikie_2007

Hi All,

I have a software module(enquiry) developed in ASP using MSSQL as back end.
In the index page , i display 4 new enquiries for the logged in user, clicking on which land to the enquiry pages. This has been working fine. But now recently i have come across the following error.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]The query has been canceled because the estimated cost of this query (353) exceeds the configured threshold of 300. Contact the system administrator.

Can anyone tell me as to why i am getting this error.

regards,
nikie

  • 2 Contributors
  • 2 Replies
  • 250 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by nikie_2007

Dani AI

Generated

Quick diagnosis and a practical path forward.

The server is stopping the statement because SQL Server’s query-governor feature estimates the query is “too expensive” and cancels it — that estimated cost is an optimizer metric, not milliseconds, so ’s 300ms interpretation is incorrect. The safest, lowest-effort fixes are to change the query so the optimizer only has to work with a small, well-indexed result set (what the index page actually needs) or to ask the DBA to raise/disable the governor.

Concrete changes to try (in this order):

  • Return only what you need: replace SELECT * with explicit columns and limit rows to the 4 newest items you show on the index page using TOP 4. This avoids scanning years of data.
  • Use proper JOIN syntax and avoid client-side cursors that force ADO to fetch every row. If you only need a few rows, a server-side execution or TOP eliminates the heavy work.
  • Make sure the columns in the WHERE and ORDER BY are covered by an index (for example, index on g_leads(client_id, lead_status, lead_id) and include any frequently selected columns). Also update statistics and rebuild fragmented indexes so the optimizer has accurate info.

Example SQL pattern to use:

SELECT TOP 4 l.lead_id, l.someColumn, e.enquiry_date, c.client_name
FROM g_leads l
JOIN g_client c ON l.client_id = c.client_id
JOIN g_enquiry e ON l.lead_id = e.lead_id
WHERE c.user_id = @UserId AND l.lead_status = 1
ORDER BY l.lead_id DESC;

If optimization is not possible, a DBA can change the server setting (requires privileges). Typical commands:

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'query governor cost limit', 0; RECONFIGURE;

(0 disables the governor.)

Notes: prefer parameterized queries or stored procedures instead of concatenating objLu.Id. If changes don’t help, capture the actual execution plan (or ask the DBA to) to see the expensive operator and tune that specific hotspot.

Member Avatar for msaqib

As your SQL server is configured to run any query which will be executed with in 300ms. And now in your case, your query's estimated execution time is 353 which is more than the time set by the DB administrator.

So either you will need to optimise the query to be executed in lesser time or you should optimise the database tables to return your desired data in less time.

If you could give more details about the query then I might be able to tell you exactly whats going wrong.

Member Avatar for nikie_2007

hi,

Thankyou for the response!

The error i am receiving is the following

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]The query has been canceled because the estimated cost of this query (335) exceeds the configured threshold of 300. Contact the system administrator.

/index.asp, line 65

and line 65 is highlighted below

Set Lead_Rec = Server.CreateObject("ADODB.Recordset")
Lead_Rec.Cursorlocation = adUseClient
Lead_Sql = "select * from g_leads,g_client,g_enquiry where (g_leads.client_id=g_client.client_id) and (g_leads.lead_id = g_enquiry.lead_id) and (g_client.user_id=" & objLu.Id & ") and (g_leads.lead_status=1) order by g_leads.lead_id desc"

Lead_Rec.open Lead_Sql,Application(db),adOpenForwardOnly,adLockReadOnly
L_R_Count = Lead_Rec.RecordCount

Actually old users have a lot of enquires and i have to display all their old enquiries right from 2004.I guess the data is really large.

Could you tell me as to how i can increase the query execution time?

regards,
nikie

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.