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?

asked 17 Oct '12, 10:47

TonyV's gravatar image

TonyV
1.2k333967
accept rate: 75%

edited 17 Oct '12, 11:57

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.)

(17 Oct '12, 12:39) Volker Barth

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.

(17 Oct '12, 15:31) TonyV

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.

permanent link

answered 18 Oct '12, 08:57

Ian%20McHardy's gravatar image

Ian McHardy
3.1k23353
accept rate: 39%

edited 18 Oct '12, 08:59

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:

Connection pooling.

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
showing 4 of 6 show all flat view

This has now been identified as bug CR #724900 - fixed in 12.0.1.3821.

permanent link

answered 25 Mar '13, 14:05

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174
accept rate: 24%

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:

A connection is cached if it is disconnected and the maximum number of connections specified by the CPOOL connection parameter has not been reached. The connection is reinitialized, and the cached connection remains connected to the database server even though the application has disconnected it. The cleanup and reinitialization of a connection includes the following activities:

  • Rolling back all outstanding transactions.
  • Dropping temporary tables, temporary functions, and variables. <- sic
  • Resetting connection options and connection counters.
  • Decrementing and incrementing the database server connection counts. You are not informed that there are active connections when a database server with cached connections shuts down.
  • Executing all defined disconnect and connect events.
  • Executing the login_procedure database option and verifying the login policy.
  • Resetting the connection ID.

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:

If you are using a product or API that supports connection pooling, then the connection pooling of the product or API supersedes SQL Anywhere connection pooling. Both types of connection pooling can be active at the same time.

The behavior of connection pooling in your product or the API may be significantly different than SQL Anywhere connection pooling. If the behavior of connection pooling in your product or API is inappropriate for an application, SQL Anywhere connection pooling can be used and may improve the performance of some applications.

permanent link

answered 17 Oct '12, 11:04

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 32%

edited 17 Oct '12, 11:09

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:

  1. If dropping the temp table (which is connection-specific) is not allowed, then obviously the connection is not "alone" or not closed correctly.

  2. As to the TRUNCATE TABLE: This has a "slow" and a "fast" version - the engine tries to use the latter as documented here:

With TRUNCATE TABLE, if all the following criteria are satisfied, a fast form of table truncation is executed:

  • There are no foreign keys either to or from the table.
  • The TRUNCATE TABLE statement is not executed within a trigger.
  • The TRUNCATE TABLE statement is not executed within an atomic statement.

If a fast truncation is carried out, individual DELETEs are not recorded in the transaction log, and a COMMIT is carried out before and after the operation. Fast truncation cannot be used within snapshot transactions.

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×131
×21
×13

question asked: 17 Oct '12, 10:47

question was seen: 4,102 times

last updated: 25 Mar '13, 14:05