IMDB data processing

[Christian Wallukat]

Hi all,


I need a fast processing of a small amount of data (up to 300K lines in max.).
I need to calculate durations between 2 entries in 2 different tables (1 is a filtered copy, which contains less data).

the table structure is very simple:

table a and table b looks like the following:
{
key varchar(50) <-- which is a GUID
key2 varchar(50) <-- which is a GUID
ts datetime
event varchar(50)
duration int
}


table a contains 300K rows, table b contains values from table a where a special "event" is set (lets say only 200K rows).

now I do the following to calculate the durations:

for each line in table a lookup the next entry in table b where the key2 is the same but b.ts greater a.ts. with the result table a will be updated...

To be honest nothing serious, on my win32 test-machine (shame on me) I installed mySQL and craeted the tables. For my test case I loaded the data from the file and created a small procedure to test the case.

Fist of all: I aborted the test after 3 hours (MS SQL Server does it in 2) but I think it could be OS related since I think that windows stored the mysql process memory in the pagefile (that could be the cause why the hdd led was continously flashing)...

Does anyone had realized the same ?
Is it faster on linux os or are there configuration switches which can improve this ?

If necessary I can provide the procedure and tables (and 40 MB compressed data), but for this case I think it is not necessary...

Thanks for any idea / help !


Kind regards

Christian