Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Posts
    7

    Cool Unanswered: How to speed up the query execution in sp.

    Hi,

    I am using a store procedure and in this sp i am having a simple select statement. Now i found that when i executes this sp in query analyzer it takes about 8-10 min to show the output. Table is having thousands of records. I can rebuild indexes on table, but apart from this what else i can do to speed up the query.

    I know there is something like we can use indexes explicitly in sql query. Is it true? if yes plz show me how to use it, by giving example

    Also is there any other way to run the query much faster.

    Plz help me, its very urgent

    Thanks And Regards,
    Shailesh

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Before you can make a good decision about how to improve your performance, you need to understand what is hurting it. Put your query into SQL Query Analyzer, and get the estimated query plan. This will show you graphically how the database engine proposes to execute the query for you.

    Look for "fat" lines, and steps that take a significant percentage of the total execution time. These are the things you need to concentrate your efforts on improving.

    Somewhat related to your other posting, you can also ask Query Analyzer to suggest what indexes will improve the performance of your query. You have to take the suggestions with a jaded eye when you do index analysis on a single query because those indexes may or may not help your performance overall, but if that query is a major problem the suggestions probably won't be all bad!

    -PatP

  3. #3
    Join Date
    Aug 2004
    Posts
    7

    Cool

    Hi,

    Thanks for the solultion,

    But plz tell me can i use explicit indexes in sql query while retriving the data from table. If yes plz give me syntax or any eg. of it.


    Thanks,
    Shailesh

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    You shouldn't need to use explicit index hints on a procedure if you designed the procedure/tables/indexes right in the first place. If your bent on using it anyway though, the syntax is in Books Online which apparently nobody reads anymore.

    Code:
    SELECT name
    FROM sysobjects WITH (INDEX(ncsysobjects))
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Hey !!! Somebody mentioned the Holy Book ... and Pat ... How can you know how to read the execution plan in the query analyzer is if you dont know about the BOL ????
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Post the DDL and the sql statement...we could probably tell you what you need...

    And thousands...shouldn't take 10 minutes.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    I don't think he's going to do that Brett. He's more of the demanding, me know and tell you, me no think kind of person I think.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •