In reply to your first comment, yes it does involve an SQL transaction. It connects to the database, prepares a query, executes it, fetches rows in turn in a while loop, and then at the end disconnects and finishes. You mention a lock, I was not aware about any form of lock (as I said I am a complete novice).
So, could it be that because the database connection and query is closed only at the end of the file, the next process needs to wait until the previous query is completed..
So with that knowledge, would it speed things up to do the query at the start of the script, load the response into an array, close the query and connection and then loop through the array? | [reply] |
It would help a bit if you post the script you are using or at least an example of what it does as that makes things easier to understand for all of us.
The next thing you should keep in mind is that it always pays of to use finte resources for the shortest amount possibe. Meaning, close a DB connection as soon as you no longer have a need for it. This will be the same for any and all resources like files, printers and well pretty much anything that you can for what ever reason not share with a second instance of the script. So check for file locks or other possible reasons why your script would not want to run more then once.
Also if you expect to have say 100 users executing this script at roughly the same time or in a relatively short time frame. See if you can maybe change the logic making an caching option for the DB results as it could quite likely be that out of the 100 users some will end up running the same query. Depending on the speed with which the data in the DB is likely to change and the need to have up to date results you might be able to simply use cached results for a certain percentage lowering the load on the database and the risk of tying up your limited resources.
| [reply] |
Not necessarily. If it is just a query (select), rather than an update, delete, or insert, then locks might not be involved. I meant the term "transaction" to be technical, that is possibily involving a lock. So it depends on the type of transaction, and the database. Some databases require table locking and some row locking. Some databases have different requirements depending on the table type (MySQL comes to mind).
This still assumes that the jobs are running concurrently. Are they?
It might also help if we knew the OS and which database product.
| [reply] |
It is just a select. The database is MySQL client version: 4.1.22. I just ran made 4 requests at the same time, and looked at the processes in the CPU. The perl script in question appeared 4 times in the cpu process list, each entry using a similar amount of CPU and Memory. The OS is linux i686 running apache 2.0.61.
| [reply] |