Data Points: Inline Views Versus Temp Tables

Data Points: Five Ways to Rev up Your SQL Performance "Inline Views Versus Temp Tables
Queries sometimes need to join data to other data that may only be gathered by performing a GROUP BY and then a standard query. For example, if you want to return the information about the five most recently placed orders, you would first need to know which orders they are. This can be retrieved by using a SQL query that returns the orders' IDs. This data could be stored in a temporary table, a common technique, and then joined to the Product table to return the quantity of products sold on those orders:

CREATE TABLE #Temp1 (OrderID INT NOT NULL, _
OrderDate DATETIME NOT NULL)

INSERT INTO #Temp1 (OrderID, OrderDate)
SELECT TOP 5 o.OrderID, o.OrderDate
FROM Orders o ORDER BY o.OrderDate DESC

SELECT p.ProductName, SUM(od.Quantity) AS ProductQuantity
FROM #Temp1 t
INNER JOIN [Order Details] od ON t.OrderID = od.OrderID
INNER JOIN Products p ON od.ProductID = p.ProductID
GROUP BY p.ProductName
ORDER BY p.ProductName

DROP TABLE #Temp1

This batch of SQL creates a temporary table, inserts the data into it, joins other data to it, and drops the temporary table. This is a lot of I/O for this query, which could be rewritten to use an inline view instead of a temporary table. An inline view is simply a query that can be joined to in the FROM clause. So instead of spending a lot of I/O and disk access in tempdb on a temporary table, you could instead use an inline view to get the same result:

SELECT p.ProductName,
SUM(od.Quantity) AS ProductQuantity
FROM (
SELECT TOP 5 o.OrderID, o.OrderDate
FROM Orders o
ORDER BY o.OrderDate DESC
) t
INNER JOIN [Order Details] od ON t.OrderID = od.OrderID
INNER JOIN Products p ON od.ProductID = p.ProductID
GROUP BY
p.ProductName
ORDER BY
p.ProductName

This query is not only more efficient than the previous one, it's shorter. Temporary tables consume a lot of resources. If you only need the data to join to other queries, you might want to try using an inline view to conserve resources."

Global Variables and SQL statements in DTS

Global Variables and SQL statements in DTS: "Global Variables and SQL statements in DTS
By Darren Green
Version 7.0+/2000
Level Advanced

For those fortunate enough to have SQL 2000, using global variables with an Execute SQL Task, or as part of the source statement in the DataPump task is now a standard feature. For those still using SQL Server 7.0 this tedious process, but here are some examples of how it can be done. Even if you are using SQL Server 2000, you may still have a need for this due to certain syntax limitations imposed when using parameters.

In SQL Server 7.0 the only way to integrate a global variable into your SQL is to actually code the value as a literal in the SQL statement. The simplest solution is to just build a new statement with the value embedded in it at run-time. You can do this from an ActiveX Script Task as illustrated below.

This example amends the SELECT statement for a DataPump task, to filter based on the value of the global variable HireDate:

' 205 (Change SourceSQLStatement)
Option Explicit

Function Main()
Dim oPkg, oDataPump, sSQLStatement

' Build new SQL Statement
sSQLStatement = "SELECT * FROM dbo.employee WHERE hire_date > '" & _
DTSGlobalVariables("HireDate").Value & "'"

' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

' Assign SQL Statement to Source of DataPump
oDataPump.SourceSQLStatement = sSQLStatement

' Clean Up
Set oDataPump = Nothing
Set oPkg = Nothing

Main = DTSTaskExecResult_Success
End Function"

SQL Server Best Practices Analyser Tool

SQL Server Best Practices Analyser Tool: "SQL Server Best Practices Analyser Tool

Posted by poonam on Thursday, June 10, 2004

Microsoft's SQL Best Practice Analyser is here - and its a great little aid for DBA's
Microsoft released its ‘Best Practices Analyser Tool’ end of May. Though this tool has been in beta since late last year, it has entered the market softly, without any hype or fuss. This is certainly not the ‘Microsoft way’ – and it makes me wonder why, because this is a great little tool and its FREE!!!. You can download it from Best Practices Analyzer Tool for Microsoft SQL Server 2000 1.0

For those of you who didn’t know about it – this is a graphical tool which scans through your database and verifies it against a set of pre-determined rules. For the most part the rules relate to administration functions and its going to be invaluable aid to a DBA’s arsenal.

Just to see what the BPA can do, I have run this against a few production databases and a few development databases I have access to and the results were definitely worth the time.

I discovered for instance, that a crucial table at a client site did not have either a primary key or a unique key constraint on it. I’m pretty sure this table at some point did have a unique constraint, but somewhere over the months and many schema changes, somehow it got dropped – and no one noticed because everyone was so sure it existed. This is just the sort of thing the BPA does so well – nothing earth shattering in terms of knowledge – we all know tables should have primary keys, but the BPA provides us a way to monitor databases effortlessly.

Some database maintenance checks are nice to have – for instance the Index fragmentation, I currently have a stored procedure on a monthly schedule to do this – BPA I guess just makes it easier. Log file growth, virtual log file count – nothing we can’t do ourselves but this just makes it so easy to monitor my databases.

Many of the rules in BPA pertain to Backup and Recovery options, this can be an eye opener sometimes. There are always backup plans in place for production databases – but I was embarrassed to find out when our development databases were last backed up.

I also appreciate the SQL Server 2005 Readiness and depreciation check – it’s nice to know much ahead of time what wont work.

As always, I have a wish list (don’t we all).

I would love to be able to create my own rules.
The ability to setup and schedule the scripts to run at pre-determined times and frequencies would be nice. I have mixed feelings about this however – The best practice analyzer does have a command line interface, and with a bit of work, I can script this out for myself without having Microsoft have a cookie cutter solution for me. I’m probably in the minority when I say this, but I think it’s important that software engineers and DBA’s have the capability to code for themselves, without always looking to Microsoft for a point and click interface.
Can we have a ‘SQL Standard Edition’ compatibility check? So many of us develop on SQL developer edition and deploy to SQL standard edition. I would love to know, what is not supported in standard edition.
I would love to be run this as a background service, keeping a track of database objects being accessed. I currently need to run a profiler trace for this. This not only has a considerable performance impact but profiler traces are not fun to read. It would be helpful to know which objects are now archaic
Again if it can run as a background service, I want to know what indexes / statistics the optimizer is currently using. I want to know if I have unused indexes, ones which were scripted for a different time and now with application changes are not being used.
All things considered – I love the best practice analyzer – it’s a great little tool, and has already pointed out a number of little problems which if I had to do manually would take me a long long time."

Joe Sack: Importing files in SQL Server 2005 using OPENROWSET

From http://www.sqljunkies.com/WebLog/joesack/default.aspx

Importing files in SQL Server 2005 using OPENROWSET
OPENROWSET has new BULK features which I'll definitely be taking advantage of in the future. With OPENROWSET you'll be able to return a result set from a file based on options in a format file (similar to bcp or BULK INSERT), or also import a file in its entirety as a single varbinary(max), varchar(max), or nvarchar(max) data type value.



It is the file import functionality that I'm really looking forward to. This is built-in functionality, so you don't have to depend on external applications to import external files into your SQL Server tables. You use the SINGLE_BLOB, SINGLE_CLOB, or SINGLE_NCLOB to tell SQL Server what kind of single-row, single-column data is being read.



For example, in this query I create a new table that will hold product images and their associated descriptions. The ProductIMG column is using the new SQL Server 2005 varbinary(max) data type to hold gif files:



CREATE TABLE dbo.ProductImage

(ProductImageID int IDENTITY(1,1) NOT NULL PRIMARY KEY,

ProductDESC varchar(255) NOT NULL,

ProductIMG varbinary(max) NOT NULL)

GO



Next, I use OPENROWSET to import a gif file directly into the table from the file system (the BulkColumn is the actual exposed varbinary(max) data being returned):



INSERT dbo.ProductImage

(ProductDESC, ProductIMG)

SELECT 'Used to represent two databases.', -- ProductDESC

BulkColumn -- ProductIMG

FROM OPENROWSET( BULK 'C:\Program Files\Microsoft SQL Server\80\Tools\HTML\dbicon.gif',

SINGLE_BLOB) as ExternalFile



That is all it takes. The file is now stored in the table in a single column of a single row. You could then use SSIS's Export Column task to export the data from these rows into external files again, assuming you added a file path and name column for the transformation to use.

In this example I imported a gif file - but definitely not a fan of serving image files from a database (storing maybe - but not serving). But this functionality definitely opens the door for those of us who don't or can't use third party applications to draw in external files. We can now use Transact-SQL instead.

One uses I may consider is in archiving critical, original legacy data files along with the normalized data. Used within reason of course, this would allow you to maintain the original file alongside the shredded/normalized data in the database so you can troubleshoot data lineage questions and issues.