Databases
StackOverflow2013
Postsdbo
POOptimize SQL Query to reduce execution time
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOptimize SQL Query to reduce execution time
    primarykey
    data
    text
    <p>I have a Query written in MS SQL Server R2. Please help optimizing it. It returns string constructs of data more than 6 Lakhs. and it takes 5-6 mins approx in returning result. Is there any way i can improve this?</p> <p>Below is my query: </p> <pre><code>CREATE proc [dbo].[Test] ( @UserTypeID int, @UserID int, @CityID int, @OperatorID int, @ParameterID int ) as begin declare @temp table ( range decimal(18,2), range2 decimal(18,2), image varchar(50), symbol varchar(20) ) If(@UserID&gt;0) Begin --print 'hii' Insert into @temp(range,range2,image,symbol) SELECT tbl_Legend_ViewNetwork_Dtls.range, tbl_Legend_ViewNetwork_Dtls.range2, tbl_Legend_ViewNetwork_Dtls.image,tbl_Legend_ViewNetwork_Dtls.symbol FROM tbl_Legend_ViewNetwork_Dtls INNER JOIN tbl_Legend_ViewNetwork ON tbl_Legend_ViewNetwork_Dtls.tbl_legend_view_network_id = tbl_Legend_ViewNetwork.id WHERE tbl_Legend_ViewNetwork.parameter_id = @ParameterID and tbl_Legend_ViewNetwork.user_type_id = @UserTypeID and tbl_Legend_ViewNetwork.is_default = 1 and tbl_Legend_ViewNetwork.user_id = @UserID update @temp set range = range2, range2 = range where symbol = '&lt;' End Else Begin Insert into @temp(range,range2,image,symbol) SELECT tbl_Legend_ViewNetwork_Dtls.range, tbl_Legend_ViewNetwork_Dtls.range2, tbl_Legend_ViewNetwork_Dtls.image,tbl_Legend_ViewNetwork_Dtls.symbol FROM tbl_Legend_ViewNetwork_Dtls INNER JOIN tbl_Legend_ViewNetwork ON tbl_Legend_ViewNetwork_Dtls.tbl_legend_view_network_id = tbl_Legend_ViewNetwork.id WHERE tbl_Legend_ViewNetwork.parameter_id = @ParameterID and tbl_Legend_ViewNetwork.user_type_id = @UserTypeID and tbl_Legend_ViewNetwork.is_default = 1 update @temp set range = range2, range2 = range where symbol = '&lt;' End --select * from @temp Select '[' + STUFF( (SELECT ',{"latitude":"'+ a.lat+ '","longitude":"' + a.long+ '","value":"' +convert(varchar(20),a.value)+ '","image":"' +temp.image +'"}' FROM (SELECT tbl_Survey_Details.lat, tbl_Survey_Details.long, tbl_Survey_Details.value FROM tbl_Survey_Details INNER JOIN tbl_Survey ON tbl_Survey_Details.tbl_survey_id = tbl_Survey.id INNER JOIN tbl_Location ON tbl_Survey.tbl_location_id = tbl_Location.id INNER JOIN tbl_Area ON tbl_Location.tbl_area_id = tbl_Area.id INNER JOIN tbl_City ON tbl_Area.tbl_city_id = tbl_City.id WHERE tbl_Survey_Details.tbl_parameter_id = @ParameterID and tbl_Survey.tbl_mobile_operator_id = @OperatorID and tbl_Area.tbl_city_id = @CityID) AS a INNER JOIN @temp temp on a.value between temp.range and temp.range2 For XML Path ('')),1,1,'') + ']' as data End </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USDeepak D
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POOptimize SQL Query to reduce execution time
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POOptimize SQL Query to reduce execution time
      1. USDeep Sharma
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload