Changing tempdb "in-query"

Question

Good day,

Is it possible to change the tempdb my current session is using? I have a very heavy query that is meant for HD usage. Ideally, I'd like the query to be ran using a tempdb we have specifically for such heavy things. (Main issue is the query creates a very large temp table)

I'd like something along the lines of:

use tempdb <tempdbname>

<query>

use tempdb <normaltempdb>

If this is at all possible, even if by other means, please let me know. Right now, the only way I know of to do this is to bind a user to a different tempdb, and then have HD login using that user, instead of the normal user.

Thanks in advance, ziv.

Answer 1

if your main concern is impact to tempdb and other users, you could consider creating multiple default tempdbs of the same size and structure. Add these to the default group and sessions are assigned to a tempdb on connection thus lessening the risk of one large query impacting the whole dataserver

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00841.1502/html/phys_tune/phys_tune213.htm

You could also consider the use of a login trigger for specific logins and check the program name which is connecting to decide upon which tempdb to use (e.g. Business Objects could go to a much larger DSS tempdb or similar).

There is no way to change your session tempdb in-flight that I'm aware of though as tempdb bindings are set on connection.

Answer 2

In Sybase ASE you cannot change your tempdb in-flight; your tempdb is automagically assigned at log in.

You have a few options:

1 (recommended) - have the DBA create a login specifically for this process and bind said login to the desired tempdb (eg, sp_tempdb 'bind', ...); have your process use this new login

2 (not recommended) - instead of creating #temp tables, create permanent tables with a 'desired_tempdb_name..' prefix; you'll likely piss off your DBA if you forget to manually drop said tables when you're done with it

3 (ok, if you've got the disk space) - as Rich has suggested, make sure all tempdb's are sized large enough to support your process

NOTE: If you're using Sybase's SQLAnywhere, IQ or Advantage RDBMSs... sorry, I don't know how temporary databases are assigned for these products.

Answer 3

It sounds like you do have at least one other tempdb created by the DBA. You can bind to this by application name as well as the login Id. Set the application name in your client session (depends on what the client is as to how you do this.) Use sp_tempdb (dba only) to bind that application name to the alternative tempdb, and your # table will be in that tempdb. Any session with that application name will use that tempdb.

tempdbs do not have to be the same size or structure and you can have separate log and data (a good idea,) with more log and less data depending on what you are doing.

markp mentions permanent tables in tempdbs, and says "not recommended". This can be a good technique though. You do need to be careful about how big they get and when they are dropped. You might not need or want to drop them straightaway, for example if you need to bcp from them and/or have them visible for Support purposes, but you do need to be clear about space usage, when to drop and how.