Show me everything

-

Pixabay / Tumisu

T-SQL Tuesday logo

Steve Jones (b | t) turned the light on and started the October party for T-SQL enjoyers. The topic: to write about producing SQL dynamically in some way. That is T-SQL Tuesday #155 –The Dynamic Code Invitation. Let's tell the story!

Once upon a time, I got the client's requirements for the report application, coming from the darkest SQL developer's dreams. Below are the main elements of this nightmare:

  1. Write a single SELECT query template, without batches, without stored procedures, etc. - a raw query,
  2. with a dynamic number of columns (from 10 to ca. 400),
  3. which can dynamically include a different number of tables (at least 10, up to ca. 30) in JOIN operations, according to parameters,
  4. which number is also dynamic (up to ca. 400),
  5. and the query is using a simple template syntax, supported by the report application, with markups formatted as SQL comments,
  6. the template syntax allows grouping parameters in sections,
  7. and these sections can be included and excluded by the section-related parameters,
  8. the parameters can be applied in every clause (SELECT, FROM, WHERE, GROUP BY, ORDER BY),
  9. some of these parameters should be included in the output column list also,
  10. and the result is paged.

"The page size is fixed?", I asked to be sure. "No, of course, no - the parameters can be applied everywhere, they are also dynamic", the client answered.

"Oh, I have almost forgotten, we have to get the result in less than 1 minute in the regular case, because we need to retrieve this data by a web application, and there is a timeout for HTTP requests, and we have SLA..."

I didn't ask about the underlying database size, because I have spent two months earlier carefully designing and tuning the ETL service. Tens of gigabytes.

"I have one tip, maybe it will help you a little: parameters are already known, and configured for the main application. The list is stored in the database, in two dedicated tables! Good news, don't you think?"

I nodded my head, what could I say?

"We need to sometimes add or remove something, of course, this is still an evolving software, so you should check on your side whether parameters are synchronized in the application and in the query template", he added. Not at the end, but I skip the rest of the story. Tuesday midnight is coming to Poland.

How I solved this problem? Of course, with the smart Excel spreadsheet empowered by VBA macros.

Fortunately, I convinced my client to give me exclusive permission to update the list of parameters on my project side. It was not so easy, but the fundamental part of the spreadsheet was the list of parameters, with different naming conventions - boomerang! did you read my first post on this party series? - with autogenerated captions for parameters, so even the client gains some automation and improvement.

I was strongly against this approach, but in the end, it was working. We had to prepare some suggestions for users, that a long list of parameters can cause troubles, but... yes, it was working.

There are only three hard things in Computer Science: cache invalidation, naming things, and how to avoid Excel.


Comments

Post a Comment

Popular posts from this blog

Be ready to change your mind

-
Image
Tell us about the experiences, the code, the posts that inspired you, and all the gory details in between. And what is it that makes you so passionate about this topic that "It Depends" gets tossed out the window? Pull out your soapbox and tell us all about it - this is the invitation of Deborah Melkin for T-SQL Tuesday #152 - It Depends . Although "the sentence" is not mentioned on the list given by Deborah, I want to write about one sentence, which unexpectedly terminated a long episode in my professional career as a programmer, and which builds my main, archetypical answer including "it depends". This sentence comes from The Clean Code Blog , the post's title is: The Clean Architecture , and the sentence is: The database is a detail. Before I read this, I was diving deeply and broadly into Books Online Ocean, SQL Server Central Sea, Simple Talk Lake, SQL Server Tips Pond... I spent more than 10 years on understanding SQL Server database detai...
Read more

Less T, more SQL

-
Image
The new SQL Server is coming soon . Fortunately, on the day of release, there will be more novelties than on the last iPhone release, so we shouldn't be disappointed. As a fan of pure SQL in T-SQL dialect, I am particularly glad to see IS [NOT] DISTINCT FROM predicate in our toolbox. I remember my training sessions for beginners when I showed examples of set logic fundamentals in T-SQL and the feeling of participation in a magic trick. I remember pieces of production code, when using pure SQL spells introduced simplicity and elegance into the product. The declarative side of T-SQL makes my programming experience so different, and so intellectually satisfying... It's like touching something ethereal or spiritual. Using imperative logic to achieve a goal - because of performance issues, time pressure, whatever - for me as a SQL developer, rarely comes without a fight. I know - there are loops, ifs, and counters under the hood, and we need indexes, but using pure SQL, f...
Read more