Is there any way of limiting rows returned (i.e. SELECT TOP x)

Data Abstract
1

Is there any way of limiting the number of rows returned from the server when opening a TDAMemDataTable? Effectively equivalent to a SELECT TOP command.

2 Likes
2

MaxRecords ?

3

Aha, never noticed that before! :smile:

Interestingly, I was just looking at the MaxRecords property of TableRequestInfoV5 and spotted this:

Limiting the amount of transferred rows can reduce traffic between the
DataAbstract server (middle tier) and the DataAbstract client (front-end
tier). It will not affect traffic between the database and the
DataAbstract server. For reducing traffic between the database and the
DataAbstract server, you have to use other approaches, like Dynamic Where.

This suggests that all data is retrieved from the database but only a subset is then returned to the client. Two questions arise:

Q1: Why is this the case, surely it would be more logical and efficient for DA to construct a SELECT TOP statement so only the subset of rows are retrieved from the database?

Q2: Does the table’s MaxRecords property function in the same way?

4

Not all DB supports SELECT TOP.
We use read-only unidirectional queries so they work for most requests with acceptable performance.
For high performance queries, you can use custom SQLs with SELECT TOP statements.

table’s MaxRecords is passed as a TableRequestInfo.MaxRecords to server from client-side.

5

Must be optimal if the drive resolve that, don’t you think?

6

Ok thanks but I tend to agree with Donald - if the database/driver supports SELECT TOP then surely it ought to be used? I have a scenario where I have a table of events and I want to retrieve the date of the earliest one. This table could be large and retrieving the whole thing from the database when all I want is the first record isn’t ideal. I realise I can use custom SQL to achieve it but it would be far simpler to use MaxRecords. Just a suggestion :slight_smile:

1 Like
7

Thanks, logged as bugs://71864

8

logged for reviewing this case

9

bugs://71864 got closed with status fixed.

10

Thats mean you include a smart way to pass limit to the auto generated SQL?

11

yes, now it can generate SELECT with

SELECT [TOP|FIRST x ] ... FROM ... WHERE ... [LIMIT|ROWS x]
1 Like
12

is this feature supported da sql or schema select query?

13

this is supported by Delphi server when client specifies MaxRecord for given table.
.NET server has similar improvements

14

What about non autogenerated SQL ? Theres any macro to use?

15

this code is generated only for autogenerated sql.
for non autogenerated, you can add manually add TOP/FIRST/LIMIT/ROWS keywords depending on your DB