我在sql server 2005中有一个表，其中包含一个IP范围和相应的信息（国家/城市/等）。大约有300万行，根据下面的查询，目前仅需要半秒多的时间即可返回一条记录。
DECLARE @ip BIGINT SELECT @ip=3561360969 SELECT TOP 1 id, ipfrom, ipto, countrycode, countryname,region,city FROM tbl_ip WHERE ipfrom <= @ip and @ip <= ipto
任何人都可以提出任何建议来缩短查询时间，因为我正在构建的系统每秒需要处理大约10个此类查询。我已经完成了以下操作，并没有大大缩短查询时间…
任何想法将不胜感激！
提姆
编辑：xml执行计划如下：
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.4053.00"><BatchSequence><Batch><Statements><StmtSimple StatementText="set statistics time on

" StatementId="1" StatementCompId="1" StatementType="SET STATS"/><StmtSimple StatementText="
DECLARE @ip BIGINT
SELECT @ip=3561360969
" StatementId="2" StatementCompId="2" StatementType="ASSIGN"/><StmtSimple StatementText="
SELECT top 1 id, ipfrom, ipto, countrycode, countryname,region,city
FROM tbl_ip 
WHERE ipfrom <= @ip and @ip <= ipto

" StatementId="3" StatementCompId="3" StatementType="SELECT" StatementSubTreeCost="0.00337934" StatementEstRows="1" StatementOptmLevel="TRIVIAL"><StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false"/><QueryPlan CachedPlanSize="8" CompileTime="0" CompileCPU="0" CompileMemory="120"><RelOp NodeId="0" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="393" EstimatedTotalSubtreeCost="0.00337934" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Id"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPFrom"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPTo"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryCode"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryName"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Region"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="City"/></OutputList><Top RowCount="0" IsPercent="0" WithTies="0"><TopExpression><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"/></ScalarOperator></TopExpression><RelOp NodeId="1" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimateIO="28.1505" EstimateCPU="3.77105" AvgRowSize="393" EstimatedTotalSubtreeCost="0.00337724" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Id"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPFrom"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPTo"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryCode"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryName"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Region"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="City"/></OutputList><IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Id"/></DefinedValue><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPFrom"/></DefinedValue><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPTo"/></DefinedValue><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryCode"/></DefinedValue><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryName"/></DefinedValue><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Region"/></DefinedValue><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="City"/></DefinedValue></DefinedValues><Object Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Index="[PK_tbl_IP2]"/><Predicate><ScalarOperator ScalarString="[RecruitmentIP].[dbo].[tbl_IP].[IPFrom]<=[@ip] AND [@ip]<=[RecruitmentIP].[dbo].[tbl_IP].[IPTo]"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="LE"><ScalarOperator><Identifier><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPFrom"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="@ip"/></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="LE"><ScalarOperator><Identifier><ColumnReference Column="@ip"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPTo"/></Identifier></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp></Top></RelOp></QueryPlan></StmtSimple><StmtSimple StatementText="
set statistics time off" StatementId="4" StatementCompId="4" StatementType="SET STATS"/></Statements></Batch></BatchSequence></ShowPlanXML>
在ipfrom，ipto上具有索引的执行计划
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.4053.00"><BatchSequence><Batch><Statements><StmtSimple StatementText="DECLARE @ip BIGINT
SELECT @ip=3561360969
" StatementId="1" StatementCompId="1" StatementType="ASSIGN"/><StmtSimple StatementText="
SELECT top 1 id, ipfrom, ipto, countrycode, countryname,region,city
FROM tbl_ip 
WHERE ipfrom <= @ip and @ip <= ipto
ORDER BY ipto
" StatementId="2" StatementCompId="2" StatementType="SELECT" StatementSubTreeCost="224.264" StatementEstRows="1" StatementOptmLevel="FULL"><StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false"/><QueryPlan CachedPlanSize="10" CompileTime="1" CompileCPU="1" CompileMemory="160"><RelOp NodeId="0" PhysicalOp="Sort" LogicalOp="TopN Sort" EstimateRows="1" EstimateIO="182.872" EstimateCPU="6.45397" AvgRowSize="393" EstimatedTotalSubtreeCost="224.264" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Id"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPFrom"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPTo"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryCode"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryName"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Region"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="City"/></OutputList><MemoryFractions Input="0" Output="1"/><TopSort Distinct="0" Rows="1"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPTo"/></OrderByColumn></OrderBy><RelOp NodeId="1" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="308528" EstimateIO="28.1505" EstimateCPU="3.77105" AvgRowSize="393" EstimatedTotalSubtreeCost="31.9216" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Id"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPFrom"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPTo"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryCode"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryName"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Region"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="City"/></OutputList><IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Id"/></DefinedValue><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPFrom"/></DefinedValue><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPTo"/></DefinedValue><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryCode"/></DefinedValue><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryName"/></DefinedValue><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Region"/></DefinedValue><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="City"/></DefinedValue></DefinedValues><Object Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Index="[PK_tbl_IP2]"/><Predicate><ScalarOperator ScalarString="[RecruitmentIP].[dbo].[tbl_IP].[IPFrom]<=[@ip] AND [@ip]<=[RecruitmentIP].[dbo].[tbl_IP].[IPTo]"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="LE"><ScalarOperator><Identifier><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPFrom"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="@ip"/></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="LE"><ScalarOperator><Identifier><ColumnReference Column="@ip"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPTo"/></Identifier></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp></TopSort></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
Martin Smiths查询的执行计划：
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.4053.00"><BatchSequence><Batch><Statements><StmtSimple StatementText="--set showplan_xml on

set statistics time on

" StatementId="1" StatementCompId="1" StatementType="SET STATS"/><StmtSimple StatementText="
DECLARE @ip BIGINT
SELECT @ip=3561360969

" StatementId="2" StatementCompId="2" StatementType="ASSIGN"/><StmtSimple StatementText="
SELECT id, ipfrom, ipto, countrycode, countryname,region,city FROM
(
SELECT TOP 1 id, ipfrom, ipto, countrycode, countryname,region,city
FROM tbl_ip 
WHERE @ip <= ipto
ORDER BY ipto
INTERSECT
SELECT TOP 1 id, ipfrom, ipto, countrycode, countryname,region,city
FROM tbl_ip 
WHERE ipfrom <= @ip
ORDER BY ipfrom DESC
) ip

" StatementId="3" StatementCompId="3" StatementType="SELECT" StatementSubTreeCost="226.409" StatementEstRows="1" StatementOptmLevel="FULL"><StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false"/><QueryPlan CachedPlanSize="33" CompileTime="6" CompileCPU="6" CompileMemory="376"><MissingIndexes><MissingIndexGroup Impact="10.1146"><MissingIndex Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]"><ColumnGroup Usage="INEQUALITY"><Column Name="[IPTo]" ColumnId="3"/></ColumnGroup><ColumnGroup Usage="INCLUDE"><Column Name="[Id]" ColumnId="1"/><Column Name="[IPFrom]" ColumnId="2"/><Column Name="[CountryCode]" ColumnId="4"/><Column Name="[CountryName]" ColumnId="5"/><Column Name="[Region]" ColumnId="6"/><Column Name="[City]" ColumnId="7"/></ColumnGroup></MissingIndex></MissingIndexGroup></MissingIndexes><RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Left Semi Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-006" AvgRowSize="86" EstimatedTotalSubtreeCost="226.409" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Id"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPFrom"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPTo"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryCode"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryName"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Region"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="City"/></OutputList><NestedLoops Optimized="0"><Predicate><ScalarOperator ScalarString="[RecruitmentIP].[dbo].[tbl_IP].[Id]=[RecruitmentIP].[dbo].[tbl_IP].[Id] AND [RecruitmentIP].[dbo].[tbl_IP].[IPFrom] = [RecruitmentIP].[dbo].[tbl_IP].[IPFrom] AND [RecruitmentIP].[dbo].[tbl_IP].[IPTo] = [RecruitmentIP].[dbo].[tbl_IP].[IPTo] AND [RecruitmentIP].[dbo].[tbl_IP].[CountryCode] = [RecruitmentIP].[dbo].[tbl_IP].[CountryCode] AND [RecruitmentIP].[dbo].[tbl_IP].[CountryName] = [RecruitmentIP].[dbo].[tbl_IP].[CountryName] AND [RecruitmentIP].[dbo].[tbl_IP].[Region] = [RecruitmentIP].[dbo].[tbl_IP].[Region] AND [RecruitmentIP].[dbo].[tbl_IP].[City] = [RecruitmentIP].[dbo].[tbl_IP].[City]"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Id"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Id"/></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPFrom"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPFrom"/></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPTo"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPTo"/></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryCode"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryCode"/></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryName"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryName"/></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Region"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Region"/></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="City"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="City"/></Identifier></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate><RelOp NodeId="1" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="4.8e-007" AvgRowSize="86" EstimatedTotalSubtreeCost="226.399" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Id"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPFrom"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPTo"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryCode"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryName"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Region"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="City"/></OutputList><Filter StartupExpression="0"><RelOp NodeId="2" PhysicalOp="Sort" LogicalOp="TopN Sort" EstimateRows="1" EstimateIO="163.119" EstimateCPU="29.7123" AvgRowSize="86" EstimatedTotalSubtreeCost="226.399" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Id"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPFrom"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPTo"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryCode"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryName"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Region"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="City"/></OutputList><MemoryFractions Input="0" Output="0"/><TopSort Distinct="0" Rows="1"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPTo"/></OrderByColumn></OrderBy><RelOp NodeId="3" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1.02843e+006" EstimateIO="28.1505" EstimateCPU="3.77105" AvgRowSize="86" EstimatedTotalSubtreeCost="31.9216" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Id"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPFrom"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPTo"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryCode"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryName"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Region"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="City"/></OutputList><IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Id"/></DefinedValue><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPFrom"/></DefinedValue><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPTo"/></DefinedValue><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryCode"/></DefinedValue><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryName"/></DefinedValue><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Region"/></DefinedValue><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="City"/></DefinedValue></DefinedValues><Object Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Index="[PK_tbl_IP2]" TableReferenceId="1"/><Predicate><ScalarOperator ScalarString="[@ip]<=[RecruitmentIP].[dbo].[tbl_IP].[IPTo]"><Compare CompareOp="LE"><ScalarOperator><Identifier><ColumnReference Column="@ip"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPTo"/></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></TopSort></RelOp><Predicate><ScalarOperator ScalarString="[RecruitmentIP].[dbo].[tbl_IP].[IPFrom]<=[@ip]"><Compare CompareOp="LE"><ScalarOperator><Identifier><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPFrom"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="@ip"/></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp><RelOp NodeId="5" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="4.8e-007" AvgRowSize="86" EstimatedTotalSubtreeCost="0.00985397" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Id"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPFrom"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPTo"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryCode"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryName"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Region"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="City"/></OutputList><Filter StartupExpression="0"><RelOp NodeId="6" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="86" EstimatedTotalSubtreeCost="0.00985349" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Id"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPFrom"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPTo"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryCode"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryName"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Region"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="City"/></OutputList><Top RowCount="0" IsPercent="0" WithTies="0"><TopExpression><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"/></ScalarOperator></TopExpression><RelOp NodeId="7" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.29882" AvgRowSize="86" EstimatedTotalSubtreeCost="0.00985339" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Id"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPFrom"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPTo"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryCode"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryName"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Region"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="City"/></OutputList><NestedLoops Optimized="0" WithOrderedPrefetch="1"><OuterReferences><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Id"/><ColumnReference Column="Expr1006"/></OuterReferences><RelOp NodeId="9" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimateIO="2.45201" EstimateCPU="1.13142" AvgRowSize="27" EstimatedTotalSubtreeCost="0.0032831" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Id"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPFrom"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPTo"/></OutputList><IndexScan Ordered="1" ScanDirection="BACKWARD" ForcedIndex="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Id"/></DefinedValue><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPFrom"/></DefinedValue><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPTo"/></DefinedValue></DefinedValues><Object Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Index="[idx_ipfrom_ipto]" TableReferenceId="2"/><SeekPredicates><SeekPredicate><EndRange ScanType="LE"><RangeColumns><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPFrom"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[@ip]"><Identifier><ColumnReference Column="@ip"/></Identifier></ScalarOperator></RangeExpressions></EndRange></SeekPredicate></SeekPredicates></IndexScan></RelOp><RelOp NodeId="11" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="373" EstimatedTotalSubtreeCost="0.00669221" Parallel="0" EstimateRebinds="1" EstimateRewinds="0.797604"><OutputList><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryCode"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryName"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Region"/><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="City"/></OutputList><IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryCode"/></DefinedValue><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="CountryName"/></DefinedValue><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Region"/></DefinedValue><DefinedValue><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="City"/></DefinedValue></DefinedValues><Object Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Index="[PK_tbl_IP2]" TableReferenceId="-1"/><SeekPredicates><SeekPredicate><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Id"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[RecruitmentIP].[dbo].[tbl_IP].[Id]"><Identifier><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="Id"/></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekPredicate></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></Top></RelOp><Predicate><ScalarOperator ScalarString="[RecruitmentIP].[dbo].[tbl_IP].[IPTo]>=[@ip]"><Compare CompareOp="GE"><ScalarOperator><Identifier><ColumnReference Database="[RecruitmentIP]" Schema="[dbo]" Table="[tbl_IP]" Column="IPTo"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="@ip"/></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple><StmtSimple StatementText="
set statistics time off" StatementId="4" StatementCompId="4" StatementType="SET STATS"/></Statements></Batch></BatchSequence></ShowPlanXML>
这是如何执行的？我希望它应该使用ipto上的索引查找来快速解析顶部，使用ipfrom上的索引查找来解析底部和2个（或可能一个）书签查找以返回其余的列。
SELECT id, ipfrom, ipto, countrycode, countryname,region,city FROM ( SELECT TOP 1 id, ipfrom, ipto, countrycode, countryname,region,city FROM tbl_ip WHERE @ip <= ipto ORDER BY ipto INTERSECT SELECT TOP 1 id, ipfrom, ipto, countrycode, countryname,region,city FROM tbl_ip WHERE ipfrom <= @ip ORDER BY ipfrom DESC ) ip
