About Robert Hatton

SQL Server uses an optimizer to figure out how to retrieve data and process a query. For every table, it evaluates roughly 350 different rules. It occurred to me that this would lead to exponential growth in the number of potential query plans. The following formula is one way to think about the problem of increasing the potential number of query plans as the number of tables in a query increases:

(t*350)^t

In the above query, ‘t’ represents the number of tables. It’s obvious that the optimizer would need to evaluate 350 rules per table (hence t*350). That’s actually not always true as the optimizer knows how to take some shortcuts, but it’s a reasonable number for this exercise. What’s less obvious is that the order that the optimizer evaluates rules for tables increases exponentially with the number of tables. This is expressed by the part of the formula that raises the result of the rules evaluation for each table to the power of the number of tables (^t). It turns out that this is an ‘NP complete’ problem.

What this means in the real world is that it quickly becomes less likely that  SQL Server’s optimizer will find the optimal execution plan once you have more than 6 tables in a join. That doesn’t mean that performance will always be awful with more that 6 tables in a join. It just means that it becomes much more likely. Once you encounter performance problems with this many tables in a query, there may be no other way to improve performance than to reduce the number of tables joined.

When you walk into a new project, it can be a challenge to build a mental image of what’s going on quickly. I could spend an hour just talking about how to get organized quickly. Instead of blathering on, here’s a script to query the system tables of the database to document it’s structure and then sample the actual data to help you see what’s actually in there.

The script will output the following columns: TableName, ColumnName, recordCnt, distinctCnt, nonNullCnt, NullCnt, fldSample. The general idea is to show you a list of the tables and columns followed by some counts of how many distinct values each table has along with how many records are null (or not). The real value is in seeing a sample of the values in each column (it’s in the fldSample column) . The script is set to limit the sample data to 200 characters, but that’s easy to change.
In any event, here’s the script:

USE [Datawarehouse]

I’ll be giving a presentation on “Designing for Analysis” at the SQL Saturday in San Diego this weekend. It’ll cover how to separate information into Facts and Dimensions and organize it into a Star Schema. Of course, that means I’ll have to explain what a Star Schema is and why it’s cool! The part I’m interested in seeing how people respond to is the description of how to organize the ETL (Extract / Transform / Load) to make everything work!

Here’s a PDF of the presentation:FactsAndDimensions

This is going to be a long post, but I see folks working way harder than needed to extract data for BI systems. Given the large quantities of data available in source systems, it’s obvious that it is not practical to transfer all of the data to the Business Intelligence solution on each extract. Incremental extracts are needed to cut quantity of data transferred down to a manageable level.

An incremental extract is simply the product of a program which will pull data from a source system for a specified date range.

It might be tempting to create an extract program which simply extracts data from the prior day. In actual practice, this often an undesirable approach. It is not unusual to encounter a situation where a given day or range of days worth of data needs to be re-extracted.

A much better way to implement an extract program is to provide the start and end dates at run time. This can be done by providing them as command line arguments when the extract program is invoked or by having the extract program pick the start and end date up from a database table or environmental variables.

When extract programs are designed to use start and end dates that are provided at run time, it is possible to query the existing data repository for the record loaded from that extract with the most recent date. This yields an extract facility which is, to a great degree, adaptive to the needs of the database. If a network failure prevents the data transfer for an entire day, no manual intervention is necessary. The next day when the extract runs it will extract data for two days instead of one. If you have to delete a week’s worth of data because of a problem with a source system then next execution of the extract will retrieve the appropriate amount of data.

While there are several ways to implement this type of extract, one way that is commonly supported is to create the extract program so that is gets the start and end date as command line arguments. Command line scripts can then be used to find the appropriate start and end dates then build the command line to start the program.

Using a Windows / SQL server environment as an example, the following SQL statement could be used to create a command line to start an extract program that would pull whatever data from a sales_facts table in the source system that is not currently in the data warehouse:

set nocount on

select ‘salesExtract ‘

+ (select convert(char(10), max(timestamp), 101) from sales_facts) + ‘ ‘

+ (select convert(char(10), dateadd(d, -1, getdate()), 101))

 

The above query would create the following output:

salesExtract 08/07/2013 08/08/2013

Assuming the above query was saved in a file named “salesExtract.sql” another batch file could be used to:

1. Create a batch file which would start the extract program
2. Start the extract program (which would create a text file in the landing area)
3. Delete the batch file

The following script would be saved as a batch file named “salesExtract.bat”, and would perform the steps outlined above.

sqlcmd -h -1 -s “localhost” -d “biDB” -i “salesExtract.sql” -o “salesExtractCurrent.bat” salesExtractCurrent.bat

del salesExtractCurrent.bat

In actual use, a job scheduler would be used to start the “salesExtract.bat” batch file, which would use the “salesExtract.sql” file to create a temporary batch file (“salesExtractCurrent”) that it would then execute. Once the extract was created in the landing zone, the salesExtractCurrent batch file would be deleted.

As previously mentioned, there are several ways to create an extract facility that will retrieve all of the data needed to bring the data repository current. The above example illustrates one approach that works in a variety of environments. However it is implemented, making the extract facility do more that simply retrieve the prior day’s activity will make the finished solution much more robust.

Starting at a new client usually means some exploring in databases to figure out how things work. The system tables can be used to find where a text string is used in stored procedures or column names. The following queries will find where a specific string is used. Just use the database of interest, set a value for the @searchStr variable, and run the query!

declare @searchStr as varchar(50)
set @searchStr = ‘%categoryID%’

–Find string in proc
select distinct OBJECT_NAME(id)
from syscomments
where OBJECTPROPERTY(id, ‘IsProcedure’) = 1
and [text] like @searchStr
order by OBJECT_NAME(id)

–Find table and column by name
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE @searchStr
ORDER BY schema_name, table_name;