communauté Apprendre Bibliothèque d'outils Loisirs
recherche
Français
Maison > développement back-end > tutoriel php > le corps du texte

Solution à la vitesse d'insertion lente de Sqlserver ou à la perte de données de grandes quantités de données

高洛峰
Libérer： 2023-03-04 19:06:01
original
1908 Les gens l'ont consulté

Mon appareil insère 2 000 éléments de données dans la base de données par seconde, et les deux appareils en totalisent 4 000. Lors de l'insertion directe à l'aide de l'instruction insert dans le programme, deux appareils peuvent insérer un total d'environ 2 800 éléments en même temps, et les données sont perdues. Il y a environ 1 200 éléments. Après avoir testé de nombreuses méthodes, deux solutions aux effets évidents ont été trouvées :

Méthode 1 : Utiliser la fonction SQL Server :

1. dans des chaînes , utilisez la fonction pour insérer des données dans la table mémoire, puis copiez les données de la table mémoire dans la table à insérer.

2. Modifiez le format des caractères combinés : '111|222|333|456,7894,7458|0|1|2014-01-01 12:15:16;1111|2222|3333| 456 ,7894,7458|0|1|2014-01-01 12:15:16', Chaque ligne de données est séparée par ";", et chaque champ est séparé par "|".

3. Fonction d'écriture : 

CREATE FUNCTION [dbo].[fun_funcname](@str VARCHAR(max),@splitchar CHAR(1),@splitchar2 CHAR(1)) 
--定义返回表  
RETURNS @t TABLE(MaxValue float,Phase int,SlopeValue float,Data varchar(600),Alarm int,AlmLev int,GpsTime datetime,UpdateTime datetime) AS  
/*     
author:hejun li   
create date:2014-06-09   
*/   
BEGIN  
DECLARE @substr VARCHAR(max),@substr2 VARCHAR(max)
--申明单个接收值 
declare @MaxValue float,@Phase int,@SlopeValue float,@Data varchar(8000),@Alarm int,@AlmLev int,@GpsTime datetime 
SET @substr=@str   
DECLARE @i INT,@j INT,@ii INT,@jj INT,@ijj1 int,@ijj2 int,@m int,@mm int
SET @j=LEN(REPLACE(@str,@splitchar,REPLICATE(@splitchar,2)))-LEN(@str)--获取分割符个数   
IF @j=0   
  BEGIN  
   --INSERT INTO @t VALUES (@substr,1) --没有分割符则插入整个字串  
   set @substr2=@substr;
   set @ii=0
   SET @jj=LEN(REPLACE(@substr2,@splitchar2,REPLICATE(@splitchar2,2)))-LEN(@substr2)--获取分割符个数
     WHILE @ii<=@jj
        BEGIN
          if(@ii<@jj)
            begin
              SET @mm=CHARINDEX(@splitchar2,@substr2)-1 --获取分割符的前一位置
              if(@ii=0)
                set @MaxValue=cast(LEFT(@substr2,@mm) as float)
              else if(@ii=1)
                set @Phase=cast(LEFT(@substr2,@mm) as int)
              else if(@ii=2)
                set @SlopeValue=cast(LEFT(@substr2,@mm) as float)
              else if(@ii=3)
                set @Data=cast(LEFT(@substr2,@mm) as varchar)
              else if(@ii=4)
                set @Alarm=cast(LEFT(@substr2,@mm) as int)
              else if(@ii=5)
                set @AlmLev=cast(LEFT(@substr2,@mm) as int)
              else if(@ii=6)
                INSERT INTO @t VALUES(@MaxValue,@Phase,@SlopeValue,&#39;&#39;+@Data+&#39;&#39;,@Alarm,@AlmLev,cast(@substr2 as datetime),GETDATE())
              SET @substr2=RIGHT(@substr2,LEN(@substr2)-(@mm+1)) --去除已获取的分割串，得到还需要继续分割的字符串
            end
          else
            BEGIN
              --当循环到最后一个值时将数据插入表
              INSERT INTO @t VALUES(@MaxValue,@Phase,@SlopeValue,&#39;&#39;+@Data+&#39;&#39;,@Alarm,@AlmLev,cast(@substr2 as datetime),GETDATE())
            END
        --END
        SET @ii=@ii+1
      END
  END  
ELSE  
BEGIN  
 SET @i=0   
 WHILE @i<=@j   
 BEGIN  
  IF(@i<@j)   
  BEGIN  
  SET @m=CHARINDEX(@splitchar,@substr)-1 --获取分割符的前一位置
  --INSERT INTO @t VALUES(LEFT(@substr,@m),@i+1) 
  -----二次循环开始
  --1.线获取要二次截取的字串
  set @substr2=(LEFT(@substr,@m));
  --2.初始化二次截取的起始位置
  set @ii=0
  --3.获取分隔符个数
  SET @jj=LEN(REPLACE(@substr2,@splitchar2,REPLICATE(@splitchar2,2)))-LEN(@substr2)--获取分割符个数
  WHILE @ii<=@jj
    BEGIN
      if(@ii<@jj)
        begin
          SET @mm=CHARINDEX(@splitchar2,@substr2)-1 --获取分割符的前一位置
          if(@ii=0)
            set @MaxValue=cast(LEFT(@substr2,@mm) as float)
          else if(@ii=1)
            set @Phase=cast(LEFT(@substr2,@mm) as int)
          else if(@ii=2)
            set @SlopeValue=cast(LEFT(@substr2,@mm) as float)
          else if(@ii=3)
            set @Data=cast(LEFT(@substr2,@mm) as varchar)
          else if(@ii=4)
            set @Alarm=cast(LEFT(@substr2,@mm) as int)
          else if(@ii=5)
            set @AlmLev=cast(LEFT(@substr2,@mm) as int)
          else if(@ii=6)
            INSERT INTO @t VALUES(@MaxValue,@Phase,@SlopeValue,&#39;&#39;+@Data+&#39;&#39;,@Alarm,@AlmLev,cast(@substr2 as datetime),GETDATE())
          SET @substr2=RIGHT(@substr2,LEN(@substr2)-(@mm+1)) --去除已获取的分割串，得到还需要继续分割的字符串
        end
      else
        BEGIN
          --当循环到最后一个值时将数据插入表
          INSERT INTO @t VALUES(@MaxValue,@Phase,@SlopeValue,&#39;&#39;+@Data+&#39;&#39;,@Alarm,@AlmLev,cast(@substr2 as datetime),GETDATE())
        END
    --END
    SET @ii=@ii+1
  END
  -----二次循环结束
  SET @substr=RIGHT(@substr,LEN(@substr)-(@m+1)) --去除已获取的分割串，得到还需要继续分割的字符串   
  END  
 ELSE  
  BEGIN
  --INSERT INTO @t VALUES(@substr,@i+1)--对最后一个被分割的串进行单独处理 
  -----二次循环开始
  --1.线获取要二次截取的字串
  set @substr2=@substr;
  --2.初始化二次截取的起始位置
  set @ii=0
  --3.获取分隔符个数
  SET @jj=LEN(REPLACE(@substr2,@splitchar2,REPLICATE(@splitchar2,2)))-LEN(@substr2)--获取分割符个数
  WHILE @ii<=@jj
    BEGIN
      if(@ii<@jj)
        begin
          SET @mm=CHARINDEX(@splitchar2,@substr2)-1 --获取分割符的前一位置
          if(@ii=0)
            set @MaxValue=cast(LEFT(@substr2,@mm) as float)
          else if(@ii=1)
            set @Phase=cast(LEFT(@substr2,@mm) as int)
          else if(@ii=2)
            set @SlopeValue=cast(LEFT(@substr2,@mm) as float)
          else if(@ii=3)
            set @Data=cast(LEFT(@substr2,@mm) as varchar)
          else if(@ii=4)
            set @Alarm=cast(LEFT(@substr2,@mm) as int)
          else if(@ii=5)
            set @AlmLev=cast(LEFT(@substr2,@mm) as int)
          else if(@ii=6)
            INSERT INTO @t VALUES(@MaxValue,@Phase,@SlopeValue,&#39;&#39;+@Data+&#39;&#39;,@Alarm,@AlmLev,cast(@substr2 as datetime),GETDATE())
          SET @substr2=RIGHT(@substr2,LEN(@substr2)-(@mm+1)) --去除已获取的分割串，得到还需要继续分割的字符串
        end
      else
        BEGIN
          --当循环到最后一个值时将数据插入表
          INSERT INTO @t VALUES(@MaxValue,@Phase,@SlopeValue,&#39;&#39;+@Data+&#39;&#39;,@Alarm,@AlmLev,cast(@substr2 as datetime),GETDATE())
        END
    SET @ii=@ii+1
  END
  -----二次循环结束
  END  
 SET @i=@i+1    
 END  
END  
RETURN  
END
Copier après la connexion

4. Instruction de la fonction d'appel : 

insert into [mytable] select * from [dbo].[fun_funcname](&#39;111|222|333|456,7894,7458|0|1|2014-01-01 12:15:16;1111|2222|3333|456,7894,7458|0|1|2014-01-01 12:15:16&#39;,&#39;;&#39;,&#39;|&#39;);
Copier après la connexion

5. 🎜>

select * from [mytable] ;
Copier après la connexion

Méthode 2 : Utilisez BULK INSERT

La première opération d'insertion de grandes quantités de données, utilisez Bulk pour insérer des données de fichier dans la base de données

Code SQL

Créer une base de données

CREATE DATABASE [db_mgr]
GO
Copier après la connexion

Créer une table de test

USE db_mgr
CREATE TABLE dbo.T_Student(
  F_ID [int] IDENTITY(1,1) NOT NULL,
  F_Code varchar(10) ,
  F_Name varchar(100) ,
  F_Memo nvarchar(500) ,
  F_Memo2 ntext ,
  PRIMARY KEY (F_ID)
)
GO
Copier après la connexion

Remplir les données de test

Insert Into T_Student(F_Code, F_Name, F_Memo, F_Memo2) select
&#39;code001&#39;, &#39;name001&#39;, &#39;memo001&#39;, &#39;备注&#39; union all select
&#39;code002&#39;, &#39;name002&#39;, &#39;memo002&#39;, &#39;备注&#39; union all select
&#39;code003&#39;, &#39;name003&#39;, &#39;memo003&#39;, &#39;备注&#39; union all select
&#39;code004&#39;, &#39;name004&#39;, &#39;memo004&#39;, &#39;备注&#39; union all select
&#39;code005&#39;, &#39;name005&#39;, &#39;memo005&#39;, &#39;备注&#39; union all select
&#39;code006&#39;, &#39;name006&#39;, &#39;memo006&#39;, &#39;备注&#39;
Copier après la connexion

Activer la procédure stockée xp_cmdshell (il y a des risques de sécurité après l'ouverture) 

EXEC sp_configure &#39;show advanced options&#39;, 1;
RECONFIGURE;EXEC sp_configure &#39;xp_cmdshell&#39;, 1;
EXEC sp_configure &#39;show advanced options&#39;, 0;
RECONFIGURE;
Copier après la connexion

Utiliser bcp pour exporter les fichiers au format : 

EXEC master..xp_cmdshell &#39;BCP db_mgr.dbo.T_Student format nul -f C:/student_fmt.xml -x -c -T&#39;
Copier après la connexion

Utilisez bcp pour exporter des fichiers de données : 

EXEC master..xp_cmdshell &#39;BCP db_mgr.dbo.T_Student out C:/student.data -f C:/student_fmt.xml -T&#39;
Copier après la connexion

Effacer les données du tableau

truncate table db_mgr.dbo.T_Student
Copier après la connexion

Utiliser l'instruction Bulk Insert pour importer par lots Fichier de données : 

BULK INSERT db_mgr.dbo.T_Student
FROM &#39;C:/student.data&#39;
WITH
(
  FORMATFILE = &#39;C:/student_fmt.xml&#39;
)
Copier après la connexion

Exemple d'utilisation d'OPENROWSET (BULK) :

La table T_Student doit déjà exister

INSERT INTO db_mgr.dbo.T_Student(F_Code, F_Name) SELECT F_Code, F_Name
FROM OPENROWSET(BULK N&#39;C:/student.data&#39;, FORMATFILE=N&#39;C:/student_fmt.xml&#39;) AS new_table_name
Copier après la connexion

Exemples d'utilisation d'OPENROWSET (BULK) :

La table tt n'a pas besoin d'exister

SELECT F_Code, F_Name INTO db_mgr.dbo.tt
FROM OPENROWSET(BULK N&#39;C:/student.data&#39;, FORMATFILE=N&#39;C:/student_fmt.xml&#39;) AS new_table_name
Copier après la connexion

Plus de solutions pour Sqlserver insertion lente de grandes quantités de données ou perte de données Pour les articles liés aux solutions, veuillez faire attention au site Web PHP chinois !

Étiquettes associées：
Sqlserver 大数据量 解决方法
source：php.cn
Article précédent：Comment utiliser des outils open source pour créer des codes de vérification de pages Web Article suivant：Solution de stockage de données volumineuses et de haute concurrence SQLserver
Déclaration de ce site Web
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn
Derniers articles par auteur
Derniers numéros
Calculer la somme des champs dans une autre table à l'aide d'une requête MySQL SQL J'ai un schéma comme celui-ci : Table utilisateur avec les attributs "user_id" e...
Depuis 2024-04-06 19:39:29
0
1
441
Utiliser les méthodes de apply Pour trouver la valeur maximale d'un tableau, un moyen simple est Math.max.apply(null,myAr...
Depuis 2024-04-03 21:41:46
0
2
278
La question est toujours la même, mais le titre est réécrit comme suit : Ma fonction aléatoire Javascript ne parvient pas à générer des réponses valides Je crée un simple jeu générateur de nombres aléatoires en utilisant JS et HTML. Dans ce je...
Depuis 2024-04-03 22:03:14
0
1
275
Comment conserver les X premières lignes et supprimer les lignes du tableau J'ai une grande table avec des millions d'enregistrements dans MySQLincident_archive, je s...
Depuis 2024-04-01 18:32:54
0
1
347
Rubriques connexes
Plus>
Recommandations populaires
Tutoriels populaires
Plus>
Tutoriels associés
Recommandations populaires
Derniers cours
Derniers téléchargements
Plus>
effets Web
Code source du site Web
Matériel du site Web
Modèle frontal