¿Qué está haciendo la ejecución de consultas a esta consulta en SQL Server 2005?
Hoy encontré esta consulta en nuestro código que muestra una lista de errores de nuestra base de datos:
SELECT *
FROM (
SELECT Substring(title, 9, Patindex('%)%', title) - 9) AS SERVICE, *
FROM core.LOG
WHERE logtypeid = 1
AND title LIKE 'Error: (%'
AND lastmodified > '2010-06-21T00:00:00'
AND lastmodified < '2010-06-22T00:00:00'
) serviceerrors
WHERE SERVICE = 'CheckHelpDeskEmail'
Falla con el siguiente error:
Parámetro de longitud no válido pasado a la función SUBSTRING.
Si elimino elWHERE cláusula en la última línea funciona bien. Alternativamente, si tomo elWHERE cláusula de la subconsulta interna y moverla a la consulta principal funciona bien. Entonces esto funciona:
SELECT *
FROM (
SELECT Substring(title, 9, Patindex('%)%', title) - 9) AS SERVICE, *
FROM core.LOG
) serviceerrors
WHERE logtypeid = 1
AND title LIKE 'Error: (%'
AND lastmodified > '2010-06-21T00:00:00'
AND lastmodified < '2010-06-22T00:00:00'
AND SERVICE = 'CheckHelpDeskEmail'
¿Alguien sabe por qué? Creo que el problema es que SQL realmente está haciendo múltiples ejecuciones a través de la tabla core.LOG y la primera vez simplemente ejecuta la línea Substring en toda la tabla que falla ya que algunas líneas no tendrán un ')' en ellas. Sin embargo, si ejecuta las líneas restantes en elWHERE cláusula no encontrará ninguna fila que falte un ')'. Luego realiza otra ejecución a través de la tabla core.LOG y ejecuta los filtros restantes en ella. Esto parece poco eficiente, ya que terminará ejecutando la función Subcadena en millones de filas, frente a aproximadamente 15 filas de los filtros restantes en elWHERE cláusula.
Aquí está mi plan de ejecución XML
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.0" Build="9.00.3310.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="36.4574" StatementText="SELECT *
FROM (
 SELECT Substring(title, 9, Patindex('%)%', title) - 9) AS SERVICE,
 *
 FROM core.LOG
 WHERE logtypeid = 1
 AND title LIKE 'Error: (%'
 AND lastmodified > '2010-06-21T00:00:00'
 AND lastmodified < '2010-06-22T00:00:00'
 ) serviceerrors
WHERE SERVICE = 'CheckHelpDeskEmail' 
" StatementType="SELECT">
<StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
<QueryPlan CachedPlanSize="47" CompileTime="10" CompileCPU="7" CompileMemory="544">
<RelOp AvgRowSize="4544" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="36.4574">
<OutputList>
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogId" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Title" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Details" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="IdentifierHash" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModifier" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModified" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogTypeId" />
<ColumnReference Column="Expr1003" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1003" />
<ScalarOperator ScalarString="substring([Aqueduct].[Core].[Log].[Title],(9),patindex(N'%)%',[Aqueduct].[Core].[Log].[Title])-(9))">
<Intrinsic FunctionName="substring">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Title" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(9)" />
</ScalarOperator>
<ScalarOperator>
<Arithmetic Operation="SUB">
<ScalarOperator>
<Intrinsic FunctionName="patindex">
<ScalarOperator>
<Const ConstValue="N'%)%'" />
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Title" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(9)" />
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="4342" EstimateCPU="0.0288036" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Gather Streams" NodeId="1" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="36.4574">
<OutputList>
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogId" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Title" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Details" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="IdentifierHash" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModifier" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModified" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogTypeId" />
</OutputList>
<Parallelism>
<RelOp AvgRowSize="4342" EstimateCPU="0.0128582" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Filter" NodeId="2" Parallel="true" PhysicalOp="Filter" EstimatedTotalSubtreeCost="36.4286">
<OutputList>
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogId" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Title" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Details" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="IdentifierHash" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModifier" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModified" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogTypeId" />
</OutputList>
<Filter StartupExpression="false">
<RelOp AvgRowSize="4342" EstimateCPU="0.0235734" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="11279.1" LogicalOp="Inner Join" NodeId="3" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="36.4157">
<OutputList>
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogId" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Title" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Details" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="IdentifierHash" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModifier" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModified" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogTypeId" />
</OutputList>
<NestedLoops Optimized="true" WithUnorderedPrefetch="true">
<OuterReferences>
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogId" />
<ColumnReference Column="Expr1004" />
</OuterReferences>
<RelOp AvgRowSize="19" EstimateCPU="0.00628203" EstimateIO="0.0186806" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="11279.1" LogicalOp="Index Seek" NodeId="6" Parallel="true" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0249626">
<OutputList>
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogId" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModified" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModified" />
</DefinedValue>
</DefinedValues>
<Object Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Index="[IX_LastModified]" />
<SeekPredicates>
<SeekPredicate>
<StartRange ScanType="GT">
<RangeColumns>
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModified" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="'2010-06-21 00:00:00.000'">
<Const ConstValue="'2010-06-21 00:00:00.000'" />
</ScalarOperator>
</RangeExpressions>
</StartRange>
<EndRange ScanType="LT">
<RangeColumns>
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModified" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="'2010-06-22 00:00:00.000'">
<Const ConstValue="'2010-06-22 00:00:00.000'" />
</ScalarOperator>
</RangeExpressions>
</EndRange>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="4447" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="11278.1" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="8" Parallel="true" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="36.3672">
<OutputList>
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Title" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Details" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="IdentifierHash" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModifier" />
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogTypeId" />
</OutputList>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Title" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Details" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="IdentifierHash" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LastModifier" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogTypeId" />
</DefinedValue>
</DefinedValues>
<Object Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Index="[PK_Core_Log]" TableReferenceId="-1" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogId" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Aqueduct].[Core].[Log].[LogId]">
<Identifier>
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogId" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="substring([Aqueduct].[Core].[Log].[Title],(9),patindex(N'%)%',[Aqueduct].[Core].[Log].[Title])-(9))=N'CheckHelpDeskEmail' AND [Aqueduct].[Core].[Log].[Title] like N'Error: (%' AND [Aqueduct].[Core].[Log].[LogTypeId]=(1)">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Intrinsic FunctionName="substring">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Title" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(9)" />
</ScalarOperator>
<ScalarOperator>
<Arithmetic Operation="SUB">
<ScalarOperator>
<Intrinsic FunctionName="patindex">
<ScalarOperator>
<Const ConstValue="N'%)%'" />
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Title" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(9)" />
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N'CheckHelpDeskEmail'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Intrinsic FunctionName="like">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="Title" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N'Error: (%'" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Aqueduct]" Schema="[Core]" Table="[Log]" Column="LogTypeId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</Filter>
</RelOp>
</Parallelism>
</RelOp>
</ComputeScalar>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>