J'ai une application qui fait des INSERT dans une base SQLServer distante de
données issues d'un fichier Excel.
Pour 2053 données ça prend 7 minutes.
Pour 7660 données ça prend 20 minutes.
Et pour 65000 données ça prend près de 2 heures.
Les fichiers excel font respectivement 378Ko, 3,3Mo et 14,5Mo.
1. D'après-vous est-normal ????? Je cherche comment optimiser l'insertion
pour que ça mette moins de temps.
2. Si plusieurs personnes importent en même temps ça ralenti terriblement
l'importation (et pourtant les ressources du serveur sont correctes, maxi 5%
des procs).
Vous pouvez utiliser directement la commande BULK INSERT, comme n'importe quelle requête SQL (SELECT par exemple). Voyez l'aide en ligne de SQL Server.
BULK INSERT MaTable FROM 'C:MonFichier....' WITH (ROWTERMINATOR = 'n', FIELDTERMINATOR = ';')
2) évitez de gérer une transaction. Celle-ci n'a pas de réel intérêt du fait que vous avez la sources des données. Donc en cas de problème vous pouvez rejouer l'insertion.
Oui c'est ce que j'ai d'ailleurs fait
3) divisez votre insertion en lot dont le volume représente un "extent" soit 64 Ko dans la table cible
Euh comment ça ?
Déterminez la taille moyenne d'une ligne de la table par exemple en utilisant DBCC SHOWCONTIG avec l'option TABLERESULTS ceci vous donnera L octets
Avec cela, insérez un nombre de lignes de N, tel que N = 64000/L
4) supprimez préalablement à l'insertion, les index (sauf clef primaire) et régénérez les en fin d'insertion
Comment on fait ça ? Vu qu'il y a plusieurs utilisateurs qui importent ça risque pas de provoquer des problèmes ??
non, au contraire, tout cela ira plus vite. Mais testez l'existence de ces index préalabalement
IF EXISTS (SELECT * FROM master.dbo.sysindexes WHERE name = 'xxx') DROP INDEX ttt.xxx
Le mieux étant d'avoir un plnan d'indexation bien établi.
Sachant que la base ne sert qu'à l'écriture, je peux peut-être supprimé tous les index ?!
sauf clef primaire oui !
Ce que vous avez fait en générant un ordre SQL INSERT par ligne le tout dans une transaction est la pire des manières.
M'étonne pas... c'est justement pour ça que je pose la question :)
Jerome
A +
-- Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Audit, conseil, expertise, formation, modélisation, tuning, optimisation ********************* http://www.datasapiens.com ***********************
Jerome a écrit :
Merci pour les infos...
1) utilisez le BULK INSERT
donc un SqlBulkCopy ?!
Vous pouvez utiliser directement la commande BULK INSERT, comme
n'importe quelle requête SQL (SELECT par exemple). Voyez l'aide en ligne
de SQL Server.
BULK INSERT MaTable
FROM 'C:MonFichier....'
WITH (ROWTERMINATOR = 'n',
FIELDTERMINATOR = ';')
2) évitez de gérer une transaction. Celle-ci n'a pas de réel intérêt du
fait que vous avez la sources des données. Donc en cas de problème vous
pouvez rejouer l'insertion.
Oui c'est ce que j'ai d'ailleurs fait
3) divisez votre insertion en lot dont le volume représente un "extent"
soit 64 Ko dans la table cible
Euh comment ça ?
Déterminez la taille moyenne d'une ligne de la table par exemple en
utilisant DBCC SHOWCONTIG avec l'option TABLERESULTS
ceci vous donnera L octets
Avec cela, insérez un nombre de lignes de N, tel que N = 64000/L
4) supprimez préalablement à l'insertion, les index (sauf clef primaire)
et régénérez les en fin d'insertion
Comment on fait ça ? Vu qu'il y a plusieurs utilisateurs qui importent ça
risque pas de provoquer des problèmes ??
non, au contraire, tout cela ira plus vite. Mais testez l'existence de
ces index préalabalement
IF EXISTS (SELECT *
FROM master.dbo.sysindexes
WHERE name = 'xxx')
DROP INDEX ttt.xxx
Le mieux étant d'avoir un plnan d'indexation bien établi.
Sachant que la base ne sert qu'à l'écriture, je peux peut-être supprimé tous
les index ?!
sauf clef primaire oui !
Ce que vous avez fait en générant un ordre SQL INSERT par ligne le tout
dans une transaction est la pire des manières.
M'étonne pas... c'est justement pour ça que je pose la question :)
Jerome
A +
--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
Vous pouvez utiliser directement la commande BULK INSERT, comme n'importe quelle requête SQL (SELECT par exemple). Voyez l'aide en ligne de SQL Server.
BULK INSERT MaTable FROM 'C:MonFichier....' WITH (ROWTERMINATOR = 'n', FIELDTERMINATOR = ';')
2) évitez de gérer une transaction. Celle-ci n'a pas de réel intérêt du fait que vous avez la sources des données. Donc en cas de problème vous pouvez rejouer l'insertion.
Oui c'est ce que j'ai d'ailleurs fait
3) divisez votre insertion en lot dont le volume représente un "extent" soit 64 Ko dans la table cible
Euh comment ça ?
Déterminez la taille moyenne d'une ligne de la table par exemple en utilisant DBCC SHOWCONTIG avec l'option TABLERESULTS ceci vous donnera L octets
Avec cela, insérez un nombre de lignes de N, tel que N = 64000/L
4) supprimez préalablement à l'insertion, les index (sauf clef primaire) et régénérez les en fin d'insertion
Comment on fait ça ? Vu qu'il y a plusieurs utilisateurs qui importent ça risque pas de provoquer des problèmes ??
non, au contraire, tout cela ira plus vite. Mais testez l'existence de ces index préalabalement
IF EXISTS (SELECT * FROM master.dbo.sysindexes WHERE name = 'xxx') DROP INDEX ttt.xxx
Le mieux étant d'avoir un plnan d'indexation bien établi.
Sachant que la base ne sert qu'à l'écriture, je peux peut-être supprimé tous les index ?!
sauf clef primaire oui !
Ce que vous avez fait en générant un ordre SQL INSERT par ligne le tout dans une transaction est la pire des manières.
M'étonne pas... c'est justement pour ça que je pose la question :)
Jerome
A +
-- Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Audit, conseil, expertise, formation, modélisation, tuning, optimisation ********************* http://www.datasapiens.com ***********************
