[Tutor] Performance of Python loops vs multiple MySQL queries

[Kent Johnson]

Bernard Lebel wrote:
> On 12/21/05, Kent Johnson <kent37 at tds.net> wrote:
>>- Don't assume there is going to be a problem.
> 
> 
> [Bernard] Okay perhaps by "problem" I have not been very accurate. I
> meant "sync" problems. You see, when the script finds a job, it makes
> updates in the database, that is, it adds an entry into another table,
> and updates a certain field in the main jobs table. Other clients then
> testing if there is something to do rely on information that must be
> totally up-to-date. I just wanted to make sure I would not run into
> the case of multiple clients getting incorrect results because of not
> so up-to-date informations. Perhaps I should investigate table locks?

Yes, you should have a plan to avoid that kind of problem. Does your database support 
transactions? If so that is the easy way to ensure this - just put the whole getJob() into 
a transaction.

>>- Your getJob() code seems to use some variables before they are assigned, such as
>>tPoolIDs and aJob. Is this working code? Also it would be easier to read if you broke it
>>up into smaller functions that each do a small piece of the problem.
> 
> 
> [Bernard] This is not working code. tPoolIDs is bound after the first
> query of the function, but aJob is an error of mine.
> 
> Indeed I could break down the getJob() function into smaller
> functions. It's just that since the class is already having a fair
> amount of methods and this is becoming some long code, I wanted to
> keep everything into a single function.

hmm, not a good choice. The code will be more readable and maintainable if it is broken 
up. If the class gets to big, think about making a helper class for some of the code. For 
example you might put the whole getJob() function into a class or module whose job it is 
to talk to the database and figure out the next job.

One hallmark of a good design is that each class or module has a single responsibility. In 
your class you have several responsibilities that could possibly be broken out into 
separate modules
- maintain the state of a single client - this is the job of the Client class
- low-level database access - the query() function could be in a separate module
- details of a single job might fit well in a Job class, this would greatly simplify 
Client.setJob()
- getJob() might move to a utility module that just accesses the database and returns a 
Job object.

> 
> Also there was a consideration of performance. I have one question on
> the topic breaking code into small functions and performance. I have
> read somewhere that *any* call whatoever, that is, methods, functions
> and such, involve a performance cost. Is that right?

Yes.

> In the case it is true, the performance deterioration would be
> proportional with the number of calls being made, so the larger the
> number of iterations and the more function calls, the slower the code
> would run, is that correct?

Yes, it is correct. Worrying about it at this point is gross premature optimization. It 
will only be a problem if you have many many function calls in a performance-critical loop.

First make working code whose design clearly expresses the intent of the code. If it is 
too slow, then profile to find the hot spots and address them.

Kent