none
Query optimization with temp table RRS feed

  • Question

  • Sometimes it happens that creating clustered index in temp table makes query take longer time because the insertion becomes long. But the clustered would make the following select queries faster.

    Is there a way to make the overall query faster by creating clustered index in temp table?

    Saturday, September 14, 2019 1:14 PM

All replies

  • Hi

    As you mentioned, Insertions will generally gets slower when we have Clustered Indexes on tables and the same in case of temp tables.

    But, When the data is really huge I would suggest creating the Clustered Index after inserting the data would certainly help to improve performance.

    As we cannot judge/provide a thumb rule whether the Indexes should be used/not as it depends on various factors as below:

    • The level of data/columns etc.,
    • Are you joining it to other tables ?
    • Execution Plan details
    • Is this implementation part of a stored Procedure?

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Saturday, September 14, 2019 1:57 PM
  • Sometimes it happens that creating clustered index in temp table makes query take longer time because the insertion becomes long. But the clustered would make the following select queries faster.

    Is there a way to make the overall query faster by creating clustered index in temp table?

    It's just as you say - sometimes it pays off, sometimes it doesn't.  Depends on so many things.

    I think it's usually better to create the empty table with the clustered index and populate it the first time that way, rather than first write it as a heap and then create the index.  But even that depends.

    Just have to try the various options and see.

    Josh

    Sunday, September 15, 2019 2:16 AM
  • Drop the index and re-create the index when you do insertion that takes long. It may not be big saving because creating the index will take time but its a trade off if you can cut down 30% downtime. Good luck.


    Sunday, September 15, 2019 2:51 AM
  • >>>Drop the index and re-create the index when you do insertion that takes long.

    This may cause recompilation and/or  force SQL Server not caching this temporary table,


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, September 16, 2019 7:06 AM
    Answerer
  • Hi

    Just checking in to see if my initial answer helped or you still facing any issues.

     

    If my initial reply answers your query, do click “Mark as Answer” and Up-Vote for the same which might be beneficial to other community members reading this thread .

    And, if you have any further query do let us know.

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Wednesday, September 18, 2019 3:03 AM