Complex OpenQuery statement

Southern_Gentleman
Southern_Gentleman used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Qlemo"Batchelor", Developer and EE Topic Advisor
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.
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Qlemo"Batchelor", Developer and EE Topic Advisor
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.
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
OK, now I understand you would like to use T-SQL MERGE command to sync your SQL Server data with SQLite database.

But you still did not specify missing info. How many rows are we talking about? You should also specify whether you need to insert missing rows or also update existing rows. And what about deletions?

This will most likely mean to read the whole SQLite table so it is irrelevant whether you use OPENQUERY or Linked Server four parts query. The OPENQUERY will read the whole table just once. Four parts query may cause multiple readings from SQLite but that depends on the execution plan used.

The SQLite syntax must be in the OPENQUERY call only. Four parts query requires T-SQL syntax.

Timestamp in SQLite table would reduce the amount of data necessary to transfer to SQL Server for synchronization. If you don't have any timestamp but you are able to identify what data were changed then it could also be OK.

What I would do is: Use OPENQUERY to read all (or changed) data from SQLite database to SQL Server temporary table and then process this temp table in SQL Server.

If you were looking for other methods of data syncing then you may look at various 3rd party tools, e.g. SyncStudio (https://www.dbsyncstudio.com/challenges-of-mobile-db-sync/) or DBSync (https://dbconvert.com/sqlite/mssql/).

Of course, you could write your own tool in e.g. C# but that's outside of this question scope probably.

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial