[RESOLVED] VB6+SQL Server 2005: Code or DB Optimization

[jlbantang]

is there anyway you can help me optimize this code. the slow movement of rs is so visible that it take time to display the other row.

Call opencon
Set rsbookmark = New ADODB.Recordset

If CLng(txtIDRef) > 0 Then
    rsbookmark.Open "tblsuppliers", conPC, adOpenKeyset, adLockReadOnly
    rsbookmark.Find "pk_supplierid =" & txtIDRef & ""            'find previous bookmark
    
    rsbookmark.MovePrevious
    
    'chk if the next record= BOF
    If rsbookmark.BOF Then
        MsgBox strBOF, vbInformation, strprogname
        GoTo rs_skip
    End If

    txtrecpos = "Record " & rsbookmark.AbsolutePosition & " OF " & rsbookmark.RecordCount
    Call search_SC(rsbookmark!pk_supplierid) 'SP Call where pk_supplierid is clustered index
End If

rs_skip:
If rsbookmark.State = adStateOpen Then rsbookmark.Close
Set rsbookmark = Nothing
Call closecon

[szlamany]

Why are you not simply selecting with a WHERE clause and getting the row you want?

Seems to me that you are loading the entire table and working the recordset on the client side - is that correct?

[jlbantang]

yes. but if using WHERE clause will return a filtered row and moving to next or previous row will not be available.

[szlamany]

How many rows in the table??

[jlbantang]

right now its only 784 but the delay is too visible.

[szlamany]

I don't use the technique you are using...

Aren't you doing the .Open every time they move from record to record??

[jlbantang]

what would you suggest for optimzation

[si_the_geek]

Not only that.. but also opening and closing the connection each time too!

I'd recommend looking at our ADO Tutorial (link in my signature, or via the FAQs in the Database forum) as the code there is organised much better (in terms of speed at least).


As far as I can see, the routine you posted only needs to contain 3 or 4 relatively simple lines (but for that you use of the connection and recordset needs to be changed), or perhaps even better would be a minor alteration to the SP that search_SC calls.

[jlbantang]

it was late last day and i cant reply. anyway, i run through beacons tutorial and i cant find it helpful. i was expecting that there was another FANCY way in moving rows. anyway my coding standard OPEN NOW and CLOSE AFTER - means that every time a move rows call it will:

1. open connection and recordset
2. load the entire table
3. find the last row
4. move to the other row (next, previous...)
5. fill form
6. close recordset and connection

i would agree with beacons coding standard but suppose you are working with multiple forms (MDI) and a distributed applicaton will you advise to open single connection for the entire application and close only when the application terminated?

[leinad31]

http://www.vbforums.com/showthread.p...hlight=primary

Navigate using important fields (eg. primary key and some columns), then load details or other fields later.

[jlbantang]

very weird i use the same code i try inserting

form_load()
rs.movefirst and rs.movelast
end sub

its a daunting response, eventually rs.movenext and rs.moveprevious are perfectly working. even i run the application through lan. really confuse wheres the bottlent neck coming.

[leinad31]

Its a matter of volume... transferring 3 columns of data over the network will take less time compared to more columns for the same number of rows.

[si_the_geek]

You seem to be adding to code that makes it noticeably slower! See the speed issues in bold:

every time a move rows call it will:

1. open connection [slow] and recordset [slow]
2. load the entire table [very slow - but can be appropriate in some situations (not here!)]
3. find the last row [extremely slow]
4. move to the other row (next, previous...)
5. fill form
6. close recordset and connection [ok in itself, but a nightmare as it causes you to re-do steps 1-3 next time]

Why not simply do steps 4 and 5?

If you are desperate to close the recordset & connection at the earliest possible opportunity (I don't know why you would be), then you should at least merge steps 2 3 and 4, so that just the row you want is returned.

i would agree with beacons coding standard but suppose you are working with multiple forms (MDI) and a distributed applicaton will you advise to open single connection for the entire application and close only when the application terminated?

If the structure of each of your forms is even vaguely similar to Beacon's tutorial, you should not even consider using the kind of code you have - your code should be like Beacon had it. I have done that for several multi-user systems, and not had speed problems (tho I do avoid returning all rows from a table if possible, and I always specify the columns I want to use).

Note that for multi-user systems you should avoid a cursor location of Client, as it stops you from seeing other peoples changes, and usually slows things down too.

really confuse wheres the bottlent neck coming.

A .MoveLast means that every record in the recordset needs to be sent from the database to your program (then checked to see if is the last one, if not check the next...), rather than simply the record you want being sent.

[jlbantang]

i tried to work on the code by opening the recordset and connection ONCE in form_load() and close when the application terminate. really there was a noticeable difference. but somethings i doubt regarding rows update since these is a multi-user system. will the updates reflect in the OPENED recordset. if not what will be the work around you will recommend.

btw, does it mean also removing SPs in move rows command. thanks.

i appreciate your comments.

[si_the_geek]

i tried to work on the code by opening the recordset and connection ONCE in form_load() and close when the application terminate. really there was a noticeable difference.

What kind of difference? Is it much faster now?

but somethings i doubt regarding rows update since these is a multi-user system. will the updates reflect in the OPENED recordset. if not what will be the work around you will recommend.

Depending on the options you used for rs.Open, updates can be seen in open recordsets, see the FAQ article What do the parameters of the recordset.Open method mean? for more details.

btw, does it mean also removing SPs in move rows command. thanks.

What does the SP do? (I've never used one in a case like that).



To be honest tho, probably the best thing to do is step back a bit, and for you to explain (in simple terms, not code) what the form actually does (and/or upload a form for us to see). We can then probably suggest better methods.

[jlbantang]

the form actually is a supplier master which allow data read and writes (add,edit,save...). prob comes with the row navigator (basic |< < > >|)

SP perform the filing of form.

http://images.cjb.net/4b812.gif

[si_the_geek]

It looks to me as if the code from Beacon's ADO Tutorial, and my Further Steps article (links in my signature) would be best for you. It should be noticeably faster.. and if it still needs tweaking, paging the data to a single record would probably improve things.


I don't understand why you would be using an SP to fill the form.. something like Beacon's FillFields sub should do it. This is more efficient as the data is already in memory (in the current record of the recordset), so there is no point going back to the database to get it again.

[jlbantang]

suppose i will open single rs and con once for the entire operation and someone on the other terminal update the contacts, will it reflect in the open recordset

[jlbantang]

let me also add this, which will be faster

1. rs.open SPName
2. rs.open TableName

both will have the same cursortype and locktype

[leinad31]

Another problem with your design is its not scalable... if the growth of the data within a year is considerable your application will perform worse ... for how many records are you designing? 10K? 50K? In some businesses some tables can have more than a million records ... and when the time comes to address the problem, you'd end up rewriting most of your application rather than doing it right the first time around.

[si_the_geek]

suppose i will open single rs and con once for the entire operation and someone on the other terminal update the contacts, will it reflect in the open recordset

If you use the right parameters when opening the recordset, yes it will - see the link I posted above (post #15) for more info.

et me also add this, which will be faster

1. rs.open SPName
2. rs.open TableName

both will have the same cursortype and locktype

That obviously depends on what the SP actually does (you said before it fills the form - but unless there is a new trick I don't know about, that is not possible).

If it just does a "SELECT * FROM TableName", then it will be the faster option.

If it does similar, but returns fewer columns and/or has a (reasonably simple) Where clause to restrict the rows, it should be much faster.


Note however that method 2 is terrible anyway - replacing it with an SQL statement (even if it is the equivalent "SELECT * FROM TableName") will be faster, and better in other ways too. For how to do that, see the same link (post #15) again.

[jlbantang]

thanks for the contribution. things work fine now.