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.