I was recently faced with a difficult performance issue. In an effort to save a client some time and money I convinced them that a view-based dimensional model was the way to go over a table/ETL based solution. I will blog about when this option is an option and the benefits to going this route some time in the near future. Anyhow, based on this decision I ended with a performance issue that I wouldn't have otherwise had.
The view-based dimensional database consisted of 30-40 fact and dimension views. As I created these views I made sure to test them for performance issues and tuned accordingly. I tested each view on its own and I tested individual joins between fact and dimension views.
Everything was working fine until I began creating sample queries to show end-users possible queries they could write against the new database. What I found was that when a particular dimension view was joined to a fact view along with one or more other dimension views I had unexpected performance issues. Queries that should take a few seconds to execute were taking 45-50 seconds.
In looking at the execution plans I saw that the optimizer was not doing what I had expected based on what I had seen for plans for the views executed on their own. I saw weird things like LEFT OUTER joins instead of INNER joins to my views resolving or partially resolving the issue.
I couldn't understand why the optimizer would treat the SQL in my views differently now that it was joined to other tables or views. To be honest, I'm still not sure. What I am sure of is that there is a query hint that forces the optimizer to create a plan for the view portion of the SQL irrespective of any other objects in the statement by forcing the optimizer to preserve the table/join order contained in the view definition. This hint is the FORCE ORDER hint and can be added to the end of the SQL statement using the following syntax.
OPTION(FORCE ORDER)
Below is an example.
SELECT *
FROM table1 t1
INNER JOIN view1 v1 ON t1.col1 = v1.col3
OPTION(FORCE ORDER)
Sunday, August 29, 2010
Friday, August 27, 2010
Windows 7 DSN Creation Guide
Lately I've been getting a lot of requests for step-by-step documentation for non-technical end-users. Well actually not just lately. I always have. But lately I've been getting tired of writing the same document over and over so I've begun to create a little library of step-by-step guides as well as templates for standard database related documentation.
My latest request was for creating a SQL Server DSN on Windows 7. Attached are the steps for creating one. You can find these same instructions in an MS Word document that contains screen-shots for the entire process here.
Follow the steps below to create a DSN on a Windows 7 machine. These steps are also very similar for prior Windows versions.
Click Start->Run. Type ODBCAD32.exe and hit enter.
Another option would be to open the Windows Control Panel and then open Administrative Tools. You should see Data Sources (ODBC). Double click this icon.
Click on the System DSN tab.
Click Add.
Select the appropriate driver.
Click Finish.
The steps below are specific to setting up a SQL Server Native 10.0 DSN.
Enter the Name you wish to use for the DSN. This is usually the name of the database you’re connecting to or if you intend to use it for multiple databases you can use the server name.
Enter the name of the Server your database(s) reside on.
*If your database(s) reside on a local non-named instance of SQL Server your Server name should be localhost or the name of your machine. If you are running a named instance of SQL Server (which is the case for the default installation of SQL Server Express Edition) your Server name should be something like localhost\SQLExpress.
Click Next.
Choose your authentication type. If your Windows Active Directory account has the necessary privileges use Integrated Windows authentication. Otherwise use SQL Server authentication with a SQL Server user name and password that has the necessary level of permissions for the database you are connecting to.
Click Next.
Click the Change the default database to checkbox and select the database you want to connect to. This will be the default database when using the DSN. If you intend to connect to multiple databases you can leave this box unchecked.
Click Next.
Click Finish.
Click the Test Data Source . . . button to ensure that the DSN has been configured correctly.
Click Ok.
Click Ok.
Click Ok.
My latest request was for creating a SQL Server DSN on Windows 7. Attached are the steps for creating one. You can find these same instructions in an MS Word document that contains screen-shots for the entire process here.
Follow the steps below to create a DSN on a Windows 7 machine. These steps are also very similar for prior Windows versions.
Click Start->Run. Type ODBCAD32.exe and hit enter.
Another option would be to open the Windows Control Panel and then open Administrative Tools. You should see Data Sources (ODBC). Double click this icon.
Click on the System DSN tab.
Click Add.
Select the appropriate driver.
Click Finish.
The steps below are specific to setting up a SQL Server Native 10.0 DSN.
Enter the Name you wish to use for the DSN. This is usually the name of the database you’re connecting to or if you intend to use it for multiple databases you can use the server name.
Enter the name of the Server your database(s) reside on.
*If your database(s) reside on a local non-named instance of SQL Server your Server name should be localhost or the name of your machine. If you are running a named instance of SQL Server (which is the case for the default installation of SQL Server Express Edition) your Server name should be something like localhost\SQLExpress.
Click Next.
Choose your authentication type. If your Windows Active Directory account has the necessary privileges use Integrated Windows authentication. Otherwise use SQL Server authentication with a SQL Server user name and password that has the necessary level of permissions for the database you are connecting to.
Click Next.
Click the Change the default database to checkbox and select the database you want to connect to. This will be the default database when using the DSN. If you intend to connect to multiple databases you can leave this box unchecked.
Click Next.
Click Finish.
Click the Test Data Source . . . button to ensure that the DSN has been configured correctly.
Click Ok.
Click Ok.
Click Ok.
Subscribe to:
Posts (Atom)