Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2005
    Posts
    13

    Unanswered: Use of Temporary Tables Vs Cursors

    I am using MSSS 2005.
    We have a need to find some cumulative values (Sum, Count, Min, max ) on an hourly basis, for values which are collected every second 24X7.

    A cursor method that exists now takes over 3 minutes for processing the same.
    I rewrote the code using Temporary tables (we have rows /hour ranging from 1000 to 7000) which is yielding data in 1-2 seconds. I am also explicity deleting the temp tables ( 2 of them per batch).

    I have read many blogs against cursors as well as against the use of temp tables. Which is worse? (in my case the timing isvery evident but..) I am concerned about any other hidden performance issues that I may not be aware of.

    I am using 'SELECT INTO ' but I was told 'CREATE TABLE' AND 'INSERT INTO'
    is better. Can any one throw more light on this/these issues?

    Thanks
    SK

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The blogs you read advising against the use of TEMP tables were probably specific to Oracle databases. You should not have performance problems with temp tables in sql server, providing your code and architecture are efficient.

    Post your code, and we can give you some better direction on your alternatives.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2005
    Posts
    13
    Ok Here goes : (/*for obvious reasons i am generalizing the functionality*/)
    ---Oops I just realized i typed all my comments in caps... Very sorry!
    BEGIN TRANSACTION

    SELECT A.ID,A.TIME_STAMP,A.VALUE INTO #TEMPTBL1
    FROM XSACTION_TBL1 A, BASE_TBL1 B
    WHERE
    A.ID=B.ID
    AND
    A.TIME_STAMP >= some_time1
    AND
    A.TIME_STAMP < some_time1+1hour

    SELECT INTO #TEMPTBL2
    MAX(VALUE) as max_val, MIN(VALUE) as min_val,
    COUNT(val) As 'COUNT_OF VAL' , -- 'this is to make sure we are getting enough values for the hour
    FROM #TEMPTBL1
    /*
    -- SINCE THERE COULD BE MULTIPLE INSTANCES OF MAX AND MIN VALUES, WE ALSO LOOK FOR LATEST TIMES AS TO WHEN THE MAX AND MIN ARE OCCURING.
    --FOR THIS, I AM INSERTING THIS ABOVE TABLE #TEMPTBL2 INTO THE ACTUAL DATA TABLE, ALL THE WHILE LOOKING FOR MAX(TIME_STAMP) FROM TBL1 WHERE VAL= SELECT MAX_VAL FROM TBL2. THIS MAX_VAL ALREADY HAS THE MAX(VALUE) AND SO I DONT NEED TO DO GROUPING OR 'HAVE' OR RUNNING MAX MULTIPLE TIMES OR IN SUB QUERY*/
    Last edited by sivay2k1; 08-09-10 at 18:18. Reason: Just realized all my comments are in caps.Sorry folks!

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    SELECT ... INTO is more efficient than insert into (in so far as the inserted data is not logged, only the page allocations). So it depends on your logging requirements vs speed.

    I assume there is more to it than this right? Because there is no need for #TEMPTBL1 at all here. Possibly not #TEMPTBL2 either.
    In fact the code and comments do not marry up.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2005
    Posts
    13

    Hmmm...

    I am by no means an expert... but I would need some place to 'park' my data
    from the main transaction table (the reason for #TEMPTBL1)
    AND
    Another one to find the cumulatives and sort out the MAX(time) when the MAX of values and/or MIN of values.

    Please guide me ( i dont need code) if there is a more efficient way of doing this. A current script using cursors takes about 2+ minutes and so I thoght I would use temp tables to increase speed.

    There are no other hidden agendas.
    --SK


    Quote Originally Posted by pootle flump View Post
    SELECT ... INTO is more efficient than insert into (in so far as the inserted data is not logged, only the page allocations). So it depends on your logging requirements vs speed.

    I assume there is more to it than this right? Because there is no need for #TEMPTBL1 at all here. Possibly not #TEMPTBL2 either.
    In fact the code and comments do not marry up.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Looking at your code even more closely, there is something missing. The last statement would not even compile - there is an errant "," before the comment.

    Also, at no point do I see you "LOOKING FOR MAX(TIME_STAMP)".
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Alright...
    Why are you using temp tables at all?
    Why did you split this into two separate statements?
    Why are you bothering to wrap it in a transaction?
    Why are you storing ID and TIME_STAMP in #TEMPTBL1 when you do not reference them for #TEMPTBL2?
    Why are you using superfluous and obfuscatory aliases?
    Why are you joining tables in you WHERE clause?
    Why are you joining to BASE_TBL1 and never referencing it?

    This would seem to satisfy your requirements:
    Code:
    SELECT	XSACTION_TBL1.ID,
    	XSACTION_TBL1.TIME_STAMP,
    	MAX(XSACTION_TBL1.VALUE) as max_val,
    	MIN(XSACTION_TBL1.VALUE) as min_val,
    	count(*) as 'COUNT_OF VAL'
    FROM	XSACTION_TBL1
    	INNER JOIN BASE_TBL1 on XSACTION_TBL1.ID=BASE_TBL1.ID
    WHERE	XSACTION_TBL1.TIME_STAMP >= some_time1
    	AND XSACTION_TBL1.TIME_STAMP < some_time1+1hour
    ...or even just this:
    Code:
    SELECT	MAX(VALUE) as max_val,
    	MIN(VALUE) as min_val,
    	count(*) as 'COUNT_OF VAL'
    FROM	XSACTION_TBL1
    WHERE	TIME_STAMP >= some_time1
    	AND TIME_STAMP < some_time1+1hour
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2005
    Posts
    13

    Ok ... my mistake

    I did not mention that part of the code. That part is a segment #3.

    Yes I did use your idea first. Running max(value), min(value) on the xsaction table.
    But, as I mention, this is a very very busy table getting automated data every sec from about 1000-1500 monitored points.

    I tried to run max and min on this , but even with one hour data, running group by s literally killed it.

    As a result, this is what I am doing:

    1. Based on ID and Time I am getting all granular data from xsaction table
    into TEMPTBL1.
    2. Based on ID I am running MAX and MIN from TEMPTBL1 into another temp table TEMPTBL2. Now I am assured that what I have for each ID (for the given hour) are the MAX and MIN values.
    3. I also need to find out the TIMES when the latest MAX and MIN happened, and I am running an INSERT into a cumulative table(ANOTHER USER TABLE) with ID, MAX & MIN values from TEMPTBL2 and (SELECT MAX(TIME) WHERE VAL=TEMPTBL2.VALUE) (Similarly for the MIN(TIME) ).

    My mistake, I did not add this piece of the code in my question. The focus of my question was wether or not my usage of TEMP tables (assuming that I cannot run straight MAX/MIN functions on my main transaction tables)
    is correct and if they would cause any performance issues.

    Question regarding TRANSACTION:
    I included all of them in one transaction so as to rol back evey thing if @@ERROR <>0

    -SK

  9. #9
    Join Date
    Feb 2005
    Posts
    13
    UPDATE:
    I am using Basetable in my initial query to limit my data selection to a subset of IDs present in the baseable. My main transaction table picks up per second data from more IDs than what I require to do summation/max/min on.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You might benefit from using the NOLOCK query hint.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by sivay2k1 View Post

    Question regarding TRANSACTION:
    I included all of them in one transaction so as to rol back evey thing if @@ERROR <>0

    -SK
    Rollback what? The inserts into the temporary tables?
    sivay, we really can't help you more unless you are going to tell us the details of what you are trying to do.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Feb 2005
    Posts
    13

    Rollback

    Jee... I did not think I would be so nebulous in my explanations.
    I appreciate your patience with me. Thanks much.

    I am running an insert into a (cumulative) user table in step 3.
    This is what I want to rollback, based on @@ERROR.

    Summary of what I need:

    From a transaction table which gets data every second for a few thousand points, I want the hourly avergaes, max & mins AND the times when the max and min happened (for a sub set of those points) loaded into another user table.



    -SK

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by sivay2k1 View Post
    ...AND the times when the max and min happened (for a sub set of those points) loaded into another user table.
    -SK
    You realize your included code does not do this, right? What are you going to do if a particular max or min value occurs more than once during the time range? Record the first instance? The last instance? Both instances?

    I assume your data is static once it is collected, and that you are not going to be aggregating statistics on time periods until after those periods have elapsed. So I would first try a direct insert (no temp tables) using the NOLOCK query hint on the source table. If you still have performance issues, do a single SELECT... INTO #TEMPTABLE to isolate the target records, and perform your aggregations with that.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Feb 2005
    Posts
    13
    Yes I do understand that.
    If you check one of my prior posts,
    I did mention that I am doing an insert into a another user table and there in I am doing a (SELECT ID,TEMPTBL2.MAX_VALUE (which is max value),
    TEMPTBL2.MIN_VALUE (which is min value), (SELECT MAX(TIME) FROM TEMPTBL1 WHERE VALUE=TEMPTBL2.MAX_VALUE)

    i do a similar select for max (time) for minimum value.

    Thank you very much for the idea of NO LOCK. I will try that.

    So you think that the no lock option would offer better performance
    than the TEMP tables?
    Coming to the original question:
    -----------------------------
    Would there be any performance impact in using TEMP tables ?

    Thanks
    SK

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Probably not.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

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