[ALL] Second project... Optimizations

[Ruslan Zasukhin]

On 7/11/06 7:57 PM, "Stan Busk" <maxprog at mac.com> wrote:

Hi Stan,


OKAY, I have correct code of V4RB itself, so now I have V4REB_LOG and can
see logic of your calls and queries.

At first lets take a look on START of your app.
Few comments.

--------------------------------------------------------------
Database_SqlSelect_1...
    PARAM: instance = 26336664
    PARAM: inQuery = "SELECT Count(*) FROM tblArticle WHERE
Article_Newsgroups LIKE '%,asp.members,%'"
    PARAM: inCursorLocation = 2
    PARAM: inCursorType = 1
    PARAM: inDirection = 2
return object = 27772320

return object = 27772320

Cursor_GetField...
    PARAM: inCursor = 27772320
    PARAM: inIndex = 1
return object = 27737108

ULong_GetValue...
return res = 3540

Cursor_GetField...
    PARAM: inCursor = 27772320
    PARAM: inIndex = 1
return object = 27737108

ULong_GetValue...
return res = 3540

Database_SqlSelect_1...
    PARAM: instance = 26336664
    PARAM: inQuery = "SELECT Count(*) FROM tblArticle WHERE
Article_Newsgroups LIKE '%,asp.members,%' AND Article_Status LIKE '%U%'"
    PARAM: inCursorLocation = 2
    PARAM: inCursorType = 1
    PARAM: inDirection = 2
return object = 27736056

return object = 27736056

Cursor_Destructor...
    PARAM: instance = 27772320
return

Field_Destructor...
    PARAM: inFld = 27737108
    VAR: data->mpField = 0
return
--------------------------------------------------------------


1) You see destructor Cursor_Destructor for first cursor is called AFTER
construction of second?

This is danger, because you can catch record locks.
I think you have loop as:

    dim curs as Vcursor

    loop
        curs = db.SqlSelect( .. )

    end


The best code is:

    dim curs as Vcursor

    loop
        curs = db.SqlSelect( .. )

        curs = nil
    end


 
--------------------------------------------------------------
2) So at first you count number of articles in thread, then you count number
of UNREAD articles in this thread.

I wonder, why you use
                Article_Status LIKE '%U%'"

Why String search? Why LIKE search ? Why not just Vboolean field ?




--------------------------------------------------------------
3) You do in loop A LOTS of queries of THE SAME kind. Only is changed
values. You see this ?

So you can optimize a lots this using BINDING !

    q1 = "SELECT Count(*) FROM tblArticle
            WHERE Article_Newsgroups LIKE :1"


    loop
      
        arr.Add( "%,asp.members,%" )
                   
        curs = db.SqlSelect( q1, ... , arr )

    end


The same is true I believe for your second case when you count UNREAD
articles in the List of threads.



P.S. Of course I also will check these queries in engine
    but take above recommendations.


-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]