I want to optimize SQL query this query is taking too long to load

[Mushigee2266]

I have a submission from in which there is a drop down [@field:RD1] . In this drop down user have to select one value from 1-60. Upon User selection I am fetching value V1 for 1 , V2 for 2 and so on v60 for 60. Below query's result is correct

But it is taking too long to load. I have 6 quries like this.

Any one help me to shrink this query , to optimize or any other alternate to achieve my requirement

 

CASE

WHEN [@field:RD1]="1" THEN (SELECT V1 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="2" THEN (SELECT V2 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="3" THEN (SELECT V3 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="4" THEN (SELECT V4 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="5" THEN (SELECT V5 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="6" THEN (SELECT V6 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="7" THEN (SELECT V7 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="8" THEN (SELECT V8 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="9" THEN (SELECT V9 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="10" THEN (SELECT V10 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="11" THEN (SELECT V11 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="12" THEN (SELECT V12 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="13" THEN (SELECT V13 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="14" THEN (SELECT V14 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="15" THEN (SELECT V15 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="16" THEN (SELECT V16 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="17" THEN (SELECT V17 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="18" THEN (SELECT V18 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="19" THEN (SELECT V19 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="20" THEN (SELECT V20 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="21" THEN (SELECT V21 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="22" THEN (SELECT V22 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="23" THEN (SELECT V23 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="24" THEN (SELECT V24 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="25" THEN (SELECT V25 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="26" THEN (SELECT V26 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="27" THEN (SELECT V27 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="28" THEN (SELECT V28 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="29" THEN (SELECT V29 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="30" THEN (SELECT V30 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="31" THEN (SELECT V31 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="32" THEN (SELECT V32 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="33" THEN (SELECT V33 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="34" THEN (SELECT V34 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="35" THEN (SELECT V35 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="36" THEN (SELECT V36 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="37" THEN (SELECT V37 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="38" THEN (SELECT V38 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="39" THEN (SELECT V39 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="40" THEN (SELECT V40 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="41" THEN (SELECT V41 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="42" THEN (SELECT V42 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="43" THEN (SELECT V43 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="44" THEN (SELECT V44 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="45" THEN (SELECT V45 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="46" THEN (SELECT V46 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="47" THEN (SELECT V47 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="48" THEN (SELECT V48 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="49" THEN (SELECT V49 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="50" THEN (SELECT V50 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="51" THEN (SELECT V51 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="52" THEN (SELECT V51 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="53" THEN (SELECT V53 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="54" THEN (SELECT V54 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="55" THEN (SELECT V55 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="56" THEN (SELECT V56 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="57" THEN (SELECT V57 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="58" THEN (SELECT V58 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="59" THEN (SELECT V59 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
WHEN [@field:RD1]="60" THEN (SELECT V60 FROM V1_SKINNY_1 WHERE WORKOUT=TARGET.[@field:WORKOUT] AND ROUND=target.[@field:ROUND1])
ELSE
0

END

[futurist]

Hi @Mushigee2266,

i dont know what your exact workflow is, but what i can see is you seem to have 60 fields that you are using for this. instead of having 60 different fields, why not just have one field and have 60 rows with values between 1 to 60? that way, instead of having this value, you can just use a cascading autocomplete that produces the, say, 60th record based on the selected value in RD1 which is 60? basically what im suggesting is optimize your table, if possible.

[Mushigee2266]

If I change the direction of the table then how I will recognise the field because I have no primary key.

If CASPIO provide indexing to table options then it is easy to speed up this. 

[futurist]

Hi @Mushigee2266,

you can add an ID field and set its DataType to either Random ID, AutoNumber, Prefixed Autonumber, or GUID, and once you save the field, the table will automatically generate IDs for the records. You can read more about those DataTypes here: https://howto.caspio.com/tables-and-views/data-types/data-types/