Conversion Strikes Again

While doing some performance tuning this week I found a query that was executing very frequently from a web application.  The query was doing a simple select from the base table with two columns in the where clause both of which were in the same index.
The query in question went something like this:
select contactid, documentid, documenttype from documents where contactid = ? and documentid = ?

Profiler showed the query is this format:
exec sp_executesql N' SELECT contactid, documentid, documenttype from Documents WHERE ContactID = @ContactID   AND DocumentID = @DocumentID', N'@ContactID nvarchar(36),@DocumentID int', @ContactID = N'464906a7-5acc-44a0-9b50-76da83ba05d8', @DocumentID = 280

The column ContactID is defined as a varchar(36) in the documents table.  When the value to compare to comes in as nvarchar a conversion is required and the index is lost, a table scan ensues and reads skyrocket.

The performance for the query is not that bad, but when that much data must be moved into memory to check for a match other items get pushed out.

The fix the developer decided to go with was to change the column definition to nvarchar from varchar.  This allowed the index to be used and the reads to drop significantly.

In my environment we are finding more and more places where conversion required for the where clauses are causing indexes to be ignored in favor of a less efficient index or a table scan.

Skipping Task when Empty File

At times just checking for a file existence may not enough, if the file exists but is empty the load task may need to be skipped. I have compiled the example below to show one method of skipping the DTS task on file empty. The example utilizes the Workflow Properties page of each DTS Task. Although most corporations are moving away from DTS in favor of new ETL work being done in SSIS, SQL Server Integration Services, there are still plenty of opportunites to improve DTS packages that are still running. This example only works if the load task is the only task in the DTS package. I’ll be posting an update later with an example of how to move to the next task without executing the current task.

The workflow properties tab is available on the following components in the DTS Task pane. Bulk Insert Task, ActiveX Script Task, Execute SQL Task and Data Driven Qeury Task. The option is located by right-clicking the component, selecting Workflow and then Properties. Once on the Workflow Property tab select Options, check ‘Use Active X Script’ and then click properties. The mentioned sequence will open an Active X Script task window. This script is executed prior to the main body of the task.

The code below opens a file that is defined in the global variable, CheckFIle. This is the full file path including name to be checked. If the file is empty or has a blank first line the task is skipped. The result in a job is that the step reports success.

'**********************************************************************
' Skip task if empty file or first line is blank, uses global variable CheckFile
'************************************************************************
Function Main()
   SET fso = CreateObject("Scripting.FileSystemObject")
   DataSetNameTxt = DTSGlobalVariables("CheckFile").value
   Set TextStream = fso.OpenTextFile(DataSetNameTxt,1)
   If TextStream.AtEndOFStream or TextStream.AtEndOfLine then
      Main=DTSStepScriptResult_DontExecuteTask
   else
      Main=DTSStepScriptResult_ExecuteTask
  end if
  TextStream.Close
  Set TextStream = nothing
End Function

By returning the value of either DontExecuteTask, skip the main task, or ExecuteTask proceed normally, execution the main body of the task is controlled.

Below are the screenshots to show the path to Workflow Properties Task input window: