Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Feb 2004
    Posts
    24

    Exclamation Unanswered: Temporary tables

    I am writing a stored procedure that outputs it's information to a temporary table while it assembles the information. Afterwards, it returns the contents of the temporary table as the results of the stored procedure.

    I found that if you create the table, inside the SP, as an ordinary table, the information builds to that table considerably faster than if you use a true temporary table.

    I found that if I create a user function that returns a table as it's return value, it is also as slow as if I used a true temporary table.

    The database can amass over 2 million records in one table in just a few days. If I have the procedure query against this table, and output to an ordinary table it creates, and summarize the information it is adding to the table, then it takes an average of around 4 minutes to return the results from the query. If I change the output table to a temporary table (#temp), it between 12 and 15 minutes. Nothing else in the procedure changed. Just the kind of table. If I take the logic and move it to a function which returns those results in a RETURN table, it also takes over 14 minutes.

    Why would it take so much longer outputing to a temporary table rather than a normal table? Is it because temporary tables are stored in a different database (tempdb)? Why would returning query results from a function be just as slow?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well #temp is using tempdb...so You're sharing that resource, and if you use a lot of batch process and temp tables you could be competing for resources....

    A function??

    How much data are we talking about?

    And how complicated is the sproc that you need top build/update (I assuming) to a temp table

    You don't mention the result set volume....or platform..

    If it's not that huge, and you're on sql 2k, look at table variables...

    Can you post the sproc?
    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.

  3. #3
    Join Date
    Feb 2004
    Posts
    24

    Exclamation

    The SP is about 2 pages long. It is designed to query data from a table in either the main database or the history database. The two tables are identical in layout. The main database usually collects about 3 to 10 days of data before a maintenance program moves "old" data to the history database to speed up access to the main database.

    I am using:
    SQL Server 2000
    C#.NET
    Crystal Reports.NET
    Windows 2K

    as the base server system. The database collects real-time data from a system of networked game machines. The more machines in the network, the quicker the Play table reaches 2 million+ records. At one site of 300 machines, it takes about 2.5 to 3 days to get 1 million records in that table.

    I was forced to use SP to query data because it is the only way to get a single Crystal Reports report to query data and build [essentially] the exact same report whether pulling data from either the main or history database. It was either use SP or create a duplicate report for the history database since CR tightly binds the report to a single query source (stored procedure, database, ...).

    Currently, I am testing on my own personal game server, so it is not being shared with anyone else. In the field, I expect this to change since anyone anywhere on the network can pull a report at any time, which is why the database access time is a concern. Not to mention the fact that the game machines are sending loads of data to the server and their performance will be impacted on lengthy queries!!!

    I am using grouping/summarization to produce as compact a temporary table data as I can. It usually averages summarizing 1.5 million records to just over 500 records in the temporary table. Using grouping/summarization has already produced a noticeable reduction in report build time. I am using the temporary table concept because:

    1. I couldn't get a join between 4 tables to produce the required output
    2. temporary tables are faster than sub queries
    3. droping and then re-creating an ordinary table, used as a temporary table and then summarizing data to it, appears to be much faster than using temporary tables.

    When I mean "function"...

    CREATE FUNCTION GameDetailFunc
    (
    @start datetime,
    @end datetime
    )
    RETURNS @TempPlay TABLE
    (
    :
    )
    AS
    BEGIN
    :
    RETURN
    END

    GO

    will return the "temporary table data" as the return table value of the function. My guess is that this apparently writes the data to the tempdb also making it as slow as just using a #TempPlay type table. Both versions took 14.5 minutes to produce a report from a database of over 2 million records. Simply creating an ordinary table and populating it took right at 4 minutes to produce the exact same report. However, if more than one person attempts to run the report at the same time, report results are unpredictable.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    End result is 500 rows....

    DECLARE @temp TABLE(Col1, int, ect...

    Will store the data in memory...fast

    Now what about these 4 tables....any indexes?

    What about the query you said couldn't get to produce the result...

    if it can be done with temp, it can be done with a join...

    It's the indexing I'd be most concerned with...
    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.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    15 minutes is a long time for two million records. I'm sorry to have to ask this, but have you made sure all your tables are indexed properly and that the statistics have been refreshed?

    I guess before we go scrapping one execution methodology for another I think it would be valuable to first take a look at the code and see if it could be made more efficient.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    if i wanted a physical reason
    you may want to look at where your tempdb is located
    it grows provides an temp space to sorts group by's distincts query overflows from memory and Temporary tables
    so if you have a lot of data in memory (2 million rows or so) you are causing a lot of writes to the tempdb
    so if it resides on the system drive which a lot of people mistakenly do, it is sharing space with the pagefile, and the winnt and system32 and everything else installed on that dir and when it grows its contending with everything else.
    in an optimal situation, i usually place the tempdb (by itself)on a raid 0 array, which is very fast on the reads and the writes and because it is dedicated to the tempdb there are no contention issues .
    even on small dbs, moving it to another drive of its own will show something when contention is the issue.

    just goin to the physical for a bit.

  7. #7
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    A lot of people are confused about table variables, which your function produces. It is faster than a temp table if you have a small amount of records like you do. It is still producing space in tempdb though and utilizing this database. Anytime a table variable is produced, it produces a # table in tempdb.

    You need to pull up profiler and see what the write cache and read cache are on the drives it's housed on.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by derrickleggett
    A lot of people are confused about table variables, which your function produces. It is faster than a temp table if you have a small amount of records like you do. It is still producing space in tempdb though and utilizing this database. Anytime a table variable is produced, it produces a # table in tempdb.

    You need to pull up profiler and see what the write cache and read cache are on the drives it's housed on.
    Not if it's properly index he won't...

    Need to see the query and DDL including the indexes...
    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.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I did not know that table variables take up space in tempdb. I'd heard that one of the reasons they are more efficent than temporary tables is that they don't hit tempdb.

    Do you have documentation or references for this?
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blindman
    I did not know that table variables take up space in tempdb. I'd heard that one of the reasons they are more efficent than temporary tables is that they don't hit tempdb.

    Do you have documentation or references for this?
    It doesn't

    It resides in memory....

    That's why you have to be careful about the size...

    Otherwise it'll page out to virtual....
    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.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    There will be a reference to it though in the tempdb catalog..

    It just won't physically reside there...
    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.

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

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

  13. #13
    Join Date
    Feb 2004
    Posts
    24

    Exclamation

    Thanks for all of the suggestions/recommendations. I'll see if any help.

    The temp variable explanation explains a bit. I noticed, when browsing through the list of objects in the sysobjects on tempdb, that #TempPlay existed in the catalog but it did not "physically" exist in the Table list. That confirms [for me] why.

    In that case, I think memory is an issue as well. My test system does not contain as much ram as we have in the systems in the field, so it is probably paging much more. Whereas, using a "real" table, as opposed to a temporary table, probably won't fill up memory as much during processing and thus not cause as much paging.

    I did do a detailed analysis of the indexes and found one more it recommended that seems to have helped quite a bit.

    I was even thinking that maybe giving the tempdb it's own partition on a drive other than the drive where the swap file exists might help there. It might also help some of our customer sites. I know that this helps dramatically when you give the swap file it's own partition.

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by sheridan101
    I was even thinking that maybe giving the tempdb it's own partition on a drive other than the drive where the swap file exists might help there. It might also help some of our customer sites. I know that this helps dramatically when you give the swap file it's own partition.
    That's good insight...that's one of the performance gain reccomendations...

    You need to look in to DBCC commands if you go that route....

    Still a table variable for only a couple hundered rows should fly...

    If you post the sql, I'm sure we could recommend so performance gains...

    Or try here as well...

    http://www.sql-server-performance.com/forum/
    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.

  15. #15
    Join Date
    Feb 2004
    Posts
    24
    After analyzing and putting in the indexes that SQL Enterprise suggested, I only got a modest gain when using the #TempPlay approach. Query time went fro 14 minutes to 10 minutes. If I use a query that does a similar query, but does not use the #TempPlay, the query time went from 4 minutes to 48 seconds querying on a date range of about one week. When doing a query for one day's worth of data using the same query (without the #TempPlay), it went from 4 minutes to 4 seconds.

    If I rework to call the function mentioned above, which returns a table rather than using the #TempPlay, it returned from the query in 48 seconds when getting a date range of one week, and about 4 seconds for a range of one day. So, the indexes seem to have really improved the function return time, but the #TempPlay is still too slow. I guess I'm going to have to use the function approach.

    My guess for why the #TempPlay is so slow is because the #TempPlay is not indexed (since it is a temporary table). For each of the 1.5 million records, it must seek the proper #TempPlay record and update it's summary fields. Since the function table is held in memory, that makes it faster. If this is true, can temp tables be indexed?? Would that make a difference??

Posting Permissions

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