Atlanta .NET Regular Guys

Me Too DL3!

Correlated subqueries in SQL Server

I want to thank Keith Rome up front for pointing the solution out to me.

The other day I had an issue trying to get the right set of data into a query’s output.  I wanted to avoid cursors and temporary tables, but I had to do several combinations of data sets selected out of the same table.  Let’s look at the problem.

I have a report which helps stores analyze how well they are selling the various departments and categories broken down by month as compared to the entire corporation.  Here is an example.  The blue numbers are the company performance in that department and the black numbers are the store’s performance in that department.  The percentage represents the sum of department’s sales in that month / the sum of all sales in that month. 

The problem with this report is that it’s old, brittle, and really hard to maintain.  To get the data in place for this report, there are three or four stored procedures, two or three views, some VB6 code in an executable which runs every night as a scheduled task, AND the web code which does more calculations and the actual display.  The dates are hard-coded and have to be manually shifted in code every year.  It’s pretty sad.

I’ve been working to replace the above process with something a little more streamlined.  This is not live data, so I’ve got a single stored proc executed nightly by a SQL Server job to collect the data into the report table.  I’ve got another stored proc (the subject of this article) which creates my dataset for the report.  Finally, I’m working on a SQL Server Reporting Services report which will replace this report with an interactive matrix report.

Here was my problem.  I’ve got a report table that has been built up by the new query I wrote (or by the three or four stored procedures, two or three views and VB6 scheduled task).  It’s essentially a summation of sales grouped by location, department, category, year, and period.  My report requires the following data elements:

• location
• department
• category
• year
• period
• store level department sales total
• store level category sales total
• store level period sales total
• company level department sales total
• company level category sales total
• company level period sales total

I need those sales totals to figure out the percentages correctly.

So I essentially have six different selects, one per total based on the different filter criteria required to get those totals.  At first glance, I was stuck trying to figure out how to get the results of those six different queries to feed into one single query output result.  I knew I could accomplish the task with a cursor and a temporary table, but I didn’t relish the though of spinning through each record (and we’re talking about 362,000 source records) six times.

It was at this point that Keith gently reminded me about correlated subqueries.  Then he actually solved the problem for me because I didn’t actually see initially how correlated subqueries could help.  While correlated subqueries function like looping mechanisms, if you look up correlated subqueries, you’ll see this definition.  “Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query.”  Huh?  Let’s break it down with an example from SQL Server Books Online.

 This query finds the names of all authors who earn 100 percent of the shared royalty (royaltyper) on a book.

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE 100 IN
   (SELECT royaltyper
   FROM titleauthor
   WHERE titleauthor.au_ID = authors.au_id)

Here is the result set:

au_lname                                 au_fname             
---------------------------------------- -------------------- 
White                                    Johnson              
Green                                    Marjorie             
Carson                                   Cheryl               
Straight                                 Dean                 
Locksley                                 Charlene             
Blotchet-Halls                           Reginald             
del Castillo                             Innes                
Panteley                                 Sylvia               
Ringer                                   Albert

Notice that in this example, the outer query is actually tied in to the inner query.  You can’t execute the inner select by itself.  This is not exactly the behavior I needed.  I had six related queries which could each be executed individually and I needed to stitch them together.  This is where Keith showed me how correlated subqueries could be used to assist me.

Let’s start with a simple case.  Let’s look at what it would take to get the Store Level Department Totals and the Company Level Department Totals into the same resultset

SELECT x.Department, 
       x.Category, 
       x.Year, 
       x.Period, 
       x.CorporateSalesTotal, 
       y.StoreSalesTotal 
  FROM (big query 1) x — big query 1 retrieves company level totals
  left outer join (big query 2) y — big query 2 retrieves store level totals
    on x.Department= y.Department
   and x.Category= y.Category
   and x.Year = y.Year 
   and x.Period = y.Period 

 Sweet!  Now I just substitute in my queries for big query 1 and big query 2.  Voilá!  My query has the combination of data elements.  Now I just have to repeat the pattern a few times to stitch together my six queries.  Here is the resultant query using several correlated subqueries. 

By taking out the Order By statement, I cut the time it takes to process this query in half thanks to a quick look at the query’s execution plan. However, I'm not sure if I can take that hit as it appears to be screwing with the sort order in the report. I'm still working on the report however and will eventually be posting about my trials with that.  Even with the Order By statement, with the proper indexes on the source table I can get my results out in less than 45 seconds.  I feel this is pretty good performance on a source table with 362,000 rows.

Windows Usability in Atlanta (Aug. 22nd - 29th)

Texas Hold'em FREE on Xbox Live

Read the full story here: http://www.xbox.com/en-us/community/news/2006/0815-texasholdem.htm

AWE Memory

I just learned about something new.  Way back in the Windows NT 4.0 days, I learned that Windows allowed software to use up to 2 Gb of memory.  The boxes could actually hold up to 4 Gb, but the memory was usually split 2 for the app, 2 for the OS.  Since then, 32 Windows versions have dramatically increased the addressable amounts of memory (my notable interactions have always been on gigantic SQL Server boxes with gigs and gigs of memory).  How does this work?  AWE Memory!

AWE stands for the Address Windowing Extenstion and is an API which allows applications to use physical memory beyond the 32 bit virtual address space.  This allows x86 processor based machines to see up to 64 Gb of memory.

Learn more here.

Windows Freeware megalist

We’re all familiar with how frequently Microsoft releases some tool or component or add-on that helps us do our jobs better.  Frequently these products receive lots of coverage and are the focus of large product groups.  Take Virtual PC and Windows SharePoint Services for example.  However, for each large example like that, there are dozens of smaller tools and powertoys developed by Microsoft folks which don’t get the same levels of coverage.  A friend of mine stumbled upon this massive list of Microsoft freebies – some of which I knew about, most I’d never heard of.  Check it out!

• 150 Windows Freeware applications
• 150 Microsoft Office Freeware applications and addins

Thanks go out to Blake Handler for compiling these Ultimate Lists

[Edit] - link corrected

SharePoint 1, 2, 3! Seminiar presentation goes down smoothly

Excellent turnout in terms of percentages at last night’s SharePoint 1, 2, 3! Level 200 presentation.  We had 23 people registered to attend and where we normally experience between 25% and 40% drop-off, last night we saw only a 17% drop-off.  That means that 19 people were in attendance last night.  Several of these were new faces to user groups, although a bunch of them had been at the Level 100 presentation.  Thanks again to our sponsor CorasWorks for providing pizza and beverages last night.

We sat in a new room in the Microsoft offices last night due to the large number of regional Microsoft TS folks occupying our normal meeting space for some kind of six state regional meeting.  Anyway – the room that our user group got was actually ideal for the group.  We all sat around a single large table and were able to discuss round table style all of the various topics that came up.

The evening went well, but that doesn’t mean that we didn’t have any problems last night.  For some reason, the air conditioning was turned off and especially at the end of the evening we were sweltering.  One of our demos crashed right at the end and we were short enough on time that we just moved on past it.  Time being our enemy, we were unable to present three of our scheduled demos.  I guess we might have spent too long in roundtable discussions about SharePoint.

Tonight is the Hands on Labs presentation and we’ve got plenty of room thanks to DeVry University allowing us into their computer labs.  The room holds nearly 50 individual PCs!  That’s a major step up from the Intellinet lab which only holds 16.  Please Register for tomorrow (hands on lab) and join us.

*** NOTE *** Food is not allowed in DeVry’s lab so we will not be providing any.  Sorry!

SharePoint 1, 2, 3! Level 200 tonight and tomorrow night

There is still room for attendees.

• Register for tonight (seminar presentation)
• Register for tomorrow (hands on lab)

Thanks in advance to everyone for coming out and supporting us!

Wedding season draws to a close

Last weekend was filled with wedding festivities as one of Kim’s best friends Amy married her long time beau John.  Kim was one of 5 bridesmaids which meant she has been working on this wedding for quite some time, what with bridal showers, bachelorette parties, and whatnot.  My particpation was limited to a brunch at McCormick & Schmick’s, a rehearsal dinner at Maggiano’s, and finally all of the food at the reception itself.  I feel like I ate my way through Atlanta last weekend.  I also met some really nice people that I hope to see again someday and in general had a blast.

Congrats to John &Amy and best of luck for a future of wedded bliss.

With this wedding over, the next one of our friends to get married is actually Jennifer out in California next month, but Kim and I unfortunately can’t make it.  After that we have one in May next year.  That’s it!  I guess next up is a crop of babies.

CompUSA on Holcomb Bridge store closing sale

I don’t know if anyone is interested, but the CompUSA right off the GA 400 exit 7, Holcomb Bridge is closing its doors and is selling everything in the store at a discount.  Right now everything is marked down 10% – 30%.  System memory, hard disks, I/O components, and video cards are all marked down 15%.  I went there yesterday and put about $350 worth of stuff in a basket, but I decided against getting any of it before I walked out of the store.  The stuff I’d picked up was pretty frivolous and not anything I really needed.  However, you can get some decent savings.

One note of caution – all sales are final.  You can’t even return the product to other CompUSA stores.  So you’d have to ask yourself if $22.50 off a $150 item is worth not being able to return a defective product to you.  Keith decided it wasn’t and put down a Hauppauge WinTV PVR.  I’d put this product down myself, but not for that reason.  I figured manufacturer warrantees would have been fine if the thing was broken.  My problem is that my PC doesn’t have USB 2.0 and isn’t near the cable outlet.

Netsh - a hidden commandline gem

The most recent issue of Windows IT Pro magazine has a short 1 page article from Mark Minasi about Netsh Tricks.  The article piqued my interest so I went out to learn a little more about it.

Netsh is the result of a joint effort between Microsoft and Cisco Systems back in the early Windows 2000 days.  Netsh allows you to control IP addresses, gateways, DNS servers, and more from the command line.  It also lets you do some neat troubleshooting tasks.

Try out Netsh yourself.  Hit Start…Run, type CMD and hit OK.  Type “netsh ?” and you’ll get a list of 14 different context sensitive commands.  Let’s take a quick look at how to change the IP address of your machine.  This can be useful if you’re setting up a lab and you want to keep it partitioned by subnet from other labs.  You could run the following commands:

View the network IP configuration with this command:
       netsh interface ip show config
Get these results
       Configuration for interface "Local Area Connection"
           DHCP enabled:                         Yes
           InterfaceMetric:                      0
           DNS servers configured through DHCP:  10.1.0.5
                                                                                10.1.0.4
                                                                                10.1.0.10
           WINS servers configured through DHCP: 10.1.0.9
           Register with which suffix:           Primary only

 Change your machine’s static IP address with these commands
       netsh interface ip set address name="Local Area Connection"
       netsh interface ip set address local static 150.100.1.2 255.255.255.0 150.100.1.1 1

Having problems with your PC’s network connection and want to completely rebuild the entire IP stack?  Try this command:
       netsh int ip reset c:\iplog.txt

If that doesn’t work you could try resetting the entire Winsock catalog, which might be necessary if you’ve recently been the victim of spyware
       netsh winsock reset

Want to get an HTMLview of every part of your system including OS build number, service pack number, free memory, and more?  This can be accomplished by this command
       netsh diag gui

Netsh can be a very powerful tool as it eases configuration changes via scripting.  Learn more about Netsh from the Microsoft Documentation

Need an Xbox or Xbox 360 game?

I have a few here at work:

Learning about Report Builder at the AtlantaMDF on August 14 2006

Charlie Arehart of ColdFusion fame is reaching out more and more to the .NET development community, as he did yesterday with his presentation of the relatively new SQL Server Report Builder tool.  Report Builder is actually a part of the much larger SQL Server Reporting Services product from Microsoft, and like SSRS itself, Report Builder is a free tool enabling your end users to generate their own ad-hoc reports.  The best part?  It works with SQL Server 2005 AND SQL Server 2000.  Let’s face it, it will be somewhere near 2008 before I’m able to migrate my company’s database engine to the new version.

Charlie did a great job with his presentation and he’s clearly comfortable in front of a crowd.  If you’re interested in the topic and you missed his presentation, you’re the poorer for it.  Charlie’s slide deck will be online on the AtlantaMDF website soon, but in the meantime you can check out these useful bits of information.

• Ad-hoc reporting presentation from Microsoft
• Mike Gunderloy’s Introduction to SQL Server Report Builder at Developer.com
• Microsoft Virtual Lab with SQL Server Report Builder
• SQL Magazine Report Builder article
• Microsoft’s Reporting Services homepage

I hope these links are useful.  Watch the site www.GotReportBuilder.com (owned by Charlie) as it gets built into a clearinghouse of helpful information about SSRS and the Report Builder.  Maybe the above links will soon appear on that site!

Reverse engineer or design a database with Microsoft Visio

I’m working on rewriting an application that handles part of the promotions and advertising sales here at work.  The application was written back in 1997 or 1998 by an Access developer who didn’t seem to really have a firm grasp on concepts like normalization and whatnot.  The application itself is a nightmare, but that is a different story.  What I’m dealing with here today is the database itself.  I’m going to show you how you can use Microsoft Visio (I’m using Microsoft Visio for Enterprise Architects SP-2) to reverse engineer your database to get nice drawings, and how to turn nice drawings into actual database objects.

Part 1: Reverse Engineer your database

 This crappy drawing here is what things looked like before I got my hands on them.  What this diagram doesn’t show you is the extreme amount of duplication of data.  There are two tables, PromoAct and PromoDtl which contain identical data save for one flag field in PromoAct.  The PromoMchCD table has nearly 900 entries to represent only 60 unique “merchandise codes” thanks to misplaced data elements being present in the table.  Notice the collection of tables with no relationships to the other tables despite shared key values.  There are also several tables here which are not used or serve no meaningful purpose anymore but have never been deprecated in the database.  This kind of thing drives me nuts and the code is so hard to follow and so brittle that its universally regarded as a huge pile of poo.

To get this type of drawing of existing database objects it’s a simple matter of connecting to the database, selecting the objects you want, and hitting GO!  Let’s look at the steps required to actually get this done. 

First up you simply open Visio and create a new Database Model Diagram as I’m doing in this screenshot on the right.  This gives you a database menu option in the menu bar with several helpful commands.  There are other database modeling types like ER diagrams and ORM diagrams, but those don’t allow you to make changes to your diagram and update the database or generate new databases later.  Now that you have your new, blank document we can use the Reverse Engineer Wizard to connect to a database and generate our drawing automatically.  On the menu bar, select Database…Reverse Engineer to launch the wizard.  The first step in the Reverse Engineer Wizard is to use your ODBC data sources to find the database.  For those of you who don’t remember ODBC data sources, they were around before you could specify connection strings in app.config and web.config files.  If you don’t already have an ODBC connection for your database, you can create a new one inside of the wizard.  Use the screenshots at the bottom of this article to guide you through creating a new User Data Source for use with Visio.

Once you have selected your database connection object, pick which types to reverse engineer (tables, keys, indexes, etc) and then pick the objects you want to reverse engineer.  You are first presented with a checkbox list of tables and views followed by a checkbox list of stored procedures.  Finally you are given the option to allow the system to add the shapes to the page or to add the shapes manually.  I recommend allowing the system to do the work.  The last screen you are presented is basically a review of your selections.  Click Finish to have the system design the drawing.  The resulting drawing will have all the primary keys, foreign keys, relationships, etc.  When you click on an object such as a a database table you’ll get the ability to look into the table’s design; column names, indexes, triggers, etc.  Here is an example of just such a view.

 Part 2: Generate a database from a drawing

Now let’s take a look at things from the other side.  In my particular case I felt that I needed to start from scratch to fully redesign my database, so I began with a blank Database Model Diagram and built up each table individually.  To design a new table, drag an Entity object from the Shapes toolbox onto the drawing page and modify its database properties as I have done in this screenshot.  To create a relationship between two tables, drag a relationship object from the Shapes toolbox onto the drawing page.  Drag the arrow end onto the parent table and the other end onto the other table.    Visio will attempt to automatically identify the relationship between your tables based on the conceptual names of your tables’ fields.  This example shows just such a relationship

I worked on my table and relationship details until I had this design.  I was able to normalize the data, reuse some existing tables, and basically reform the data architecture underneath this application to something that seemed to be a lot more reasonable.  Now, with this kind of a table layout, I’m properly positioned to use custom business objects or an OR Mapper like the one from Paul Wilson.  The old architecture made that choice difficult if not impossible.

Now that I have my nice new database object design, I want to create these tables.  Actually, what I really want is the DDL script I can edit and use in any database to generate these tables.  In my case, some of the tables represented in my drawing pre-exist and don’t need to be changed in any way.  To do this, I go back to the handy Database menu and select Generate from the list.  The first thing that will happen is that the model will be checked for any errors and warnings – things like similar field names with different data types or mismatched relationships.  If I have any errors I must fix them first, much like in Visual Studio.  Once I pass the error checking, I am presented with the Generate Wizard – .  The Generate wizard allows me to chose a file location for a DDL script or I can actually have Visio connect to the database engine for me and create a brand new database.  Chose to generate the script and click next to get to the database specification portion.  Here you can pick which type of database rules you’re going to follow, SQL Server, Access, Oracle, DB2, etc.  Pick SQL Server, give your database a name for use in the script and click Next.   You will be presented with a review of everything you are about to export.  Press next to do an automatic physical review of your model against that database’s rules.  Once the validation is completed you can press Finish to generate the DDL file.  Even though you might not have selected to generate the new database, you might be presented with a SQL Server Create Database dialog.  Click Close on this dialog to get to the option to view the generated DDL script in the Visio code editor.  Here is a screenshot of that generated code:

Now that you have this code file you can modify it and run it in query analyzer (yes – I’m still running SQL 2000) against any database you like.  The best part is that you can use a tool like Visio to maintain your database.  You can easily reverse engineer a subset of tables in a database, check on relationships, etc.  You can make modifications and persist those changes back to the database.  This is an easy way to learn about your database at a glance.  There is an Update command on the Visio database menu that I didn’t go into.  This command allows you do generate change scripts, detect differences, log errors, etc.  All in all, Visio can be an important tool in your toolbox.

 Appendix: Create an ODBC Data Source from within Visio

Step 1		Step 2		Step 3
Step 4		Step 5		Step 6
Step 7		Step 8		Step 9
Step 10

Where to go for SharePoint 2007 - BETA 2 info