I have read several threads on this site regarding stored procedures and I have a question regarding the use of bulk collect.

I have several tables in my application that I read from the server and store locally in order to reduce overhead. These are tables that change rarely, such as Nation, State, County, Department, etc. The local copies can be updated by the user at will. Sometimes the fields that I store locally are a subset of the fields in the tables on the server.

My original code sent a SQL statement to the server, such as:

insert into table1 (code, description)
select servercode, serverdescription from servertable@remotedb

I then discovered bulk collect into and the forall statement and I moved the code to a stored procedure so that I could use that functionality:

CREATE OR REPLACE PACKAGE BODY "TABLEADAPTER" IS
    --Local record schema
    type LocalRecord is table of Table1%rowtype;

  PROCEDURE "REBUILDLOCALTABLE" IS
    LocalData LocalRecord;

    BEGIN -- executable part starts here
    
                                --Avoid exceptions with FK constraints
        Utility.DISABLECONSTRAINTS('Table1');
        
        --Clear local table
        delete from Table1;
        
        --Retrieve server data
        select 
            servertable_code,
            servertable_desc
        bulk collect into LocalData
        from servertable@remotedb;

        --Insert records into local table
        forall r in LocalData.first .. LocalData.last
            insert into Table1 values LocalData(r);

        Utility.ENABLECONSTRAINTS('Table1');

    END "REBUILDLOCALTABLE";

END "TABLEADAPTER";

This sped up execution dramatically for the larger tables.

Then I purchased LLBLGen Pro.

My question is this: Should I keep this code in a stored procedure or can this idea work as well in generated code? The issue, as I see it, with trying to do this in LLBL is that I'm working with two tables so I have to read the data from the server table (TypedList or TypedView, right?), transport it over to the local table (EntityCollection, right?), and then save the data from the EntityCollection using the adapter UpdateEntitiesDirectly() method.

Will this scenario be as efficient as the stored procedure method? Is there a way to move data from a TypedList or TypedView en masse to an EntityCollection or do I have to move each field of each row individually? Am I missing something?

Will this scenario be as efficient as the stored procedure method?

I can't tell, but it needs to be tested, you can try it out and tell us the result in your case

Is there a way to move data from a TypedList or TypedView en masse to an EntityCollection or do I have to move each field of each row individually? Am I missing something?

Yup, Projection. Please check the LLBLGen Pro manual: "Uaing the generated code -> SelfServicing/Adapter -> Fetching DataReaders and projections"

I've done some testing, but before I publish any results I want to make sure that I understood the correct procedure for peforming this with LLBLGen Pro. I currently have 3 methods for updating a local table:

1. Call the stored procedure.

2. EntityCollection a. Fetch data into an server table EntityCollection. b. Project the server table EntityCollection into a local table EntityCollection. c. Call SaveEntityCollection on the local table EntityCollection.

3. DataReader a. Fetch data from the server into a DataReader. b. Project the data into a local table EntityCollection. c. Call SaveEntityCollection on the local table EntityCollection.

The code is shown below.

Notes: 1. Globals.LocalDBAdapter is a DataAccessAdapter object in a static global class. 2. The "server table" is a local view that selects data from the server table using a dblink. The SQL for the view was copied from the SQL used in the stored procedure. I created an entity from the view. 3. The name of the local table is "HighSchool". 4. I am currently retrieving 37,170 records in my test.

        static public void RebuildLocalTable()
        {
            int commandTimeOut = Globals.LocalDBAdapter.CommandTimeOut;
            Globals.LocalDBAdapter.CommandTimeOut = 600;

            #region StoredProcedure method
            ActionProcedures.HighSchoolAdapterRebuildLocalTable(Globals.LocalDBAdapter);
            #endregion

            #region EntityCollection method
            // Retrieve records from Server
            EntityCollection<ServerHighSchoolEntity> ServerHighSchool =
                new EntityCollection<ServerHighSchoolEntity>(new ServerHighSchoolEntityFactory());
            Globals.LocalDBAdapter.FetchEntityCollection(ServerHighSchool, null);

            // Create projection properties
            List<IEntityPropertyProjector> propertyProjectors = new List<IEntityPropertyProjector>();
            propertyProjectors.Add(new EntityPropertyProjector(ServerHighSchoolFields.Id, "Id"));
            propertyProjectors.Add(new EntityPropertyProjector(ServerHighSchoolFields.Name, "Name"));
            propertyProjectors.Add(new EntityPropertyProjector(ServerHighSchoolFields.StreetLine1, "StreetLine1"));
            propertyProjectors.Add(new EntityPropertyProjector(ServerHighSchoolFields.StreetLine2, "StreetLine2"));
            propertyProjectors.Add(new EntityPropertyProjector(ServerHighSchoolFields.StreetLine3, "StreetLine3"));
            propertyProjectors.Add(new EntityPropertyProjector(ServerHighSchoolFields.City, "City"));
            propertyProjectors.Add(new EntityPropertyProjector(ServerHighSchoolFields.State, "State"));
            propertyProjectors.Add(new EntityPropertyProjector(ServerHighSchoolFields.County, "County"));
            propertyProjectors.Add(new EntityPropertyProjector(ServerHighSchoolFields.Zip, "Zip"));
            propertyProjectors.Add(new EntityPropertyProjector(ServerHighSchoolFields.Nation, "Nation"));

            // Create local collection
            EntityCollection<HighSchoolEntity> localHighSchool =
                new EntityCollection<HighSchoolEntity>(new HighSchoolEntityFactory());
            localHighSchool.DoNotPerformAddIfPresent = false;
            EntityView2<ServerHighSchoolEntity> ServerHighSchoolView = ServerHighSchool.DefaultView;

            // Project Server records onto local collection
            ServerHighSchoolView.CreateProjection(propertyProjectors, localHighSchool);

            // Disable constraints
            ActionProcedures.UtilityDisableConstraints("HighSchool", Globals.LocalDBAdapter);

            // Clear local table
            Globals.LocalDBAdapter.DeleteEntitiesDirectly("HighSchoolEntity", null);

            // Add new records
            Globals.LocalDBAdapter.SaveEntityCollection(localHighSchool);

            // Enable constraints
            ActionProcedures.UtilityEnableConstraints("HighSchool", Globals.LocalDBAdapter);
            #endregion

            #region DataReader method
            // Create local collection
            EntityCollection<HighSchoolEntity> localHighSchool2 =
                new EntityCollection<HighSchoolEntity>(new HighSchoolEntityFactory());
            localHighSchool2.DoNotPerformAddIfPresent = false;

            // Retrieve records from Server
            using (DataAccessAdapter adapter = new DataAccessAdapter(Globals.LocalDBAdapter.ConnectionString))
            {
                ResultsetFields fields = new ResultsetFields(10);
                fields.DefineField(ServerHighSchoolFields.Id, 0);
                fields.DefineField(ServerHighSchoolFields.Name, 1);
                fields.DefineField(ServerHighSchoolFields.StreetLine1, 2);
                fields.DefineField(ServerHighSchoolFields.StreetLine2, 3);
                fields.DefineField(ServerHighSchoolFields.StreetLine3, 4);
                fields.DefineField(ServerHighSchoolFields.City, 5);
                fields.DefineField(ServerHighSchoolFields.State, 6);
                fields.DefineField(ServerHighSchoolFields.County, 7);
                fields.DefineField(ServerHighSchoolFields.Zip, 8);
                fields.DefineField(ServerHighSchoolFields.Nation, 9);

                using (IDataReader reader =
                    adapter.FetchDataReader(fields, null, CommandBehavior.CloseConnection, 0, true))
                {
                    // Create projection properties
                    List<IDataValueProjector> valueProjectors = new List<IDataValueProjector>();
                    valueProjectors.Add(new DataValueProjector(HighSchoolFieldIndex.Id.ToString(), 0, typeof(string)));
                    valueProjectors.Add(new DataValueProjector(HighSchoolFieldIndex.Name.ToString(), 1, typeof(string)));
                    valueProjectors.Add(new DataValueProjector(HighSchoolFieldIndex.StreetLine1.ToString(), 2, typeof(string)));
                    valueProjectors.Add(new DataValueProjector(HighSchoolFieldIndex.StreetLine2.ToString(), 3, typeof(string)));
                    valueProjectors.Add(new DataValueProjector(HighSchoolFieldIndex.StreetLine3.ToString(), 4, typeof(string)));
                    valueProjectors.Add(new DataValueProjector(HighSchoolFieldIndex.City.ToString(), 5, typeof(string)));
                    valueProjectors.Add(new DataValueProjector(HighSchoolFieldIndex.State.ToString(), 6, typeof(string)));
                    valueProjectors.Add(new DataValueProjector(HighSchoolFieldIndex.County.ToString(), 7, typeof(string)));
                    valueProjectors.Add(new DataValueProjector(HighSchoolFieldIndex.Zip.ToString(), 8, typeof(string)));
                    valueProjectors.Add(new DataValueProjector(HighSchoolFieldIndex.Nation.ToString(), 9, typeof(string)));

                    // Project results onto local entity collection
                    DataProjectorToIEntityCollection2 projector = new DataProjectorToIEntityCollection2(localHighSchool2);
                    adapter.FetchProjection(valueProjectors, projector, reader);
                    reader.Close();
                }
            }

            // Disable constraints
            ActionProcedures.UtilityDisableConstraints("HighSchool", Globals.LocalDBAdapter);

            // Clear local table
            Globals.LocalDBAdapter.DeleteEntitiesDirectly("HighSchoolEntity", null);

            // Add new records
            Globals.LocalDBAdapter.SaveEntityCollection(localHighSchool2);

            // Enable constraints
            ActionProcedures.UtilityEnableConstraints("HighSchool", Globals.LocalDBAdapter);
            #endregion

            Globals.LocalDBAdapter.CommandTimeOut = commandTimeOut;
    }

Please verify whether I have used the LLBLGen methods correctly and let me know if you see anything that could be improved.

I currently run all 3 methods back-to-back in order to get timings for each. The code that outputs the timings to the Debug window has been removed to reduce clutter for this post.

Thanks.

Scott

In the second method (#region EntityCollection method) I see no point of projecting the entities from the ServerCollection to the LocalCollection, as you are using the DefaultView, hence you are not filtering the entities that you copy, and since you are copying the entire collection, Why don't you just use the ServerCollection as your local copy?

Also, projections are slower than normal entity fetches (a little bit).

If you're just copying data from one table to the other, it's often more efficient to do that directly inside the db with an INSERT INTO ... SELECT ... statement, because this doesn't have to transport the data out of the db to the client just for sending it back to the DB again.

Walaa wrote:

In the second method (#region EntityCollection method) I see no point of projecting the entities from the ServerCollection to the LocalCollection, as you are using the DefaultView, hence you are not filtering the entities that you copy, and since you are copying the entire collection, Why don't you just use the ServerCollection as your local copy?

My understanding was that, to save to the local table, the entites needed to be in a collection "attached" to the local table. The server collection is attached to the server table and would attempt to save back to the server, right? I'm putting a local copy of the data in a database on the user's machine to avoid frequent trips to the server for data that changes rarely.

Otis wrote:

Also, projections are slower than normal entity fetches (a little bit).

If you're just copying data from one table to the other, it's often more efficient to do that directly inside the db with an INSERT INTO ... SELECT ... statement, because this doesn't have to transport the data out of the db to the client just for sending it back to the DB again.

That's basically what the stored procedure is doing, although it's using bulk collect and forall. My original question was whether this logic should remain in a stored procedure or be ported to LLBLGen and it appears from what I'm hearing that I should leave the stored procedure alone. I'm moving data between two databases, one on the server and one on the user's machine, not between two tables in the same database.

Thanks for your help.