|
The documentation states that a LOCAL TEMPORARY TABLE exists only while the connection is maintained. But if you've got connection pooling turned on, does the table cease to exist when I return the connection to the pool or only when the connection is actually removed from the pool and closed? EDIT: I have a process in my C# code that establishes a connection, creates a local temporary table, does some processing and then closes the connection, like this: using ( MyEntities context = new MyEntities() ) { if ( context.Connection.State != ConnectionState.Open ) { context.Connection.Open(); } DbTransaction dbTransaction = context.Connection.BeginTransaction(); SAConnection saConnection = ( (EntityConnection) context.Connection ).StoreConnection as SAConnection; SATransaction saTransaction = (SATransaction) dbTransaction.GetType() .InvokeMember( "StoreTransaction", BindingFlags.FlattenHierarchy | BindingFlags.NonPublic | BindingFlags.InvokeMethod | BindingFlags.Instance | BindingFlags.GetProperty | BindingFlags.NonPublic, null, dbTransaction, new object[ 0 ] ); try { context.ExecuteStoreCommand( LOADTABLEDDL ); // <--- Get error here } catch ( Exception ex ) { Log( "Could not create the ListDetailsLoad table: " + ex, EventTypeEnum.Error ); throw; } . . . } When this code runs, I get an error that reads "Cannot create table LOADTABLE as it already exists" or something like that at the line that has the "Get error here" comment. Here's the SQL being run by the ExecuteStoreCommand method: private const string LOADTABLEDDL = "CREATE LOCAL TEMPORARY TABLE \"DBA\".\"ListDetailsLoad\" ( " + "\"ListDetailId\" UNIQUEIDENTIFIER NOT NULL, " + "\"ListId\" UNIQUEIDENTIFIER NOT NULL, " + "\"CountryId\" VARCHAR(3) NULL, " + "\"LocaleCode\" VARCHAR(3) NULL, " + "\"Plate\" VARCHAR(50) NULL, " + "\"HashedPlate\" UNIQUEIDENTIFIER NULL, " + "\"AlarmClassId\" INTEGER NULL, " + "\"BeginDate\" DATETIMEOFFSET NULL, " + "\"EndDate\" DATETIMEOFFSET NULL, " + "\"ListPriorityId\" INTEGER NULL, " + "\"VehicleTypeId\" INTEGER NULL, " + "\"PlateClassId\" INTEGER NULL, " + "\"MakeId\" INTEGER NULL, " + "\"ModelId\" INTEGER NULL, " + "\"Year\" INTEGER NULL, " + "\"ColorId\" INTEGER NULL, " + "\"Notes\" VARCHAR(8000) NULL, " + "\"OfficerNotes\" VARCHAR(8000) NULL, " + "\"CreateDate\" DATETIMEOFFSET NOT NULL, " + "\"Subscriber\" UNIQUEIDENTIFIER NULL, " + "\"InstanceId\" UNIQUEIDENTIFIER NOT NULL, " + "\"Seq\" INTEGER NOT NULL DEFAULT AUTOINCREMENT, " + "PRIMARY KEY CLUSTERED ( \"Seq\" ASC ) " + ") NOT TRANSACTIONAL;"; So it looks like it's not actually discarding my temporary table. Do I have to close it manually before the using statement goes out of scope? |
|
I verified that if you are using SQLAnywhere's connection pooling available to all client interfaces (controlled by the CPOOL connection parameter), temporary tables are dropped when the connection is cached (when the application disconnects and the connection is put in the connection pool). The SQL Anywhere .NET Data Provider's connection pooling does not do as much work to make connection pooling transparent (i.e., there is little or no cleanup done on the connection at the point it is cached to the pool - likely just a commit or rollback). In particular, I'm pretty sure it doesn't drop temporary tables. By default the .NET Provider's connection pooling is enabled, and the generic CPOOL connection pooling is disabled. Depending on how the .NET Provider is implemented (I'm not sure about this), you may be able to disable the .NET provider's connection pooling, and rely on the CPOOL connection pooling by including the connection parameters "Max Pool Size=0;CPOOL=YES" to get only the CPOOL connection pooling (or you may not get any connection pooling at all). Alternately, you can make your application tolerant of temporary objects left over from pooled connections by, for example, just truncating rows in the temporary table if the CREATE LOCAL TEMPORARY TABLE fails. 1
So my answer was correct (i.e. the docs are correct) but doesn't apply as the .Net provider does its own pooling...? One might think that CREATE LOCAL TEMPORARY TABLE could get an "IF NOT EXISTS" clause for such cases - like CREATE TABLE has.
(18 Oct '12, 09:24)
Volker Barth
I didn't know that Entity Framework provided any connection pooling at all. Microsoft's implementation for SQL Server doesn't. It seems redundant to implement connection pooling in Entity Framework when the server has the facility. It's seems wrong to implement connection pooling and not drop temporary tables like the server's connection pool does when returning the connection to the pool.
(18 Oct '12, 10:27)
TonyV
Replies hidden
It's not Entity Framework that implements connection pooling, but SQL Anywhere ADO.Net Provider that implements it.
(18 Oct '12, 10:46)
Ian McHardy
FYI, I talked to the engineer responsible for the SQL Anywhere ADO.Net driver, and there is no way to disable the ADO.Net connection pooling and enable the CPOOL connection pooling. But disabling the ADO.Net connection pooling with "Max Pool Size=0" would fix your issue. We are also investigating if we can clean pooled connections so that when a pooled connection is reused, the connection behaves the same as if it was a band new connection (does the same cleaning as the connection pooling controled by CPOOL). What version of SQL Anywhere are you using?
(18 Oct '12, 11:32)
Ian McHardy
Replies hidden
2
FWIW, the .NET Data Provider pooling is documented as well, including the remark that is does disable the server-side (CPOOL) pooling, cf. this page: According to that, you might also disable provider-wise pooling by using "POOLING=FALSE;" in the DSN. - No, I haven't tested this.
(18 Oct '12, 12:12)
Volker Barth
We're using 12.0.1.3769
(18 Oct '12, 16:09)
TonyV
|
|
Yes, as otherwise pooling would not be as transparent for the client as it needs to be. Confine to the following doc page for 12.0.1:
CAVEAT: I should note that this relates to SQL Anywhere's own connection pooling. See the warning on the same doc page for other kinds of pooling:
Please see my edits to the question.
(17 Oct '12, 11:32)
TonyV
Replies hidden
I can't comment on .Net/EF connection pooling - that might be outside the SQL Anywhere pooling... Besides that, could you show your SQL statement to declare the temp table (as there are a bunch of variants, i.e. CREATE/DECLARE LOCAL TEMPORARY TABLE, SELECT INTO #table, SELECT INTO LOCAL TEMPORARY etc.) with subtle differences in scope/duration. That being said, I guess all kinds of temporary tables can be explicitly dropped.
(17 Oct '12, 11:39)
Volker Barth
I EF does not use any kind of connection pooling other than that provided by the database. Therefore, it only uses SQL Anywhere pooling. The fact that I'm seeing the table exists error is not good. The other issue is that I've tried to drop other temporary tables (but not this kind) in my C# code and that has lead to "Table in use" errors. If that happens with a LOCAL TEMPORARY TABLE, I have no choice but to go back to what I originally was using, a GLOBAL TEMPORARY TABLE and truncating it at the end of the operation. TRUNCATE, by the way, apparently doesn't just release the table pages but delete each row one by one, and therefire is a lot slower than a TRUNCATE TABLE on SQL server.
(17 Oct '12, 12:11)
TonyV
Just 2 remarks:
As you're using a NOT TRANSACTIONAL temp table without FKs and triggers, I think it should do a fast truncate. And now we're waiting for the SQL Anywhere engineers - the real experts - to head on:)
(17 Oct '12, 12:36)
Volker Barth
|
Just as a hint: AFAIK, storing the PK as the last column - behind "long" varchar data - is said to have bad performance implications. I'm not sure whether this is still correct with v12 but for older versions, it is highly recommended to store PKs "in front".
(IMHO, it also seems way more natural.)
That's at the end of the table because it used to be a global temporary table and the column was added using an ALTER TABLE statement. I just copied the SQL to build the table from Sybase Central and pasted it into my program. I'll move it to the top of the column list.