Another option just for the fun:

Include %SYS.PTools.Stats

Class dc.test [ Abstract ]
{

ClassMethod setValue(args...) As %Status
{
  quit:args<2 $$$ERROR($$$DataMissing)

  $$$convertArrayToList(args,list)
  quit:$listlength(list)'=args $$$ERROR($$$RequiredArgumentMissing)
  
  set $list(list,*,*)="",
         var=##class(%Utility).FormatString(list),
         $extract(var,1,3)=$name(%sessionData), ##; or $name(%session.Data)
         @var=args(args)
  quit $$$OK
}

/// do ##class(dc.test).Test()
ClassMethod Test()
{
  new %sessionData

  try{

    do $system.OBJ.DisplayError(..setValue("key1", "val1")),
          $system.OBJ.DisplayError(..setValue("key1", "key2", "key3", "key4", "val2")),
          $system.OBJ.DisplayError(..setValue("key1", "key2", , "key4", "val3")),
          $system.OBJ.DisplayError(..setValue())
    
    write !
    zwrite %sessionData
  }
  catch(ex){
    write ex.DisplayString(),!
  }
}

}

See also $SYSTEM.SQL.Schema.QueryToTable()

Try it with between 1 and 5 on the %VID. Just as slow.

I ran your code with new index.
count(prop1=2) = 2629833

%VID BETWEEN 1 AND 5
.000293 secs

%VID BETWEEN 2629829 AND 2629833
3.63472 secs

I can't create an index on every possible filter and order by combination.

Waiting for @Kyle.Baxter response.

Try adding a new index and don't forget make rebuild index/tunetable/recompile class

Index idx On (prop1, prop3) [ Type = bitmap ];

Here yet need the help of @Kyle.Baxter.

PS: by the way, check

select count(*) from mp.test where prop1=2

to insert the correct values in the code

%VID BETWEEN 3000000 AND 30000005

Because of this, is obtained

0 Rows(s) Affected

I don't think I quite understand you.

That is, you need to:

1. do
   

   SELECT * FROM <table> WHERE <difficult condition> ORDER BY <some field> DESC

2. then select a certain data page from the resulting result
   

   select * from (step1) where %VID between N1 and N2

If not, provide a ready-made example with data similar to the example by @MARK PONGONIS

USER>d ##class(mp.test).Fill(5000000)

USER>d ##class(mp.test).Query()
count=3833346

first 5 [1:5]
ID prop3
3 3
4 3
24 3
30 3
97 3

5 Rows(s) Affected
.000328 secs

last 5 [3833342:3833346]
ID prop3
4999798 1
4999817 1
4999836 1
4999866 1
4999947 1

5 Rows(s) Affected
2.884304 secs

PS: for those who put a minus. May I ask why?

Have you tried this?

There is at least one case where this encoding style is acceptable - it is a generator method. Just look at the code generated by &sql() or CSP, for example.
No wonder there are two versions for javascript libraries: for the developer and for production (processed by the minimizer).

And what if the answer is given, but the author either does not want to accept the answer at all or does not make a more elegant decision?

https://community.intersystems.com/post/there-way-defermine-column-jsono...
https://community.intersystems.com/post/creating-json-objects-objectscri...

My EAV implementation is the same as your Approach 3, so it will work fine even with fully filled 4.000.000 attributes.
Since the string has a limit of 3,641,144, approaches with serial and %List are dropped.

All other things being equal, everything depends on the specific technical task: speed, support for Objects/SQL, the ability to name each attribute, the number of attributes, and so on.

Entity–attribute–value model is usually used for this purpose.

I have already written about this at the time: SQL index for array property elements.

Here is a ready-made example (works even in last Caché):

Class dc.test Extends %Persistent
{

Property title As %VarString;

Property author As %VarString;

/// do ##class(dc.test).test()
ClassMethod test()
{
  &sql(truncate table dc.test)
  
  &sql(insert into dc.test(title,author)
    select 'For Whom the Bell Tolls','Hemmingway' union
    select 'The Invisible Man','Ellison' union
    select 'Tender is the Night','Fitzgerald')
  
  set provider=##class(%ZEN.Auxiliary.altJSONSQLProvider).%New(),
      provider.sql="select title,author from dc.test",
      provider.arrayName="items",
      provider.maxRows = 0,
      provider.%Format = "tw"
  
  do provider.%WriteJSONStreamFromSQL(.stream,,,,,provider)
  
  set json={}.%FromJSON(stream),
      json.results=json.items.%Size()

  write json.%ToJSON()
}

}

Result:

USER>do ##class(dc.test).test()
{"items":[{"title":"For Whom the Bell Tolls","author":"Hemmingway"},{"title":"The Invisible Man","author":"Ellison"},{"title":"Tender is the Night","author":"Fitzgerald"}],"results":3}

Google the JDBC Driver for Excel.
Here is a good option: HXTT Excel Pure Java JDBC (1.2, 2.0, 3.0, 4.0, 4.1, 4.2, 4.3) Drivers for MS Excel (XLS, XLSX)

Class dc.test [ Abstract ]
{

ClassMethod ValType(ByRef var) As %String
{
  s a(-1)="undefined",a(0)="empty",a(1)="string",a(2)="integer",a(3)="float",a(4)="double",a(5)="object",a(6)="list",a(7)="array",a(8)="bitstring"
  s r=$s('$d(var):-1, $isobject(var):5, $d(var)>9:7, var="":0, $lv(var):6, $l(var)>254:1, 1:$tr($a($lb(var),2),1245678,1122334))
  i r=1 {
    s s=##class(%Utility).FormatString(var)
    s:($f(s,"$c(")=4)||($f(s,"$zwc(")=6) r=8
  }
  q a(r)
}

/// d ##class(dc.test).test()
ClassMethod test()
{
  s old=$system.Process.Undefined(2)
  try{

    w ##class(%Utility).FormatString(undef)," => ",..ValType(.undef),!

    f i="","1",1,1.1,$double(1.1),##class(%ListOfDataTypes).%New(),$lb(),$factor(1),$c(7) {
      w ##class(%Utility).FormatString(i)," => ",..ValType(.i),!
    }
    
    s (i,i(1))=""
    w ##class(%Utility).FormatString(i)," => ",..ValType(.i),!

  }catch{
  }
  
  d $system.Process.Undefined(old)
}

}

Result:

USER>d ##class(dc.test).test()
"" => undefined
"" => empty
1 => string
1 => integer
1.1 => float
$double(1.1000000000000000888) => double
"1@%Library.ListOfDataTypes" => object
$lb() => list
$zwc(128,4)_$c(1,0,0,0)/*$bit(1)*/ => bitstring
$c(7) => bitstring
"" => array

InterSystems IRIS Community Edition Limitations
Also see %SYSTEM.License, e.g. method MaxConnections

It can be simpler:

Property HowManyRecords As %Integer [ Calculated, ReadOnly, Required, SqlComputeCode = {n r,SQLCODE &sql(select count(*) into :r from schema_package.table) s {*}=$s(SQLCODE:-1,1:r)}, SqlComputed ];