Using Link Server Performance Issue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Using Link Server Performance Issue

Author

Topic

PoseyRobert
Starting Member

27 Posts

Posted - 2014-12-10 : 12:33:36

Hi,

My query is taking a long time to run.

How can I improve the performance on the below query statement.

select scd.container_no as Lpn_Number, scd.create_date, scd.so_no as Order_ID, scd.line_no as Detail_Line_ID, scd.ship_no as Ship_Nbr
from shp_container_detail as scd
inner join SQL03_LINKED_SERVER.JTP_REPORTS.dbo.ORDERS_DETAILS AS od on scd.so_no = od.Order_ID AND scd.line_no = od.Order_Detail_Line_ID
inner join SQL03_LINKED_SERVER.JTP_REPORTS.dbo.ORDERS AS o on scd.so_no = o.Order_ID
where scd.create_date >= '12/01/14' and scd.create_date <= '12/14/14'

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-10 : 13:23:18

Try inner remote join

PoseyRobert
Starting Member

27 Posts

Posted - 2014-12-11 : 10:57:52

I am sorry what do you mean by inner remote join. I am already using inner join.

PoseyRobert
Starting Member

27 Posts

Posted - 2014-12-11 : 14:50:52

Okay.

I tried the inner remote join. It did not improve the performance.

select scd.container_no as Lpn_Number, scd.create_date, scd.so_no as Order_ID, scd.line_no as Detail_Line_ID, scd.ship_no as Ship_Nbr
from shp_container_detail as scd
inner remote join SQL03_LINKED_SERVER.JTP_REPORTS.dbo.ORDERS_DETAILS AS od on scd.so_no = od.Order_ID AND scd.line_no = od.Order_Detail_Line_ID
inner remote join SQL03_LINKED_SERVER.JTP_REPORTS.dbo.ORDERS AS o on scd.so_no = o.Order_ID
where scd.create_date >= '12/01/14' and scd.create_date <= '12/14/14'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-11 : 14:55:25

Is create_date indexes on the scd table? How about the join conditions? Show us the showplan xml.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

PoseyRobert
Starting Member

27 Posts

Posted - 2014-12-22 : 11:08:52

Here my showplan TXT. Yes the create_date is indexes on the scd.

<b>
StmtText
select scd.container_no as Lpn_Number, scd.create_date, scd.so_no as Order_ID, scd.line_no as Detail_Line_ID, scd.ship_no as Ship_Nbr from shp_container_detail as scd inner join SQL03_LINKED_SERVER.JTP_REPORTS.dbo.ORDERS_DETAILS AS od on scd.so_no = od.Order_ID AND scd.line_no = od.Order_Detail_Line_ID inner join SQL03_LINKED_SERVER.JTP_REPORTS.dbo.ORDERS AS o on scd.so_no = o.Order_ID where scd.create_date >= '12/01/14' and scd.create_date <= '12/14/14'
</b>

<b>
StmtText
|--Nested Loops(Inner Join, OUTER REFERENCES:([scd].[so_no]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([scd].[so_no], [Expr1006]))
| |--Sort(ORDER BY:([scd].[so_no] ASC, [Expr1006] ASC))
| | |--Compute Scalar(DEFINE:([Expr1006]=CONVERT_IMPLICIT(int,[COLLECT_MAIN].[dbo].[SHP_CONTAINER_DETAIL].[line_no] as [scd].[line_no],0)))
| | |--Index Seek(OBJECT:([COLLECT_MAIN].[dbo].[SHP_CONTAINER_DETAIL].[Created_Date] AS [scd]), SEEK:([scd].[create_date] >= '2014-12-01 00:00:00.000' AND [scd].[create_date] <= '2014-12-14 00:00:00.000') ORDERED FORWARD)
| |--Remote Query(SOURCE:(SQL03_LINKED_SERVER), QUERY:(SELECT 1 FROM "JTP_REPORTS"."dbo"."ORDERS_DETAILS" "Tbl1003" WHERE ?="Tbl1003"."Order_Detail_Line_ID" AND ?=CONVERT(nvarchar(10),"Tbl1003"."Order_ID",0)))
|--Row Count Spool
|--Remote Query(SOURCE:(SQL03_LINKED_SERVER), QUERY:(SELECT 1 FROM "JTP_REPORTS"."dbo"."ORDERS" "Tbl1005" WHERE ?=CONVERT(nvarchar(10),"Tbl1005"."Order_ID",0)))

</b>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-22 : 12:45:15

I'd prefer to see the XML plan, but I don't see any issues in what you've posted.

Do you have a composite index on od.Order_ID, od.Order_Detail_Line_ID?
How about an index on o.Order_ID?

I would modify the index on scd.create_date so that it also has so_no as a key column and then INCLUDEs the columns in the SELECT portion.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

PoseyRobert
Starting Member

27 Posts

Posted - 2014-12-22 : 14:20:44

Yes the od.Order_ID and the o.Order_ID are indexes

Here's the showplan xml

Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1.1" Build="10.0.5520.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="#xd;#xa;select scd.container_no as Lpn_Number, scd.create_date, scd.so_no as Order_ID, scd.line_no as Detail_Line_ID, scd.ship_no as Ship_Nbr#xd;#xa;from shp_container_detail as scd#xd;#xa;inner join SQL03_LINKED_SERVER.JTP_REPORTS.dbo.ORDERS_DETAILS AS od on scd.so_no = od.Order_ID AND scd.line_no = od.Order_Detail_Line_ID#xd;#xa;inner join SQL03_LINKED_SERVER.JTP_REPORTS.dbo.ORDERS AS o on scd.so_no = o.Order_ID#xd;#xa;where scd.create_date >= '12/01/14' and scd.create_date <= '12/14/14'#xd;#xa;#xd;#xa;" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="211.554" StatementEstRows="12566.2" StatementOptmLevel="FULL" QueryHash="0x04195E8F18D64194" QueryPlanHash="0x51808029E600FC3F"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"/><QueryPlan CachedPlanSize="168" CompileTime="72" CompileCPU="70" CompileMemory="1032"><RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="12566.2" EstimateIO="0" EstimateCPU="0.0525268" AvgRowSize="53" EstimatedTotalSubtreeCost="211.554" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="container_no"/><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="so_no"/><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="ship_no"/><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="line_no"/><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="create_date"/></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="so_no"/></OuterReferences><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="12566.2" EstimateIO="0" EstimateCPU="0.0525268" AvgRowSize="53" EstimatedTotalSubtreeCost="130.605" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="container_no"/><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="so_no"/><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="ship_no"/><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="line_no"/><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="create_date"/></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="so_no"/><ColumnReference Column="Expr1006"/></OuterReferences><RelOp NodeId="2" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="12566.2" EstimateIO="0.00563063" EstimateCPU="0.392765" AvgRowSize="57" EstimatedTotalSubtreeCost="0.563213" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="container_no"/><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="so_no"/><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="ship_no"/><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="line_no"/><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="create_date"/><ColumnReference Column="Expr1006"/></OutputList><MemoryFractions Input="1" Output="1"/><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="so_no"/></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Column="Expr1006"/></OrderByColumn></OrderBy><RelOp NodeId="3" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="12566.2" EstimateIO="0" EstimateCPU="0.000628311" AvgRowSize="57" EstimatedTotalSubtreeCost="0.164817" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="container_no"/><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="so_no"/><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="ship_no"/><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="line_no"/><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="create_date"/><ColumnReference Column="Expr1006"/></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1006"/><ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[COLLECT_MAIN].[dbo].[SHP_CONTAINER_DETAIL].[line_no] as [scd].[line_no],0)"><Convert DataType="int" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="line_no"/></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="4" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="12566.2" EstimateIO="0.157199" EstimateCPU="0.00698993" AvgRowSize="53" EstimatedTotalSubtreeCost="0.164189" TableCardinality="1.17385e+006" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="container_no"/><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="so_no"/><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="ship_no"/><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="line_no"/><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="create_date"/></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="container_no"/></DefinedValue><DefinedValue><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="so_no"/></DefinedValue><DefinedValue><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="ship_no"/></DefinedValue><DefinedValue><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="line_no"/></DefinedValue><DefinedValue><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="create_date"/></DefinedValue></DefinedValues><Object Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Index="[Created_Date]" Alias="[scd]" IndexKind="NonClustered"/><SeekPredicates><SeekPredicateNew><SeekKeys><StartRange ScanType="GE"><RangeColumns><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="create_date"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="'2014-12-01 00:00:00.000'"><Const ConstValue="'2014-12-01 00:00:00.000'"/></ScalarOperator></RangeExpressions></StartRange><EndRange ScanType="LE"><RangeColumns><ColumnReference Database="[COLLECT_MAIN]" Schema="[dbo]" Table="[SHP_CONTAINER_DETAIL]" Alias="[scd]" Column="create_date"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="'2014-12-14 00:00:00.000'"><Const ConstValue="'2014-12-14 00:00:00.000'"/></ScalarOperator></RangeExpressions></EndRange></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></ComputeScalar></RelOp></Sort></RelOp><RelOp NodeId="8" PhysicalOp="Remote Query" LogicalOp="Remote Query" EstimateRows="1" EstimateIO="0" EstimateCPU="0.0103333" AvgRowSize="9" EstimatedTotalSubtreeCost="129.851" Parallel="0" EstimateRebinds="12565.2" EstimateRewinds="0"><OutputList/><RemoteQuery RemoteQuery="SELECT 1 FROM "JTP_REPORTS"."dbo"."ORDERS_DETAILS" "Tbl1003" WHERE ?="Tbl1003"."Order_Detail_Line_ID" AND ?=CONVERT(nvarchar(10),"Tbl1003"."Order_ID",0)" RemoteSource="SQL03_LINKED_SERVER"/></RelOp></NestedLoops></RelOp><RelOp NodeId="9" PhysicalOp="Row Count Spool" LogicalOp="Lazy Spool" EstimateRows="1" EstimateIO="0" EstimateCPU="0.0001001" AvgRowSize="9" EstimatedTotalSubtreeCost="80.8962" Parallel="0" EstimateRebinds="7705.93" EstimateRewinds="4859.3"><OutputList/><RowCountSpool><RelOp NodeId="10" PhysicalOp="Remote Query" LogicalOp="Remote Query" EstimateRows="1" EstimateIO="0" EstimateCPU="0.0103333" AvgRowSize="9" EstimatedTotalSubtreeCost="79.6383" Parallel="0" EstimateRebinds="7705.93" EstimateRewinds="0"><OutputList/><RemoteQuery RemoteQuery="SELECT 1 FROM "JTP_REPORTS"."dbo"."ORDERS" "Tbl1005" WHERE ?=CONVERT(nvarchar(10),"Tbl1005"."Order_ID",0)" RemoteSource="SQL03_LINKED_SERVER"/></RelOp></RowCountSpool></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-22 : 14:39:24

I'm not able to read that xml plan in SSMS or SQL Sentry's Plan Explorer. Getting a hexadecimal error.

I would create a composite index on od.Order_ID, od.Order_Detail_Line_ID and remove the od.Order_ID index since it would be a "duplicate".

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources