Complex OpenQuery statement
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;
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/
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/
ASKER
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.
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.
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.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I went with a tool to sync my linked server to my sql server database tables.
OpenQuery does not allow optimizing the remote part, it executes the complete SQL remotely and than can only work on that complete resultset.