I am attempting so speed up some queries using temporary tables within a script which creates the table, populates it from here and there, and opens a cursor on it. I would prefer the whole operation to be in memory.

This leads to several questions.

I thought I would create an in-memory DB to hold the temporary table(s). Does the in-memory db catalog entry persist over a server restart? If so, do the entries for tables, columns, triggers, procedures etc persist?

Given a main DB x and an in-memory db q, and a script s initialized with db x, is it possible to create a table in q from s? I'm getting complaints that "create temporary table q.t (...) " is illegal. There is no "USE q" statement that I can find.

I'm getting the impression I need some form of superscript to run a script associated with q to create the tables, then run a script associated with x to populate and query them. Much more difficult to do with no Pascal intervention at all.

I appreciate I can achieve almost the same effect by wrapping the create/populate steps in a transaction with commit no flush, but I think the explicit in memory DB is purer.

Also, how to delete a temporary table when the cursor has returned eof to the client? Again, all within a script.

Gregory


>I thought I would create an in-memory DB to hold the temporary table(s). Does the in-memory db catalog entry persist over a server restart? If so, do the entries for tables, columns, triggers, procedures etc persist?

No only the existence of the database is retained. The database list is stored in the configuration file (EDBConfig.EDBCfg on a standard install) the catalog file (EDBDatabase.EDBCat) is held in memory so is lost when the application closes.

>Given a main DB x and an in-memory db q, and a script s initialized with db x, is it possible to create a table in q from s? I'm getting complaints that "create temporary table q.t (...) " is illegal. There is no "USE q" statement that I can find.

There is a USE q statement for use in SQL/PSM scripts. Can you post the script you're using? I have a suspicion you're not talking ElevateDB scripts.

>I'm getting the impression I need some form of superscript to run a script associated with q to create the tables, then run a script associated with x to populate and query them. Much more difficult to do with no Pascal intervention at all.

This is the script Tim gave me yonks which does some of what you want
------------------------------------------------------------------------------------------------------------------------------------------------------
SCRIPT (IN SQLStatement VARCHAR, IN TableName VARCHAR, IN IdxSet VARCHAR)
BEGIN     
DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;
DECLARE ResultCursor SENSITIVE CURSOR FOR ResultStmt;

PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?';
OPEN InfoCursor USING TableName;

IF (ROWCOUNT(InfoCursor) > 0) THEN
 EXECUTE IMMEDIATE 'DROP TABLE "'+TableName+'"';
END IF;

CLOSE InfoCursor;

PREPARE ResultStmt FROM 'CREATE TABLE "'+TableName+'" AS '+SQLStatement+' WITH DATA';
EXECUTE ResultStmt;

IF IdxSet IS NOT NULL THEN
 PREPARE ResultStmt FROM 'CREATE INDEX Idx ON "'+TableName+'"('+IdxSet+')';
 EXECUTE  ResultStmt;
END IF;
END
------------------------------------------------------------------------------------------------------------------------------------------------------

An important point is that the Script's DatabaseName is set to the in-memory database

>Also, how to delete a temporary table when the cursor has returned eof to the client? Again, all within a script.

This depends - if all of the action takes place in the script then simply DROP the table at the end of the script, might need to be EXECUTE IMMEDIATE 'DROP .....'

Roy

Gregory,

<< I am attempting so speed up some queries using temporary tables within a script which creates the table, populates it from here and there, and opens a cursor on it. I would prefer the whole operation to be in memory. >>

If you want to email me the query execution plans, I can give you further information on how they could be further optimized.  Typically, breaking down queries into smaller bits can provide better performance in some cases, but this is normally due to the original query not being optimal in the first place.

<< I thought I would create an in-memory DB to hold the temporary table(s). Does the in-memory db catalog
entry persist over a server restart? If so, do the entries for tables, columns, triggers, procedures etc persist? >>

No, the in-memory database does not persist past a server restart.  However, please note that in-memory databases are *shared* just like normal databases, so in-memory databases are probably *not* what you want.  Rather, what you want is a temporary table (CREATE TEMPORARY TABLE ….).

<< Given a main DB x and an in-memory db q, and a script s initialized with db x, is it possible to create a table in q from s? I'm getting complaints that "create temporary table q.t (...) " is illegal. There is no "USE q" statement that I can find. >>

You cannot use database correlation names with DDL statements in EDB.  At some point there's going to be DDL transactions in EDB, and I'm trying to avoid getting into a situation where DDL transactions can deadlock.

<< I'm getting the impression I need some form of superscript to run a script associated with q to create the tables, then run a script associated with x to populate and query them. Much more difficult to do with no Pascal intervention at all. >>

You can do everything in the same script, or put some of the more common steps directly into the database as a function/procedure that can be called from any other scripts.  However, you cannot call a script from another script in EDB.

<< Also, how to delete a temporary table when the cursor has returned eof to the client? Again, all within a script.
>>

You should use something like this in the script:

EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE...';
BEGIN


FINALLY
  EXECUTE IMMEDIATE 'DROP TABLE....';
END;

Tim Young
Elevate Software
www.elevatesoft.com

Roy Lambert wrote:

Gregory


>I thought I would create an in-memory DB to hold the temporary table(s). Does the in-memory db catalog entry persist over a server restart? If so, do the entries for tables, columns, triggers, procedures etc persist?
---
No only the existence of the database is retained. The database list is stored in the configuration file (EDBConfig.EDBCfg on a standard install) the catalog file (EDBDatabase.EDBCat) is held in memory so is lost when the application closes.
---
Ok,  that makes sense; as I'm only using it for temporary tables, there is only one timing problem, and it's a small one: I need a script connected to the in-memory DB to create the temporary table, and another connected to the main DB to populate it. The server can go down between scripts. So in the second script I will have to ensure the  temp tables exist and rewind to step 1 if they don't. No danger of getting a stale version as the table itself gets lost if the dbms goes down.

Gregory

>>I thought I would create an in-memory DB to hold the temporary table(s). Does the in-memory db catalog entry persist over a server restart? If so, do the entries for tables, columns, triggers, procedures etc persist?
>---
>No only the existence of the database is retained. The database list is stored in the configuration file (EDBConfig.EDBCfg on a standard install) the catalog file (EDBDatabase.EDBCat) is held in memory so is lost when the application closes.
>---
>Ok, that makes sense; as I'm only using it for temporary tables, there is only one timing problem, and it's a small one: I need a script connected to the in-memory DB to create the temporary table, and another connected to the main DB to populate it. The server can go down between scripts. So in the second script I will have to ensure the temp tables exist and rewind to step 1 if they don't. No danger of getting a stale version as the table itself gets lost if the dbms goes down.


No, you just need one script. Point the script component at the in-memory database and qualify the tables used to populate with the database. Here's a sample. The TEDBScript is pointed at my in-memory database (Memory) and the database with the data to use is passed in as a parameter:

SCRIPT(IN tblname VARCHAR,IN ProjectID INTEGER,IN UserID VARCHAR,IN dbName VARCHAR,IN Mode VARCHAR, IN CallID INTEGER, IN PName VARCHAR, IN StageSQL VARCHAR, OUT DebugInf VARCHAR)
BEGIN

DECLARE Creator VARCHAR;
DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;
DECLARE ResultCursor SENSITIVE CURSOR FOR ResultStmt;

IF Mode <> 'Refresh' THEN
 PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?';
 OPEN InfoCursor USING tblName;
 IF (ROWCOUNT(InfoCursor) > 0) THEN
  EXECUTE IMMEDIATE 'DROP TABLE "'+tblName+'"';
 END IF;
 CLOSE InfoCursor;
END IF;

SET STATUS MESSAGE TO 'Extracting project ['+PName+'] from Calls table';

SET Creator = 'SELECT ';
SET Creator = Creator + '_CallID, '
+'_fkContacts, '
+'_fkCompanies, '
+'_fkSites, '
+'_fkStaff, '
+'(SELECT MAX(_Date) FROM "' + dbName + '".CallStats WHERE CallStats._fkContacts = PCT._fkContacts) AS _LastDialed, '
+'_LastResult, '
+'_Stage, '
+StageSQL + ', '
+'_HideAlarm, '
+'_AlarmSet,'
+'_HideSet,'
+'_CallBack,'
+'_Headhunted,'
+'RAND (RANGE 999999) AS _Random, '
+'_DateAdded, '
+'_Done, '
+'_IsCandidate, '
+'IF(_Secretary IS NOT NULL,''PA: ''+_Secretary,NULL) AS _Secretary,'
+'J._UnlistedCompany, '
+'J._JobTitle, '
+'C._Name AS _ListedCompany, '
+'IF(M._MktDesc IS NOT NULL OR O._Description IS NOT NULL'
+' THEN CAST(RCF(IF(M._MktDesc IS NOT NULL THEN ''(''+M._MktDesc+'')'' ELSE ''''),O._Description,'' '') AS VARCHAR(110))'
+' ELSE CAST(RCF(SC._ID+'': ''+SC._Description,SG._ID+'': ''+SG._Description,'' - '') AS VARCHAR(110))) AS _Arena, '
+'COALESCE(J._MainPhone, Site._Switchboard) AS _MainPhone, '
+'COALESCE(W._Forename,COALESCE(W._Title,''''))+'' ''+COALESCE(W._Surname,'''') AS _FullName, '
+'RCF(W._Forename,W._Surname,'', '') AS _ReverseName, '
+'RCF(RCF(W._Surname,W._Forename,'' ''),W._Title,'' '') AS _TFS, '
+'W._Title, '
+'W._Forename, '
+'W._Surname, '
+'W._HomePhone, '
+'W._Mobile, '
+'W._HomeEMail, '
+'W._Status, '
+'IFNULL(W._LatestCV THEN FALSE ELSE TRUE) AS _HasCV, '
+'J._EMail, '
+'J._fkJobCodes, '
+'IFNULL(J._Left THEN TRUE ELSE FALSE) AS _StillEmployed, '
+'J._Left,'
+'COALESCE(C._Name,J._UnlistedCompany) AS _CompanyName, '
+'Site._Town, '
+'Site._County, '
+'Site._Country, '
+'Site._Address1, '
+'Site._Address2, '
+'CAST(RCF(RCF(RCF(RCF(Site._Country,Site._County,'', ''),Site._Town,'', ''),Site._Address2,'', ''),Site._Address1,'', '') AS VARCHAR(185)) AS _Location, '
+'C._fkMarkets, '
+'C._fkOrgType, '
+'Site._Switchboard, '
+'S._Band AS _Staff, '
+'T._Band AS _Turnover, '
+'CAST(RCF(IF(S._Staff IS NOT NULL THEN ''Staff ''+S._Staff ELSE ''Staff Unknown''),IF(T._Turnover IS NOT NULL THEN ''T/O ''+T._Turnover ELSE ''T/O unknown''),'': '') AS VARCHAR(70)) AS _Size, '
+'C._fkSICGroup, '
+'C._fkSICCode, '
+'C._Status AS _CompanyStatus, '
+'C._Website, '
+'IFNULL(P._Name THEN '''' ELSE ''Parent: ''+ P._Name) AS _Parent ';
SET Creator = Creator +'FROM "'+dbName+'".Calls PCT ';
SET Creator = Creator
+'LEFT OUTER JOIN "'+dbName+'".Companies C ON PCT._fkCompanies = C._ID '
+'LEFT OUTER JOIN "'+dbName+'".Contacts W ON PCT._fkContacts = W._ID '
+'LEFT OUTER JOIN "'+dbName+'".Career J ON PCT._fkCareer = J._ID '
+'LEFT OUTER JOIN "'+dbName+'".SandT S ON C._fkSandT_Staffing = S._Band '
+'LEFT OUTER JOIN "'+dbName+'".SandT T ON C._fkSandT_Turnover = T._Band '
+'LEFT OUTER JOIN "'+dbName+'".Markets M ON C._fkMarkets = M._ID '
+'LEFT OUTER JOIN "'+dbName+'".OrgType O ON C._fkOrgType = O._ID '
+'LEFT OUTER JOIN "'+dbName+'".SICGroup SG ON C._fkSICGroup = SG._ID '
+'LEFT OUTER JOIN "'+dbName+'".SICCode SC ON C._fkSICCode = SC._ID '
+'LEFT OUTER JOIN "'+dbName+'".Companies P ON C._fkCompanies_Parent = P._ID '
+'LEFT OUTER JOIN "'+dbName+'".Sites Site ON  _fkCompanies = Site._fkCompanies AND _fkSites = Site._SiteID '
+'WHERE ';

IF Mode = 'Callbacks' THEN
SET Creator = Creator + ' _CallBack';
ELSEIF Mode = 'Alarms' THEN
SET Creator = Creator + ' _fkStaff = '''+ UserID + ''' AND _AlarmSet';
ELSEIF Mode = 'Unmanaged' THEN
SET Creator = Creator + ' _HideAlarm IS NOT NULL AND (NOT _HideSet) AND ((NOT _AlarmSet) OR (_fkStaff IS NULL))';
ELSEIF Mode = 'Headhunts' THEN
SET Creator = Creator + '_Headhunted';
ELSEIF Mode = 'Project' THEN
SET Creator = Creator + '_fkProjects = '+ CAST(ProjectID AS VARCHAR)+' '
/*
+  'AND '
+  '(_HideSet = FALSE OR _HideAlarm IS NULL OR CAST(_HideAlarm AS DATE) <= CURRENT_DATE) '
*/
+  'AND '
+  '(C._Name IS NOT NULL OR W._Surname IS NOT NULL) ';
ELSEIF (Mode = 'Refresh') OR (Mode = 'Singleton') THEN
SET Creator = Creator + ' _CallID = ' + CAST(CallID AS VARCHAR);
END IF;

SET DebugInf = Creator;

IF Mode <> 'Refresh' THEN
EXECUTE IMMEDIATE 'CREATE TABLE "'+ tblName +'" AS '+ Creator + ' WITH DATA';

SET STATUS MESSAGE TO 'Creating indices for project ['+PName+']';

IF (Mode = 'Callbacks') OR (Mode = 'Singleton') THEN
 EXECUTE IMMEDIATE 'CREATE INDEX  "Contact" ON "'+tblName+'" (_Surname,_Forename)';
ELSEIF (Mode = 'Alarms') or (Mode = 'Unmanaged') THEN
 EXECUTE IMMEDIATE 'CREATE INDEX  "Alarm" ON "'+tblName+'" (_HideAlarm)';
ELSEIF Mode = 'Headhunts' THEN
 EXECUTE IMMEDIATE 'CREATE INDEX  "Contact" ON "'+tblName+'" (_Surname,_Forename)';
 EXECUTE IMMEDIATE 'CREATE INDEX  "Company" ON "'+tblName+'" (_CompanyName)';
ELSEIF Mode = 'Project' THEN
/*
 EXECUTE IMMEDIATE 'CREATE INDEX  "Random" ON "'+tblName+'" (_Random)';
 EXECUTE IMMEDIATE 'CREATE INDEX  "Contact" ON "'+tblName+'" (_Surname,_Forename)';
 EXECUTE IMMEDIATE 'CREATE INDEX  "Company" ON "'+tblName+'" (_CompanyName)';
 EXECUTE IMMEDIATE 'CREATE INDEX  "ContactCode" ON "'+tblName+'" (_fkContacts)';
 EXECUTE IMMEDIATE 'CREATE INDEX  "CompanyCode" ON "'+tblName+'" (_fkCompanies)';
 EXECUTE IMMEDIATE 'CREATE INDEX  "Done" ON "'+tblName+'" (_Done)';
 EXECUTE IMMEDIATE 'CREATE INDEX  "Status" ON "'+tblName+'" (_Status)';
 EXECUTE IMMEDIATE 'CREATE INDEX  "Stage" ON "'+tblName+'" (_Stage)';
 EXECUTE IMMEDIATE 'CREATE INDEX  "On-Contact" ON "'+tblName+'" (_DateAdded,_Surname,_Forename)';
 EXECUTE IMMEDIATE 'CREATE INDEX  "On-Company" ON "'+tblName+'" (_DateAdded,_CompanyName)';
 EXECUTE IMMEDIATE 'CREATE INDEX  "RecentFirst" ON "'+tblName+'" (_LastDialed DESC)';
 EXECUTE IMMEDIATE 'CREATE INDEX  "OldFirst" ON "'+tblName+'" (_LastDialed)';
*/
END IF;
ELSE
EXECUTE IMMEDIATE 'DELETE FROM "'+tblName+'" WHERE _CallID = ' + CAST(CallID AS VARCHAR);
EXECUTE IMMEDIATE 'INSERT INTO "'+tblName+'" '+ Creator;
END IF;

END



Roy