O que a execução da consulta está fazendo com essa consulta no SQL Server 2005?
Hoje encontrei esta consulta em nosso código que exibe uma lista de erros do nosso banco de dados:
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'
Falha com o seguinte erro:
Parâmetro de comprimento inválido passado para a função SUBSTRING.
Se eu remover oWHERE Cláusula na última linha que funciona bem. Como alternativa, se eu fizer oWHERE cláusula da subconsulta interna e mova-a para a consulta principal que funciona bem. Então, isso 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'
Alguem sabe por quê? Eu acho que o problema é que o SQL está realmente fazendo várias execuções na tabela core.LOG e, pela primeira vez, simplesmente executa a linha Substring em toda a tabela que falha, pois algumas linhas não terão um ')' nelas. No entanto, se executar as linhas restantes noWHERE na cláusula, não encontrará nenhuma linha faltando um ')'. Em seguida, ele executa outra execução na tabela core.LOG e executa os filtros restantes nela. Isso parece ineficiente, pois acabará executando a função Substring em milhões de linhas, contra cerca de 15 linhas dos filtros restantes noWHERE cláusula.
Aqui está o meu plano de execução 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>