Post reply

Optimizing Physical Temp Tables

  • October 6, 2008 at 10:34 am

    #199306

    Hi everyone, I hope you guys can help me out with this one. I have a few tables which receive data everyday but before doing this, they are truncated and then we load the data on them. After this our system must run some queries on it which are quite slow. Is there any way I could improve this queries knowing I will have to truncate/delete the table data?

    Thanks in advance

  • October 6, 2008 at 11:21 am

    #881249

    You should be adding indexes to the tables. Are they indexed?

  • October 6, 2008 at 11:50 am

    #881264

    Steve Jones - Editor (10/6/2008)

    You should be adding indexes to the tables. Are they indexed?

    That's what I dunno if I should do or not, because like I said my physical temporary tables will be truncated or deleted the next day. So my guess is that all the indexes I add to those temporary tables will be dropped and my sqlserver will have to redo all the indexing again once I write the data right?

  • October 6, 2008 at 12:31 pm

    #881279

    Truncating or deleting data from the tables will not drop the indexes you create. If you drop the tables themselves every day, then you'd lose the indexes you create.

    😎

  • October 6, 2008 at 12:34 pm

    #881280

    Try adding the indexes back after inserting the data but before querying it. If you're doing more than 1 or 2 queries, you may well see a large benefit.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 6, 2008 at 4:15 pm

    #881376

    Some more considerations in addition to the others:

    1. Try to load the data in the same order as the clustered index / clustered PK to improve load speed with existing index / pk.

    2. If you only query the table one or two times per day, an index might not be helpful.

    3. If you need fast response times with the queries and at the same time have enough time and space for index maintenance (maybe during off hours), indexing is the way to go.

    Best Regards,

    Chris Büttner

  • October 6, 2008 at 8:48 pm

    #881404

    You didn't specify where the data is coming from to load your temp table(s). Are you doing a bulk insert, pulling data from a linked server, or what? Likewise we don't even have an idea about the queries you are running, much less the query plans that are being generated. Help us out with a little more detail and you may get better answers. 😉

    Search here and in MSDN for articles about minimizing logging with temp tables. That can speed up your load time for large masses of data. Proper indexes will speed up some queries.

    Our standard procedure is to drop temp tables tables, load them in the order of their clustered index, and then build indexes over them. After that, query away.

  • October 7, 2008 at 11:11 am

    #881835

    thanks guys for the replies 😀 well, we load data to this physical temporary tables from other physical temporary tables. They are probably over 200 thousand records we have to move from one table to another one. What I was wondering was that if everytime I truncate the table, sql server would re-do all my indexes or nor. If it does, this would make my operations slower right? but it would help the queries I run after the copying hhhmmm am I right?Please correct me if I am wrong.

    The queries we ran on these physical temp tables compare a varchar field with another varchar field using substring, could that be an issue too?

    Thanks again for the replies guys

  • October 7, 2008 at 12:04 pm

    #881871

    If you simply truncate a table, indexes still exist but are empty. They are reloaded when you reload the underlying table.

    Yes, building or loading indexes takes work, which slows down the load somewhat but can really pay off when you run your queries. Test a simple truncate/insert with all indexes intact. Then test dropping the indexes, truncating, inserting into the base table, and rebuilding the indexes.

    Also be aware of whether or not your bottleneck is putting data into the temp table or getting it out of the source tables. The execution plan and IO statistics will help you out there.

    Finally, be sure to check the execution plan of your queries to be sure they are actually using your indexes. 😉

    Good luck.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply