CODEWITHSUNDEEP
mysqldatabaseiisasp-classiciis-7.5
Mem
Mem

Reputation: 31

MySQL: How to work with multiple connection?

I have developed a script which works with a large MySQL database. The script works on IIS, MySQL, ASP Classic. The script selects 10,000 or 100,000 records and works with the records one by one and updates the database. Everything works fine, but with very slow performance. The reason for the slowness is not because of select or update statements or slow server, but because of working with those records one by one and doing some changes, then updates.

For example

SELECT * from mytable
WHERE isempty(title)
ORDER BY length(title) DESC LIMIT 100000;

Then working with those 100,000 records one by one takes, e.g. 100,000 minutes. So, I want to run the same script with 2 or 3 browsers, let say IE, Chrome, FireFox..

I was thinking to do it like this, but I am not sure if it is possible or not.

On IIS when runs the script on browser 1, it selects 100,000 records and starts work on them and starts making some changes. On browser 2 it selects 100,000 but on database less records with same condition, it might selects 90,000 and start work. Since the browser started little early, it might do some changes, so while both threads work, each other has to see those changes and work with those changes. For example the title finished on current record, then pass that record and choose another one. Is that possible? I am not sure and never used the cursor location and cursor type or whatever..

Let say 101,000 records are on the database, script 1 started first and selects 100,000 rows. After 100 minutes browser 2 starts. But when browser 2 selects 100,000 rows, that time the browser 1 has already finished 10,000, so the browser 2 will get only 91,000 records. But since those two browsers work on the same record, how can they see each others changes?

Is there any solution for my current situation? I am not MySQL expert, thats why I don't know what to do.

I am sorry for my English, but I hope you understand my question.

UPDATE;

this is not because of any script problems, or slow server problem or any other problem. this is slow because between "DO WHILE RS.EOF" AND "LOOP" I do lots of things AND aswell it doesn't really takes one minute per record, just saying an example. but I was thinking simultaneously 2 or 3 instances running the script.

Upvotes: 0

Views: 1053

Answers (1)

AnonJr
AnonJr

Reputation: 2757

ASP-Classic does not support the type of multi-threading you are looking for, however you could write a COM component or something similar that would and call it from your page.

Unless there is some sort of input required from the user, you could also write a server-side task in VBScript/PowerShell/Python/etc. to occasionally run through the data and perform what ever task it is you are trying to accomplish. It's hard to be specific when the question isn't very specific.

Having said all that, it really does sound like there are more problems with your code than you realize. It's hard to point out due to the lack of a concrete example in front of us. If you haven't already, I'd double-check to make sure the bottlenecks are where you think they are.

I've used a crude ASP Profiler in the past to look for where the specific bottlenecks are in the ASP/VBScript sites I still maintain, and on a few occasions I've found that the problem was in the least likely spots.

The bottom line is that your question is missing a fair amount of information for providing useful answers, and seems to make some assumptions that might not necessarily be true. Show us some code, provide us with some data, and you'll probably get better answers.

Upvotes: 1

Related Questions