That works great, thanks! What about to instantiate a new object?

Ex. 

s var = "MyClass"

s obj=##class(var).%New()

This is perfect, thanks!

Also works like a charm!!

works like a charm! Thanks!!

is there a way to ORDER this? Say by prop3 DESC

Whoa!!! Upon initial testing that is lightning fast!!! Went from +1s down to .001. I'm going to fool around with this some more but so far this is exactly what I was looking for. Thank you so much!!!!

Pardon the formatting but please see below. Conclusion - Every time I add 100,000 to the table, it takes longer to perform the initial %Next(), even though I'm still only asking for the first 5 results. This is a simple table with a simple query. The one I'm dealing with in my app is a much more complex table and query with an ORDER BY clause which slows it down even more, but the example below gives an idea of what I am talking about.

Class mp.test Extends (%Persistent)
{
  Property prop1;
  Property prop2;
  Property prop3;
  Index idx3 On (prop1, prop2, prop3);

ClassMethod Fill(total)
{
    ;d DISABLE^%NOJRN
    ;d ..%KillExtent()
    s prop1="name"
    s prop2="prop2"
    f i=1:1:total {
         s prop3=$r(30)
         s id=$g(^mp.testD)+1
         s ^mp.testD(id)=$lb(""," "_prop1," "_prop2," "_prop3)
         s ^mp.testI("idx3",$$$SQLUPPER(prop1),$$$SQLUPPER(prop2),$$$SQLUPPER(prop3),id)=""
         s ^mp.testD=$g(^mp.testD)+1
    }
}

ClassMethod Query()
{
    s sql=##class(%SQL.Statement).%New()
    s query = "SELECT *,%vid FROM (SELECT %ID,prop3 FROM mp.test WHERE prop1='name' AND prop3>='1' AND prop3<='30' AND prop2='prop2') WHERE %VID BETWEEN 1 AND 5"
    s sc=sql.%Prepare(query)
    i $$$ISOK(sc) {
         s rset=sql.%Execute()
         i 'rset.%SQLCODE {
              s t1=$zh
             d rset.%Next()
            w $zh-t1,!
            d rset.%Display()
         }     
    }
 }
}
 

TEST 1 - Loading 100,000 items into table. Query takes .314 secs

user>D ##class(mp.test).Fill(100000)                                      
user>D ##class(mp.test).Query()    
.314167 secs                            
ID prop3 Literal_3                      
2  19 2                                 
3  19 3                                 
4  19 4                                 
5  15 5                                 
                                        
5 Rows(s) Affected                      

------------------------------------------------

TEST 2 - adding another 100,000 to table. Same query takes .64secs

user>D ##class(mp.test).Fill(100000)                                       
user>D ##class(mp.test).Query()    
.640992 secs                            
ID prop3 Literal_3                      
2  15 2                                 
3  23 3                                 
4  26 4                                 
5  19 5                                 
                                        
5 Rows(s) Affected  

--------------------------------------------------------

TEST 3 - another 100,000. query takes .865 secs

user>D ##class(mp.test).Fill(100000)                                         
user>D ##class(mp.test).Query()     
.865654 secs                             
ID prop3 Literal_3                       
2  15 2                                  
3  23 3                                  
4  26 4                                  
5  19 5                                  
                                         
5 Rows(s) Affected                                            

----------------------------------------

TEST 4 - another 100,000 takes 1.16secs

user>D ##class(mp.test).Fill(100000)                                       
user>D ##class(mp.test).Query()    
1.169932 secs                           
ID prop3 Literal_3                      
2  15 2                                 
3  23 3                                 
4  26 4                                 
5  19 5                                 

--------------------------------------------

TEST 5 - another 100,000. query takes 1.44 secs

user>D ##class(mp.test).Fill(100000)                                       
user>D ##class(mp.test).Query()    
1.44849 secs                            
ID prop3 Literal_3                      
2  15 2                                 
3  23 3                                 
4  26 4                                 
5  19 5                                 
                                        
5 Rows(s) Affected                      

I'm sure I'm missing something, but so far, I'm just not seeing how this would speed up my query.

My reason for thinking the 4th article wouldn't work for me is because I am allowing the user to jump to a page, and with filtering and sorting also available (as well as a constantly changing database), I don't see how I could determine the keyval/id to use in the query.

Also, this query will work when grabbing the first page, but not for subsequent pages SELECT TOP 100 prop FROM table WHERE prop=?

The TOP N and %vid are very slow when dealing with a large result set.

The example you give is great, but I don't think it has 1mil possible matches which are being filtered down to 100 results (not 100% sure about that statement..:)). This is the problem I am having with using TOP N and %vid.

For example, say I want to grab all data for the month of July that match some specific search parameters (which happens to result in 1,000,000 matched items), and since I am currently on page 5 (and each page displays 100 results), I want to offset the results by 400 items. The first %Next() takes a tremendous performance hit.

s sql=##class(%SQL.Statement).%New()

s query="SELECT *,%VID FROM (SELECT prop FROM table WHERE prop=val) WHERE %VID BETWEEN 401 AND 500"                                                  

s sc=sql.%Prepare(query)

s rset=sql.%Execute()

s t=$zh d rset.%Next() w $zh-t
6.105871                     

Thanks for the suggestions, but I'm afraid none of these will fix my problem.

- ScrollableResultSet has the most promise, but still does not satisfy what I need. The data is constantly being updated so using this would not be viable.

-The second suggestion is what I am doing above. Using %VID with a subquery is just too slow when dealing with large datasets. For ex. The following query has a potential pool of 1mil results. SELECT *,%VID FROM (SELECT prop FROM table WHERE prop=x) WHERE %VID BETWEEN 1 AND 100. Eventhough I am only asking for the first 100, there is still a major performance hit when executing the first rset.%Next() due to, what I assume is, the code trying to find the 100 records I am requesting out of the 1 mil records.

- The third article mentions a few options of which some are not available in Cache, such as LIMIT and OFFSET. Also using Cursors or Keysets would not be viable as my application allows jump to, sorting and filtering functionality.

- And the 4th talks about using LIMIT and OFFSET which are, again, not available in Cache.

Any other thoughts?