We help IT Professionals succeed at work.

Complex OpenQuery statement

117 Views
Last Modified: 2018-12-27
I'm wondering if it possible to query statement below in openquery. What are the limitations. I'd like to query the linkedserver directly instead of creating a merged statement to sync the tables from the linkedserver to sql server table.


	select CAST(a.dtevent as date) as 'date',
	Count(*) as '#ofTrans', max(CAST(a.dtevent as datetime)) as 'lastTrans'
	From [dbo].[wdir_Weborderupload_wdevents] as a 
	GROUP BY CAST(a.dtevent as date)
	order by date desc
	OFFSET 0 ROWS FETCH NEXT 7 ROWS ONLY;

Open in new window

Comment
Watch Question

Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
You are working with 7 rows from the linked server. That doesn't look like you should invest effort in optimizing. But that depends on what kund of synchronisation and correlation you have between the tables.
OpenQuery does not allow optimizing the remote part, it executes the complete SQL remotely and than can only work on that complete resultset.
CERTIFIED EXPERT

Commented:
In short:
OPENQUERY sends the complete query to the remote server so all optimizations are done on the remote server.
Commands executed as Linked Server four parts queries may vary.

The optimization in your case depends on index on [date] column existence. Does the table have such index? If yes then there should be no major difference in the speed between the two commands.

You may read more about the two kinds of query e.g. here: https://blogs.msdn.microsoft.com/sqlsakthi/2011/05/08/best-performer-distributed-query-four-part-or-openquery-when-executing-linked-server-queries-in-sql-server/

Author

Commented:
so is there a good way for me to sync the linked server to my SQL server database tables. I used an odbc driver to add a linked server from an SQLite database im just having trouble syncing the linked database to the SQL server database without always using a merge statement.
Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
Without knowing a lot about the data, good advice is difficult to give. Usually you use local staging table containing interesting rows, and act on that one. It depends on what kind of sync you need, how many rows are to process and much more.
CERTIFIED EXPERT

Commented:
First of all you should explain what do you mean by the "merge statement", second the query in your question does not follow SQLite syntax.

If the "merge" means to create a text string which is then used in OPENQUERY call then I don't see it problematic in any way.

If you need to sync some data from SQLite to SQL Server then you would need some timestamp which you may use to retrieve just the data updated since the last synchronization. Do you have such column in your data? Or would you like to "sync" just the last 7 rows independently on the current SQL Server data contents?

Once you know what should be the correct syncing process then you may design SQL command for data reading with appropriate syntax for given environment.  To read just the 7 rows in SQLite you would need LIMIT 7 OFFSET 0.

The best way I would propose is to read all the new/changed rows (or whatever number of rows you need to transfer) from SQLite db and store them to a temporary table and process them at the SQL Server side.

Author

Commented:
From the my original question how would I write that statement in sqlite syntax? from what i'm reading in all my online articles, i guess i'll have to create a merge query to update,insert and delete to sync my linkedservers (in my sql server linked server folder) to my MS SQL Server tables. I was simply looking for advice on if there is any other method of syncing data from a linedserver to an MSSql table.  If i'm using a Merge i don't understand why i need to create a timestamp.

linkedserver
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
I went with a tool to sync my linked server to my sql server database tables.