Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

We have a PowerBuilder Client Server Application using SA11. We have a number of cases where there are user reports where the User can select a large number of items which need to form part of query join in the report. In some cases, literally hundreds of products might be included.

In order to facilitate this type of reporting we want to be able to take these individually selected items and insert them into a temporary table to be used as part of the join for the report.

The SA documentation seems a bit thin on use of temporary tables and our various experiences do not seem to correspond to the documentation as to behavior.

Ideally we would like a tablename (like #prodselect) that can be shared by multiple queries but each user would only see their own records. Some of the documentation alludes to this behavior being available but when we try each use see''s all the entries.

We realize that we could dynamically create uniquely named tables for each user report instance, but this creates various problems with the datawindows and queries.

Is there a worktable that each user can create and destroy (with the same name) that does not interfere with other users who might need to use a work table of the same name?

Any best practices and references to more detail on using temporary tables for this kind of requirement would be appreciated.

asked 29 Jun '11, 21:15

Glenn%20Barber's gravatar image

Glenn Barber
accept rate: 8%

HEY EVERYBODY! ...let's start voting up questions, so folks like Glenn can get some points's not that hard, just click on the up-thumb :)

(30 Jun '11, 14:53) Breck Carter

I would usually tend to base complex queries on stored procedures and would then use local temporary tables within in the procedure to "collect data". That works with the DECLARE LOCAL TEMPORARY TABLE statement - recommended with the NOT TRANSACTIONAL clause.

These temporary tables are connection-specific. When declared in stored procedures, they do only exist during the block/compound statement they're declared in. In such cases, these temporary table are not visible for users at all.

When you are looking for permanently accessibe temporary tables, use the CREATE GLOBAL TEMPORARY TABLE statement. Unless you use the SHARE BY ALL clause, each connection can only access its own data. You would create such a table once (just like a base table), and each user can make use of it. Note when using them, it's important to use the appropriate setting of the ON COMMIT clause - just make sure the data you report logic adds are still there when they are selected: By default, the rows of a temporary table are deleted on COMMIT, so an intermediate COMMIT might empty the temporary table for that connection. (Yep, been there, too.)

permanent link

answered 30 Jun '11, 04:11

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

From the SA Docs

Tables created by preceding the table name in a CREATE TABLE statement with a pound sign (#) are declared temporary tables, which are available only in the current connection. See DECLARE LOCAL TEMPORARY TABLE statement.

It seams that you did something wrong with your tests. I don't know Powerbuilder so a quick test with 2 iSql Connections will prove the documentation.

In Connection 1

create table #Test( t int );
insert into #test values (1);
select * from #Test;

In Connection 2

create table #Test( t int );
insert into #test values (2);
select * from #Test;

The result is that each select only shows one row.


permanent link

answered 30 Jun '11, 07:51

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
accept rate: 17%

Volker said it, but he did not SHOUT it: CREATE GLOBAL TEMPORARY TABLE T ( columns ) NOT TRANSACTIONAL

You CREATE it once, and the schema is global and permanent, and it behaves exactly like a regular permanent base table EXCEPT the data is not global or persistent. To each connection, the table is empty when the connection starts. The connection can only SELECT FROM T and UPDATE T and DELETE T what it INSERTs INTO t itself. Other connections cannot see that connection's data. When the connection ends, its data goes away.

It is a wonderful invention, exactly what you are asking for: "can be shared by multiple queries but each user would only see their own records".

If you come from the Transact SQL world, think of it as a CREATE TABLE with @@SPID as part of the primary key, and all the necessary code to purge rows matching on @@SPID when the connection starts and end, and an extra predicate in every WHERE clause to select rows matching on @@SPID... which is exactly what folks have to do when they use ASE ("those poor people!" - Galaxy Quest).

permanent link

answered 30 Jun '11, 14:51

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

Hi Breck - we used the Syntax as above but did not use the NOT TRANSACTIONAL. The table name was preceeded with a # which we believe would have asserted some of the requred behaviors.

I have been dragged back into other issues, but will try this syntax when I revist.

One question we had was whether this behavior is conditional on how the database is being run. Developers often run a local instance rather than running as a server process. The still connect separately with two different logins - both members of a single group which controls access to the application tables by means of a Grant.

(30 Jun '11, 15:30) Glenn Barber
Replies hidden

FWIW, CREATE TABLE #MyTable is not the same as CREATE GLOBAL TEMPORARY TABLE MyTable - the former builds a local temporary table with ON COMMIT PRESERVE ROWS.

(If you have used CREATE GLOBAL TEMPORARY TABLE #MyTable, ignore this comment.)

(01 Jul '11, 03:10) Volker Barth

IMO there are so many variations on temporary tables that I have lost track... what does CREATE TABLE #MyTable even do? ( that was a rhetorical question, I don't want to know :)

(01 Jul '11, 03:55) Breck Carter

The personal and network servers are 99.999% the same, and for this stuff they are 100% the same.

(01 Jul '11, 03:57) Breck Carter

Breck, have a look at chapter 1.15 of your book - it contains a very thorough overview of the many types of temporary tables:)

Though it does not document SHARE BY ALL - possibly it wasn't there in 9.x (the 10.0 docs don't tell)...

(01 Jul '11, 06:36) Volker Barth

FWIW we did use CREATE GLOBAL TEMPORARY TABLE #MyTable... SO there was some other problem...

(01 Jul '11, 13:10) Glenn Barber
showing 2 of 6 show all flat view

Another attempt to improve the performance of complex joins is to materialize the result set (or parts of that).

That's done with materialized views.

permanent link

answered 30 Jun '11, 04:14

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

Hi Volker

We implemented Materialized views for our Accounts Receivable system early in the features release in SA. We did some good testing prior to implementation but shortly after we put this into production with 50+ users the system started locking users out. We had to pull the feature. Later it was identified by Sybase as a problem and we heard that it was corrected - but we have yet to convince the customer to try it again. We will probably give it a try again when we get go ahead to upgrade to 12.

(30 Jun '11, 15:23) Glenn Barber

Thanks everyone for your responses:

We tried to follow the documentation which was pretty much as described above. We appreciate the pointers.

We tried to use it much as described in Thomas Duemesnil's 2 connection example above - but both queries saw the same data.

I will have to back track to see how we got off the rails.

permanent link

answered 30 Jun '11, 14:15

Glenn%20Barber's gravatar image

Glenn Barber
accept rate: 8%

Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 29 Jun '11, 21:15

question was seen: 3,683 times

last updated: 01 Jul '11, 13:10