August 9, 2012 at 12:16 am
I'm trying to use a temp table and I'm not entirely clear on how to do what I want. From the reading I've been doing, it sounds like it's not possible, but maybe I'm just not understanding it correctly.
I have a set of stored procedures that I would like to have operate on a temp table for each user (connection). My idea is that one initial procedure would create a table, then subsequent ones (various ones, called in no special order by the user) would then operate on the table until something is achieved. In this case, an initial select that would pick a user's set of records out of a main table into the temp, and subsequent refinements would whittle away at this table until it was down to a size that could be easily scanned by the user. The selection criteria (and so which procedures would be called) can vary quite a lot, depending on what the user is trying to find.
But as I understand it, a local temp table vanishes as soon as the stored procedure that created it terminates, whereas a global temp table is visible to everyone. My test results are consistent with this. I don't like the hassle of potential naming conflicts with the global tables, nor do I want them staying around forever - there is no guarantee that the application will terminate gracefully and so clean up after itself. But I do need them to live longer than just through the cycle of one procedure. Is there no way to create a "MY" temp table, visible always and only to the user that created it, but that self-destructs when the user disconnects?
Or is there a better way to approach this whole concept?
August 9, 2012 at 1:16 am
What about creating temp table not inside stored procedure.
create proc proc1
as
select * from #t1
go
For example, opening connection, creating temp table, executing procedure
create table #t1(a int);
insert #t1 values(1);
exec proc1
You may also cosider creating one parent procedure, and creating temp table there. In this case, the reference to that table will be available for the child procs called inside a parent one.
here is example
create proc proc1
as
--creating table in parent proc
create table #t1(a int);
insert #t1 values(0);
--executing child procs
exec sub_proc1
exec sub_proc2
--quering results
select * from #t1
go
create proc sub_proc1
as
insert #t1 values(1);
go
create proc sub_proc2
as
insert #t1 values(2);
go
exec proc1
go
drop proc sub_proc1
drop proc sub_proc2
drop proc proc1
August 9, 2012 at 1:34 am
SomewhereSomehow (8/9/2012)
What about creating temp table not inside stored procedure.
Unfortunately, I don't have all the actions available at once. I need to create the table and return a count to the user. The user then selects an action that invokes another stored procedure, with which I want to continue operating on that temp table (paring it down) and again returning a count. This cycle can repeat numerous times, each time calling a different procedure, or the same one with different parameters, but all operating on that initially created temp table. I may want to destroy it and start again, or simply let it expire when the user disconnects, but I can't do it all in one call, even a chained call.
August 9, 2012 at 2:49 am
And what about creating temp table outside of the SP, just with command create table directly?
Anyway, I'd like to note that this approach maybe not so good.
Think, what will happen if connection suddenly brokes? The user will loose all the modifications!
Also, it not scales well if there will be a lot of users. Because it means that there will be a lot of open sql connections - which is no good for resourse consumption.
As an alternative advice.
Maybe it worth to revise selected approach, and to make a king of abstraction like user session, and create only one table for all users distinguishing them by UserSessionID?
August 9, 2012 at 2:51 am
Sounds like an interesting problem!
I might be oversimplifying this, but is the purpose of each step just to refine the selection criteria (i.e. your WHERE clause)?
If so, would it be possible to build your SQL statement within the app through the iterations?
You'll be hitting the original table at each step, but I can't think of a good way to do this purely from the SQL side.
August 9, 2012 at 3:24 am
Gazareth (8/9/2012)
Sounds like an interesting problem!I might be oversimplifying this, but is the purpose of each step just to refine the selection criteria (i.e. your WHERE clause)?
If so, would it be possible to build your SQL statement within the app through the iterations?
You'll be hitting the original table at each step, but I can't think of a good way to do this purely from the SQL side.
Yes, essentially I want to repeatedly modify my WHERE clause. I was trying to keep from scanning the entire original table at each step. It's not huge (cca 100,000 records), but some of the conditions can be pretty convoluted, or use leading wildcards, to the point where SS gives up and does a table scan. If I could slowly pick away at an ever-shrinking temp table, it should go considerably faster, especially since the first step would be selecting out only the records that 'belong' to a particular user, already bring the total data down by roughly a factor of ten, just in the initial creation of the temp table.
August 9, 2012 at 3:31 am
SomewhereSomehow (8/9/2012)
And what about creating temp table outside of the SP, just with command create table directly?
You mean create a permanent table that would stay in the database? I could, but how would I distinguish between various tables and their users? And I'd prefer not to have a permanent table floating around, when the whole notion is to use something temporary, that will vanish when it's no longer needed.
Think, what will happen if connection suddenly brokes? The user will loose all the modifications!
Not a problem, it's only used for searching. Rebuilding the search criteria is a fairly trivial task, and if the app crashes, the user would have to do that anyway.
Maybe it worth to revise selected approach, and to make a king of abstraction like user session, and create only one table for all users distinguishing them by UserSessionID?
Again, that would mean stuff piling up in this table, with no reliable mechanism for purging session data when someone disconnects. And since my goal here is to reduce the amount of data SS has to shovel through, piling it all into one table rather defeats the purpose. In that case, it would be simpler to leave the data in the original table and just repeatedly query it with an ever-more-complicated WHERE clause.
August 9, 2012 at 3:41 am
Gazareth (8/9/2012)
Sounds like an interesting problem!
It is, but also somewhat frustrating. It seems odd to me that there isn't an automatic session-specific, private temp table mechanism. It doesn't seem like I'm doing anything that unusual. Maybe what I will have to do is name the temp table to something which includes the session ID, so that other stored procedures will know what to call it and just live with the fact that other users could theoretically impact it. I'm the only developer, so I don't have to worry about other programmers accidentally stepping on my toes. And since it's purely search criteria, security isn't all that big a deal. The located records may subsequently be tagged for modification, but presumably the user verifies that he has selected only the records of interest before doing that. And if he does not, there isn't much I can do about it anyway.
August 9, 2012 at 4:08 am
pdanes (8/9/2012)
SomewhereSomehow (8/9/2012)
And what about creating temp table outside of the SP, just with command create table directly?You mean create a permanent table that would stay in the database? I could, but how would I distinguish between various tables and their users? And I'd prefer not to have a permanent table floating around, when the whole notion is to use something temporary, that will vanish when it's no longer needed.
No, if you create a local temp table outside a proc, it isn't destroyed until the creating session disconnects (as it doesn't go out of scope).
So this table could be queried by successive procs.
You'd need to maintain a connection to the db, but could be the way to go.
pdanes (8/9/2012)
It seems odd to me that there isn't an automatic session-specific, private temp table mechanism.
There is, local temp tables 😀
The problem here is keeping in the correct scope!
August 9, 2012 at 4:19 am
Gazareth (8/9/2012)
No, if you create a local temp table outside a proc, it isn't destroyed until the creating session disconnects (as it doesn't go out of scope).So this table could be queried by successive procs.
You've lost me - how do I create a temp table on demand if not in a proc?
You'd need to maintain a connection to the db, but could be the way to go.
The app (An Access database) remains connected through an ODBC link until the user shuts it down (or it crashes, which I'm trying very hard to not let happen).
It seems odd to me that there isn't an automatic session-specific, private temp table mechanism.
There is, local temp tables 😀
The problem here is keeping in the correct scope!
But I've tried that. If I make a global one, everyone can access it. If I make a local one, it vanishes once the code that creates it terminates. I must be missing something in how to create it properly.
August 9, 2012 at 4:33 am
You'd need some way in Access to execute T-SQL (or equivalent) rather than calling a proc.
I don't use Access so I've no idea if it has that ability, sorry.
e.g. Create TABLE #Results (definition)
#Results will then be available for the duration of the connection or until you drop it yourself.
Procs will be able to work on it, and it'll be specific to that connection.
It's not a great way to do it, as SomewhereSomehow said - lots of open connections to SQL with (I guess) large working sets & tempDB will take a hammering. Don't think there's many options though.
August 9, 2012 at 5:35 am
Gazareth,
Yes, exactly what I meant, thx!
pdanes,
Now I understand what are you trying to do, and my idea with temp table (even it is technically possible, and even if you'll find the way to issue a direct t-sql command from access) seems not so good, as for me.
I'd rather prefered to focus on constructing a dynamic sql for search query, and achiving a best performance, for the major or most popular combinations of search conditions. For example you may write a proc that depending on the input params, will run sub procedures for the popular search conditions and construct a dynamic sql for uncommon. Or maybe build dynamic always, as you like.
August 9, 2012 at 5:54 am
Gazareth (8/9/2012)
You'd need some way in Access to execute T-SQL (or equivalent) rather than calling a proc.I don't use Access so I've no idea if it has that ability, sorry.
e.g. Create TABLE #Results (definition)
#Results will then be available for the duration of the connection or until you drop it yourself.
Procs will be able to work on it, and it'll be specific to that connection.
Oh, okay. Yes, I think I can do that with Access, via what's called a pass-through query. I've always used them to execute SPs, never occurred to me to simply send a command like that. Although, now that I think about it, a couple of them do directly execute T-SQL statements like DELETE or INSERT. Never occurred to me to create and leave a temp table that way, but it seems like it should work. I'll give it a try and post back.
It's not a great way to do it, as SomewhereSomehow said - lots of open connections to SQL with (I guess) large working sets & tempDB will take a hammering. Don't think there's many options though.
Not that many users and very few simultaneously - one or two is the norm, and only 16 in the entire department, very few of which would be likely to be working in the DB at once. Contention just isn't an issue for me, but straight-line performance sometimes is, so that's where I'm focusing my efforts.
August 9, 2012 at 6:07 am
SomewhereSomehow (8/9/2012)
Now I understand what are you trying to do, and my idea with temp table (even it is technically possible, and even if you'll find the way to issue a direct t-sql command from access) seems not so good, as for me.I'd rather prefered to focus on constructing a dynamic sql for search query, and achiving a best performance, for the major or most popular combinations of search conditions. For example you may write a proc that depending on the input params, will run sub procedures for the popular search conditions and construct a dynamic sql for uncommon. Or maybe build dynamic always, as you like.
I do have fixed procedures for the common searches, tuned, indexed and all that. They work quite well, but now I need to handle the cases where someone is not sure if what they're looking for exists or how it looks. Sometimes they don't even know themselves just what they're looking for, so the search parameters can get rather strange. And often just knowing how many of something they found is a good clue towards how to direct subsequent searches. I can't tune such queries, because I have no idea how they will look, so I am trying to create a system that will give acceptable performance under such circumstances. Creating a temp table with intermediate results for further searches seems like a reasonable idea - I've done similar things in the past with other sorts of software.
Dynamic SQL is a possibility, but I don't like the security risk and even if I handle that, in most cases it's still going to mean a full table scan, with multiple joined tables. If I can do that ONCE and park the much smaller result set somewhere, it seems like a better way to go.
August 9, 2012 at 6:34 am
a couple of other possibilities;
create lots of global temp tables, with a naming convention like #temp_51 (dynamic SQL = 'CREATE TABLE #tmp_' + CONVERT(varchar,@@spid) )
then in your procs, you'd have to decide which table based on the spid. i bet that would turn everything into dynamic SQL just to determine the right table.
another crappy idea:
what about going ahead and using either a permenant or a global temp table, but adding an extra column with some identifier to the user or session, ie the value of @@spid, or login name, or something?
that's a bit of a kludge, as you'd need to filter the results of the temp table on spid=@@spid or something.
you could stick something in each users CONTEXTINFO() so you have a session/unique identifier, and use that for filtering.
DECLARE @var VARCHAR(64)
DECLARE @varb VARBINARY(128)
--create a unique identifier for my session:
SET @var = NEWID()
SET @varb = CAST(@var AS VARBINARY(128))
SET CONTEXT_INFO @varb
SELECT @var,@varb
SELECT CAST(context_info AS VARCHAR(64)) AS RESULTS FROM master.dbo.sysprocesses
WHERE spid = @@spid
Lowell
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply