[ALL] Second project... SOLVED -- how to use EXPLAIN !

[Ruslan Zasukhin]

>> On 6/2/06 2:13 PM, "Stan Busk" <maxprog at mac.com> wrote:
>> 
>>> Problems:
>>> 
>>> 1.- When starting-up it calculates each 37 groups unread articles
>>> count, it is the number between parentesis on the 'Source' listbox
>>> (left). (Profile code added)
>>> 
>>> 2.- When selecting a group (for exemple comp.lang.basic.realbasic),
>>> moving the mouse to the list of article, it calculates each thread
>>> unread article count. Same when scrolling thru the list. When using
>>> the mouse scroll button there is a moment the whole app will lock
>>> waiting for calculation to be done. They are simple SQL queries in a
>>> thread. (Profile code added)
>>> 
>>> 3.- When selecting a group pressing Control+Option it reset settings
>>> for thread creation. (Profile code added)
>>> 
>>> It is very likely all 3 problems above have same origin.

Okay Stan,

Using V4RB_Log and EXPLAIN feature of 2.4 I can see now that
slow speed is result not of Valentina engine problem,
but your design of database.

If look into EXPLAIN LOG we see that some queries take 15 and 10 ticks.
You have 100 such queries, so this is your 6 second delay on start of app.

Now look self on this EXPLAINs and find where time is lost.

----------------------------------------------------------------------------
FOR SQL QUERY TIME = 15 :
     DO SELECT : TIME = 15 : ROWS = 1
         DO SIMPLE SELECT : TIME = 15 : ROWS = 1
             DO Single Table Without GROUP BY : TIME = 15 : ROWS = 1
                FOR Table [tblArticle]  DO resolve WHERE statement : TIME =
15 : ROWS = 3765
                    FOR Field [Article_Newsgroups]  DO FindLike : TIME = 15
: ROWS = 3765

FOR SQL QUERY TIME = 10 :
     DO SELECT : TIME = 10 : ROWS = 1
         DO SIMPLE SELECT : TIME = 10 : ROWS = 1
             DO Single Table Without GROUP BY : TIME = 10 : ROWS = 1
                FOR Table [tblArticle]  DO resolve WHERE statement : TIME =
10 : ROWS = 3538
                    FOR Predicate [AND] TIME = 10 : ROWS = 3538
                         DO AND Indexed operands : TIME = 0 : ROWS = 29231
                            FOR Field [Article_Status]  DO FindLike : TIME =
0 : ROWS = 29231
                         DO AND Non-Indexed operands : TIME = 10 : ROWS =
3538
                            FOR Field [Article_Newsgroups]  DO FindLike :
TIME = 10 : ROWS = 3538
----------------------------------------------------------------------------



Right it is lost on NON-IDEXED LIKE for field Article_Newsgroups
You need simply to INDEX this field to get normal speed.


On question: why this have work for 1.x fast ?
Answer is: Because 1.x did self indexed each field used in WHERE.

In V2 we have deprecate this behavior. Developer SELF must specify,
which fields are indexed, and DB ENGINE do not change structure of db.


-------------------------------------------------------------------------
RESUME:

I want underline, that I have use only TOOLS which you have on your hands:

    a) V4RB_Log
    b) Warnings.log  - that contains EXPLAIN output

And these tools was enough to isolate problem.
The same I did yesterday on db from Russ and his queries with IN.

So you have nove EXPLAIN. Very Powerful Tool.
This is kind of profiler built-in into Valentina engine.

USE IT PLEASE ! :-)

To turn it on you need execute query

    db.SqlExecute( ³² )


-- 
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]