dcsimg
Results 1 to 10 of 10
  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Temporary Tables in SQL Server

    I have a request to write the code which uses data from SQL Server multiple tables. Data is displayed on the pretty complex screen in controls such as grids, text boxes located on multiple dynamic tab control pages. The user needs to modify data, use that modified data for MS Word document population via automation. Document creation is done on the different screen. When the user has modified data on one Tab on the first screen and wants to switch to another tab, the changes should not be lost. But it should not be saved back into database. All changes the user makes must be kept somewhere. I decided to use temporary tables as copy of the original ones.
    Connection to the database is established when the application starts and closes when the application stops.
    I run SP vrom my VB project which checks the existance of temp tables and delete them if they exist then create the new ones. This code for one of the temp tables:
    Code:
    	IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'#TempSopaProjectType') AND type = (N'U'))
    	DROP TABLE #TempSopaProjectType
    
    	SELECT SopaProjectTypeID, JobID, ProjectTypeID, Assigned, RowDeleted, SpecificationQualityID, QuotePrice, Qty, Multiplier, Freight, StartupCost, Markup, TotalListPrice, 
    						  TotalCost, SellPrice, BasisOfDesign, Specified, Note, DescriptionRtf, AddAlt, HideQuotePrice, DrawingAddendumID, HideProject
    	INTO #TempSopaProjectType
    	FROM dbo.SopaProjectType
    	WHERE (JobID = @JobID) AND (RowDeleted IS NULL OR RowDeleted = 0)
    When the user switches to another screen the temp tables are not available.
    If I use ## in order to create global temp tables then the code above doesn't work saying that the table already exists.
    If I'll find the way to delete ## then I'm not sure that when one user created those temp tables and another user is trying to create same tables for her own needs the tables will be created.
    I read on internet this explanation :
    "There are two types of temporary tables: local and global. Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server. Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server."
    By some reason although as I said the connection exist for all application life the tables with # are not visible.

    Thank you

  2. #2

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: Temporary Tables in SQL Server

    I think I understand why #Temp tables disappear. It is because they are created inside of Stored Procedure. I will try to move the code into VB. Maybe it will keep them alive.

    Thank you

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: Temporary Tables in SQL Server

    I found the way to keep temp tables in DB by using this code:
    Code:
        strTempSQL = "if object_id('tempdb..#TempSopaProjectType') is not null DROP TABLE #TempSopaProjectType " & _
        "SELECT SopaProjectTypeID, JobID, ProjectTypeID, Assigned, RowDeleted, SpecificationQualityID, QuotePrice, Qty, Multiplier, Freight, StartupCost, Markup, TotalListPrice, " & _
        "TotalCost , SellPrice, BasisOfDesign, specified, Note, DescriptionRtf, AddAlt, HideQuotePrice, DrawingAddendumID, HideProject " & _
        "INTO #TempSopaProjectType " & _
        "From dbo.SopaProjectType " & _
        "WHERE (JobID = " & lngCurrentJobID & ") AND (RowDeleted IS NULL OR RowDeleted = 0)"
        
        Set rsTemp = New ADODB.Recordset
    
        rsTemp.Open strTempSQL, objConn, adOpenForwardOnly, adLockReadOnly
    I can access temp tables from other screens. It is what I needed.

  4. #4
    Fanatic Member wqweto's Avatar
    Join Date
    May 2011
    Posts
    566

    Re: Temporary Tables in SQL Server

    Quote Originally Posted by chapran View Post
    . When the user has modified data on one Tab on the first screen and wants to switch to another tab, the changes should not be lost. But it should not be saved back into database. All changes the user makes must be kept somewhere. I decided to use temporary tables as copy of the original ones.
    Weird decision and poor choice IMO. Just use client-side recordsets. Modify these on the first screen and then pass the recordsets (along with modifications) to the second form.

    Using temp tables the way you are describing is a shaky proposition -- e.g. if your db connection is intermittenly dropped you loose them.

    Accessing these temp tables from stored procedures will be another source of weird behavior when delayed name resolution kicks in.

    cheers,
    </wqw>

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,709

    Re: Temporary Tables in SQL Server

    ^+1 to al of that.

    Certainly don't use global temp tables if this is a multi user system. all your users are going to be accessing the same tables so they'll keep reading overwriting each others data - that won't work at all.

    Local would work but your creating a big read and write to the DB every time they switch between screens/tabs/whatever. That's going to become a performance bottleneck. Also, what you're doing in that last snippet is dropping the temp table if it exists and then repopulating from the "Real" tables - meaning you're not remembering anything anyway.

    You should be using some client side mechanism for this.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  6. #6
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    17,217

    Re: Temporary Tables in SQL Server

    I use global temp tables when uncompiled and local when compiled. The reason I use global is that I can place breakpoints in code and then go to SQL server and run queries on the global temp table to more easily verify what's in there and test out alternate queries.

    Edited: Typically, the temp tables are created/used as intermediary work tables; containing results at different stages of complex dynamic queries.
    Last edited by LaVolpe; May 18th, 2018 at 07:10 AM.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Apr 2014
    Posts
    249

    Re: Temporary Tables in SQL Server

    Quote Originally Posted by wqweto View Post
    Weird decision and poor choice IMO. Just use client-side recordsets. Modify these on the first screen and then pass the recordsets (along with modifications) to the second form.

    Using temp tables the way you are describing is a shaky proposition -- e.g. if your db connection is intermittenly dropped you loose them.

    Accessing these temp tables from stored procedures will be another source of weird behavior when delayed name resolution kicks in.

    cheers,
    </wqw>
    That was my first scenario, but tabs are created dynamically, so I have to have an array of recordsets. Public arrays are not allowed. Arrays of forms properies? I selected the way I described. I have no request to keep that data permanently, if connection is dropped (happens no more than ones a year) it is not a big deal.
    Thank you.

  8. #8
    Fanatic Member wqweto's Avatar
    Join Date
    May 2011
    Posts
    566

    Re: Temporary Tables in SQL Server

    So you add a separate temp table for each tab? Can you consider a single (non-temp) table with TabId column or similar so called "relational" approach?

    Then please consider also using permanent tables w/ data partioned/clustered by ProcessId column which gets populated from current connection's @@spid. This so called @@spid approach can be wrapped in views that automatically populate ProcessId column on insert and return only data w/ ProcessId=@@spid predicate

    Temp tables are designed to be short-lived. 99% of their legitimate use-cases are for use inside stored procedures and as you've figured out such temp tables cannot survive SPs scope by design.

    YMMV

    cheers,
    </wqw>

  9. #9
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,709

    Re: Temporary Tables in SQL Server

    I'm bound to say, using temp table (or any form of database table) is a bizarre and bad choice for this. Either you need to persist the data or you don't. If you need to persist it then you use database tables and you use permanent ones, not temporary. If you don't need to persist it (which you've said you don't) then you keep the data client side using recordsets, collections etc. (recordsets seems like an obvious choice in your case). Unless you need to persist data you shouldn't be interacting with the database at all. You're creating unnecessary network traffic and burden on the server - your two primary bottlenecks.

    As I understand it, you need your data to exist for the duration of a session, so hold it in recordsets. You've said that public arrays are not allowed. 1. Why does it need to be public? Is this just a code separation consideration or is there some other reason? 2. Public arrays absolutely are allowed.

    Can you consider a single (non-temp) table with TabId column or similar so called "relational" approach?
    I like the relational suggestion but still dislike any of this existing in DB tables, perm or temp. I like the Spids idea but they get reused all the time so the chances of data collisions in a multi user system would be high. Pessimistic concurrency would be a must which would turn this table into a deadlock factory.

    I think we're missing something about the requirement though.
    Last edited by FunkyDexter; May 18th, 2018 at 06:04 AM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  10. #10
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,608

    Re: Temporary Tables in SQL Server

    Yeah, this sounds like a classic case of misuse of temp tables. Temp tables should be used for processing on the server, once that's done, they should be dropped & released. Temp tables do NOT come without a price. They get physically created in the tempdb database. So the more temp tables you create, the more that database gets inflated, taking physical hard drive space on the server. Depending on the db settings, this could be bad.

    Once the data is served up to the client, the temp table should just be irrelevant and dropped.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width


×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.