OpenQuery Vs four-part names(linked server)

Hi,

I just want to know, is there any difference,advantages or disadvantages in using openquery & four-part names(linked server)?

I generally use four-part names to combine multiple tables. so just want to know is there any perf gain if i use openquery.

Sample Queries:
select * FROM OPENQUERY(ServerName, ''SELECT a.code ....

select b.code from ServerName.Database.dbo.tableName a


Thanks,
Balasundaram

There can be a major difference.

A standard four-part linked server query will often return the data row by row with a slow cursor. OPENQUERY will generate a pass-through query and return the data in a single batch but will generally use more memory. For simple selects I would use OPENQUERY. For complex joins the four-part syntax with correct indexes is probably the way to go.

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1944
http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=118
http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=449

Thanks Argyle,

This is really useful information.

Hi Argyle,

I am looking out for difference by running below query using Openquery.

select a.* from openquery(rcip_shadow,'select count(bseg_id) from CI_BSEG_SQ ') a
where a.bseg_id = '598889689650'

select a.* from openquery(rcip_shadow,'select count(bseg_id) from CI_BSEG_SQ where a.bseg_id = ''598889689650''') a

** The table contains 75000000 records (DW database in oracle)

First one is very slow. and second one runs faster and gives me within a second. I have also

My problem is, i need to pass nearly about 30000 values to the openquey, so i am using while loop now. I am trying to avoid the while loop as it runs for # of times.

What is way to improve the query performance using four part linked query. Please note there are couple of other tables being involved in this query.

I am looking forward to your reply. Thanks