return best value found for query in N seconds

Question

I have this query:

SELECT MAX(LEN(MyColumn)) FROM MyTable

But there are many rows and no index on the column. Is there a way to get the highest MAX(LEN(MyColumn)) found in N seconds instead of the absolute maximum?

are you unable to add indexes (say a computed column on len of the column?) or are you worried about the performance on data entry? Take the hit on data entry, or take the hit when querying. I know of know way to tell the engine to stop trying after N seconds and return what you have... — xQbert, May 27 '14 at 15:54
no, not really. that query is atomic operation, it'll return results, or nothing. if you use an external "kill long job" operation, you'll just get nothing back. there's no "stop after X seconds and give me whatever you found" option available in mysql. — Marc B, May 27 '14 at 15:56
Rather than enforcing a timeout you could examine a sample of the data E.g. `TOP .. FROM MyTable TABLESAMPLE (10 PERCENT)` but I would question the utility of the resulting figure. Much better to add a derived column as xQbert suggests. — Alex K., May 27 '14 at 15:57
Might not help but for something like that I would do a with (nolock). Even it it does not help that query you don't want to impact updates. — paparazzo, May 27 '14 at 19:38

score 1 · Accepted Answer · answered Aug 20 '14 at 06:45

If you really need such functionality you can create function with loop containing sequential comparing of MyColumn length with some max value and checking you query time limit. In case each record contains id column you can implement some kind of cache - store previously calculated max value and maximal checked id value somewhere in your database so next time you need to check only new records. Of course it depends on your real task.

return best value found for query in N seconds

1 Answers1