SOCI C++ 数据库访问库 示例代码及插入效率测试

1 #include "soci.h"
 2 //#include "sqlite3/soci-sqlite3.h"
 3 #include "mysql/soci-mysql.h"
 4 #include <vector>
 5 #include <string>
 6 #include <exception>
 7 #include <boost/date_time/posix_time/posix_time.hpp>
 8 
 9 using namespace soci;
10 using namespace std;
11 
12 
13 struct Person
14 {
15     int id;
16     std::string data1;
17     std::string data2;
18     std::string data3;
19     std::string data4;
20 };
21 
22 namespace soci
23 {
24 template<>
25 struct type_conversion<Person>
26 {
27     typedef values base_type;
28 
29     static void from_base(values const & v, indicator /* ind */, Person & p)
30     {
31 
32         p.data1 = v.get<std::string>("data1");
33         p.data2 = v.get<std::string>("data2");
34         p.data3 = v.get<std::string>("data3");
35         p.data4 = v.get<std::string>("data4");
36 
37     }
38 
39     static void to_base(const Person & p, values & v, indicator & ind)
40     {
41         v.set("id", p.id);
42         v.set("data1", p.data1);
43         v.set("data2", p.data2);
44         v.set("data3", p.data3);
45         v.set("data4", p.data4);
46         ind = i_ok;
47     }
48 };
49 }
50 
51 void struct_insert()
52 {
53     try
54     {
55         //sqlite3 访问方法
56         //session sql(sqlite3, "/home/clouder/tt/test.db");
57 
58         session sql(mysql, "db=xxx host=xxx user=xxx password='xxx'");
59         sql<<"create table insert_test6( id integer primary key AUTO_INCREMENT,data1 varchar(255),data2 varchar(255),data3 varchar(255),data4 varchar(255))";
60 
61         Person person;
62         person.data1="1111111111111111123234234142352345243523452345";
63         person.data2="2312314123432452356456456456756785678678678967";
64         person.data3="1231446437564567567567865786789789789089-0890-";
65         person.data4="23123123345356324523564561456456756785678678678967";
66         std::vector<Person> vPerson;
67         for(int i=0;i<100000;++i)
68         {
69             vPerson.push_back(person);
70         }
71 
72         boost::posix_time::ptime tbegin_;
73         boost::posix_time::ptime tend_;
74         tbegin_ = boost::posix_time::microsec_clock::universal_time();
75 
76 
77         Person tPerson;
78         tPerson.id = 1;
79         std::vector<Person>::const_iterator itor = vPerson.begin();
80         statement st = (sql.prepare <<
81                         "insert into insert_test6(data1,data2,data3,data4) values(:data1,:data2,:data3,:data4)",
82                         use(tPerson));
83         transaction tr(sql);
84         for(;itor != vPerson.end();++itor)
85         {
86             tPerson = (*itor);
87             st.execute(true);
88         }
89         tr.commit();
90 
91         tend_ = boost::posix_time::microsec_clock::universal_time();
92         boost::posix_time::millisec_posix_time_system_config::time_duration_type time_elapse;
93         time_elapse = tend_ - tbegin_;
94         cout<<" time cost: "<< time_elapse.total_milliseconds();
95     }

机器配置不具体描述 可以对比一下相对时间 30.372s

直接用mysql C api插入

1 void mysql_insert()
 2 {
 3     MYSQL m_cMySQL;
 4     mysql_init(&m_cMySQL);
 5     MYSQL*  returnHandle = mysql_real_connect(&m_cMySQL, "xxx", "xxx",
 6                            "xxx", "xxx", 0, NULL, CLIENT_MULTI_STATEMENTS);
 7 
 8     boost::posix_time::ptime tbegin_;
 9     boost::posix_time::ptime tend_;
10     tbegin_ = boost::posix_time::microsec_clock::universal_time();
11 
12     mysql_autocommit(&m_cMySQL,false);
13 
14     for(int i=0;i<100000;++i)
15     {
16         int result = mysql_query(&m_cMySQL, "insert into insert_test6(data1,data2,data3,data4) values('1111111111111111123234234142352345243523452345','2312314123432452356456456456756785678678678967','1231446437564567567567865786789789789089-0890-','23123123345356324523564561456456756785678678678967')");
17     }
18     mysql_commit(&m_cMySQL);
19 
20     tend_ = boost::posix_time::microsec_clock::universal_time();
21     boost::posix_time::millisec_posix_time_system_config::time_duration_type time_elapse;
22     time_elapse = tend_ - tbegin_;
23     cout<<" time cost: "<< time_elapse.total_milliseconds();
24 
25     mysql_close(&m_cMySQL);
26 }

插入时间 26.699s

soci 使用简单sql插入速度

1 void transaction_insert()
 2 {
 3     try
 4     {
 5         session sql(mysql, "db=DB_2200 host=192.168.0.169 user=clouder password='123456'");
 6 
 7         boost::posix_time::ptime tbegin_;
 8         boost::posix_time::ptime tend_;
 9         tbegin_ = boost::posix_time::microsec_clock::universal_time();
10 
11         transaction tr(sql);
12         for(int i=0;i<100000;++i)
13         {
14 
15             sql<<"insert into insert_test6(data1,data2,data3,data4) values('1111111111111111123234234142352345243523452345','2312314123432452356456456456756785678678678967','1231446437564567567567865786789789789089-0890-','23123123345356324523564561456456756785678678678967')";
16         }
17         tr.commit();
18 
19         tend_ = boost::posix_time::microsec_clock::universal_time();
20         boost::posix_time::millisec_posix_time_system_config::time_duration_type time_elapse;
21         time_elapse = tend_ - tbegin_;
22         cout<<" time cost: "<< time_elapse.total_milliseconds();
23     }
24     catch (exception const &e)
25     {
26         cerr << "Error: " << e.what() << '\n';
27     }
28 }

33.564s

原文链接: https://www.cnblogs.com/hello--world/archive/2013/05/08/3066701.html

欢迎关注

微信关注下方公众号，第一时间获取干货硬货；公众号内回复【pdf】免费获取数百本计算机经典书籍