Microsoft Soft SQL Server Big Data----partitioned table performance test

Partition table

MSSQL has a big data storage solution that can improve efficiency that's the partition table.

There is no difference between the use and the normal table. As for the specific principle of their own Niang bar.

The real performance improvement is dependent on the addition of the hardware. It is also said that when a table is set to the constituent area table, each partition should be stored on a different hard disk.

If the partition table uses the same hard disk, performance is not improved.

Assembling a computer

In its own case, will use three hard disk storage, set a table three partitions.

Well, eat to start work, first assemble the computer, in order to three pieces of hard disk, I will the other two computers, I put them ...

OK, start to install the system, Windows for convenience, I still install the GUI system bar.

After successfully installing WINDOSW Server R2 Datacenter version, install MSSQL R2

Add Simulation data

usingSystem;usingSystem.Text;usingSystem.Collections.Generic;usingSystem.Linq;usingDapper;usingSystem.Data.SqlClient;usingSystem.Data;usingSystem.Threading.Tasks;namespacewrite{classProgram {Static voidMain (string[] args) {Console.WriteLine ("start writing analog data"); //multi-threaded write             for(vari =0; I < -; i++) {Task.run (Action) Add);        } console.readkey (); }         Public Static voidAdd () {varRand =NewRandom (); StringBuilder str1=NewStringBuilder (); Str1. Append ("Zhao, Qian, Sun, Li, Zhou, Wu, Zheng, Wang, Feng, Chen, Chu, Wei, Jiang, Shen, Han, Yang, Zhu, Qin, especially, Xu, he, Lu, Shi, Zhang, Hole, Cao"); varFamilynameitems = str1. ToString (). Split (","); StringBuilder str2=NewStringBuilder (); Str2. Append ("Acer tingting Rong Rong Linana yu Tian xia Guangungong Mei Shuhui muqing Hongshan"); Str2. Append ("Hope Li Red Sweet ladies binbin silverred Dawn Ting Guang Rombe small Yan Xin such as auxiliary Renjianwen ting Jade Red Morning Chardonnay"); Str2. Append ("Han Minlina Quinri yi qun Siyang zhen yong zhi light grams tao jing Huahuixia Wei Weixian Wen Qing xiao Xin"); Str2. Append ("Ann Qvod Qvod Anna Anne Enron Proud winter proud Snow White White cloud bi bi-jennifer Jasper Ice Blue"); Str2. Append ("containing Yuhan Han Han Han Yun Han Xiang Han Yan Han and the United States and Yee wo ya Han and Jade"); Str2. Append ("Hongluo Yuhong Rainbow Choi Ying Hong Ying Rainbow Shadow Huai Jade scalpels hui escha ya mu Green asked Lan Lang Wenshan Manven"); varFirstnameitems = str2. ToString (). Split (" "); varDepartmentitems =Newlist<string> {"Business Unit","Ministry of Culture","Technical Department","Customer Service Department","Operations Department" }; varPositionitems =Newlist<string> {"Clerk","salesman","Development Staff","Service staff","Sales Manager" }; varStatusitems =Newlist<int> {1,2,3 }; IDbConnection Connection=NewSqlConnection ("Data source=192.168.99.5;initial catalog=test; User Id=sa; password=123;"); varItems =NewList<oauser>();  while(true)            {                varuser =NewOauser {UserName= $"15{rand. Next (100000000, 999999999). ToString ()}", PassWord="123456", Salt= Rand. Next (1111111,6666666). ToString (), nickname= Familynameitems[rand. Next (0, Familynameitems.count ())] + Firstnameitems[rand. Next (0, Firstnameitems.count ())], IsFrozen=false, Position= Positionitems[rand. Next (0, Positionitems.count ())], Department= Departmentitems[rand. Next (0, Departmentitems.count ())], Status= Statusitems[rand. Next (0, Statusitems.count ())], Balance= Rand. Next (Ten,99999), Createtime=DateTime.Now}; Items.                ADD (user); //bulk Data writes, reducing the number of times a single connection pool is being called                if(Items. Count < -)                    Continue; StringBuilder strSQL=NewStringBuilder (); Strsql.append ("INSERT into [Test]. [dbo]. [Oauser] ([Balance],[createtime],[department],[isfrozen], [Nickname],[password],[position],[salt],[status], [UserName])"); Strsql.append ("VALUES (@Balance, @CreateTime, @Department, @IsFrozen, @NickName, @PassWord, @Position, @Salt, @Status, @UserName) "); Connection.                Execute (Strsql.tostring (), items); Console.WriteLine ($"The user is successfully {items. Count} data Write"); Items.            Clear (); }        }    }        /// <summary>    ///User Table/// </summary>     Public classOauser {/// <summary>        ///Main Health/// </summary>         Public intId {Set;Get; } /// <summary>        ///User Account/// </summary>         Public stringUserName {Set;Get; } /// <summary>        ///User Password/// </summary>         Public stringPassWord {Set;Get; } /// <summary>        ///Nickname/// </summary>         Public stringNickname {Get;Set; } /// <summary>        ///Encrypted Salt/// </summary>         Public stringSalt {Set;Get; } /// <summary>        ///whether to freeze/// </summary>         Public BOOLIsFrozen {Set;Get; } /// <summary>        ///Balance/// </summary>         Public decimalBalance {Set;Get; } /// <summary>        ///Department/// </summary>         Public stringDepartment {Set;Get; } /// <summary>        ///Job Title/// </summary>         Public stringPosition {Set;Get; } /// <summary>        ///status (1 Job 2 turnover 3 part-time)/// </summary>         Public intStatus {Set;Get; } /// <summary>        ///creation Time/// </summary>         PublicDateTime Createtime {Set;Get; } }}

Speed is also good, up to 7.2m/s fast close to my network cable the highest speed, we are hundred m network card, the highest is 10m/s around

Normal table performance Query

Ah, first write here, dragged for two weeks, also no time, or no mood to write down, first put it, and then back up the

Microsoft Soft SQL Server Big Data----partitioned table performance test