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 have records in lakhs, 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
3  
Please post your execution plan. –  JNK May 31 '13 at 12:52
4  
The best would be downloading SQL Sentry Plan explorer (free) and using the upload feature. It can obfuscate your object names so it's private but still post a quality execution plan on the web for us to check out. –  JNK May 31 '13 at 13:01
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
show 27 more comments

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

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

2 Answers

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
add comment

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
    
I like the suggestions, but, like @MartinSmith, I'm still waiting for the real .sqlplan. Pictures of the plan are good, but not like the real one. –  Marian Jun 2 '13 at 0:16
    
@Marian, Plan created by Optimiser in the given circumstances. It cannot make the query different or achieve same result with less effort by going outside the query boundaries. After all a query is a command to do certain operations. Computer cannot disobey, but it's our task to make its life easier. –  Stoleg Jun 2 '13 at 0:23
    
the main problem I have with the pic (not with the real plan itself) is that I can't see the real numbers (estimated rows vs actual rows, no of executions for certain operations). Call it laziness, yes, maybe you're right. But I still prefer the real plan. –  Marian Jun 2 '13 at 0:28
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
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
show 9 more comments

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.