Code Tense: Imperfect

by Lisa Nicholls Sun, September 09 2007 16:13

C and I were away for a bit, and since then we have been bearing down on some fierce deadlines. 

Several massive reporting code bases for which the two of us are responsible are having their back-end pulled out from under them -- again.  This is the third time in four years that one set of (cough!) enterprise applications have been chucked out, rejected by users because they don't fit their business processes, and replaced by others -- to the tune of we-don't-know-how-many millions of dollars. 

Meanwhile our small, light ASP.Net front ends keep on pulling data out and making sense of the whole thing, transparently changing to fit the data underneath, and with enviable (especially if you're an enterprise application) up-time records. 

This week, while working on this latest makeover, I made what is probably a beginner's mistake in a join. I watched, horrified, as my new queries slowed to a crawl.  In the interests of completely humiliating myself and at the same time maybe saving somebody else some hair-tearing-out, I'm going to tell you exactly what I did wrong:

  • the join is on two fields of varchar type: an order key and a line item key.
  • the fields are referenced in a SQL Server clustered index. So everything should be fine.  Right?
  • except I wrote:

    SSD.OrderKey + SSD.LineKey =  SD.OrderKey + SD.LineKey
      
  • instead of

    SSD.OrderKey = SD.OrderKey AND SSD.LineKey = SD.LineKey
      
  • There is actually a sub-line-item key as well, which may be null (also referenced in the index).  Things are going to get much, much worse (gotta work in a Serenityquote whenever possible) if you do this:

    SSD.OrderKey + SSD.LineKey + ISNULL(SSD.SubLineKey,SPACE(0))  =
       SD.OrderKey + SD.LineKey + ISNULL(SD.SubLineKey,SPACE(0))
      
Well thank goodness that's over. 

To apologize for my absence here, and because I already know my code will never be perfect, I've decided not to delay any longer in uploading the first cut of my RDLDocumenter Reporting Services documentation utility.

You can read its docs on line in our articles section (they now contain a link to the new zip) or just get the zip from our downloads page.  If you choose just to download, the zip has up-to-date docs as of this posting. You will need them if you want to get a full picture of what XMLRSDocs is for, and even if you don't you will need to read them to properly deploy the Report Designer add-on component in your Visual Studio environment and also, if desired, add the rendering component to your Reporting Services environment.

This is a deployment set of files, completely usable with some extra sample scripts, a test harness form, an SSIS production-style package, and even a local CE database implementation if you want to use it as a back end (not required).  It's not full source. Perfect or not, I want to delay releasing source for now.  For one thing, there are two versions of the source: the VB source, which is up to date with the version you'll see in the deployment files, and a C# version I froze some time back, before I realized some facts of life (we generally call them "infelicities") in Report Designer integration opportunities. 

They aren't deficiencies in the RDL schema, only in the Designer and its underlying classes, so I am still hoping that Katmai will allow me to lift some of the limitations that I faced in building this thing.  Whether Katmai changes the ground rules significantly or not, I will be discussing this topic (Report Designer Extensibility and some crazy stuff you can do with it) further here, I hope.  And no matter what happens in Katmai, the results you can get from this version of RDLDocumenter should continue to be valid, we'll just have a more elegant implementation underneath.

Code doesn't have to be perfect to be durned helpful.

As I have mentioned in previous posts, RDLDocumenter caused me to face down a whole gamut of dynamic reporting scenarios, practically comprising a set of FAQs for questions that show up in the Reporting Services forums.  I expect to be posting illustrative samples from the RDLDocumenter code base here, long before I post full source. 

Tags:

Reporting | SQL Server | SSIS