So, recently I needed to make an application that allowed a user to enter an arbitrary SQL query, and elsewhere in the UI I needed to display a drop-down with the fields that this arbitrary query returned. This poses a small problem.
It's very simple if the user is doing simple queries, that don't take long to execute. You could just run the query, then, take the first result, and get the list of fields. Well.. This works for simple queries that return small result sets, but we needed to put in queries that potentially return as many as 48 million results, using complex queries including joins between multi-million rowed tables, aggregates, and that sort of thing..
In other words, the queries are slow. Really slow. They create a lot of UI lag when I go to get the field names for the drop down box.
My first attempt was to take the query and wrap it up like this:
SELECT TOP(1) * FROM (
-- original query here
) fieldNamesTable My thinking was that if I specified that I only wanted the first record it's be really quick, even with a complex query. This is true. It's must faster, but it's still slow. Too slow. A lot of UI lag still remained.
So, my second attempt worked much better. I wrapped the query again, but now it looks like:
SELECT * FROM (
-- original query here
) fieldNamesTable WHERE 1 = 0 Instead of specifying I wanted the first record, I put a phrase in the WHERE clause that will always be false. The SQL Server's query execution engine realizes that, and so it knows that the query will never be able to return data. So it immediately returns with 0 results. But I get the field names!! This is SUPER fast!