Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 9 of 9 total
Thread in-memory table memory usage by fielddefs
Sat, Nov 15 2008 1:29 PMPermanent Link

"Monte Etherton"
I have a table that is 88 columns wide, and 20K rows long (dbInvoices), I
want to make an in-memory copy of it for quicker lookups but only need 19 of
the fields-and those fields are the only fields I have defined in the memory
DBISAM table (dbMemInvLookup).

When I run dbInvoices.CopyTable to populate the memory table, the
dbMemInvLookup.fieldcount is 19, but the dbMemInvLookup.fieldDefs.count is
88.

Is the CopyTable method actually copying all 88 fields and values into
memory, even though I only need 19?

If yes, is there a way around this other than selecting the 19 fields into a
query and then copying that to memory?

Thanks,

Monte Etherton
using D7, DBISAM 4.27, XPPro



Sat, Nov 15 2008 4:03 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Monte,

<< Is the CopyTable method actually copying all 88 fields and values into
memory, even though I only need 19? >>

Yes, it makes a copy of the physical table.

<< If yes, is there a way around this other than selecting the 19 fields
into a query and then copying that to memory? >>

Just use this:

SELECT <Columns>
INTO "\Memory\MyTableCopy"
FROM MyTable

And then do whatever you want with the table in memory.

--
Tim Young
Elevate Software
www.elevatesoft.com

Wed, Nov 19 2008 10:42 AMPermanent Link

"Monte Etherton"
Thanks, Tim.

Since the copy is going through a query, I won't get indexes for the memory
table as I would with CopyTable, correct? If this is the case, is there a
way to copy the indexes after the table is created via SQL, or do I manually
recreate the indexes I need?

Any minor elaboration would be appreciated.

-Monte


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:5EE2A0F1-A444-48E7-A3C5-70733EC77913@news.elevatesoft.com...
> Monte,
>
> << Is the CopyTable method actually copying all 88 fields and values into
> memory, even though I only need 19? >>
>
> Yes, it makes a copy of the physical table.
>
> << If yes, is there a way around this other than selecting the 19 fields
> into a query and then copying that to memory? >>
>
> Just use this:
>
> SELECT <Columns>
> INTO "\Memory\MyTableCopy"
> FROM MyTable
>
> And then do whatever you want with the table in memory.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Wed, Nov 19 2008 11:17 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Monte


You'll have to manually create them - if you use SQL to do that you can set up a single script to do the transfer and add the indices so all you have to do is run one query and you're in business.

Roy Lambert [Team Elevate]
Wed, Nov 19 2008 2:17 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Monte,

<< Since the copy is going through a query, I won't get indexes for the
memory table as I would with CopyTable, correct? >>

Correct.  However, in some cases this can be an advantage, especially if you
only want a subset of the available indexes.

<< If this is the case, is there a way to copy the indexes after the table
is created via SQL, or do I manually recreate the indexes I need? >>

You need to manually recreate them.  However, as Roy indicated, you can do
so via a single SQL script, so it is something that you can execute all in
one shot:

SELECT <Columns>
INTO "\Memory\MyTableCopy"
FROM MyTable;

CREATE INDEX MyIndex ON "\Memory\MyTableCopy" (MyColumn);
CREATE INDEX MyIndex2 ON "\Memory\MyTableCopy" (MyColumn2);

--
Tim Young
Elevate Software
www.elevatesoft.com

Wed, Nov 19 2008 7:35 PMPermanent Link

"Monte Etherton"
Thanks to both of you. I am no expert on SQL syntax, it took quite a few
tries to get the query to do what I wanted...but I see incredible speed
increases and memory usage reduction with method! well worth the time spent.

I have one more question in regards to this lookup memory table creation:

If I (inner?) join a detail table to a master table (i.e. customers to
invoices where inv.CustID=cust.CustID) can I somehow import the customer's
names into the memory table *and* also create an index on the customer name?
Do I have to create a same-named field in the IDE memory table component
also?

Thanks for your help, sorry if these are elementary questions.  I am "learn
as you go"!

-Monte





"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:D1D5935C-5E4D-45DB-88FE-6D68C6E709D4@news.elevatesoft.com...
> Monte,
>
> << Since the copy is going through a query, I won't get indexes for the
> memory table as I would with CopyTable, correct? >>
>
> Correct.  However, in some cases this can be an advantage, especially if
> you only want a subset of the available indexes.
>
> << If this is the case, is there a way to copy the indexes after the table
> is created via SQL, or do I manually recreate the indexes I need? >>
>
> You need to manually recreate them.  However, as Roy indicated, you can do
> so via a single SQL script, so it is something that you can execute all in
> one shot:
>
> SELECT <Columns>
> INTO "\Memory\MyTableCopy"
> FROM MyTable;
>
> CREATE INDEX MyIndex ON "\Memory\MyTableCopy" (MyColumn);
> CREATE INDEX MyIndex2 ON "\Memory\MyTableCopy" (MyColumn2);
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Thu, Nov 20 2008 2:52 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Monte


If I understand what you're asking you can extend the simple SQL that Tim gave you to pull in any fields you want from other tables (providing a suitable JOIN can be formulated). You can then set an index on ANY field in the result set.

The way to test these things out is to play with SELECTs in DBSys. To give you an idea something like

SELECT I.InvNo, I.CustID, C.CustName INTO "Memory\temp"  M
FROM INVOICES I
JOIN Customers C ON C.CustID = I.CustID
WHERE......;

CREATE INDEX CustName ON "\Memory\temp" (CustName);

Totally untested obviously. The way I would approach it is to create a SELECT query in DBSys and get the extraction part right then add the INTO memorytable and indexing stuff in your app secure in the knowledge that you're getting what you want as a starter.


Roy Lambert [Team Elevate]
Thu, Nov 20 2008 8:36 AMPermanent Link

"Monte Etherton"
I had been doing this in code and got it working, but then wanted to do some
experimenting, and as you suggested, am using DBSYS for that.  I guess I
didn't realize DBSYS would allow you to create memory tables via SQL!
Cool...thanks.

Problem: I am getting a null result set when adding the CREATE INDEX line
that is commented out below (run as-is results are correct). The PLAN with
the CREATE INDEX is below that.  Can you see what I am doing wrong? I having
tried many various syntax versions on the CREATE INDEX statement with no
luck...probably every one except the one that will work!

SELECT Roma._Group, Roma._ID, Roma._CustID, Roma._VehcID, Roma._InsID,
Roma._JobMgr, Cust._FName, Cust._LName
INTO "\Memory\RomaLUm"
FROM Roma JOIN Cust ON (Roma._CustID=Cust._ID)
WHERE ((Roma._ID > 0) AND ((Roma._Group >= 1) AND (Roma._Group <= 6)));

/*CREATE INDEX mIndexCust ON "\Memory\RomaLUm" (_LName, _FName, _ID);*/

================================================================================
SQL statement (Executed with 4.25 Build 7)
================================================================================

SELECT Roma._Group, Roma._ID, Roma._CustID, Roma._VehcID, Roma._InsID,
Roma._JobMgr, Cust._FName, Cust._LName
INTO "\Memory\RomaLUm"
FROM Roma JOIN Cust ON (Roma._CustID=Cust._ID)
WHERE ((Roma._ID > 0) AND ((Roma._Group >= 1) AND (Roma._Group <= 6)))

Tables Involved
---------------

Roma (Roma) table opened shared, has 19963 rows
Cust (Cust) table opened shared, has 12525 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

WHERE Clause Execution
----------------------

The expression:

Roma._ID > 0 AND Roma._Group >= 1 AND Roma._Group <= 6

has been rewritten and is OPTIMIZED, covers 609 rows or index keys, costs
538799 bytes, and will be applied to the Roma table (Roma) before any joins

Join Ordering
-------------

The driver table is the Roma table (Roma)

The Roma table (Roma) is joined to the Cust table (Cust) with the INNER JOIN
expression:

Roma._CustID = Cust._ID

Optimizer will attempt to re-order the joins to a more optimal order
Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the
optimizer to leave the joins in their declared order

The joins are already in optimal order and cannot be optimized any further

Join Execution
--------------

Costs ARE NOT being taken into account when executing this join
Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force
the
optimizer to consider costs when optimizing this join

The expression:

Roma._CustID = Cust._ID

is OPTIMIZED

================================================================================
>>>>> 584 rows affected in 0.046 seconds
================================================================================

================================================================================
SQL statement (Executed with 4.25 Build 7)
================================================================================

CREATE INDEX "mIndexID" ON "\Memory\RomaLUm" ("_LName", "_FName", "_ID")

================================================================================
>>>>> 0 rows affected in 0 seconds
================================================================================






"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:B0D8410A-2094-448D-A552-89F680BE5696@news.elevatesoft.com...
> Monte
>
>
> If I understand what you're asking you can extend the simple SQL that Tim
> gave you to pull in any fields you want from other tables (providing a
> suitable JOIN can be formulated). You can then set an index on ANY field
> in the result set.
>
> The way to test these things out is to play with SELECTs in DBSys. To give
> you an idea something like
>
> SELECT I.InvNo, I.CustID, C.CustName INTO "Memory\temp" M
> FROM INVOICES I
> JOIN Customers C ON C.CustID = I.CustID
> WHERE......;
>
> CREATE INDEX CustName ON "\Memory\temp" (CustName);
>
> Totally untested obviously. The way I would approach it is to create a
> SELECT query in DBSys and get the extraction part right then add the INTO
> memorytable and indexing stuff in your app secure in the knowledge that
> you're getting what you want as a starter.
>
>
> Roy Lambert [Team Elevate]
>

Thu, Nov 20 2008 9:09 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Monte

Why do you think you're doing something wrong?

Two points:

1. remember this is not the primary index for the table (you'd have to use something like

ALTER TABLE IF EXISTS "Memory\RomaLUm"
PRIMARY KEY (_LName, _FName, _ID) COMPRESS NONE');

for that.

2. no rows are affected so you won't be told any are.

Here's another neat trick - open RomaLUm in DBSys and have a look at the indices - hint use the built in memory database and DO NOT close DBSys until after you've had a look otherwise the memory is wiped.


Roy Lambert [Team Elevate]

ps Nice to see someone else prefacing field (column) names with underscore.
Image