It is very slow to deploy the program on the server !!!, Deploy the program to the server

It is very slow to deploy the program on the server !!!, Deploy the program to the server

 

Conclusion:

1. EF queries are slower than ADO queries, and even reports timeout errors directly. The cause is unknown.

2. Using parameterized query in native ADO. Net is dozens of times slower than directly using SQL concatenation !!!

 

ADO. Net code Test

Public List <v_yjdateggjgModel> SelectList (int yjxzqid, int ncpid, DateTime start, DateTime end) {List <v_yjdateggjgModel> list = new List <v_yjdateggjgModel> ();
// The first SQL statement is used to query 1100 pieces of data in 36 seconds. // string SQL = "select yjxzqid, ncpid, rq, sjttjg, sjpfjg, sjlsjg, ycttjg, ycpfjg, yclsjg from v_yjdateggjg where yjxzqid = @ yjxzqid and ncpid = @ ncpid and (rq >=@ start and rq <= @ end )";
// The second SQL statement is used to query 1.5 data in 1100 seconds. string SQL = "select yjxzqid, ncpid, rq, sjttjg, sjpfjg, sjlsjg, ycttjg, ycpfjg, yclsjg from v_yjdateggjg where yjxzqid = "+ yjxzqid +" and ncpid = "+ ncpid +" and (rq> = '"+ start. toString ("yyyy-MM-dd") + "'and rq <='" + end. toString ("yyyy-MM-dd") + "')"; // SqlParameter [] sqlparms = new SqlParameter [] // {// new SqlParameter ("@ ncpid ", ncpid), // new SqlParameter ("@ yjx Zqid ", yjxzqid), // new SqlParameter (" @ start ", start), // new SqlParameter (" @ end ", end ),//}; using (SqlDataReader reader = SqlHelper. executeReader (SQL) {if (reader. hasRows) {while (reader. read () {v_yjdateggjgModel info = new v_yjdateggjgModel (); info. yjxzqid = (int) SqlHelper. fromDbNull (reader ["yjxzqid"]); info. ncpid = (int) SqlHelper. fromDbNull (reader ["ncpid"]); info. rq = (DateTime) SqlHelper. fro MDbNull (reader ["rq"]); info. sjttjg = (decimal ?) SqlHelper. FromDbNull (reader ["sjttjg"]); info. sjpfjg = (decimal ?) SqlHelper. FromDbNull (reader ["sjpfjg"]); info. sjlsjg = (decimal ?) SqlHelper. FromDbNull (reader ["sjlsjg"]); info. ycttjg = (decimal ?) SqlHelper. FromDbNull (reader ["ycttjg"]); info. ycpfjg = (decimal ?) SqlHelper. FromDbNull (reader ["ycpfjg"]); info. yclsjg = (decimal ?) SqlHelper. FromDbNull (reader ["yclsjg"]); list. Add (info) ;}} return list ;}

EF code (discarded)

// EF query method 1 // var data1 = db. v_yjdateggjg.Where (d => d. yjxzqid = yjxzqid & d. ncpid = ncpid & (d. rq> = start & d. rq <= end )). toList (); // EF query method 2 // string SQL = "select * from v_yjdateggjg where yjxzqid = @ yjxzqid and ncpid = @ ncpid and (rq >=@ start and rq <= @ end) "; // var sqlparms = new SqlParameter [] {// new SqlParameter (" @ ncpid ", ncpid), // new SqlParameter (" @ yjxzqid ", yjxzqid ), // new SqlParameter ("@ start", start), // new SqlParameter ("@ end", end), //}; // var data1 = db. database. sqlQuery <v_yjdateggjg> (SQL, sqlparms ). toList ();

　　

Internal database Test

// SQL parameterized query of 1100 data records for 3 seconds
Declare @ yjxzqid int = 9; declare @ ncpid int = 35; declare @ start datetime = '2017-5-1 '; declare @ end datetime = '2017-5-1'; select yjxzqid, ncpid, rq, sjttjg, sjpfjg, sjlsjg, ycttjg, ycpfjg, yclsjg from v_yjdateggjg where yjxzqid = @ yjxzqid and ncpid = @ ncpid and (rq >=@ start and rq <= @ end) // SQL concatenates 1100 select yjxzqid, ncpid, rq, sjttjg, sjpfjg, sjlsjg, ycttjg, ycpfjg, yclsjg from v_yjdateggjg where yjxzqid = 9 and ncpid = 35 and (rq> '2017-5-1 'and rq <'2017-5-1 ')

　　

SQL statement in the v_yjdateggjg View

   SELECT   dbo.v_yjdatejg.yjxzqid, dbo.datalocation.cjdd AS yjxzqname, dbo.v_yjdatejg.ncpid, dbo.products.ncpmc,                 dbo.v_yjdatejg.rq, dbo.v_yjdatealljg.ttjg AS sjttjg, dbo.v_yjdatealljg.pfjg AS sjpfjg, dbo.v_yjdatealljg.lsjg AS sjlsjg,                 dbo.v_yjdateycjg.ttjg AS ycttjg, dbo.v_yjdateycjg.pfjg AS ycpfjg, dbo.v_yjdateycjg.lsjg AS yclsjgFROM      dbo.v_yjdatejg INNER JOIN                dbo.datalocation ON dbo.v_yjdatejg.yjxzqid = dbo.datalocation.id INNER JOIN                dbo.products ON dbo.v_yjdatejg.ncpid = dbo.products.id LEFT OUTER JOIN                dbo.v_yjdateycjg ON dbo.v_yjdatejg.yjxzqid = dbo.v_yjdateycjg.yjxzqid AND                 dbo.v_yjdatejg.ncpid = dbo.v_yjdateycjg.ncpid AND dbo.v_yjdatejg.rq = dbo.v_yjdateycjg.ycrq LEFT OUTER JOIN                dbo.v_yjdatealljg ON dbo.v_yjdatejg.yjxzqid = dbo.v_yjdatealljg.yjxzqid AND                 dbo.v_yjdatejg.ncpid = dbo.v_yjdatealljg.ncpid AND dbo.v_yjdatejg.rq = dbo.v_yjdatealljg.cjrq

　

SqlHelper class

Public static class SqlHelper {private static readonly string conStr = ConfigurationManager. connectionStrings ["lyc2ConnString"]. connectionString; // insert delete update public static int ExecuteNonQuery (string SQL, params SqlParameter [] pms) {using (SqlConnection con = new SqlConnection (conStr )) {using (SqlCommand cmd = new SqlCommand (SQL, con) {if (pms! = Null) {cmd. parameters. addRange (pms);} con. open (); return cmd. executeNonQuery () ;}}// returns a single public static object ExecuteScalar (string SQL, params SqlParameter [] pms) {using (SqlConnection con = new SqlConnection (conStr )) {using (SqlCommand cmd = new SqlCommand (SQL, con) {if (pms! = Null) {cmd. parameters. addRange (pms);} con. open (); return cmd. executeScalar () ;}}// returns DataReader public static SqlDataReader ExecuteReader (string SQL, params SqlParameter [] pms) {SqlConnection con = new SqlConnection (conStr ); using (SqlCommand cmd = new SqlCommand (SQL, con) {if (pms! = Null) {cmd. parameters. addRange (pms);} // con. open (); try {if (con. state = ConnectionState. closed) {con. open ();} return cmd. executeReader (CommandBehavior. closeConnection);} catch {con. close (); con. dispose (); throw ;}}// query multiple public static DataTable ExecuteDataTable (string SQL, params SqlParameter [] pms) {DataTable dt = new DataTable (); using (SqlDataAdapter adapter = new SqlDataAdapter (SQL, conStr) {if (pms! = Null) {adapter. selectCommand. parameters. addRange (pms);} adapter. fill (dt);} return dt ;} /// <summary> /// convert DbNull to null // </summary> /// <param name = "obj"> </param> // <returns> </returns> public static object FromDbNull (object obj) {if (obj = DBNull. value) {return null;} else {return obj ;}} /// <summary> /// convert null to DbNull // </summary> /// <param name = "obj"> </param> // <returns> </returns> public static object ToDbNull (object obj) {if (obj = null) {return DBNull. value ;}else {return obj ;}}}}

 

The blog Park says: the Homepage cannot be published with few words and 150 words.

A: Is the Code not counted? It seems that the management of the blog garden has gradually become fixed, and it is more and more like writing the composition standards of middle school students.