sql server - Slow SQL 2008 Cursor Performance - Index scan/seek -
i have found interesting "issue" when using ado driver sql server 2008 (or rather when using specific sql cursors created driver). want execute following query:
select id, televeid, teldayid, telmobid, qualityflag, weekbegin cef (televeid = '+44xxxx') or (teldayid = '+44xxxxx') or (telmobid = '+44xxxx') order id desc where there (for sake of example) 3 indexed on cef table tuples [id; televeid], [id; teldayid], , [id; telmobid]
the query above "translated" driver in following way:
declare @p1 int declare @p2 int declare @p5 int set @p5=4 declare @p6 int set @p6=4 declare @p7 int set @p7=-1 exec sp_cursorprepexec @p1 output,@p2 output,null,n'select id, televeid, teldayid, telmobid, qualityflag, weekbegin cef (televeid = ''+44xxxx'') or (teldayid = ''+44xxxx'') or (telmobid = ''+44xxxx'') order id desc',@p5 output,@p6 output,@p7 output exec sp_cursorfetch @p2,2,1,10 the problem if execute queries above first 1 approx. 10 times faster second one. , if have @ actual execution plan can see reason difference
cursor omits existing indexes , uses index scan instead whereas "direct query" merges 3 "separate" resultsets final 1 appropriate index usage.
if change "where" part within cursor definition looks 1 field only, use appropriate index (it still slower due cursor's running costs).
is there way how avoid behavior without removing cursors (as not possible in legacy application performs action)?
thanks
martin f.
this (little bit shortened - have removed information columns in cef table) (xml) execution plan cursor base query:
<?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.1" build="10.50.1617.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> <batchsequence> <batch> <statements> <stmtsimple statementcompid="6" statementestrows="3.3163" statementid="1" statementoptmlevel="full" statementsubtreecost="134.243" statementtext="select id, televeid, teldayid, telmobid, qualityflag, weekbegin cef (televeid = '+44xxxx') or (teldayid = '+44xxxx') or (telmobid = '+44xxxx') order id desc" statementtype="declare cursor" queryhash="0x4dc45eb62bccae06" queryplanhash="0x72acc7b98e06e06e"> <statementsetoptions ansi_nulls="true" ansi_padding="true" ansi_warnings="true" arithabort="true" concat_null_yields_null="true" numeric_roundabort="false" quoted_identifier="true" /> <queryplan degreeofparallelism="0" cachedplansize="152" compiletime="7" compilecpu="3" compilememory="992"> <relop avgrowsize="913" estimatecpu="3.3163e-06" estimateio="0.01" estimaterebinds="0" estimaterewinds="0" estimaterows="3.3163" logicalop="insert" nodeid="0" parallel="false" physicalop="clustered index insert" estimatedtotalsubtreecost="134.243"> <outputlist> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="id" /> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="televeid" /> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="teldayid" /> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="telmobid" /> <columnreference column="expr1005" /> </outputlist> <runtimeinformation> <runtimecountersperthread thread="0" actualrows="0" actualendofscans="0" actualexecutions="1" /> </runtimeinformation> <update dmlrequestsort="false"> <object database="[tempdb]" index="[cwt_primarykey]" /> <setpredicate> <scalaroperator scalarstring="[cwt].[column0] = [lms].[dbo].[cef].[id],[cwt].[checksum1] = [chk1002],[cwt].[rowid] = [expr1005]"> <scalarexpressionlist> <scalaroperator> <multipleassign> <assign> <columnreference table="[cwt]" column="column0" /> <scalaroperator> <identifier> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="id" /> </identifier> </scalaroperator> </assign> <assign> <columnreference table="[cwt]" column="checksum1" /> <scalaroperator> <identifier> <columnreference column="chk1002" /> </identifier> </scalaroperator> </assign> <assign> <columnreference table="[cwt]" column="rowid" /> <scalaroperator> <identifier> <columnreference column="expr1005" /> </identifier> </scalaroperator> </assign> </multipleassign> </scalaroperator> </scalarexpressionlist> </scalaroperator> </setpredicate> <relop avgrowsize="917" estimatecpu="3.3163e-07" estimateio="0" estimaterebinds="0" estimaterewinds="0" estimaterows="3.3163" logicalop="compute scalar" nodeid="1" parallel="false" physicalop="compute scalar" estimatedtotalsubtreecost="134.233"> <outputlist> <columnreference column="chk1002" /> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="id" /> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="televeid" /> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="teldayid" /> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="telmobid" /> <columnreference column="expr1005" /> </outputlist> <runtimeinformation> <runtimecountersperthread thread="0" actualrows="0" actualendofscans="0" actualexecutions="1" /> </runtimeinformation> <computescalar> <definedvalues> <definedvalue> <columnreference column="expr1005" /> <scalaroperator scalarstring="cwt_rowid()"> <intrinsic functionname="cwt_rowid" /> </scalaroperator> </definedvalue> </definedvalues> <relop avgrowsize="913" estimatecpu="1.68105" estimateio="130.748" estimaterebinds="0" estimaterewinds="0" estimaterows="3.3163" logicalop="clustered index scan" nodeid="2" parallel="false" physicalop="clustered index scan" estimatedtotalsubtreecost="132.429" tablecardinality="1528090"> <outputlist> <columnreference column="chk1002" /> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="id" /> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="televeid" /> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="teldayid" /> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="telmobid" /> </outputlist> <runtimeinformation> <runtimecountersperthread thread="0" actualrows="0" actualendofscans="0" actualexecutions="1" /> </runtimeinformation> <indexscan ordered="true" scandirection="backward" forcedindex="false" forceseek="false" noexpandhint="false"> <definedvalues> <definedvalue> <columnreference column="chk1002" /> </definedvalue> <definedvalue> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="id" /> </definedvalue> <definedvalue> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="televeid" /> </definedvalue> <definedvalue> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="teldayid" /> </definedvalue> <definedvalue> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="telmobid" /> </definedvalue> </definedvalues> <object database="[lms]" schema="[dbo]" table="[cef]" index="[pk_cef]" indexkind="clustered" /> <predicate> <scalaroperator scalarstring="[lms].[dbo].[cef].[televeid]='+44xxxx' or [lms].[dbo].[cef].[teldayid]='+44xxxx' or [lms].[dbo].[cef].[telmobid]='+44xxxx'"> <logical operation="or"> <scalaroperator> <compare compareop="eq"> <scalaroperator> <identifier> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="televeid" /> </identifier> </scalaroperator> <scalaroperator> <const constvalue="'+44xxxx'" /> </scalaroperator> </compare> </scalaroperator> <scalaroperator> <compare compareop="eq"> <scalaroperator> <identifier> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="teldayid" /> </identifier> </scalaroperator> <scalaroperator> <const constvalue="'+44xxxx'" /> </scalaroperator> </compare> </scalaroperator> <scalaroperator> <compare compareop="eq"> <scalaroperator> <identifier> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="telmobid" /> </identifier> </scalaroperator> <scalaroperator> <const constvalue="'+44xxxx'" /> </scalaroperator> </compare> </scalaroperator> </logical> </scalaroperator> </predicate> </indexscan> </relop> </computescalar> </relop> </update> </relop> </queryplan> </stmtsimple> </statements> </batch> <batch> <statements> <stmtsimple statementcompid="7" statementestrows="3.3163" statementid="2" statementoptmlevel="full" statementsubtreecost="134.243" statementtext="fetch api_cursor000000000007aa17" statementtype="fetch cursor" queryhash="0x4dc45eb62bccae06" queryplanhash="0x72acc7b98e06e06e"> <statementsetoptions ansi_nulls="true" ansi_padding="true" ansi_warnings="true" arithabort="true" concat_null_yields_null="true" numeric_roundabort="false" quoted_identifier="true" /> <queryplan degreeofparallelism="0" cachedplansize="152" compiletime="7" compilecpu="3" compilememory="992"> <relop avgrowsize="913" estimatecpu="3.3163e-06" estimateio="0.01" estimaterebinds="0" estimaterewinds="0" estimaterows="3.3163" logicalop="insert" nodeid="0" parallel="false" physicalop="clustered index insert" estimatedtotalsubtreecost="134.243"> <outputlist> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="id" /> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="televeid" /> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="teldayid" /> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="telmobid" /> <columnreference column="expr1005" /> </outputlist> <runtimeinformation> <runtimecountersperthread thread="0" actualrows="1" actualendofscans="1" actualexecutions="1" /> </runtimeinformation> <update dmlrequestsort="false"> <object database="[tempdb]" index="[cwt_primarykey]" /> <setpredicate> <scalaroperator scalarstring="[cwt].[column0] = [lms].[dbo].[cef].[id],[cwt].[checksum1] = [chk1002],[cwt].[rowid] = [expr1005]"> <scalarexpressionlist> <scalaroperator> <multipleassign> <assign> <columnreference table="[cwt]" column="column0" /> <scalaroperator> <identifier> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="id" /> </identifier> </scalaroperator> </assign> <assign> <columnreference table="[cwt]" column="checksum1" /> <scalaroperator> <identifier> <columnreference column="chk1002" /> </identifier> </scalaroperator> </assign> <assign> <columnreference table="[cwt]" column="rowid" /> <scalaroperator> <identifier> <columnreference column="expr1005" /> </identifier> </scalaroperator> </assign> </multipleassign> </scalaroperator> </scalarexpressionlist> </scalaroperator> </setpredicate> <relop avgrowsize="917" estimatecpu="3.3163e-07" estimateio="0" estimaterebinds="0" estimaterewinds="0" estimaterows="3.3163" logicalop="compute scalar" nodeid="1" parallel="false" physicalop="compute scalar" estimatedtotalsubtreecost="134.233"> <outputlist> <columnreference column="chk1002" /> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="id" /> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="televeid" /> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="teldayid" /> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="telmobid" /> <columnreference column="expr1005" /> </outputlist> <runtimeinformation> <runtimecountersperthread thread="0" actualrows="1" actualendofscans="1" actualexecutions="1" /> </runtimeinformation> <computescalar> <definedvalues> <definedvalue> <columnreference column="expr1005" /> <scalaroperator scalarstring="cwt_rowid()"> <intrinsic functionname="cwt_rowid" /> </scalaroperator> </definedvalue> </definedvalues> <relop avgrowsize="913" estimatecpu="1.68105" estimateio="130.748" estimaterebinds="0" estimaterewinds="0" estimaterows="3.3163" logicalop="clustered index scan" nodeid="2" parallel="false" physicalop="clustered index scan" estimatedtotalsubtreecost="132.429" tablecardinality="1528090"> <outputlist> <columnreference column="chk1002" /> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="id" /> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="televeid" /> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="teldayid" /> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="telmobid" /> </outputlist> <runtimeinformation> <runtimecountersperthread thread="0" actualrows="1" actualendofscans="1" actualexecutions="1" /> </runtimeinformation> <indexscan ordered="true" scandirection="backward" forcedindex="false" forceseek="false" noexpandhint="false"> <definedvalues> <definedvalue> <columnreference column="chk1002" /> </definedvalue> <definedvalue> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="id" /> </definedvalue> <definedvalue> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="televeid" /> </definedvalue> <definedvalue> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="teldayid" /> </definedvalue> <definedvalue> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="telmobid" /> </definedvalue> </definedvalues> <object database="[lms]" schema="[dbo]" table="[cef]" index="[pk_cef]" indexkind="clustered" /> <predicate> <scalaroperator scalarstring="[lms].[dbo].[cef].[televeid]='+44xxxx' or [lms].[dbo].[cef].[teldayid]='+44xxxx' or [lms].[dbo].[cef].[telmobid]='+44xxxx'"> <logical operation="or"> <scalaroperator> <compare compareop="eq"> <scalaroperator> <identifier> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="televeid" /> </identifier> </scalaroperator> <scalaroperator> <const constvalue="'+44xxxx'" /> </scalaroperator> </compare> </scalaroperator> <scalaroperator> <compare compareop="eq"> <scalaroperator> <identifier> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="teldayid" /> </identifier> </scalaroperator> <scalaroperator> <const constvalue="'+44xxxx'" /> </scalaroperator> </compare> </scalaroperator> <scalaroperator> <compare compareop="eq"> <scalaroperator> <identifier> <columnreference database="[lms]" schema="[dbo]" table="[cef]" column="telmobid" /> </identifier> </scalaroperator> <scalaroperator> <const constvalue="'+44xxxx'" /> </scalaroperator> </compare> </scalaroperator> </logical> </scalaroperator> </predicate> </indexscan> </relop> </computescalar> </relop> </update> </relop> </queryplan> </stmtsimple> </statements> </batch> </batchsequence> </showplanxml>
Comments
Post a Comment