Jump to content
  • 0

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


Mushigee2266

Question

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

Link to comment
Share on other sites

3 answers to this question

Recommended Posts

  • 0

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...