SQL long Join statement and performance

[daniel futurebase]

Hello,

i have a table with id, participant_id, varname, varname_value (1, 1, 'servwww', '1')

where a bunch of variables and values of variables per participant are stored.
Variable names are 'servhotel' or 'servwww' for example.

Now i have to reformat it to a table like

participant_id, servwww, servhotel ... and so on...
(1, '1', '1' .... )

i use the following sql statement to do the reformating with 9 variables. That works quite good for 9 variables (12 sec. for 4 records per variable). But if i use a longer sql statement for 10 variables it takes 230 sec. .. why? Can someone give me a hint you i can avoid that?

SELECT varX.IsParticipant_id, varX.CSVData as 'servhotel' , 
                              var0.CSVData as 'servpers' , 
                              var1.CSVData as 'servwww' , 
                              var2.CSVData as 'servprice' , 
                              var3.CSVData as 'servoffers' , 
                              var4.CSVData as 'servshopping' , 
                              var5.CSVData as 'servamb' , 
                              var6.CSVData as 'servalternative' , 
                              var7.CSVData as 'servgastronom' , 
                              var8.CSVData as 'servnature' , 
                              var9.CSVData as 'servcosi' FROM base_data as varX  
                              
INNER JOIN `base_data` AS var0 ON ( varX.IsParticipant_id = var0.IsParticipant_id ) 
INNER JOIN `base_data` AS var1 ON ( varX.IsParticipant_id = var1.IsParticipant_id ) 
INNER JOIN `base_data` AS var2 ON ( varX.IsParticipant_id = var2.IsParticipant_id ) 
INNER JOIN `base_data` AS var3 ON ( varX.IsParticipant_id = var3.IsParticipant_id ) 
INNER JOIN `base_data` AS var4 ON ( varX.IsParticipant_id = var4.IsParticipant_id ) 
INNER JOIN `base_data` AS var5 ON ( varX.IsParticipant_id = var5.IsParticipant_id ) 
INNER JOIN `base_data` AS var6 ON ( varX.IsParticipant_id = var6.IsParticipant_id ) 
INNER JOIN `base_data` AS var7 ON ( varX.IsParticipant_id = var7.IsParticipant_id ) 
INNER JOIN `base_data` AS var8 ON ( varX.IsParticipant_id = var8.IsParticipant_id ) 
INNER JOIN `base_data` AS var9 ON ( varX.IsParticipant_id = var9.IsParticipant_id ) 
INNER JOIN `base_participant` ON ( varX.IsParticipant_id = base_participant.id ) 

WHERE varX.Varname = 'servhotel' 
AND var0.Varname = 'servpers'
AND var1.Varname = 'servwww'
AND var2.Varname = 'servprice'
AND var3.Varname = 'servoffers'
AND var4.Varname = 'servshopping'
AND var5.Varname = 'servamb'
AND var6.Varname = 'servalternative'
AND var7.Varname = 'servgastronom'
AND var8.Varname = 'servnature'
AND var9.Varname = 'servcosi' 
AND base_participant.IsProject_id = 3  

ORDER BY varX.IsParticipant_id DESC

Edited 1 time(s). Last edit at 02/17/2010 11:26AM by daniel futurebase.