[Search for users] [Overall Top Noters] [List of all Conferences] [Download this site]

Conference orarep::nomahs::sql_services

Title:SQL/Services Forum
Notice:kits(3) ft info(7) QAR access (8) SPR access (10)
Moderator:SQLSRV::MAVRIS
Created:Thu Oct 13 1988
Last Modified:Fri Jun 06 1997
Last Successful Update:Fri Jun 06 1997
Number of topics:2214
Total number of notes:8586

2148.0. "GENERIC process performance degradation for 1st time use" by BROKE::BASTINE () Fri Feb 21 1997 11:31

I had a customer call this morning with the following scenerio:

He is attaching to a GENERIC service from ODBC.  (It works the same in
V7.0 and V6.1-02 of SQL/Services using the 2.10.11 driver).  When he attaches,
he then runs a program that inserts 3000 rows into an rdb database.  When
the program finishes, he disconnects, and re-attaches to the GENERIC class
(it is now using the same executor process), and runs the program again, the
program inserts 3000 rows.

The first time the program runs, it takes 142 seconds to insert the 3000
rows.

The second time the program runs, it takes 33 seconds to insert the 3000 rows.

I tried to reason that some of the additional time was due to process creation
of the executor process, but he said the lapsed time he captures is insert
time only.

It is very re-creatable for him and is using pre-attached db class servers
to get around the performance hit.

Anyone know of what might explain this performance degradation?  I will work
on trying to reproduce the problem here, but wondered if anyone had already
seen this?

Thanks,
Renee
T.RTitleUserPersonal
Name		DateLines
2148.1M5::JBALOGHFri Feb 21 1997 12:2315
    Sounds like process creation/attach time to me. Especially if he is
    getting around the problem with preattached class servers. 
    
    If he is using a 4gl like VB or Access it could be actually doing more
    than one attach...
    
    Try increasing the MIN on generic to keep some processes around,
    increase IDLE for the same reason, or simply stick with the class
    server. 
    
    Or, set idle to 1 and have him put a wait in his code long enough for
    the first process to time out. His second set of inserts should be slow now
    too... 
    
    John
2148.2M5::JHAYTERFri Feb 21 1997 12:4411
>The first time the program runs, it takes 142 seconds to insert the 3000
>rows.
>The second time the program runs, it takes 33 seconds to insert the 3000 rows.

~~100 seconds dif, sounds like a long time for process creation, even adding in
global buffers for the attach.

>Anyone know of what might explain this performance degradation?

rmu/show stat ??
2148.3More guesses?BROKE::BASTINEFri Feb 21 1997 13:2214
What about caching?

Let's say that the GENERIC process creates the executor process, you attach,
do some work in Rdb using this executor pid.  Then you disconnect, but the
executor process is still out there.  Is Rdb smart enough to keep cache active
so that the second attach goes faster because this process already touched all
these pages the first time???  Just wild theory's.

Jerry is right... it isn't the process creation... I already threw that back
at the customer.  Customer times the insert time only to leave out the
attach/process creation overhead.

Thanks,
Renee
2148.4M5::JHAYTERFri Feb 21 1997 13:5411
>Let's say that the GENERIC process creates the executor process, you attach,
>do some work in Rdb using this executor pid.  Then you disconnect, but the
>executor process is still out there.  Is Rdb smart enough to keep cache active
>so that the second attach goes faster because this process already touched all
>these pages the first time???  Just wild theory's.

with generic, when the user disconnects from sql/srv the db attach goes
away, so on reconnect, a new attach to the db is done.

rmu/show stat time if you ask me.
2148.5See what show/stat shows BROKE::BASTINEFri Feb 21 1997 14:168
>rmu/show stat time if you ask me.

Ok, Ok... I am trying to re-create the situation here.  Almost there, but 
won't be able to continue until next week... will ask customer on Monday
what he sees.

Thanks! 
Renee
2148.6M5::JBALOGHFri Feb 21 1997 15:404
    What is this written in? How about a driver log to see what they are
    actually doing...
    
    John
2148.7BROKE::BASTINEFri Feb 21 1997 17:286
It is a VB program... never thought of getting a log!  I hate it when
that happens!! :(

Thanks John!

Renee
2148.8M5::JHAYTERFri Feb 21 1997 19:1623
>It is a VB program... never thought of getting a log!  I hate it when

I did and eeehhhhh...

ooh, i get to argue with john - about time since he has taught me 99.9% of
what I know about odbc and sql/srv.  THANKS JOHN

Renee, from what you first posted, almost 2 minutes of time difference
between the first insert and the second still makes me say rmu/show stat.
One thing that comes to mind is constraints with no index.  The first
insert goes well, but the second is reading, reading to valiadate the
constraint.  But, if there is already lots of records previously loaded
and this problem is occuring during each "daily" odbc program run, then,
more postulation....

How is you CT determining the insert times are really "insert times"?
More curosity than anything else.

Getting a log is still a good idea.  Painfully learned that by NOT
listening to John.

Jerry
2148.9hard to tell without a logM5::JBALOGHSat Feb 22 1997 14:2411
    I am a little suspicious on how the customer is measuring only 
    insert times. VB has a nasty habit of making 2 or more attaches,
    sometimes when you least expect it to. I agree, the time difference
    sounds excessive when you only consider one attach, maybe not so
    excessive with 2 or more... :)
    
    If this truly is only insert time, Jerry is right, we need to look
    at show stats to see what it is doing. The log files would tell us
    if we are making more than one attach...
    
    John