OPENQUERY vs. select * from Server.db.dbo.table

[vadimg]

SQL2K SP3a
Windows 2000 Advanced Server

gurus... How is OPENQUERY different from SELECT * FROM SERVER.DB.DBO.TABLE ? (where linked server is another SQL2K server)

and why is OPENQUERY so much faster?


select * from LINKEDSERVER.database.dbo.table where value = 1

execution time (freeproccache+dropcleanbuffers) -- 180ms

select * from openquery(LINKEDSERVER, 'select * from database.dbo.table where value = 1')

execution time ((freeproccache+dropcleanbuffers)) -- 20ms

 

[NathanGriffiths]

A delve into BOL reveals the following that may help;

[blue]from "Passing Queries from SQL Server to a Linked Analysis Server" in the Analysis Services Programming section.[/blue]


"For best results with pass-through queries from SQL Server to Analysis Services, use the Transact-SQL function OPENQUERY to execute SQL commands between servers. OPENQUERY sends the commands of the query directly to the Analysis server, which then returns flattened rowsets (as described in the OLE DB documentation) that contain the requested data."

"It is possible to access the data of a cube directly from SQL Server using queries with four-part naming. (The four parts are linked-server-name, catalog, schema, and table.) However, this option is not recommended because SQL Server attempts to copy the contents of the entire fact table and then perform the calculations for aggregating the data itself, substantially increasing the query response time."




Nathan

----------------------------------------
Want to get a good response to your question? Read this FAQ! -> faq183-874
----------------------------------------

 

[vadimg]

Nathan, thanks, but the question is not about linked analysis server.

4 part name request does not copy over the whole table in this case. The filter is run on the remote server (can see it in profiler), and returns only 1 row (you can see it in the execution plan).

 

[kevinbk]

Are you sure that the "fact table" transfer is the actual data table and not some sort of schema definition transfer or reference validation?

The problem I have with OPENQUERY is that it doesn't allow parameterization of the query string. The problem with the 4 part naming convention method is that not all data sources support exposing the relevant parts, schema name is often left unexposed or is non-existant in some data sources.

It is likely that the mere existence of the 4 part naming on the table reference causes the overhead you saw in your timing stats. If they are there, Microsoft checks/validates them.