How Salesforce Connect Uses OData Options & API Calls

Salesforce Connect is a service that links external data to Salesforce and uses them as external Salesforce objects. It supports connecting to an external data source via either a custom Salesforce Connect adapter or OData protocol.

This article sheds some light on how exactly Salesforce Connect works with an external data source via OData v4 protocol and which API calls it uses when working with Skyvia Connect.

Skyvia Connect Overview

Skyvia Connect is a connectivity-as-a-service solution that exposes your database and cloud data via the OData protocol with no coding and minimal configuration. It creates ready-to-use endpoints, so you don’t need to care about deployment, administration, site certificates, etc.

Skyvia Connect provides a separate security layer and allows creating users with passwords for your OData endpoints and limiting access to them by IP address ranges. It also provides an advanced logging feature for your OData endpoint, which we will use to demonstrate how Salesforce works with external data via Salesforce Connect and which OData API calls it uses.

This article can be especially interesting for Salesforce Connect and Skyvia Connect users because Skyvia Connect pricing is based on the endpoints’ traffic. So it’s very important to know which and how many data are queried and returned when accessing them in Salesforce.

Data Source Creation

You can find a description of creating an external data source in Skyvia documentation or in Salesforce documentation. In this article, we just describe what calls Salesforce performs when creating and working with a data source.

For this example, we link Salesforce Connect to an external database with a subset of tables from Microsoft’s sample AdventureWorks 2014 database, running on SQL Server 2014. We have created an OData endpoint for this database in Skyvia Connect. To simplify the example, We haven’t created any users for this endpoint; thus, it is public (does not require authentication).

When an external data source is created, Salesforce Connect performs two OData calls when you validate and sync the data source – to the endpoint root and to its metadata.

https://endpoint.skyvia.com/********/

https://endpoint.skyvia.com/********/$metadata

As you can see on the screenshot, these are requests from the IP 13.110.14.8, which is one of the Salesforce service IP addresses.

Salesforce Connect repeats these calls when you sync the external data source objects. It also performs these requests before any access to external objects if you haven’t accessed them for some time in order to make sure that the source endpoint works.

Viewing Tab with External Objects

So, let’s create a tab for an external object and try opening this tab. When you open the tab for the first time, and there are no recent objects, no additional requests are made. If you have some recent items, Salesforce Connect performs the following request:

https://endpoint.skyvia.com/********/Products?$filter=ProductID eq 4 or ProductID eq 878&$count=true&$select=ProductID

As we can see, it queries the IDs of Products, which are equal to the IDs of the remembered recent items. Skyvia Connect generates the following SQL for such a request:

SELECT COUNT_BIG(*) FROM Production.Product AS t WITH (NOLOCK) WHERE (t.ProductID = 4 OR t.ProductID = 878) 

SELECT t.ProductID FROM Production.Product AS t WITH (NOLOCK) WHERE (t.ProductID = 4 OR t.ProductID = 878)  

All Objects

The All objects view for an external object displays external IDs and object URLs by default. In our log, we can see that the following call is performed:

https://endpoint.skyvia.com/********/Products?$top=201&$orderby=ProductID&$count=true&$select=ProductID

As we can see, Salesforce queries only the ProductID primary key column for the first 201 products ordered by ProductID, to display the first page with 200 products. By default, Salesforce always also queries the count of objects.

Skyvia Connect generates the following SQL to query these data:

SELECT COUNT_BIG(*) FROM Production.Product AS t WITH (NOLOCK)  
and

SELECT t.ProductID FROM Production.Product AS t WITH (NOLOCK) ORDER BY t.ProductID OFFSET 0 ROWS FETCH FIRST 201 ROWS ONLY

When we navigate to the next pages, Salesforce connect executes similar requests, using OData $top and $skip query options for paging.

https://endpoint.skyvia.com/********/Products?$skip=200&$top=201&$orderby=ProductID&$count=true&$select=ProductID

https://endpoint.skyvia.com/********/Products?$skip=400&$top=201&$orderby=ProductID&$count=true&$select=ProductID

…

etc.

Every time to display the next page with 200 objects, Salesforce Connect requests 201 object to check if there are more objects after the 200th.

Skyvia Connect generates the following SQL for such requests.

SELECT COUNT_BIG(*) FROM Production.Product AS t WITH (NOLOCK) 

SELECT t.ProductID FROM Production.Product AS t WITH (NOLOCK) ORDER BY t.ProductID OFFSET 200 ROWS FETCH FIRST 201 ROWS ONLY

View with More Fields

Let’s create a new view in our tab and display more fields of the product object.

When switching to such a view, Salesforce Connect performs the following OData request:

https://endpoint.skyvia.com/********/Products?$top=201&$orderby=ProductID&$count=true&$select=Color,ListPrice,Name,ProductID,Size,Style,WeightAs you can see, Salesforce Connect adds all the displayed fields to the $select query options and queries the necessary records.

In turn, Skyvia Connect generates the following SQL:

SELECT COUNT_BIG(*) FROM Production.Product AS t WITH (NOLOCK) 

SELECT t.Color, t.ListPrice, t.Name, t.ProductID, t.Size, t.Style, t.Weight FROM Production.Product AS t WITH (NOLOCK) ORDER BY t.ProductID OFFSET 0 ROWS FETCH FIRST 201 ROWS ONLY

View with Filters

Next, let’s add a couple of filters to our view.

Salesforce Connect in such case performs the following request:

https://endpoint.skyvia.com/********/Products?$top=201&$filter=DiscontinuedDate eq null and ListPrice ge 10&$orderby=ProductID&$count=true&$select=Color,ListPrice,Name,ProductID,Size,Style,Weight

I.e. Salesforce Connect uses the $filter query option in requests when it needs to filter data, and thus, all the filtering is performed on the data source side, and only filtered data are returned by the endpoint. Skyvia Connect generates the following SQL:

SELECT COUNT_BIG(*) FROM Production.Product AS t WITH (NOLOCK) WHERE (t.DiscontinuedDate IS NULL AND t.ListPrice >= 10) 

SELECT t.Color, t.ListPrice, t.Name, t.ProductID, t.Size, t.Style, t.Weight FROM Production.Product AS t WITH (NOLOCK) WHERE (t.DiscontinuedDate IS NULL AND t.ListPrice >= 10) ORDER BY t.ProductID OFFSET 0 ROWS FETCH FIRST 201 ROWS ONLY

Ordering

If we sort data in our view by some field, for example, ListPrice, Salesforce Connect performs a request, ordering data via $orderby query option:

https://endpoint.skyvia.com/********/Products?$top=201&$filter=DiscontinuedDate eq null and ListPrice ge 10&$orderby=ListPrice&$count=true&$select=Color,ListPrice,Name,ProductID,Size,Style,Weight

Skyvia Connect generates the following SQL:

SELECT COUNT_BIG(*) FROM Production.Product AS t WITH (NOLOCK) WHERE (t.DiscontinuedDate IS NULL AND t.ListPrice >= 10) 

SELECT t.Color, t.ListPrice, t.Name, t.ProductID, t.Size, t.Style, t.Weight FROM Production.Product AS t WITH (NOLOCK) WHERE (t.DiscontinuedDate IS NULL AND t.ListPrice >= 10) ORDER BY t.ListPrice OFFSET 0 ROWS FETCH FIRST 201 ROWS ONLY 

Working with Single Object

Viewing Object

When you open an external object in Salesforce, Salesforce Connect queries all the available fields of this object by its ID.

https://endpoint.skyvia.com/********/People?$top=2&$filter=BusinessEntityID eq 3&$count=true&$select=AdditionalContactInfo,BusinessEntityID,Demographics,EmailPromotion,FirstName,LastName,MiddleName,ModifiedDate,NameStyle,PersonType,Suffix,Title,rowguid

As we can see, it queries an object by id using the $filter query option instead and queries up to two records to check whether there are objects with a duplicate key.

Skyvia Connect generates the following SQL for such a request:

SELECT COUNT_BIG(*) FROM Person.Person AS t WITH (NOLOCK) WHERE (t.BusinessEntityID = 3) 

SELECT t.AdditionalContactInfo, t.BusinessEntityID, t.Demographics, t.EmailPromotion, t.FirstName, t.LastName, t.MiddleName, t.ModifiedDate, t.NameStyle, t.PersonType, t.Suffix, t.Title, t.rowguid FROM Person.Person AS t WITH (NOLOCK) WHERE (t.BusinessEntityID = 3) ORDER BY (SELECT NULL) OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY

Editing Object

When you click Edit, Salesforce re-reads the object (re-executes the above request). After you modify the object and click Save, Salesforce Connect performs three requests.

First, it re-reads the object again with the same request as above. Then it performs POST requests for the object referenced by ID.

https://endpoint.skyvia.com/********/People(3)

This request updates the object.

And finally, the object is re-read after the update, with the same request as in the previous section.

Object with Relationships

In the AdventureWorks database Products belong to ProductSubcategories, which in turn belong to ProductCategories. We have created corresponding External Lookup relationships between these external objects in Salesforce. So let’s see Salesforce Connect behavior when working with an external object, having relationships. We’ll open a ProductSubcategory, because it is a child for the ProductCategories object and a parent for the Products object.

By default, the related list of products is not displayed. So, when opening such an object, Salesforce Connect queries the fields of the corresponding ProductSubcategory, in the same way as in the previous example with the People object.

https://endpoint.skyvia.com/********/ProductSubcategories?$top=2&$filter=ProductSubcategoryID eq 2&$count=true&$select=ModifiedDate,Name,ProductCategoryID,ProductSubcategoryID,rowguid

Let’s add the related products list to the ProductSubcategories layout and see what will change.

As you can see, Salesforce now displays the subcategory with the list of its products.

In the Skyvia Connect log, we can see two calls, one querying the subcategory (in the same way as in the previous time), and the second – querying the Products fields, displayed in the related object list, for the products, belonging to the subcategory:

https://endpoint.skyvia.com/********/ProductSubcategories?$top=2&$filter=ProductSubcategoryID eq 2&$count=true&$select=ModifiedDate,Name,ProductCategoryID,ProductSubcategoryID,rowguid

https://endpoint.skyvia.com/********/Products?$top=86&$filter=ProductSubcategoryID eq 2&$orderby=Name&$count=true&$select=ListPrice,Name,ProductID,Weight

These two calls result in the following SQL queries:

SELECT COUNT_BIG(*) FROM Production.ProductSubcategory AS t WITH (NOLOCK) WHERE (t.ProductSubcategoryID = 2) 

SELECT t.ModifiedDate, t.Name, t.ProductCategoryID, t.ProductSubcategoryID, t.rowguid FROM Production.ProductSubcategory AS t WITH (NOLOCK) WHERE (t.ProductSubcategoryID = 2) ORDER BY (SELECT NULL) OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY 


SELECT COUNT_BIG(*) FROM Production.Product AS t WITH (NOLOCK) WHERE (t.ProductSubcategoryID = 2) 

SELECT t.ListPrice, t.Name, t.ProductID, t.Weight FROM Production.Product AS t WITH (NOLOCK) WHERE (t.ProductSubcategoryID = 2) ORDER BY t.Name OFFSET 0 ROWS FETCH FIRST 86 ROWS ONLY  

Reports

Now let’s see how Salesforce queries data for its reports. Let’s create a report with filtering and grouping.

Note that you need to edit the external object and select its Allow Reports check box to make it available for reports.

To demonstrate Salesforce reporting features, we have created a report type on four connected external objects from the SQL Server: ProductCategories, ProductSubcategories, Products, and ProductListPriceHistories. In our report, we get the average price for the product from ProductListPriceHistories, grouping results by product, subcategory, and category names. ProductCategories are filtered by the ModifiedDate field.

Creating Report

When creating and previewing this report, Salesforce shows results for 20 records. Let’s see what API calls Salesforce Connect performs to get the necessary data:

Salesforce Connect performs five requests, a request for ProductCategories, ProductSubcategories, and Products, and two requests for ProductListPriceHistories:

https://endpoint.skyvia.com/********/ProductCategories?$top=50&$filter=ModifiedDate ge 1999-12-31T22:00:00Z and ModifiedDate lt 2018-12-31T22:00:00Z&$count=true&$select=Name,ProductCategoryID

https://endpoint.skyvia.com/********/ProductSubcategories?$top=50&$filter=(ProductCategoryID eq 1 or ProductCategoryID eq 2 or ProductCategoryID eq 3 or ProductCategoryID eq 4)&$count=true&$select=Name,ProductCategoryID,ProductSubcategoryID

https://endpoint.skyvia.com/********/Products?$top=50&$filter=(ProductSubcategoryID eq 22 or ProductSubcategoryID eq 23 or ProductSubcategoryID eq 24 or ProductSubcategoryID eq 25 or ProductSubcategoryID eq 26 or ProductSubcategoryID eq 27 or ProductSubcategoryID eq 28 or ProductSubcategoryID eq 29 or ProductSubcategoryID eq 30 or ProductSubcategoryID eq 31 or ProductSubcategoryID eq 10 or ProductSubcategoryID eq 32 or ProductSubcategoryID eq 11 or ProductSubcategoryID eq 33 or ProductSubcategoryID eq 12 or ProductSubcategoryID eq 34 or ProductSubcategoryID eq 13 or ProductSubcategoryID eq 35 or ProductSubcategoryID eq 14 or ProductSubcategoryID eq 36 or ProductSubcategoryID eq 15 or ProductSubcategoryID eq 37 or ProductSubcategoryID eq 16 or ProductSubcategoryID eq 17 or ProductSubcategoryID eq 18 or ProductSubcategoryID eq 19 or ProductSubcategoryID eq 1 or ProductSubcategoryID eq 2 or ProductSubcategoryID eq 3 or ProductSubcategoryID eq 4 or ProductSubcategoryID eq 5 or ProductSubcategoryID eq 6 or ProductSubcategoryID eq 7 or ProductSubcategoryID eq 8 or ProductSubcategoryID eq 9 or ProductSubcategoryID eq 20 or ProductSubcategoryID eq 21)&$count=true&$select=Name,ProductID,ProductSubcategoryID

https://endpoint.skyvia.com/********/ProductListPriceHistories?$top=50&$filter=(ProductID eq 709 or ProductID eq 750 or ProductID eq 751 or ProductID eq 752 or ProductID eq 753 or ProductID eq 710 or ProductID eq 754 or ProductID eq 711 or ProductID eq 712 or ProductID eq 713 or ProductID eq 714 or ProductID eq 715 or ProductID eq 716 or ProductID eq 717 or ProductID eq 718 or ProductID eq 719 or ProductID eq 680 or ProductID eq 720 or ProductID eq 721 or ProductID eq 722 or ProductID eq 723 or ProductID eq 724 or ProductID eq 725 or ProductID eq 726 or ProductID eq 727 or ProductID eq 728 or ProductID eq 729 or ProductID eq 730 or ProductID eq 731)&$count=true&$select=ListPrice,ProductID,StartDate

https://endpoint.skyvia.com/********/ProductListPriceHistories?$top=50&$filter=(ProductID eq 732 or ProductID eq 733 or ProductID eq 734 or ProductID eq 735 or ProductID eq 736 or ProductID eq 737 or ProductID eq 738 or ProductID eq 739 or ProductID eq 740 or ProductID eq 741 or ProductID eq 742 or ProductID eq 743 or ProductID eq 744 or ProductID eq 745 or ProductID eq 746 or ProductID eq 747 or ProductID eq 748 or ProductID eq 749 or ProductID eq 706 or ProductID eq 707 or ProductID eq 708)&$count=true&$select=ListPrice,ProductID,StartDate

As we can see from these requests, Salesforce Connect first queried names and IDs of 50 ProductCategories, satisfying the ModifiedDate filter. Then it queried names, IDs, and category IDs first 50 ProductSubcategories from the returned ProductCategories (filtered subcategories by IDs of parent categories). Then the same with Products – Salesforce Connect queries names, IDs, and subcategory IDs of 50 products from the subcategories, returned by the previous requests. Finally, Salesforce Connect queries primary key values and ListPrice from the ProductListPriceHistories of the products from the previous requests. It performs two requests for different product IDs because Salesforce Connect limits the URL length, and if it becomes too big, Salesforce Connect splits a request in two.

Salesforce Connect filters data on the data source side using the $filter query option and queries only the necessary fields, but all the joins and aggregations are performed on the Salesforce side.

Running Report

Now let’s start our report and see what requests will be made by Salesforce Connect.

In the log we can see the following requests for categories, subcategories, and products:

https://endpoint.skyvia.com/********/ProductCategories?$top=2000&$filter=ModifiedDate ge 1999-12-31T22:00:00Z and ModifiedDate lt 2018-12-31T22:00:00Z&$count=true&$select=Name,ProductCategoryID

https://endpoint.skyvia.com/********/ProductSubcategories?$filter=(ProductCategoryID eq 1 or ProductCategoryID eq 2 or ProductCategoryID eq 3 or ProductCategoryID eq 4)&$count=true&$select=Name,ProductCategoryID,ProductSubcategoryID

https://endpoint.skyvia.com/********/Products?$filter=(ProductSubcategoryID eq 1 or ProductSubcategoryID eq 2 or ProductSubcategoryID eq 3 or ProductSubcategoryID eq 4 or ProductSubcategoryID eq 5 or ProductSubcategoryID eq 6 or ProductSubcategoryID eq 7 or ProductSubcategoryID eq 8 or ProductSubcategoryID eq 9 or ProductSubcategoryID eq 10 or ProductSubcategoryID eq 11 or ProductSubcategoryID eq 12 or ProductSubcategoryID eq 13 or ProductSubcategoryID eq 14 or ProductSubcategoryID eq 15 or ProductSubcategoryID eq 16 or ProductSubcategoryID eq 17 or ProductSubcategoryID eq 18 or ProductSubcategoryID eq 19 or ProductSubcategoryID eq 20 or ProductSubcategoryID eq 21 or ProductSubcategoryID eq 22 or ProductSubcategoryID eq 23 or ProductSubcategoryID eq 24 or ProductSubcategoryID eq 25 or ProductSubcategoryID eq 26 or ProductSubcategoryID eq 27 or ProductSubcategoryID eq 28 or ProductSubcategoryID eq 29 or ProductSubcategoryID eq 30 or ProductSubcategoryID eq 31 or ProductSubcategoryID eq 32 or ProductSubcategoryID eq 33 or ProductSubcategoryID eq 34 or ProductSubcategoryID eq 35 or ProductSubcategoryID eq 36 or ProductSubcategoryID eq 37)&$count=true&$select=Name,ProductID,ProductSubcategoryID

As we can see, requests to ProductCategories limit the result to 2000 records instead of 50. This is Salesforce Connect limitations for reports over external objects – up to 2000 records can be queried from the main report object.

Requests to ProductSubcategories and Products entity sets don’t have a limit on the number of records. Otherwise, they are the same as in the previous example because there are only so many categories and subcategories in the database.

However, the number of products is much more than the 50 record limit, applied in Salesforce for preview. So, after these three requests, a series of requests to ProductListPriceHistories are filtered by product IDs. We won’t provide this series here, because it is too long, and the requests are very similar to the corresponding requests for the report preview. The only difference is the absence of the $top query option.

Search

Search in external objects is disabled by default. To be able to search for an external object, you need to edit the corresponding external data source and select the Enable Search check box for it. You must also select the Allow Search check box for the corresponding external objects.

When searching for some text in Salesforce, Salesforce Connect performs the following request to each of the external objects for which search is enabled in Salesforce:

https://endpoint.skyvia.com/********/Products?$top=6&$search=Touring Seat&$count=true&$select=Class,Color,DaysToManufacture,DiscontinuedDate,FinishedGoodsFlag,ListPrice,MakeFlag,ModifiedDate,Name,ProductID,ProductLine,ProductModelID,ProductNumber,ProductSubcategoryID,ReorderPoint,SafetyStockLevel,SellEndDate,SellStartDate,Size,SizeUnitMeasureCode,StandardCost,Style,Weight,WeightUnitMeasureCode,rowguid

As we can see, Salesforce Connect queries all the fields of the first six records from the searched object and uses the $search query option to search text. Skyvia Connect generates the following SQL for this request:

SELECT COUNT_BIG(*) FROM Production.Product AS t WITH (NOLOCK) WHERE ((LOWER(t.Name) LIKE '%touring%' OR LOWER(t.ProductNumber) LIKE '%touring%' OR LOWER(t.Color) LIKE '%touring%' OR LOWER(t.Size) LIKE '%touring%' OR LOWER(t.SizeUnitMeasureCode) LIKE '%touring%' OR LOWER(t.WeightUnitMeasureCode) LIKE '%touring%' OR LOWER(t.ProductLine) LIKE '%touring%' OR LOWER(t.Class) LIKE '%touring%' OR LOWER(t.Style) LIKE '%touring%') AND (LOWER(t.Name) LIKE '%seat%' OR LOWER(t.ProductNumber) LIKE '%seat%' OR LOWER(t.Color) LIKE '%seat%' OR LOWER(t.Size) LIKE '%seat%' OR LOWER(t.SizeUnitMeasureCode) LIKE '%seat%' OR LOWER(t.WeightUnitMeasureCode) LIKE '%seat%' OR LOWER(t.ProductLine) LIKE '%seat%' OR LOWER(t.Class) LIKE '%seat%' OR LOWER(t.Style) LIKE '%seat%')) 

SELECT t.Class, t.Color, t.DaysToManufacture, t.DiscontinuedDate, t.FinishedGoodsFlag, t.ListPrice, t.MakeFlag, t.ModifiedDate, t.Name, t.ProductID, t.ProductLine, t.ProductModelID, t.ProductNumber, t.ProductSubcategoryID, t.ReorderPoint, t.SafetyStockLevel, t.SellEndDate, t.SellStartDate, t.Size, t.SizeUnitMeasureCode, t.StandardCost, t.Style, t.Weight, t.WeightUnitMeasureCode, t.rowguid FROM Production.Product AS t WITH (NOLOCK) WHERE ((LOWER(t.Name) LIKE '%touring%' OR LOWER(t.ProductNumber) LIKE '%touring%' OR LOWER(t.Color) LIKE '%touring%' OR LOWER(t.Size) LIKE '%touring%' OR LOWER(t.SizeUnitMeasureCode) LIKE '%touring%' OR LOWER(t.WeightUnitMeasureCode) LIKE '%touring%' OR LOWER(t.ProductLine) LIKE '%touring%' OR LOWER(t.Class) LIKE '%touring%' OR LOWER(t.Style) LIKE '%touring%') AND (LOWER(t.Name) LIKE '%seat%' OR LOWER(t.ProductNumber) LIKE '%seat%' OR LOWER(t.Color) LIKE '%seat%' OR LOWER(t.Size) LIKE '%seat%' OR LOWER(t.SizeUnitMeasureCode) LIKE '%seat%' OR LOWER(t.WeightUnitMeasureCode) LIKE '%seat%' OR LOWER(t.ProductLine) LIKE '%seat%' OR LOWER(t.Class) LIKE '%seat%' OR LOWER(t.Style) LIKE '%seat%')) ORDER BY (SELECT NULL) OFFSET 0 ROWS FETCH FIRST 6 ROWS ONLY

Skyvia Connect implements the $search query option via a lot of LIKE clauses, so this query may take a lot of time to run, especially if there are a lot of data to search, and it may require a significant amount of resources on the database side. So please consider this aspect when enabling search for external objects accessed via Skyvia Connect.

Export via API

Now let’s try to retrieve all data from an external object via Salesforce API. We will use Skyvia’s export to do it.

In the log, we can see a number of calls querying data from the Person table (the People object). These requests look like the following:

https://endpoint.skyvia.com/********/People?$top=251&$count=true&$select=AdditionalContactInfo,BusinessEntityID,Demographics,EmailPromotion,FirstName,LastName,MiddleName,ModifiedDate,NameStyle,PersonType,Suffix,Title,rowguid

https://endpoint.skyvia.com/********/People?$skip=250&$top=251&$count=true&$select=AdditionalContactInfo,BusinessEntityID,Demographics,EmailPromotion,FirstName,LastName,MiddleName,ModifiedDate,NameStyle,PersonType,Suffix,Title,rowguid

https://endpoint.skyvia.com/********/People?$skip=500&$top=251&$count=true&$select=AdditionalContactInfo,BusinessEntityID,Demographics,EmailPromotion,FirstName,LastName,MiddleName,ModifiedDate,NameStyle,PersonType,Suffix,Title,rowguid

…

As you can see, Salesforce Connect queries data in pages with 250 records each, and each time it queries one more record to check whether there are more data after the current page. Skyvia Connect generates SQL like this for such requests:

SELECT COUNT_BIG(*) FROM Person.Person AS t WITH (NOLOCK) 

SELECT t.AdditionalContactInfo, t.BusinessEntityID, t.Demographics, t.EmailPromotion, t.FirstName, t.LastName, t.MiddleName, t.ModifiedDate, t.NameStyle, t.PersonType, t.Suffix, t.Title, t.rowguid FROM Person.Person AS t WITH (NOLOCK) ORDER BY (SELECT NULL) OFFSET 500 ROWS FETCH FIRST 251 ROWS ONLY

If we query data from another object, we will see the following in the log:

As you can see, for the Addresses object, Salesforce Connect sets the page size to 2000 records. Page size depends on the external object structure.

Count

As you probably noticed, by default Salesforce Connect adds $count=true with all OData requests. In Skyvia Connect, this results in an additional SQL query SELECT COUNT_BIG(*) …

For better performance, consider disabling requesting count. To disable it, edit the external data source and clear the Request Row Counts check box. Please note, however, that some Salesforce features, like using the COUNT() aggregate function for external objects in SOQL or using batch Apex with Database.QueryLocator to access external objects require the Request Row Counts check box selected.

Conclusion

Salesforce Connect in most cases queries only the data it needs to display or use. It selects only the necessary fields, and it performs all the filtering and searching on the data source side. Salesforce Connect queries data in pages with the size either corresponding to the displayed data pages or depending on the object structure.

Aggregations and grouping for reports, however, are performed by Salesforce itself. This is because not many OData producers support all the necessary OData aggregation features. Salesforce Connect uses only filtering to reduce the size of loaded data for reports.

Salesforce Connect practically never queries all the data from an external object unless you query all the data from it, for example, via the Salesforce API. As for Skyvia Connect pricing, which depends on traffic, you don’t need to worry much. Salesforce Connect queries only the data it needs in almost all cases with little overhead, and it uses only the amount of traffic that is really needed.