Infrastructure Map Server Forum
Welcome to Autodesk’s Infrastructure Map Server Forums. Share your knowledge, ask questions, and explore popular Infrastructure Map Server topics.
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Runtime copy to SDF - slow

7 REPLIES 7
Reply
Message 1 of 8
o019909
842 Views, 7 Replies

Runtime copy to SDF - slow

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?

 

 

 

7 REPLIES 7
Message 2 of 8
o019909
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?

Message 3 of 8
jackie.ng
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

Message 4 of 8
o019909
in reply to: jackie.ng

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

Message 5 of 8
o019909
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

 

Message 6 of 8
o019909
in reply to: o019909

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

Message 7 of 8
o019909
in reply to: o019909

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

Message 8 of 8
o019909
in reply to: o019909

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

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

Autodesk Design & Make Report