Was macht die Abfrageausführung mit dieser Abfrage in SQL Server 2005?
Heute habe ich diese Abfrage in unserem Code gefunden, der eine Fehlerliste aus unserer Datenbank abruft:
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'
Es schlägt mit dem folgenden Fehler fehl:
Unzulässiger Längenparameter an SUBSTRING übergeben.
Wenn ich das @ entferWHERE -Klausel in der allerletzten Zeile funktioniert gut. Alternativ, wenn ich das @ nehWHERE -Klausel aus der inneren Unterabfrage und verschieben Sie es in die Hauptabfrage, es funktioniert gut. Das funktioniert also:
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'
Weiß jemand warum? Ich denke, das Problem ist, dass SQL tatsächlich mehrere Durchläufe durch die core.LOG-Tabelle macht und das erste Mal einfach die Substring-Zeile für die gesamte Tabelle ausführt, was fehlschlägt, da einige Zeilen kein ')' enthalten. Wenn jedoch die restlichen Zeilen imWHERE -Klausel findet keine Zeilen ohne ein ')'. Anschließend wird die core.LOG-Tabelle erneut durchlaufen und die verbleibenden Filter darauf ausgeführt. Dies scheint ineffizient zu sein, da die Teilzeichenfolge-Funktion letztendlich in Millionen von Zeilen ausgeführt wird, im Gegensatz zu etwa 15 Zeilen in den verbleibenden Filtern imWHERE Klausel.
Hier ist mein XML-Ausführungsplan
<?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>