Solved

optimizing vb code (uses sql)

Posted on 2002-03-08
21
237 Views
Last Modified: 2010-05-02
i have this snippet :

Private Sub populateCaseList()
    Dim chargeRS As Recordset
    Dim x As Integer
    Dim strCharges As String
   
    Dialog.lblMessage = "Refreshing Case List, Please Wait..."
    Dialog.Show
   
    sql = "select * from personal"
    Set rs = dbase.GetRS(sql)
    If (Not dbase.EmptyRS(rs)) Then
        flexCaseList.Rows = 1
        While Not rs.EOF()
            sql = "select * from charges where criminalcaseno='" & rs!CriminalCaseNo & "'"
            Set chargeRS = dbase.GetRS(sql)
            For x = 1 To chargeRS.RecordCount
                strCharges = strCharges & chargeRS!Description
                chargeRS.MoveNext
                If (x < chargeRS.RecordCount) Then
                    strCharges = strCharges & ", "
                End If
            Next x
            flexCaseList.AddItem rs!CriminalCaseNo & Chr(9) & rs!pdfileno & Chr(9) & rs!lastname & Chr(9) & rs!firstname & Chr(9) & rs!middlename & Chr(9) & rs!citizenship & Chr(9) & strCharges
            rs.MoveNext
            strCharges = ""
        Wend
    End If
    Dialog.Hide
End Sub

it populates an MSFlexGrid. fetches data from ms-access database using sql. its too slow. the dialog wont even display correctly hehe. how can i speed this up?
the effect is like a 2 to 3 second halt on the VB as if it hanged (but didnt).
0
Comment
Question by:ibo
  • 10
  • 6
  • 2
  • +3
21 Comments
 
LVL 3

Author Comment

by:ibo
ID: 6850355
i know its was coded poorly. im a VB newbie and this is a quick hack :P. so experts.. help me :)
0
 
LVL 3

Author Comment

by:ibo
ID: 6850371
no.. not only 2 to 3 secs.. it goes up to 4 to 5 secs with only 60 records. pathetic.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 150 total points
ID: 6850386
Your main problem is that you query multiple times inside the loop, which you could eliminate by doing 1 query, and looping on that:

your original query (from which you use only the CriminalCaseNo field):
select * from personal
and the inside query:
select * from charges where criminalcaseno=CriminalCaseNo

Now, query this:

select *
from charges c
join personal p
on c.criminalcaseno= p.CriminalCaseNo
order by p.CriminalCaseNo

By this, you get a recordset, ordered by the CriminalCaseNo. loop on that recordset, and compare the CriminalCaseNo with the value of the previous record (using a variable that holds the value from the previous record), if they differ, you have a complete output row, if they equal, start a new output row.

I would give you the whole code, but you should be able to get the idea, and learn from it this way.

CHeers

0
 
LVL 10

Expert Comment

by:smegghead
ID: 6850388
If you disable the MSFlexGrid, or just make it Invisible, the population of the grid is a lot faster.

This is because it doens't try to redraw ever time you add a line.

Smegg.

PS. I can't remember, but you might not be able to disable, but you can defintetely make it invisible, just experiment with these two properties.
0
 
LVL 7

Expert Comment

by:Z_Beeblebrox
ID: 6850393
The reason it is so slow is that you do so many SQL queries, 1 for each record in the personal table. There are ways to do all of the processing you need with only one query, but they are more complicated. One choice that I can think of off the top of head would be to do a join between the two tables and then iterate through the recordset checking the CriminalCaseNo. Something like this:

sql = "select * from personal inner join charges on personal.CriminalCaseNo = charges.CriminalCaseNo"
Set rs = dbase.GetRS(sql)
lngCaseNo = 0
while not rs.eof
if rs!CriminalCaseNo <> lngCaseNo and lngCaseNo <> 0 then
  ' this is a new record, so add the previous to the grid (you have to have stored the values for that record)
  lngCaseNo = rs!CriminalCaseNo
  strCharges = ""
end if
strCharges = strCharges & "," & rs!Description
wend

Of course this won't work perfectly but hopefully you get the idea.

Zaphod.

0
 
LVL 3

Author Comment

by:ibo
ID: 6850500
ok heres the revised version.. combining all ur advices.
im still have probs.

is the sql correct? coz the line :
If (rs!CriminalCaseNo <> oldCaseNo) And (oldCaseNo <> "") Then
is returning an error : "Item cannot be found in the collection corresponding to the requested name or ordinal".
and im only getting 7 records.. which is.. surprisingly few.


Private Sub populateCaseList()
    Dim chargeRS As Recordset
    Dim x As Integer
    Dim strCharges As String
    Dim oldCaseNo As String
    Dim oneRow As String
   
    sql = "select * from personal inner join charges on personal.CriminalCaseNo = charges.CriminalCaseNo order by personal.CriminalCaseNo"
    Set rs = dbase.GetRS(sql)
    If (Not dbase.EmptyRS(rs)) Then
        flexCaseList.Visible = False
        flexCaseList.Rows = 1
        oldCaseNo = ""
        Debug.Print rs.RecordCount
        While Not rs.EOF()
            If (rs!CriminalCaseNo <> oldCaseNo) And (oldCaseNo <> "") Then
                oldCaseNo = rs!CriminalCaseNo
                flexCaseList.AddItem oneRow & strCharges
                strCharges = ""
                oneRow = rs!CriminalCaseNo & Chr(9) & rs!pdfileno & Chr(9) & rs!lastname & Chr(9) & rs!firstname & Chr(9) & rs!middlename & Chr(9) & rs!citizenship & Chr(9)
            Else
                strCharges = strCharges & rs!Description & ", "
            End If
            rs.MoveNext
        Wend
        flexCaseList.Visible = True
    End If

End Sub
0
 
LVL 10

Expert Comment

by:smegghead
ID: 6850512
It's probably because 'CriminalCaseNumber' is now ambiguous, it exists on both Personal and Charges.. so you must distinguish between the two.

Use.. rs.fields("Personal.CriminalCaseno") rather than rs!CriminalCaseNo
0
 
LVL 10

Expert Comment

by:smegghead
ID: 6850516
Also, change the line that starts 'oneRow = ' in the same way
0
 
LVL 3

Author Comment

by:ibo
ID: 6850522
yeah :) i noticed that :)
0
 
LVL 10

Expert Comment

by:smegghead
ID: 6850533
Also also.. there is a major difference between your initial set of code and this one.

Your original code listed everybody in the personal table, along with any charges - even if they have no charge.

Whereas your new code only lists people if they have a charge
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 3

Author Comment

by:ibo
ID: 6850593
aha.. thats the prob.. how i change the sql so that it will get all the records even if it has no charge?
i need to display them all.. with or w/o charge.
sorry im feeling bonehead today and im not that familiar with table joins..
0
 
LVL 7

Expert Comment

by:Z_Beeblebrox
ID: 6850598
To fix that problem, change inner join to left join and make sure to check for null charge fields.

Zaphod.
0
 
LVL 10

Expert Comment

by:smegghead
ID: 6850602
left join rather than inner join.

so...

  sql = "select * from personal left join charges on personal.CriminalCaseNo = charges.CriminalCaseNo
order by personal.CriminalCaseNo"


This will include all records from personal, and only those from charges what exist on personal (which should be all)

Smg.
0
 
LVL 3

Author Comment

by:ibo
ID: 6850690
thank you very much guys for your help.
id like to split the points for the 3 of you.
ill give this 150 pts to angelIII (coz he beat you to 1 second in posting the answer. the fast finger wins hehe), then ill post two more 100 pointer each for Z_Beeblebrox and smegghead. okey dokey? :)
0
 
LVL 18

Expert Comment

by:deighton
ID: 6850691
  Dim chargeRS As Recordset
   Dim x As Integer
   Dim strCharges As String
   
   Dim vHold As String
   Dim sOutput As String
   
   Dim bBreak As Boolean
   
   Dialog.lblMessage = "Refreshing Case List, Please Wait..."
   Dialog.Show
   
   sql = "select * from personal Left Join Charges ON Charges.criminalcaseno = personal.CriminalCaseNo ORDER BY Charges.criminalcaseno"
   Set rs = dbase.GetRS(sql)
   If (Not dbase.EmptyRS(rs)) Then
       flexCaseList.Rows = 1
       vHold = rs!criminalcaseno
       While Not rs.EOF()
   '        sql = "select * from charges where criminalcaseno='" & rs!CriminalCaseNo & "'"
    '       Set chargeRS = dbase.GetRS(sql)
   
            If Not IsNull(rs!Description) Then
               strCharges = strCharges & rs!Description
               chargeRS.MoveNext
               strCharges = strCharges & ", "
           End If
           
           sOutput = rs!criminalcaseno & Chr(9) & rs!pdfileno & Chr(9) & rs!lastname & Chr(9) & rs!firstname & Chr(9) & rs!middlename & Chr(9) & rs!citizenship & Chr(9) & strCharges
           

           rs.MoveNext
           
           
           bBreak = rs.EOF
           If Not bBreak Then
             bBreak = vHold <> rs!criminalcaseno
           End If
           
           
            If bBreak Then
           
                flexCaseList.AddItem sOutput
                strCharges = ""
               
                If Not rs.EOF Then
               
                    vHold = rs!criminalcaseno
                   
                End If
                   
               
            End If
               
       Wend
   End If
   Dialog.Hide
0
 
LVL 3

Author Comment

by:ibo
ID: 6850699
thank you very much guys for your help.
id like to split the points for the 3 of you.
ill give this 150 pts to angelIII (coz he beat you to 1 second in posting the answer. the fast finger wins hehe), then ill post two more 100 pointer each for Z_Beeblebrox and smegghead. okey dokey? :)
0
 
LVL 10

Expert Comment

by:smegghead
ID: 6850705
Hi IBO,

Very generous of you, but seriously don't worry about the points - otherwise it's a very "expensive" question.

As long as you got the answer you were looking for.

Smegg.
0
 
LVL 3

Author Comment

by:ibo
ID: 6850714
ey deighton dude.. thanx for the effort. ur kinda late i already solved the prob with the help of the early birds hehe. thx nyway.
0
 
LVL 3

Author Comment

by:ibo
ID: 6850719
smegg.. im just in a mood to give the refunded points i got :) have a nice day.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 6850735
three last efficiency boosters:

1. Use intrinsic constants
oneRow = rs!CriminalCaseNo & vbTab & rs!pdfileno & vbTab & rs!lastname & vbTab & rs!firstname & vbTab & rs!middlename & vbTab & rs!citizenship & vbTab

2. better yet, have the output of the SQL include the tab separators, so all you have to do is add the row:

sql = "select CriminalCaseNo &" & vbtab & "& pdfileno &"
 & vbtab & "& lastname &" & vbtab & "& firstname &" & vbtab & "& middlename &" & vbtab & "& citizenship &" & vbtab & " As OneRow from personal left join ..."

flexCaseList.AddItem rs!oneRow & strCharges

So there is no need to assemble the line in your code.

3. change your While...Wend to a Do...Loop.
0
 
LVL 3

Author Comment

by:ibo
ID: 6850762
thanx aikimark. that was very valuable.
the code is now fassst. :)
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now