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?
Copy in SQL Server to virtual (runtime) Features (SDF shema)
Read in SQL - 6 seconds
Write to Features - 1 minutes
Why so slowly?
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
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
insertCms.BatchParameterValues returned NULL ................ FDO 3.8 not support batch insert to SDF !?!?!?!?!?