Fastest light-weight method of executing and displaying results of SQL query

Question

Many if not most applications can display the results of SQL queries in some human readable format. However, they typically either must be open with an active window (SSMS or excel for example, both of which take significant system resources on local machine) or involve extra overhead generating reports (SSRS is good example). My platform is Microsoft SQL server. With SSMS, there is also the issue that you must find the right query, get a window open and hit execute.

What method would you use to simply execute read only queries with arbitrary content (could be stored procedures, dynamic queries, CTE's etc.) as instantaneously as possible? The display format could be quite minimal (simple table with almost no formatting changes) with aggregated data not to exceed a very small record set (certainly under 1000 records on average).

I am thinking that something like autohotkey could do this in real time. If I could map a keystroke combo to queries, I'd have the results super fast.

A good example of what I am trying to avoid is as follows:

I like to frequently check the number of users logged into a system by querying a table that sends back no more than 10 records. This should actually take less than one second to view those results. However, because I have it stored in an SSRS report, I must navigate to the website and go through a report generation process that likely takes me 15-40 seconds. I'd like to provide users with a method of seeing the data instantly. How would you accomplish this?

Answer 1

I'd create a database user with permissions only to read the table of interest, and then write a batch file containing a sqlcmd invocation with that user's credentials and the necessary query. Depending on the need, I might then do some light parsing of the output to produce an HTML file with an auto-refresh meta tag, or just dump the results straight into a text file.

Then I'd use an AutoHotkey script to bind a keystroke to run the batch file and open the result, or for somewhat greater speed (but less immediacy of results) I might run the batch file periodically as a scheduled task and use the key sequence to just open the resulting file.

(I'm not sure how to run an SSRS report from the command line, but I'm sure a way exists, and if it's something users will be accessing at random then I probably would run it on a schedule to keep the load down, depending on how many users it needed to support.)