Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am running a query and it has records in the hundreds of thousands, it takes more than 20 mins to fetch data. After running the execution plan i noticed that clustered index seek cost can be be the reason. How can I reduce the clustered index seek cost of below mentioned query?

Foreign keys costs around 13% to 23%.

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 > 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 = '<'
        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 = '<'
        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 

Execution plan screenshot

Revised Query:

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 > 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 = '<'
        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 = '<'
        END

   SELECT   a.lat, a.long,a.value, 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

  END 

Revised Execution Plan: Revised Execution Plan

Link to XML Execution plan file : click here

share|improve this question

migrated from stackoverflow.com May 31 '13 at 13:54

This question came from our site for professional and enthusiast programmers.

3  
I suspect the actual cost/time is mostly in the XML, which is not normally expanded in the execution plan (because it's a function call). You're joining on an XML value function output, which is not sargable unless you have XML indexes in place. –  JNK May 31 '13 at 13:21
2  
That XML UDX bit at the end of the execution plan is probably the worst thing in the query, but the optimizer can't calculate the cost so it shows it at 0% –  JNK May 31 '13 at 13:22
3  
Well you are creating XML and then querying it - can you not just query the tables directly? –  JNK May 31 '13 at 13:34
1  
That clustered index scan on the inside of the nested loops looks possibly problematic. How many rows does tbl_Legend_ViewNetwork_DTLS have? How many executions of the scan are happening? Uploading the XML for the actual execution plan would be best. –  Martin Smith May 31 '13 at 14:01
1  
@MartinSmith also the nested loop on the left of the one you mentioned looks problematic. Thick data line from Tbl_Area and thick data line from Tbl_Legend_ViewNetwork_DTLS. Maybe a nested loop is not the most appropriate here. –  Marian May 31 '13 at 14:24

2 Answers 2

Since it takes about the same amount of time to fetch 300,000 rows using a very simple query, I'd suggest you stop looking at query tuning temporarily and decide whether this scenario you're currently testing is realistic.

  • You are running Management Studio on your local workstation, connected to a SQL Server instance on a godaddy server. Therefore on top of the query cost within SQL Server you are also constrained by:

    • godaddy's bandwidth (shared with other people on those servers)
    • your bandwidth (potentially shared with other people in your neighborhood)
    • the time it takes Management Studio to gather the results and render them
  • You are retrieving 300,000 rows in your result. Usually this is not something you do - what user is going to consume 300,000 rows? Consider aggregating or only return a subset on each pull, and thinking about what purpose this query actually serves.

Since it is unlikely that this is how your database will be expected to produce results in reality, I suggest you change your testing methodology somewhat. Either have Management Studio installed on some server within godaddy's infrastructure, taking bandwidth and general Internet volatility out of the equation, or test your query logic using your local copy of Management Studio, but don't use that for timing the results. Rather, use an app within godaddy's infrastructure to test the timing (after all, this is how your application will eventually work, right?).

If the query is also slow when you've taken bandwidth/Internet out of the picture, then you can start to consider whether the I/O you're getting from godaddy's server is sufficient (or whether you really need to pull 300,000 rows at any one time anyway, so maybe the point is moot).

share|improve this answer

These steps should help:

  1. Add OPTION RECOMPILE to both queries with parameters. It will recompile execution plan for actual parameters' values.

  2. You have index on tbl_Legend_ViewNetwork (parameter_id, user_type_id, is_default,user_id) with user_id being the last column. Don't you?

  3. Create a summary table or materialized view to substitute 3 sequential joins from the 2nd query.

  4. Change update statement for a case statement in the query.

  5. Use CTE instead of @Temp, as you do not need to update anything

  6. Get rid of IF by using `user_id=@UserID OR @UserID is null)

  7. When you do multiple JOINs you need to filtering as soon as possible. Thus you should do Select * from tbl_City JOIN ... Where City_id = @CityID. So you first select 1 city and then join, rather than selecting nay rows, joining to all cities and then filtering.

  8. You do not need to JOIN tbl_City in the last query, because you are not using contents on this table anywhere (you use: tbl_Area.tbl_city_id = @CityID). Here is what I get:

    CREATE PROC [dbo].[Test] (@UserTypeID  INT,
                          @UserID      INT,
                          @CityID      INT,
                          @OperatorID  INT,
                          @ParameterID INT)
    AS BEGIN
    
    with cte1 as (
            SELECT 
    
                case  tbl_Legend_ViewNetwork_Dtls.symbol
                when '<' then tbl_Legend_ViewNetwork_Dtls.range2
                else tbl_Legend_ViewNetwork_Dtls.range
            end 
            as range,
    
            case  tbl_Legend_ViewNetwork_Dtls.symbol
                when '<' then tbl_Legend_ViewNetwork_Dtls.range
                else tbl_Legend_ViewNetwork_Dtls.range2
            end 
            as range2,
           tbl_Legend_ViewNetwork_Dtls.image
            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 OR @UserID is NULL)
    
    
    ),
    
    cte2 as (
    
        SELECT  
            tbl_Survey_Details.lat,
                        tbl_Survey_Details.long,
                        tbl_Survey_Details.value
    
        FROM    tbl_City
    
        INNER JOIN tbl_Area
            ON tbl_Area_tbl_city_id = tbl_City.id AND tbl_city_id = @CityID
    
        INNER JOIN tbl_Location
            ON tbl_Location.tbl_area_id = tbl_Area.id
    
        INNER JOIN tbl_Survey
            ON tbl_Survey.tbl_location_id = tbl_Location.id
    
        INNER JOIN tbl_Survey_Details
            ON tbl_Survey_Details.tbl_survey_id = tbl_Survey.id
    
        WHERE   tbl_Survey_Details.tbl_parameter_id = @ParameterID
                        AND 
            tbl_Survey.tbl_mobile_operator_id = @OperatorID
    )
    
    
    SELECT  a.lat, a.long,a.value, cte1.image
                FROM cte2
    
                INNER JOIN cte1
                       ON cte2.value BETWEEN cte1.range AND cte1.range2
    OPTION (RECOMPILE)
    END
    

P.S. Marian, thank you for your help with formatting!

share|improve this answer
3  
@DeepakD you're fetching 300K rows from a godaddy server. Are you sure the problem is the query, and not the fetching of 300K rows? How long does it take SELECT TOP 300000 * FROM dbo.Some_Big_Enough_Table;? –  Aaron Bertrand Jun 4 '13 at 13:01
    
@AaronBertrand i created simple query according to you. And it is taking approx same time (with a difference of 15-20 secs) as my original query. i don't know what to do now. :( –  Deepak D Jun 5 '13 at 8:50
2  
I'm afraid there is nothing you can do further without SQL Profiler. Use it to find out bottlenecks on your server (I/O, CPU, Memory). To me it seems to be a constrain on data reading (I/O ~ HDD reading speed) performance of your hardware. There a limit how fast a query will run on any given hardware. –  Stoleg Jun 5 '13 at 9:16
    
Another thing you can try, actually, is try to get this data set in parts using several connections from your applicaion (where the data goes to). If you have several disks, then store parts of your table on different disks to increase reading speed. You can look at partitioning, but it looks like overshoot to me. If you are bound by HDD reading speed, then partitioning and straming would not help much. –  Stoleg Jun 5 '13 at 9:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.