Feature in Focus – Deep Fetch Queries

This is the first in a series of posts that will be tagged Feature in Focus (FiF). The goal of these entries will be to further high-light some of the new and interesting ways in which SQL Sets allows users to capture, organize, share and collaborate on their relational data.

Today I'm going to talk about a feature that we call a Deep Fetch Query. Deep Fetch is simply a catchy name for what is essentially a recursive parent-child query. A recursive parent-child query is a query in which the rows for some root table (top level table) are returned along with related rows in that table's children, their children's children, their children's children's children and so on....you get the picture. It's a simple concept but also an incredibly powerful one. It's simple because it makes sense to want to look at and work with data in the context of its defined relationships to other data in a database. In a SQL Sets sense, it's powerful because we have a facility that lets you retrieve data in this manner in one simple step. The power of being able to do this quickly and easily is magnified as the size and complexity of the database grows. Here's how it works:

1. Select new Deep Fetch Query from main menu (by right-clicking on a table in the database explorer)
2. Choose the database connection and table you would like to run the Deep Fetch against
3. Enter the query criteria to filter appropriate rows on this table (root parent)
4. Click Execute  SQL Sets will interrogate database catalog and build appropriate query script to run recursive parent-child query...and then run it.
5. Data is returned

Let's take a look at an example. We're going to use the DotNetNuke database as our sample database. For those not familiar with the DotNetNuke project, it is an open source CMS for the ASP.NET platform. The top level entity in this database schema is the Portal table. A Portal can contain multiple pages (Tabs) and each page can contain multiple modules (TabModules). These pages and modules in turn have many other attributes and entities associated with them. So, starting from the top:

1. Select Portals in the Connections Explorer, right click and choose New Deep Fetch Query. The Deep Fetch Query dialog is displayed. Enter 1 in the PortalID value column.

2. Click Execute

3. The recursive parent-child query script is generated, executed against the database and the results are returned. In this case, queries were created for 45 tables and resulted in 1748 rows being returned.

Let's take a closer look at what we got for our money.....

a) Excerpt of of generated script... this one shows the TabModules table which is 1 tables removed from the Portals table (via the Tabs table) and the TabModuleSettings table which is 2 tables removed via the Module and TabModules tables respectively.

--[dbo.TabModules]
SELECT [dbo].[TabModules].*
FROM [dbo].[Portals] INNER JOIN [dbo].[Tabs]
      ON [dbo].[Portals].[PortalID] = [dbo].[Tabs].[PortalID]
   INNER JOIN [dbo].[TabModules]
      ON [dbo].[Tabs].[TabID] = [dbo].[TabModules].[TabID]
WHERE [dbo].[Portals].[PortalID] = 1

--[dbo.TabModuleSettings]
SELECT [dbo].[TabModuleSettings].*
FROM [dbo].[Portals] INNER JOIN [dbo].[Modules]
      ON [dbo].[Portals].[PortalID] = [dbo].[Modules].[PortalID]
    INNER JOIN [dbo].[TabModules]
      ON [dbo].[Modules].[ModuleID] = [dbo].[TabModules].[ModuleID]
    INNER JOIN [dbo].[TabModuleSettings]
      ON [dbo].[TabModules].[TabModuleID] =[dbo]. [TabModuleSettings].  [TabModuleID]
WHERE [dbo].[Portals].[PortalID] = 1

B. Results pane.... the data for each of the 45 related tables are placed into a separate tab and grid.

C. This data can be easily navigated using either the Table Navigator or the Relationship Navigator. In the case of the Relationship Navigator, the tree is arranged hierarchically by relationship. In the case of the Table Navigator, they are arranged sequentially in alphabetical order.

Hopefully this example showed how simple and powerful the Deep Fetch Query is. Now that we've created a SQL Set from a Deep Fetch Query, we can save it as a Portable Data Document and share it with co-workers and business partners or use for our own purposes. The bottom line is that in the time that it took you to read the first two paragraphs of this post, you would have been able to do the same thing with your own SQL Server databases and pulled back related data from 10, 20, 50 or 100 or more tables. Thanks for reading and please give us feedback as to how we can make this feature even better!