I love the PIVOT and UNPIVOT commands. They have saved me a ton of time on quite a few projects. UNPIVOT especially, since many times when working on BI projects I'm handed a monster spreadsheet by someone on the business side. Business people like seeing pivoted data. Database people like seing normalized data. I won't get into the keywords/commands here since there is plenty of material out there, most of which do a better job than I ever could of explaining their use.
However, I will tell you that SQL Server does not like "UNPIVOTing" a large number of columns as I recently found out. I should note that the query I have contains a few nested SELECTs (which I'm sure is part of the problem). I don't know the internals of what the database engine is doing when you using these commands but I do know they produce some pretty ugly execution plans. Since I just started this project and have a year of work to deliver in about 2 months (I'm not kidding) I haven't had the time to deconstruct the execution (nor should I really be writing about this topic).
What I have figured out is that if I break up a 280+ column UNPIVOT into 20 column chunks (an unpivot for every 20 columns - yes this is a maintenance nightmare) then I get reasonable performance (a couple of minutes to execute) whereas my original query never completed.
There's no way I'm going to be ok with having 15 queries instead of one to solve a performance issue but this is what I know for now and I'm able to present a working proof-of-concept. I hope to have a better understanding of the issue and as a result, the solution soon. I'll keep you posted. In the meantime, if you know what's going on please share . . .
Monday, March 29, 2010
Sunday, February 28, 2010
Clustered Index Doesn't Have to be the Primary Key
Believe it or not, you don’t have to cluster your primary keys.
Most SQL Server mortals assume that their primary keys should be clustered because that is the default behavior of SQL Server Management Studio when using the designer to create a table. Unfortunately, the primary key is not always the best candidate for the one and only clustered index you're allowed on a table.
I won't go into detail here about what a clustered index is but I'll say that it is essentially your table and the order in which your table rows/records/tuples are stored is based on the column(s) used in your clustered index.
In an OLTP database where your pimary keys consist of a single integer identity column you're usually in good shape if your clustered index is also your primary key.
However, if you use composite and/or "intelligent" keys you're going to experience a number of performance problems. I won't expound on these problems here because I almost never use intelligent or composite keys in an OLTP system and don't believe they should be used in such systems.
The place I most often have a non-primary key clustered index is in an OLAP fact table. OLAP systems are more concerned about high performance reads of blocks of data as opposed to reads and writes of individual rows as is the case in transactional systems.
In a data warehouse/OLAP database date is often an attribute used in most analyses. By creating a clustered index on the most important date in a fact table (i.e. order date in an order table), you enable better I/O and therefore faster query response times for the majority of queries executed.
Some might argue that the unique surrugate key of the fact table (if you even have one) should have a clustered index. Hmmm . . . how often do you query a fact table and have a surrogate key based predicate? The answer should probably be never (although I almost never say never)!
How about writes? Fact tables are often written to based on a date. By clustering on date we typically have sequential writes to the disk. When we load today's data we end up adding data to the end of the last data page for the table because today's date comes after yesterday's date. If we were to cluster on some kind of an intelligent key we'd end up having to insert rows into pre-existing data pages which leads to much higher I/O and therefore decreased performance.
How about archiving and partitioning? In most OLAP scenarios the business requirement is to have something like 25 months worth of data available in the system at all times. It's often beneficial for performance and disk expense reasons to take old data offline. The best way to do this in a SQL Server environment is to partition your fact tables using a year/month based partition function. For partitioning to be successful it is necessary for your partition column to be part of the clustered index. If you cluster on your date column and partition on your date column as well, you're all set.
In short, if you're working in a data warehouse/OLAP environment pay careful attention to what column(s) you create your clustered indexes on. It can make or break the performance of your solution!
Most SQL Server mortals assume that their primary keys should be clustered because that is the default behavior of SQL Server Management Studio when using the designer to create a table. Unfortunately, the primary key is not always the best candidate for the one and only clustered index you're allowed on a table.
I won't go into detail here about what a clustered index is but I'll say that it is essentially your table and the order in which your table rows/records/tuples are stored is based on the column(s) used in your clustered index.
In an OLTP database where your pimary keys consist of a single integer identity column you're usually in good shape if your clustered index is also your primary key.
However, if you use composite and/or "intelligent" keys you're going to experience a number of performance problems. I won't expound on these problems here because I almost never use intelligent or composite keys in an OLTP system and don't believe they should be used in such systems.
The place I most often have a non-primary key clustered index is in an OLAP fact table. OLAP systems are more concerned about high performance reads of blocks of data as opposed to reads and writes of individual rows as is the case in transactional systems.
In a data warehouse/OLAP database date is often an attribute used in most analyses. By creating a clustered index on the most important date in a fact table (i.e. order date in an order table), you enable better I/O and therefore faster query response times for the majority of queries executed.
Some might argue that the unique surrugate key of the fact table (if you even have one) should have a clustered index. Hmmm . . . how often do you query a fact table and have a surrogate key based predicate? The answer should probably be never (although I almost never say never)!
How about writes? Fact tables are often written to based on a date. By clustering on date we typically have sequential writes to the disk. When we load today's data we end up adding data to the end of the last data page for the table because today's date comes after yesterday's date. If we were to cluster on some kind of an intelligent key we'd end up having to insert rows into pre-existing data pages which leads to much higher I/O and therefore decreased performance.
How about archiving and partitioning? In most OLAP scenarios the business requirement is to have something like 25 months worth of data available in the system at all times. It's often beneficial for performance and disk expense reasons to take old data offline. The best way to do this in a SQL Server environment is to partition your fact tables using a year/month based partition function. For partitioning to be successful it is necessary for your partition column to be part of the clustered index. If you cluster on your date column and partition on your date column as well, you're all set.
In short, if you're working in a data warehouse/OLAP environment pay careful attention to what column(s) you create your clustered indexes on. It can make or break the performance of your solution!
Thursday, January 14, 2010
Derived Table, Subquery, Correlated Subquery, etc.
I was recently attempting to explain to someone the difference between derived tables and subqueries. I didn't do a great job and came to the conclusion that I wasn't completely clear on the terminology. I don't like not knowing things so I did a little research and this is what I found . . .
A derived table is not what I thought it was. I always referred to a derived table as being a query in a FROM clause. For example:
SELECT
id AS EmployeeId,
name AS EmployeeFullName,
COUNT(*) AS PaychecksIssuedCount
FROM
(SELECT
id,
name
FROM
employee
WHERE
employee_type_cd = 101) emp
INNER JOIN paycheck ps
on emp.id = ps.emp_id
Apparently this is actually called a subquery and can be referred to as a subquery in a FROM clause. It's a bit of symantics but a derived table is actually the result of a table subquery.
For some reason people that use SQL Server (like me) tend to refer to this as a derived table. I guess I'll have to stop doing that now.
As for correlated subqueries, these can be found in the list of projected columns as well as the WHERE and HAVING clauses. For example:
SELECT
id,
name
FROM
employee emp
WHERE
years_employed =
(SELECT
AVG(years_employed)
FROM
employee emp_avg_years
WHERE
emp_avg_years.business_unit = emp.business_unit)
As you can see, the subquery has a WHERE clause that refers (correlates) to the outer/parent query.
So there you have it. Stop saying derived tables! Most likely you're misusing it the way I have ever since I learned about the concept (of subqueries - not derived tables ;)). Or maybe you're not as anal as I am. However, if you've read this far I'd say you are.
A derived table is not what I thought it was. I always referred to a derived table as being a query in a FROM clause. For example:
SELECT
id AS EmployeeId,
name AS EmployeeFullName,
COUNT(*) AS PaychecksIssuedCount
FROM
(SELECT
id,
name
FROM
employee
WHERE
employee_type_cd = 101) emp
INNER JOIN paycheck ps
on emp.id = ps.emp_id
Apparently this is actually called a subquery and can be referred to as a subquery in a FROM clause. It's a bit of symantics but a derived table is actually the result of a table subquery.
For some reason people that use SQL Server (like me) tend to refer to this as a derived table. I guess I'll have to stop doing that now.
As for correlated subqueries, these can be found in the list of projected columns as well as the WHERE and HAVING clauses. For example:
SELECT
id,
name
FROM
employee emp
WHERE
years_employed =
(SELECT
AVG(years_employed)
FROM
employee emp_avg_years
WHERE
emp_avg_years.business_unit = emp.business_unit)
As you can see, the subquery has a WHERE clause that refers (correlates) to the outer/parent query.
So there you have it. Stop saying derived tables! Most likely you're misusing it the way I have ever since I learned about the concept (of subqueries - not derived tables ;)). Or maybe you're not as anal as I am. However, if you've read this far I'd say you are.
Wednesday, January 6, 2010
Should Foreign Key Columns be Indexed?
People seem to be confused when it comes to indexing of foreign key columns. For one, I believe some people assume that when you create a foreign key constraint (in SQL Server) an index is also created on the column(s) that make up the key/constraint. This is not the case.
That leads us to the original question . . . Should we create indexes on our foreign keys??
I believe that in most cases the answer is yes.
There are a few things to consider here:
1) Constraint checks
2) Cascading Updates and Deletes
2) Joins
1) If you delete a row in a table whose primary key or unique constraint is referenced by one or more foreign keys, SQL Server will search the foreign key tables to determine if the row can be deleted (assuming you have no cascade behavior defined). An index on the foreign key column(s) will speed this search.
2) The same is also true for update and delete cascades. If either are defined, the child/foreign key rows must be identified and updated and/or deleted. Again, the index helps find these rows more efficiently.
3) More often than not foreign key columns will be joined to primary key columns in queries. Joins are always more efficient when an index exists on both sides of the join.
If you ask me, it's a no-brainer to index your foreign key columns. How often do you have a database with foreign key relationships that don't fall under one of the 3 above scenarios??
That leads us to the original question . . . Should we create indexes on our foreign keys??
I believe that in most cases the answer is yes.
There are a few things to consider here:
1) Constraint checks
2) Cascading Updates and Deletes
2) Joins
1) If you delete a row in a table whose primary key or unique constraint is referenced by one or more foreign keys, SQL Server will search the foreign key tables to determine if the row can be deleted (assuming you have no cascade behavior defined). An index on the foreign key column(s) will speed this search.
2) The same is also true for update and delete cascades. If either are defined, the child/foreign key rows must be identified and updated and/or deleted. Again, the index helps find these rows more efficiently.
3) More often than not foreign key columns will be joined to primary key columns in queries. Joins are always more efficient when an index exists on both sides of the join.
If you ask me, it's a no-brainer to index your foreign key columns. How often do you have a database with foreign key relationships that don't fall under one of the 3 above scenarios??
Monday, December 21, 2009
T-SQL End of Previous Week, Month, Quarter, Year
I recently had a requirement to capture periodic snapshots of an incident/activity type OLTP table into a fact table. I already had an accumulating snapshot type fact table where I was capturing the same facts on a daily basis. But, in addition to this, the client wanted the ability to perform period over period (weekly, monthly, quarterly, yearly) reporting.
My approach was simple enough and in case anyone was thinking about an overly complicated approach I figured I'd share . . .
After my daily load of the fact table I check to see if snapshot data exists for the prior week, month, quarter, and year. If any of these snapshots do not exist I simply do an INSERT selecting all data from the accumulating snapshot table and adding the As-Of date along with the level of periodicity (W,M,Q,Y).
I used 4 T-SQL if statements to determine if the pior period data exists and if it doesn't I call the INSERT.
Below are the statements used to capture the pior period as-of dates in an INT key format. The same format you would get converting a date to a VARCHAR using the 112 style (e.g. 20091231 for December 31st, 2009). I'll leave it up to you to apply this logic to your own INSERT statements.
--Previous end of month
DECLARE @EndOfWeekKey AS INT
SET @EndOfWeekKey = (SELECT CAST(CONVERT(VARCHAR,DATEADD(wk,-1,DATEADD(dd,-(DATEPART(dw, GETDATE()) - 7), GETDATE())),112) AS INT))
--Previous end of month
DECLARE @EndOfMonthKey AS INT
SET @EndOfMonthKey = (SELECT CAST(CONVERT(VARCHAR,DATEADD(d,-1,CAST(CAST(MONTH(GETDATE()) AS VARCHAR) + '/1/' + CAST(YEAR(GETDATE()) AS VARCHAR) AS DATETIME)),112) AS INT))
--Previous end of quarter
DECLARE @EndOfQuarterKey AS INT
SET @EndOfQuarterKey = (
SELECT
CASE
WHEN MONTH(GETDATE()) IN (1,2,3) THEN CAST(YEAR(GETDATE()) - 1 AS VARCHAR) + '1231'
WHEN MONTH(GETDATE()) IN (4,5,6) THEN CAST(YEAR(GETDATE()) AS VARCHAR) + '0331'
WHEN MONTH(GETDATE()) IN (7,8,9) THEN CAST(YEAR(GETDATE()) AS VARCHAR) + '0630'
WHEN MONTH(GETDATE()) IN (10,11,12) THEN CAST(YEAR(GETDATE()) AS VARCHAR) + '0930'
END)
--Previous end of year
DECLARE @EndOfYearKey AS INT
SET @EndOfYearKey = (SELECT CAST(CAST(YEAR(GETDATE()) -1 AS VARCHAR) + '1231' AS INT))
My approach was simple enough and in case anyone was thinking about an overly complicated approach I figured I'd share . . .
After my daily load of the fact table I check to see if snapshot data exists for the prior week, month, quarter, and year. If any of these snapshots do not exist I simply do an INSERT selecting all data from the accumulating snapshot table and adding the As-Of date along with the level of periodicity (W,M,Q,Y).
I used 4 T-SQL if statements to determine if the pior period data exists and if it doesn't I call the INSERT.
Below are the statements used to capture the pior period as-of dates in an INT key format. The same format you would get converting a date to a VARCHAR using the 112 style (e.g. 20091231 for December 31st, 2009). I'll leave it up to you to apply this logic to your own INSERT statements.
--Previous end of month
DECLARE @EndOfWeekKey AS INT
SET @EndOfWeekKey = (SELECT CAST(CONVERT(VARCHAR,DATEADD(wk,-1,DATEADD(dd,-(DATEPART(dw, GETDATE()) - 7), GETDATE())),112) AS INT))
--Previous end of month
DECLARE @EndOfMonthKey AS INT
SET @EndOfMonthKey = (SELECT CAST(CONVERT(VARCHAR,DATEADD(d,-1,CAST(CAST(MONTH(GETDATE()) AS VARCHAR) + '/1/' + CAST(YEAR(GETDATE()) AS VARCHAR) AS DATETIME)),112) AS INT))
--Previous end of quarter
DECLARE @EndOfQuarterKey AS INT
SET @EndOfQuarterKey = (
SELECT
CASE
WHEN MONTH(GETDATE()) IN (1,2,3) THEN CAST(YEAR(GETDATE()) - 1 AS VARCHAR) + '1231'
WHEN MONTH(GETDATE()) IN (4,5,6) THEN CAST(YEAR(GETDATE()) AS VARCHAR) + '0331'
WHEN MONTH(GETDATE()) IN (7,8,9) THEN CAST(YEAR(GETDATE()) AS VARCHAR) + '0630'
WHEN MONTH(GETDATE()) IN (10,11,12) THEN CAST(YEAR(GETDATE()) AS VARCHAR) + '0930'
END)
--Previous end of year
DECLARE @EndOfYearKey AS INT
SET @EndOfYearKey = (SELECT CAST(CAST(YEAR(GETDATE()) -1 AS VARCHAR) + '1231' AS INT))
Cognos 8.3 -- Framework Manager -- Model objects and shortcuts
I was recently tasked with creating a relational model in Cognos Framework Manager and got a little confused when deciding what kind of objects to use. I found myself asking if I should use a Query Subject or a Shortcut. Furthermore, it seemed there had been some changes to the Shortcut functionality since I had last used Cognos. I found the following that made it rather clear which object(s) I should use when creating my model.
Taken from the Cognos documentation . . .
The main difference between model objects and shortcuts is that model objects give you the freedom to include or exclude items and to rename them. You may choose to use model objects instead of shortcuts if you need to limit the query items included or to change the names of items.
Shortcuts are less flexible from a presentation perspective than model objects, but they require much less maintenance because they are automatically updated when the target object is updated. If maintenance is a key concern and there is no need to customize the appearance of the query subject, use shortcuts.
Framework Manager has two types of shortcuts:
● regular shortcuts, which are a simple reference to the target object.
● alias shortcuts, which behave as if they were a copy of the original object with completely independent behavior.
Alias shortcuts are available only for query subjects and dimensions. Regular shortcuts are typically used as conformed dimensions with star schema groups, creating multiple references with the exact same name and appearance in multiple places. Alias shortcuts are typically used in role-playing dimensions or shared tables.
Being able to specify the behavior of shortcuts is new to Cognos 8.3. When you open a model from a previous release, the Shortcut Processing governor is set to Automatic. When Automatic is used, shortcuts work the same as in previous releases, that is, a shortcut that exists in the same folder as its target behaves as an alias, or independent instance, whereas a shortcut existing elsewhere in the model behaves as a reference to the original. To take advantage of the Treat As property, it is recommended that you verify the model and, when repairing, change the governor to Explicit. The repair operation changes all shortcuts to the correct value from the Treat As property based on the rules followed by the Automatic setting, this means that there should be no change in behavior of your model unless you choose to make one or more changes to the Treat As properties of your shortcuts.
When you create a new model, the Shortcut Processing governor is always set to Explicit. When the governor is set to Explicit, the shortcut behavior is taken from the Treat As property and you have complete control over how shortcuts behave without being concerned about where in the model they are located.
Taken from the Cognos documentation . . .
The main difference between model objects and shortcuts is that model objects give you the freedom to include or exclude items and to rename them. You may choose to use model objects instead of shortcuts if you need to limit the query items included or to change the names of items.
Shortcuts are less flexible from a presentation perspective than model objects, but they require much less maintenance because they are automatically updated when the target object is updated. If maintenance is a key concern and there is no need to customize the appearance of the query subject, use shortcuts.
Framework Manager has two types of shortcuts:
● regular shortcuts, which are a simple reference to the target object.
● alias shortcuts, which behave as if they were a copy of the original object with completely independent behavior.
Alias shortcuts are available only for query subjects and dimensions. Regular shortcuts are typically used as conformed dimensions with star schema groups, creating multiple references with the exact same name and appearance in multiple places. Alias shortcuts are typically used in role-playing dimensions or shared tables.
Being able to specify the behavior of shortcuts is new to Cognos 8.3. When you open a model from a previous release, the Shortcut Processing governor is set to Automatic. When Automatic is used, shortcuts work the same as in previous releases, that is, a shortcut that exists in the same folder as its target behaves as an alias, or independent instance, whereas a shortcut existing elsewhere in the model behaves as a reference to the original. To take advantage of the Treat As property, it is recommended that you verify the model and, when repairing, change the governor to Explicit. The repair operation changes all shortcuts to the correct value from the Treat As property based on the rules followed by the Automatic setting, this means that there should be no change in behavior of your model unless you choose to make one or more changes to the Treat As properties of your shortcuts.
When you create a new model, the Shortcut Processing governor is always set to Explicit. When the governor is set to Explicit, the shortcut behavior is taken from the Treat As property and you have complete control over how shortcuts behave without being concerned about where in the model they are located.
Monday, December 14, 2009
SSIS Error Codes (Hresults)
Ever get an Hresult error code and have no idea what it meant or how to figure out what it's related to?
The SSIS run time engine and the data flow engine are written in native code. Therefore, many errors are returned as Hresults. In case your unfamiliar with what an Hresult is, below is a Wikipedia definition for Hresult.
In the field of computer programming, the HRESULT is a data type used in many types of Microsoft technology. HRESULTs are used as function parameters and return values to describe errors and warnings in a program.
An HRESULT value has 32 bits divided into three fields: a severity code, a facility code, and an error code. The severity code indicates whether the return value represents information, warning, or error. The facility code identifies the area of the system responsible for the error. The error code is a unique number that is assigned to represent the exception. Each exception is mapped to a distinct HRESULT.
Now that you know what Hresults are, you can look up their mappings (for SSIS) using the link below.
http://msdn.microsoft.com/en-us/library/ms345164.aspx
The SSIS run time engine and the data flow engine are written in native code. Therefore, many errors are returned as Hresults. In case your unfamiliar with what an Hresult is, below is a Wikipedia definition for Hresult.
In the field of computer programming, the HRESULT is a data type used in many types of Microsoft technology. HRESULTs are used as function parameters and return values to describe errors and warnings in a program.
An HRESULT value has 32 bits divided into three fields: a severity code, a facility code, and an error code. The severity code indicates whether the return value represents information, warning, or error. The facility code identifies the area of the system responsible for the error. The error code is a unique number that is assigned to represent the exception. Each exception is mapped to a distinct HRESULT.
Now that you know what Hresults are, you can look up their mappings (for SSIS) using the link below.
http://msdn.microsoft.com/en-us/library/ms345164.aspx
Subscribe to:
Posts (Atom)