Re: Sorting Large table, small select set

From: keith boulton <boulke_at_globalnet.co.uk>
Date: Sat, 13 Mar 1999 06:50:39 GMT
Message-ID: <36ea06e0.1042869@195.147.246.90>

On 11 Mar 1999 20:51:32 PST, "Mark Foley" <foleym_at_meftechno.com> wrote:

>I have a database for which a certain recordset contains 20,000 records. I
>am
>displaying 20 or so records at a time to the user. These records need to be
>displayed
>in a sorted manner (ie ORDER by DATE,ACCTNUM).
>
>I had been using something like SELECT X From Y Where Rownum < 20 ORDER by
>DATE,ACCTNUM;
>
>only to realize that the ORDER by only works on the 20 rows.
>
>
>I am reading records through ADO/Active Server pages and displaying them to
>the user through
>the browser interface.
>

I don't know anything about ASP but ADO certainly allows fetch on demand, so you merely ensure that you fetch twenty records at a time and make use of an index and a hint to avoid the need for oracle to sort the resultset.

It may be that ASP requires you to return an entire resultset as a batch to the browser and doesn't support returning a partial resultset, in which case, maybe you could return a batch at a time.

given a table test2

 Name                            Null?    Type
 ------------------------------- -------- ----
 ACCTNUM                         NOT NULL NUMBER
 ACCTDATE                        NOT NULL DATE
 DESCRIPTION                              VARCHAR2(200)

with an index on acctdate and acctnum then the sql statement to avoid a sort is "select /*+ index( test2 test2i) */ * from test2 order by acctdate, acctnum"

To confirm this, I built a test vb class which returns twenty rows at a time from the query.:

Option Explicit

Dim mobjConnection As ADODB.Connection
Dim mobjRecordSet As ADODB.Recordset

Public Sub OpenQuery()
  'open a connection to the database as required   If mobjConnection Is Nothing Then
    Set mobjConnection = New Connection     With mobjConnection

      .Provider = "MSDAORA"
      .Properties("user id") = "/"
      .Open

    End With
  End If   

  Set mobjRecordSet = New ADODB.Recordset   With mobjRecordSet
    .CacheSize = 20
    .Open "select /*+ index( test2 test2i) */ * from test2 order by acctdate, acctnum", mobjConnection, adOpenForwardOnly, adLockReadOnly, adCmdText

    .MoveFirst
  End With       

End Sub

Public Function FetchNext() As ADODB.Recordset   Dim i As Integer
  Dim r As ADODB.Recordset   

  'define a recordset to hold the batch   Set r = New ADODB.Recordset     

  With r.Fields

    .Append "acctnum", adInteger
    .Append "acctdate", adDate
    .Append "description", adBSTR, 200

  End With
  With r

    .CursorType = adOpenStatic
    .LockType = adLockOptimistic
    .Open

  End With      

  With mobjRecordSet
    For i = 1 To 20

      r.AddNew
      r!acctnum = .Fields(0)
      r!acctdate = .Fields(1)
      r!Description = .Fields(2).Value
      .MoveNext

    Next i
  End With   

  Set FetchNext = r     

End Function Received on Sat Mar 13 1999 - 00:50:39 CST