POSTGRE查询满

2020-12-02 06:14:14 180 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

SpringBoot下Postgre GeoJson数据查询处理 1. 一条 Sql 命令直接从 Postgre 数据库，查询出 GeoJson 格式数据 GeoJson数据一般存储在Postgre 数据库，参考其它blog查询出来对应的json数据。 SELECT row_to_json ( fc ) FROM ( SELECT 'FeatureCollection' AS type, array_to_json ( array_agg ( f ) ) 阅读详情

各位：
我发现POSTGRE数据库查询慢，比SQLSERVER多两倍，这是怎么回事？
我用的是C#，
新建数据库：
string connectionString = "Server=localhost;Port=5432;Username=postgres;Password=12345";
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
conn.Open();
string sql = "CREATE DATABASE " + Name + " WITH OWNER = postgres ENCODING='UTF8' LC_COLLATE = 'Chinese (Simplified)_China.936' LC_CTYPE = 'Chinese (Simplified)_China.936' TABLESPACE = pg_default CONNECTION LIMIT = -1;";
NpgsqlCommand objCommand = new NpgsqlCommand(sql, conn);
objCommand.ExecuteNonQuery();
conn.Close();
conn.Dispose();
新建表：
string connectionString = "Server=localhost;Port=5432;Username=postgres;Password=12345;Database=" + DataBaseName + ";";
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
conn.Open();
string sql = "CREATE TABLE " + TableName +
"( 时间 timestamp without time zone, TempratureA integer, PowerA integer,ACVoltageA integer,CoilVoltageA integer,DCCurrentA integer,HzA integer,DCVoltageA integer,StoveWaterFlowA integer,MachingWaterFlowA integer,CapVoltageA integer,INVCurrentA integer,TPreheatSA integer,TColdSA integer,RESA1 integer,RESA2 integer) WITH(OIDS = FALSE);";

NpgsqlCommand objCommand = new NpgsqlCommand(sql, conn);
objCommand.ExecuteNonQuery();

sql = "CREATE INDEX x" + TableName + " ON " + TableName + " USING btree(时间 ASC NULLS LAST);";
objCommand = new NpgsqlCommand(sql, conn);
objCommand.ExecuteNonQuery();

sql = "ALTER TABLE " + TableName + " CLUSTER ON x" + TableName + ";";
objCommand = new NpgsqlCommand(sql, conn);
objCommand.ExecuteNonQuery();

conn.Close();
查询：

string connectionString = "Server=localhost;Port=5432;Username=postgres;Password=12345;Database=" + DataBaseName + ";";
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
conn.Open();

DateTime StTime, EdTime;
StTime = DateTime.Parse(StartTime);
EdTime = DateTime.Parse(EndTime);

string sql = "SELECT * FROM " + TableName + " where 时间> '" + StTime + "' and 时间<'" + EdTime + "';";
NpgsqlCommand objCommand = new NpgsqlCommand(sql, conn);

da = new NpgsqlDataAdapter(objCommand);
ds = new DataSet();
da.Fill(ds, "ds");
objCommand.Parameters.Clear();

objCommand.Dispose();
ds.Dispose();

conn.Close();
conn.Dispose();
每次查询两万多条记录，为什么比较慢，是SQLSERVER的三倍。
大师们能帮忙看一下吗？

...全文