Hi all,
we have installed on SQL 2019 Developer Edition the Polybase features.
After that, we've created an external data source towards our Mongodb server, where we've stored lots of big tables containing applicative logs.
*CREATE DATABASE SCOPED CREDENTIAL mongo_creds WITH IDENTITY = 'XXXXXXXXXXXX', Secret = 'XXXXX!';
go
CREATE EXTERNAL DATA SOURCE MongoDBSource
WITH (LOCATION = 'mongodb://nnn.nnn.nnn.nn:27017',
CREDENTIAL = mongo_creds,
CONNECTION_OPTIONS = 'ssl=false;')
go
CREATE EXTERNAL TABLE...
It works, but we have experienced many performance problems in querying large tables (about 50-100 GB) on Mongodb, as soon as we try to query with conditions.
The "select top 100 *" is lightning fast, but running a query "...where field = 15" can take more than twenty minutes. On any table, despite the same query on Mongo lasts just a second.
In addition, we have noticed that when we run the query from SQL, the system tries to "download" the entire table to the swap files on the Windows machine ( C: \ Windows \ ServiceProfiles \ NetworkService \ AppData \ Local \ Temp)...
Anyone have any idea what we could try to speed up queries and avoid this kind of swap?
Thanks in advance.
Luca