Time limited select queries?

Imagine I allow our users to enter search terms. Typically, a search term would lead to our database returning a handful of thousand rows, and the result set returns very quickly. But it's possible to send in silly search requests that end up returning pretty much all the data in the system. Think, for example, a search query searching for the substring "e" in rows containing English text. Such a query could run for 30 seconds and cause the system to appear slow.

So wouldn't it be great if you could write a query like "SELECT ...columns... FROM ...whatever... WHERE ...criteria ... ... WITHIN N seconds. It runs the query for N seconds and it returns whatever it managed to gather within those N seconds, regardless of whether the full query has completed or not.

Is this possible? Without the use of cursors or some such evil?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
>Imagine I allow our users to enter search terms.
Let's ask questions directly and without the imaginging.  John Lennon we ain't.  Thanks in advance.

>It runs the query for N seconds and it returns whatever it managed to gather within those N seconds,
I don't believe SQL has a 'N seconds' construct, but there is the query hint OPTION FAST {number of rows} that will optimize a query to return the first {number of rows} quickly.

Also, the front-end can validate the user entry to make sure it's something more substantial then 'e'.  Since we're searching using a LIKE wildcard comparison this could result in higher query processing which can have consequences on other processes.
Vitor MontalvãoMSSQL Senior EngineerCommented:
It runs the query for N seconds and it returns whatever it managed to gather within those N seconds, regardless of whether the full query has completed or not.
That doesn't exists in SQL Server and I don't think it exists in any RDBMS. What you can do is to set a timeout for the query execution and when the timeout limit is reached you throw a message informing that the query performed took much long and the user should restrict more his/her filter criteria.
Lee SavidgeCommented:
Surely you could just have the query timeout set to, say 30 seconds. If the query times out after that period, it is terminated anyway, and you can pick the timeout event up in your code and display a message along the lines of:

"Query timed out. Either your search term is too broad and is returning too much data, or there was a problem retrieving the data set".
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Jim HornSQL Server Data DudeCommented:
Side thought:  I know a lot of DBA's that would threaten violence with child-like glee to any developer that attempts to build a wildcard search that has the potential to hang a production database.
WernerVonBraunAuthor Commented:
I know you can set a query timeout and I know that you can tell SQL to return TOP N records. If that had been an acceptable solution I would have gone with that and I would not have felt the need to post this question.

And Jim, don't tell me how to ask my questions, mkay?
WernerVonBraunAuthor Commented:
Gee whiz, well ain't that exactly why I am trying to avoid it?
Vitor MontalvãoMSSQL Senior EngineerCommented:
No stress. We don't know what you know and didn't want to give you a negative answer without giving you an alternative solution.
I can't recall nothing in SQL Server that does what you pretend to do.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lee SavidgeCommented:
Apologies, I wasn't sure if you had considered those options. Clearly you had.

It is probably the only option you have as SQL, to my knowledge, doesn't have a mechnism to do what you're asking, certainly not in the way you'd like to see it unless SQL 2014 or later has something as I'm not fully up to speed with everything they to offer.
WernerVonBraunAuthor Commented:
Thanks guys. I was afraid this might not be possible but it's always worth asking, right? I think the best thing to do is to tell the user in the Front End that if they enter a search criterion that is too short and that is likely to yield too many results that the system will restrict the output to N rows, and to then apply that if they insist. Not the answer I was hoping for but since it's the correct answer the grade must of course be "A".
Lee SavidgeCommented:
Thanks. I wish I could have helped more but sometimes, there just isn't another option. I must admit, I would like to see a query hint option where you could specify a timeout on the query.
Jim HornSQL Server Data DudeCommented:
>And Jim, don't tell me how to ask my questions, mkay?
Sorry, but that's part of the service.  I've written an entire article on that that's generated a lot of fan mail.

Glad you received an actionable answer.
WernerVonBraunAuthor Commented:
"Sorry, but that's part of the service"

Maybe you need a little help with that. Customers don't tend to respond well to condescension.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.