Infrastructure Map Server Developer

Reply
Active Member
o019909
Posts: 8
Registered: ‎11-13-2012
Message 1 of 8 (385 Views)

Runtime copy to SDF - slow

385 Views, 7 Replies
09-19-2013 03:33 AM

Procedure in main page copies objects in SQL Server to SDF file

 

read in SQL - 10 seconds

write to SDF - 3 minute

read\write - 233781 objects

 

Why so slowly?

 

 

 

Active Member
o019909
Posts: 8
Registered: ‎11-13-2012
Message 2 of 8 (384 Views)

Re: Runtime copy to SDF - slow

09-19-2013 03:37 AM in reply to: o019909

Copy in SQL Server to virtual (runtime) Features (SDF shema)

 

Read in SQL - 6 seconds

Write to Features - 1 minutes

 

Why so slowly?

Valued Mentor
jackie.ng
Posts: 273
Registered: ‎10-06-2008
Message 3 of 8 (339 Views)

Re: Runtime copy to SDF - slow

10-01-2013 11:03 PM in reply to: o019909

The MapGuide Web API is not geared for bulk copy scenarios. If you want speed, you should go closer to the metal and build your process around FDO itself.

 

- Jackie

Active Member
o019909
Posts: 8
Registered: ‎11-13-2012
Message 4 of 8 (324 Views)

Re: Runtime copy to SDF - slow

10-07-2013 01:53 AM in reply to: jackie.ng

hmmm
I present a copying code to the file....
but a copying code in a virutalny source..... hmmmmm

Active Member
o019909
Posts: 8
Registered: ‎11-13-2012
Message 5 of 8 (285 Views)

Re: Runtime copy to SDF - slow

10-10-2013 06:00 AM in reply to: o019909

hm...  I started FDO in my WEB APP!!!

 

if to write down every line - that very much and very slowly

_______________________________________________________________________________________

try

        {

            IProviderRegistry mProvider = FeatureAccessManager.GetProviderRegistry();

IConnectionManager mManager = FeatureAccessManager.GetConnectionManager();

            IConnection mConnection = FeatureAccessManager.GetConnectionManager().CreateConnection(

"OSGeo.SDF.3.8");

 

            mConnection.ConnectionString =

@"File=C:\Program Files\OSGeo\MapGuide\Server\SDF_Data\ELEMENTmednogorsk.sdf";

            OSGeo.FDO.Connections.ConnectionState state = mConnection.Open();

            IInsert insertCmd = (IInsert)mConnection.CreateCommand(OSGeo.FDO.Commands.CommandType.CommandType_Insert);

            insertCmd.SetFeatureClassName(

"SHP_Schema:ELEMENT");

           

foreach(DataRow dr in tablo.Rows)

            {

                insertCmd.PropertyValues.Add(

new PropertyValue("FeatId", new Int64Value((Int64)dr[0])));

               

                GeometryValue gm = (GeometryValue) dr[1];

                insertCmd.PropertyValues.Add(

new PropertyValue("Geometry", gm));

                insertCmd.PropertyValues.Add(

new PropertyValue("ID_CKZ", new Int32Value((Int32)dr[2])));

                insertCmd.PropertyValues.Add(

new PropertyValue("ID_obsl", new Int64Value((Int64)dr[3])));

               

Int64 mT = (Int64)dr[4];

                insertCmd.PropertyValues.Add(

new PropertyValue("ID_TYPE", new Int64Value(mT)));

                insertCmd.PropertyValues.Add(

new PropertyValue("Zapoln", new BooleanValue((Boolean)dr[5])));

               

if (mT == 1)

                {

                    insertCmd.PropertyValues.Add(

new PropertyValue("Preasure", new DoubleValue((Double) dr[6])));

                }

               

using (IFeatureReader Freader = insertCmd.Execute())

                {

                    Freader.Close();

                    col_insert_obj++;

                }

            }

            mConnection.Close();

            mConnection.Dispose();

            mManager.Dispose();

            mProvider.Dispose();

        }

       

catch(System.Runtime.InteropServices.SEHException a)

        {

            Response.Write(a.Message);

        }

_____________________________________________________________________________________

 

if to write down everything at once - a error

______________________________________________________________________________________

IConnectionManager mManager = FeatureAccessManager.GetConnectionManager();

        IConnection mConnection = FeatureAccessManager.GetConnectionManager().CreateConnection(

"OSGeo.SDF.3.8");

        mConnection.ConnectionString =

@"File=C:\Program Files\OSGeo\MapGuide\Server\SDF_Data\ELEMENTmednogorsk.sdf";

        OSGeo.FDO.Connections.ConnectionState state = mConnection.Open();

 

 

        IInsert insertCmd = (IInsert)mConnection.CreateCommand(OSGeo.FDO.Commands.CommandType.CommandType_Insert);

        insertCmd.SetFeatureClassName(

"SHP_Schema:ELEMENT");

        insertCmd.PropertyValues.Add(

new PropertyValue("FeatId", new OSGeo.FDO.Expression.Parameter("PFeatId")));

        insertCmd.PropertyValues.Add(

new PropertyValue("Geometry", new OSGeo.FDO.Expression.Parameter("PGeometry")));

        insertCmd.PropertyValues.Add(

new PropertyValue("ID_CKZ", new OSGeo.FDO.Expression.Parameter("PID_CKZ")));

        insertCmd.PropertyValues.Add(

new PropertyValue("ID_obsl", new OSGeo.FDO.Expression.Parameter("PID_obsl")));

        insertCmd.PropertyValues.Add(

new PropertyValue("ID_TYPE", new OSGeo.FDO.Expression.Parameter("PID_TYPE")));

        insertCmd.PropertyValues.Add(

new PropertyValue("Zapoln", new OSGeo.FDO.Expression.Parameter("PZapoln")));

        insertCmd.PropertyValues.Add(

new PropertyValue("Preasure", new OSGeo.FDO.Expression.Parameter("PPreasure")));

 

 

       

String myConnectionString = sDataSource + ";" + Session["InitialCatalog"].ToString() + ";" + sPresistSecurityInfo + ";" + sUser + ";" + sPassword;

       

String filter_type = " (dbo.ELEMENT.ID_TYPE=1) or (dbo.ELEMENT.ID_TYPE=2) or (dbo.ELEMENT.ID_TYPE=3) or (dbo.ELEMENT.ID_TYPE=4) or  (dbo.ELEMENT.ID_TYPE=5) or (dbo.ELEMENT.ID_TYPE=6) or (dbo.ELEMENT.ID_TYPE=7) or (dbo.ELEMENT.ID_TYPE=8) or (dbo.ELEMENT.ID_TYPE=17) ";

 

       

//String mySelectQuery = "SELECT  FeatId, Geometry, ID_obsl, ID_TYPE, Zapoln, ID_CKZ FROM ELEMENT WHERE (" + filter_type + ") and (" + filter_obsl + ")";

 

       

String mySelectQuery = "SELECT   dbo.ELEMENT.FeatId, dbo.ELEMENT.Geometry, dbo.ELEMENT.ID_obsl, dbo.ELEMENT.ID_TYPE, dbo.ELEMENT.Zapoln, dbo.ELEMENT.ID_CKZ, "

        +

"              dbo.GASLINE.Pressure "

        +

"FROM         dbo.GASLINE RIGHT OUTER JOIN "

        +

"              dbo.GASLINESOSTAV ON dbo.GASLINE.ID_Gazline = dbo.GASLINESOSTAV.ID_Gasline FULL OUTER JOIN "

        +

"              dbo.ELEMENT ON dbo.GASLINESOSTAV.FeatID = dbo.ELEMENT.FeatId"

        +

" WHERE (" + filter_type + ") and (" + filter_obsl + ")";

 

 

        SqlConnection myConnection =

new SqlConnection(myConnectionString);

        SqlCommand myCommand =

new SqlCommand(mySelectQuery, myConnection);

        myCommand.CommandType = System.Data.CommandType.Text;

        myCommand.Connection.Open();

        SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

       

while (myReader.Read())

        {

            col_read_obj++;

            ParameterValueCollection Pprops =

new ParameterValueCollection();

           

Int64 mF = myReader.GetInt64(0);

           

// add to FDOCOllection

            Pprops.Add(

new ParameterValue("PFeatId", new Int64Value(mF)));

           

// ********************

           

// add to FDOCOllection

            GeometryValue gm = (GeometryValue)Expression.Parse(

"GEOMFROMTEXT('" + myReader.GetValue(1) + "')");

            Pprops.Add(

new ParameterValue("PGeometry", gm));

           

// ********************

 

           

//Response.Write(myReader.GetValue(1).ToString() + "<br>");

           

//Response.Write("<br> - " + agfReaderDB.Write(mGEOMDB).ToString() + " - <br>");

 

           

if (!myReader.IsDBNull(5))

            {

                Pprops.Add(

new ParameterValue("PID_CKZ", new Int32Value(myReader.GetInt32(5))));

            }

           

else

            {

                Pprops.Add(

new ParameterValue("PID_CKZ", new Int32Value(0)));

            }

            Pprops.Add(

new ParameterValue("PID_obsl", new Int64Value(myReader.GetInt64(2))));

           

Int64 mT = myReader.GetInt64(3);

            Pprops.Add(

new ParameterValue("PID_TYPE", new Int64Value(myReader.GetInt64(3))));

           

if (!myReader.IsDBNull(4))

            {

                Pprops.Add(

new ParameterValue("PZapoln", new BooleanValue(myReader.GetBoolean(4))));

            }

           

else

            {

                Pprops.Add(

new ParameterValue("PZapoln", new BooleanValue(false)));

            }

           

if (mT == 1)

            {

               

if (!myReader.IsDBNull(6))

                {

                   

//props.Add(new MgInt64Property("Preasure", readPreasureGasline(mF)));

                   

Double mP = (Double)myReader.GetDecimal(6);

                    Pprops.Add(

new ParameterValue("PPreasure", new DoubleValue(mP)));

                }

               

else

                {

                    Pprops.Add(

new ParameterValue("PPreasure", new DoubleValue(0)));

                }

            }

            insertCmd.BatchParameterValues.Add(Pprops);

        }

        myReader.Close();

        StringEndDBDateTime =

DateTime.Now.ToString(" hh:mm:ss");

       

using (IFeatureReader Freader = insertCmd.Execute())

        {

            Freader.Close();

            Freader.Dispose();

        }

        insertCmd.Dispose();

        mConnection.Close();

        mConnection.Dispose();

______________________________________________________________________________________

ERROR:  Object reference not set to an instance of an object

 

Active Member
o019909
Posts: 8
Registered: ‎11-13-2012
Message 6 of 8 (283 Views)

Re: Runtime copy to SDF - slow

10-10-2013 06:02 AM in reply to: o019909

ERORR in line code:  insertCmd.BatchParameterValues.Add(Pprops);

Active Member
o019909
Posts: 8
Registered: ‎11-13-2012
Message 7 of 8 (267 Views)

Re: Runtime copy to SDF - slow

10-13-2013 11:32 PM in reply to: o019909

insertCms.BatchParameterValues returned NULL ................ FDO 3.8 not support batch insert to SDF !?!?!?!?!?

Active Member
o019909
Posts: 8
Registered: ‎11-13-2012
Message 8 of 8 (258 Views)

Re: Runtime copy to SDF - slow

10-14-2013 02:26 AM in reply to: o019909

hmmm....
in while cicle im post insertCmd.PropertyValue.Clear(); - copying its very very quickly

You are not logged in.

Log into access your profile, ask and answer questions, share ideas and more. Haven't signed up yet? Register

Announcements
Are you familiar with the Autodesk Expert Elites? The Expert Elite program is made up of customers that help other customers by sharing knowledge and exemplifying an engaging style of collaboration. To learn more, please visit our Expert Elite website.

Need installation help?

Start with some of our most frequented solutions to get help installing your software.

Ask the Community