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?

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.
If this question can be reworded to fit the rules in the help center, please edit the question.

1 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.)

Thanks. This solution should work well for me and be about as fast as possible. I already have the server logins needed for this and believe sqlcmd is a really easy way to implement the solution.
–
MichaelOct 2 '12 at 2:11