asked on

how to call or update data through linked server

Hi,
I'm interested to know what other query that I can use to select or update data through a linkedserver, at of now I'm using below query through my linked server, but not so sure whether is good or bad way to select data

SELECT * FROM OPENQUERY(kiptox,'SELECT top 20000 * FROM northwind..Index_views_Custom')

Guy Hengel [angelIII / a3]

there is nothing "wrong" with using openquery. good or bad... well, what are your alternatives? if you have no alternative, there is no "discussion" about it, only possibly if it is usable or not.

ASKER CERTIFIED SOLUTION

Otana

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

motioneye

ASKER

Hi
Yes how about this one as per advised by Otana??

SELECT * FROM [SERVERNAME].[northwind].[Index_views_Custom]

as comparisons to use openquery with a normal select statement??

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

motioneye

ASKER

Hi,
the reason why I asking what the benefit between two is bcoz when I anylze the query through estimated execution plan, a query with T-sql openquery performing better since the subtree cost are lower compare to the normal select.

Using openquery subtree cost =3.36333

StmtText EstimateCPU AvgRowSize TotalSubtreeCost
-------------------------------------------------------------------------------------------------- ------------- ----------- ----------------
SELECT * FROM OPENQUERY(ktopix,'SELECT top 20000 * FROM northwind..Index_views_Custom') NULL NULL 3.363333
|--Remote Scan(SOURCE:(Ktopix), OBJECT:(SELECT top 20000 * FROM northwind..Index_views_Custom)) 3.363333 90 3.363333

(2 row(s) affected)

another query with normal T-sql TotalSubtreeCost

StmtText EstimateCPU AvgRowSize TotalSubtreeCost=6.676667
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - ------------- ----------- ----------------

SELECT top 20000 * FROM [KYAHYA].[northwind].[DBO].[Index_views_Custom] 1 NULL NULL 6.676667 N
|--Remote Query(SOURCE:(KYAHYA), QUERY:(SELECT TOP 20000 "Tbl1002"."CustomID" "Col1004","Tbl1002"."CustomName" "Col1005","Tbl1002"."Phone" "Col1006" FROM "northwind"."DBO"."Index_views_Custom" "Tbl1002")) 6.676667 90 6.676667

(2 row(s) affected)

any idea why it behave this such way?

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial