SQL Query cost calculation before firing the query

Posted on 2011-10-24
Medium Priority
Last Modified: 2012-05-12
I am working on a Windows Forms based .Net application that uses SQL 2008 database as back-end. We have a few search screens that allow the user to enter search criteria. Some queries take a lot of time to execute while most of the queries take only a few seconds. Is there a way to prevent a query from being fired if the cost of the query is expected or calculated to be very high?I'd like to display a warning message to the user that the query would potentially run for a while. The user should given the choice of refining the query or agree to wait several seconds.

We are using select top 100 is the queries but is there a different solution that would warn the user before firing the query?

Question by:shekhar_shashi
LVL 61

Accepted Solution

HainKurt earned 1000 total points
ID: 37020081
maybe this query will help

select b.text, *
from sys.dm_exec_query_stats as a
cross apply sys.dm_exec_sql_text(sql_handle) as b

check your query above and try to guess time (use total_ellapsed_time & text to find a similar query)

but there is no guarantee to find the actual time...
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1000 total points
ID: 37021989
Very good question and unfortunately no good answer, at least that I know of.  Yes, you can use TOP and that will reduce network traffic, but it may not diminish the actual execution of the query.

One approach you could take is set a very short timeout (as in CommandTimeout = 10).  That means that any query that takes longer than 10 seconds would fail and you could trap the error.  You can then display an appropriate message, so that the user can refine their query.

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Screencast - Getting to Know the Pipeline

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question