reduce data retrieval time

[shukla]

is there any way to reduce time required to retrrive whole data from database and adding it to listview control.
i am having records about 3500 from 1 tables and for for each record it search for record in 2nd table and depending on 4to 5 conditons, i have to make color of each record dispalyed in list view control as diffrent color.
my database is in access and located on server.
it takes much time.
can you help me to reduce time ?

[Mark Gambo]

Try hiding the Listview when you are loading it, that should speed up things a bit. Then make it visible after it is loaded.

[Hack]

Reducing the number of records loaded would help significantly.

Is it necessary to load all 3,500 records at once? Can these records be divided into categories and only those records meeting the category criteria get loaded?

[disruptivehair]

A slow network can slow you down too.

[leinad31]

Its probably the multiple iterations and per record test that's slowing you down.

What are the conditions? The tables structures?

Maybe we can create a joined table (left join, where tabel1 is lefthand side) and in addition to the fields shown in the listview we can add other fields for each condition (checked against table2) and these fields will be used to format the listview output. That way you need to iterate through the recordset only once for adding the listitem and formatting it.

[shukla]

yes.
no of conditions per record is about 4 to 5.
and i can not use any join condition as i have to look up a whole table record in another table.

[leinad31]

what's the table structure?
post your current code

[shukla]

my code is so long.

rs.Open strsql, conn_proc.con, 3, 3
While rs.EOF = False
    Set t1 = listv1.ListItems.Add(Text:=rs!proj)
    i = 0
    ReDim cnt(rs.Fields.Count - 7) As Long
    
    
    For Each J In cnt
        i = i + 1
        If rs.Fields(i).Type = adDate Then
            t1.SubItems(i) = Trim(" " & Format(rs.Fields(i).Value, "dd-mmm-yyyy") & " ")
        Else
            t1.SubItems(i) = Trim(" " & rs.Fields(i).Value & " ")
        End If
        If i = 9 Then
            'If Val(Trim(" " & rs!cflag & " ")) = 0 Then
             t1.ListSubItems(9).ForeColor = RGB(255, 0, 0)
            'If Val(Trim(" " & rs!bal & " ")) = 0 Then t1.ListSubItems(9).ForeColor = &H8000&
            If Val(Trim(" " & rs!rqty & " ")) > Val(Trim(" " & rs!bal & " ")) And Val(Trim(" " & rs!bal & " ")) > 0 Then t1.ListSubItems(9).ForeColor = RGB(0, 0, 255)
        End If
    Next
 '   If Val(Trim(" " & rs!cflag & " ")) = 0 Then t1.Selected = False
'    If Val(Trim(" " & rs!cflag & " ")) = 1 Then
'        t1.Checked = True
'    End If
    schno = ""
    If rs!reci = "" Then
        t1.ListSubItems(1).ForeColor = RGB(255, 0, 250)
    End If
        
    act1 = ""
    remdate = ""
    '*************************8
    
    srno = rs!srno
    rno = rs!rno
    rs1.Open "select * from follow_up where srno = " & srno & " and rno = '" & rno & "' order by id desc", conn_proc.con, 3, 3
    If rs1.RecordCount > 0 Then
        If rs1!ddate <> "" Then r1 = Format(rs1!ddate, "dd-mmm-yyyy")
        If rs1!remdate <> "" Then remdate = Format(rs1!remdate, "dd-mmm-yyyy")
        If rs1!act <> "" Then act1 = Trim(" " & rs1!act)
        If rs1!schno <> "" Then schno = Trim(" " & rs1!schno)
        If rs1!pname <> "" Then pname = Trim(" " & rs1!pname)
        t1.SubItems(15) = pname
        actprev = ""
 
            rs1.MoveNext
                   If rs1.EOF = False Then
                    actprev = Trim(" " & rs1!act)
                    rs1.MovePrevious
                End If
        If actprev = "" Then actprev = act1
    End If
      'If LCase(Trim(act1)) = "sch" Or LCase(Trim(act1)) = "po" Then
        colchange = ""
        
        
        If schno <> "" Then
            If ((LCase(actprev) <> "enquiry") Or (LCase(act1) = "sch" Or LCase(act1) = "po")) Then
                colchange = &H8000&
            End If
        End If
        
     
        If Val(Trim(" " & rs!EMER)) = 1 Or Val(Trim(" " & rs!pref)) = 1 Then
               colchange = &HFAAF4
        End If
    
    
         
    If Val(Trim(" " & rs!rtype)) = 1 Then
        colchange = &H8582F0
    End If
    
             
    If Val(Trim(" " & rs!descr)) = 1 Then
        colchange = &HC0C000
    End If
    
    If colchange <> "" Then
        For ij = 1 To t1.ListSubItems.Count
            t1.ListSubItems(ij).ForeColor = colchange
        Next
        t1.ForeColor = colchange
    End If
    
    rs1.Close
    
    '**************************8
            
'    r1 = get_last_rcd(rs!rno, rs!srno)
    If r1 <> "" Then t1.SubItems(14) = r1
       t1.SubItems(13) = schno
           
    If LCase(frmlogin.cat) = "power user" Or LCase(frmlogin.cat) = LCase("administrator") Or LCase(frmlogin.cat) = LCase("purchase department") Then
        t1.SubItems(12) = act1
    Else
        t1.SubItems(12) = ""
    End If
    If remdate <> "" Then t1.SubItems(10) = Format(rs!ddate, "dd-mmm-yyyy")
    rs.MoveNext
Wend

[leinad31]

what's the sql on line 1?

rs.Open strsql, conn_proc.con, 3, 3

[leinad31]

And the table structures (field names, data types) pls.

[rory]

shukla, have you tried GetRows yet?

[shukla]

i have not tried GetRows method.
can you tell me about it.

strsql = "select proj,req.rno,req.dt,item,req_details.size1,reason,rqty,req_details.unit1,bal,edate,ddate,srno,cflag,reci,emer,rtype,descr,pref from req,req_details where req.rno  = req_details.rno and bal > 0 and sanc <> '' and val(canreq) <> 1 "

[rory]

first i need you to identify each field with table name first .. eg. like you have with a couple of them... table.FieldName.

[shukla]

req is first table then req_details is sencond and third is follow_up
for each record in req.thre is 1 or more records and for each record in req_details there i s or not record in follow_up

[leinad31]

Is this an access database? If so, could you please zip up and post the database

[shukla]

i can not because my organisation will not allow for it.

[shukla]

please tell me if anyone can suggest me.