Solved

SQL script running extremely slowly

Posted on 2008-06-20
12
1,249 Views
Last Modified: 2008-09-16
I have some very large scripts consisting of hundreds of thousands of INSERT statements.  (these is the only "option" I've been given to import this data into my database).  The script first creates a new table, then proceeds to populate with INSERT statements

The scripts take hours to run.  For example one script contain 108,000 INSERT statements takes 45 minutes to run.  The size of the script file is 37MB.  I am not sure why it is taking so long.

Here is the command line I use to execute the SQL:
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd" -d DBname -i .\DataFiles\sqlscript.sql  -o Logfile.log

Included below is a code snippet with the first two INSERT statements.

Any idea why this is taking so long and what I can do to improve on it?


USE DBname;
CREATE TABLE VOUCHER
(
VO004_VENDOR_NO   CHAR (11) NOT NULL,
VO012_VOUCHER_NO  CHAR (07) NOT NULL,
VO028_ACCT_1_6    CHAR (06) NOT NULL,
VO032_ACCT_7_10   CHAR (04) NOT NULL,
VO052_DROP                 CHAR (01),
VO054_RECON                CHAR (01),
VO056_PURGE                CHAR (01),
VO062_HOLD                 CHAR (01),
VO064_VOID                 CHAR (01),
VO066_PF                   CHAR (01),
VO068_ENC                  CHAR (01),
VO070_DISC_LOST            CHAR (01),
VO072_1099                 CHAR (01),
VO089_TC_ALL               CHAR (03),
VO094_PO_NO       CHAR (07) NOT NULL,
VO100_BANK        CHAR (02) NOT NULL,
VO110_CHECK       CHAR (06) NOT NULL,
VO122_INV_NO   VARCHAR (14) NOT NULL,
VO126_DESC              VARCHAR (15),
VO130_INV_DATE             CHAR (08),
VO140_DUE_DATE             CHAR (08),
VO150_CHECK_DATE           CHAR (08),
VO151_CHECK_AMT            DECIMAL(9,2),
VO152_CHECK_AMT_VOIDED     DECIMAL(9,2),
VO170_INV_AMT              DECIMAL(9,2),
VO172_DISC_AMT             DECIMAL(7,2),
VO174_NET_AMT              DECIMAL(9,2),
VO180_LIAB_ACCT            CHAR (10),
VO204_TAX_AMT              DECIMAL(9,2),
VO310_BATCH_REF            CHAR (06),
VO331_BATCH_YY             CHAR (02),
VO332_BATCH_MM             CHAR (02),
VO333_BATCH_DD             CHAR (02),
VO338_FY          CHAR (07) NOT NULL,
VO346_FISCAL_MONTH CHAR (02) NOT NULL,
VO350_WORK_ORDER  CHAR (10) NOT NULL
);
GO
INSERT INTO VOUCHER VALUES (
'C0000042310','4503546','141381','5210','0','0','1','0','0',' ',' ','0','S','140','IC     ','25','838255','SPRING 2006   ','               ','20060713','20060718','20060717',     31620.28,          .00,     31620.28,        .00,     31620.28,'0140002100',          .00,'VPJ172','06','07','17','2006-07','06','          '
);
INSERT INTO VCHR VALUES (
'C0000042310','4509329','141381','5210','0','0','1','0','0',' ',' ','0','S','140','IC     ','25','840756','SUMMER 2006   ','               ','20060919','20060921','20060920',     25929.28,          .00,     25929.28,        .00,     25929.28,'0140002100',          .00,'VPJ201','06','09','20','2006-07','09','          '
);

Open in new window

0
Comment
Question by:roymene
12 Comments
 
LVL 12

Expert Comment

by:Serge Fournier
ID: 21836041
whew, big question, but i have same problem, even if i know how to resolve it, i did not yet, because my script still have enough time to execute in one night

first, you have to chek if the table exist before creating it
same for the data, verify if it exist before inserting it

but something that will help a LOT is when you create your table, create an index field in it

i include in code snippet a VBS part where i use the index creation sql command
just adding the primary key creation with your table, as an int id autoincremental, will help i think
id01 is the column name
CREATE TABLE database.dbo.table (id01 INT IDENTITY (1, 1) PRIMARY KEY)"

you can also make your index clustered
(aligned with hard disk sector for readahead)
but that would be for reading speed (5x to 10x faster read), not writing mostly

CREATE CLUSTERED INDEX tablename_INDEX ON database.DBO.table (fltime)

now i know my answer is not an A hehe
because i just point you in a direction
i do most of my imports with VBS, sending all sql update one by one, not as a big sql script

but i will have to do same as you one day, when my vbs wont be fast enough to do it in one night

i will have to do it very soon, because my script are missing the deadline actually for 30 min right now

so ill post something new soon here

'=== vbs script (partial, do not run it)
DES2 = "ALLTAB01"
sql = "CREATE TABLE " & SQLD & ".dbo." & DES2 & "(id01 INT IDENTITY (1, 1) PRIMARY KEY)"
Set tag = con_02.Execute(sql)
SQL = "ALTER TABLE " & SQLD & ".dbo." & DES2 & " ADD NAME01 VARCHAR(30)"
Set tag = con_02.Execute(sql)
SQL = "ALTER TABLE " & SQLD & ".dbo." & DES2 & " ADD TYPE01 INT"
Set tag = con_02.Execute(sql)
SQL = "ALTER TABLE " & SQLD & ".dbo." & DES2 & " ADD DESCR01 TEXT"
Set tag = con_02.Execute(sql)
SQL = "ALTER TABLE " & SQLD & ".dbo." & DES2 & " ADD VITACQ INTEGER"
Set tag = con_02.Execute(sql)
SQL = "ALTER TABLE " & SQLD & ".dbo." & DES2 & " ADD IPADD VARCHAR(15)"
Set tag = con_02.Execute(sql)
 
'=== a = table name, flink_data2 = database name
SQL = "CREATE CLUSTERED INDEX " & A & "_INDEX ON FLINK_DATA2.DBO." & A & "(fltime)"

Open in new window

0
 
LVL 12

Expert Comment

by:Serge Fournier
ID: 21836048
CREATE CLUSTERED INDEX tablename_INDEX ON database.DBO.table (fltime)

fltime is the column name that will be aligned with hard disk sector at the next sql defragmenting database
0
 
LVL 12

Expert Comment

by:Serge Fournier
ID: 21836079
my original script do this:
analyse all csv to import and generate a csv containing the data type (Str or int etc)
loop till all csv file are analyzed

chek if database exist, create it if not
chek if table exist, create it with schema csv file if not
read a csv line and generate a sql query to insert and update the data
(my week point is here, cause sql will pause on the insert command error if the data already exist)
loop till all csv line are read
loop till all csv files are done

just say if you want the whole script (it's a bit complicated)

0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 12

Expert Comment

by:Serge Fournier
ID: 21836097
i found my csv to sql script even if i am not at job at the moment

in it you have the sql commands to chek if a table already exist (at the end)

you will have to convert all this to sql statement because i use vbs variables for table and columns names, not sql variables

database chek if exist: (vbs)
   sql = "SELECT COUNT(*) As 'tableCount' FROM " & db & ".dbo.sysObjects WHERE name = '" & table & "'"

table chek if exist: (vbs)
     '=== count the number of columns that have a certain name in the database
     sql = "SELECT COUNT(*) As tableCount FROm " & db & ".dbo.syscolumns WHERE ID = (SELECT ID from " & db & ".dbo.sysobjects WHERE name='" & table & "') AND name = '" & col_clef_name & "';"

my script require CSV file with tablename_schema to create the columns at the same time it import
but i doubt you will run it, since i will evolve it in sql query, instead of vbs soon

so for you, it would be a devolution, in speed and code :P


'=== main program procedure:.
' 1. read the sql formats: csv_to_sql_import_formats.csv
' 2. read the list of files to import: csv_to_sql_liste_fichier_a_importer.txt
' 3. open sql database with admin password and dns adress
' 4. read a file with the name of the CSV + "_schema" and use it to know the format of columns to create sql database
'    you must create this file manually before importation
'    in this file, there is a column format number wich is +1000, its destined to be the main key for database
'    (i did not add the sql code to define the main key)
' 5. if database does not exist, we create it
' 6. if column for main key does not exist, we create it
' 7. search in main key for a key identical, if not found, create the record, if found, update record
 
dim upd_ii, sql1, dummy
'=== take name of the sql server from a window variable
sql1=environ("stassql1")
'=== we just remove "\\" cause its a computer network name
'if sql1<>"" then
'  sql1=right(sql1,len(sql1)-2)
'else
'  msgbox("serveur sql invalide invalide!!!")
'  wscript.quit
'end if
 
upd_ii=10  '=== update frequency for writing in report log database_log.txt
ecmax=10    '=== nbr of max try to connect to database
 
Dim objFSO, file_02, ligne,a
 
 
Set objFSO = wscript.CreateObject("Scripting.FileSystemObject")
thepath=WScript.ScriptFullName
p=instrRev(thepath,"\")
basedir=left(thepath,p)
 
'=== _liste_fichier_a_importer.txt (this file contain the list of file to import)
' contient:
' 01 name of database (is also the name of directory where to get the csv)
' 02 root directory
' 03 file name (is also "_" + the name of the database, cause what i import is for magica)
 
catfile=basedir & "csv_to_sql_liste_fichier_a_importer.txt"
if objfso.fileexists(catfile) then
    Set file_02 = objFSO.OpenTextFile(base_dir & "csv_to_sql_liste_fichier_a_importer.txt", 1, 0) 
else
    msgbox("ERREUR" & chr(10) & chr(13) & catfile & chr(10) & chr(13) & "n'existe pas, on ne peux pas commencer")
'    msgbox("ERROR" & chr(10) & chr(13) & catfile & chr(10) & chr(13) & "list of file to import does not exist, cannot start import")
    
    wscript.quit
end if
 
Set objFileSystem = CreateObject("Scripting.fileSystemObject")
Set objOutputFile02 = objFileSystem.OpenTextFile(base_dir & "zz_csv_to_sql_log_fini_TOUT.txt", 2, true)
 
set fso=nothing
 
'=== LOOP for folders ================================================
Do While file_02.AtEndOfStream <> True 
ligne = file_02.Readline 
 
'=== change folder is there is more than one folder that contain some csv to import
If InStr(ligne,":\")=0 and instr(ligne,".")=0 Then
   db_main=ligne
   ligne = file_02.Readline
End If
 
'=== change fichier (table)
'=== change file (the name of file is also the name of the table)
If InStr(ligne,":\") Then
   'db_dir = "M:\Temp\" & db_main
   ligne =lcase(ligne)
   if ligne = "m:\temp" then
      ligne = environ("stasmag") & "\Dunin\Temp"
   end if
   db_dir = ligne & "\" & db_main
   ligne = file_02.Readline
End If
 
'=== name of table, remove the ".csv" part
nom_table = mid(ligne,1, len(ligne) -(len(ligne) -(instr(ligne,".")-1)))
 
'nom_table = "personnes"             '=== ajoute .CSV pour nom fichier
'msgbox(nom_Table & " " & len(nom_table) & " " & db_dir & " " & db_main)
 
'=== add "mag_" at the start of database name, cause the importation is for a software called "magica"
'=== TEST ========================================================----------------------------
nom_db_pre = "mag_"
nom_db_suf = db_main
nom_db = nom_db_pre & nom_db_suf
nom_table_suf = "_schema"          '=== ajoute _schema pour le fichier de quel type sont chaque table
 
'=== database finie === on crée un fichier bidon pour indiquer que toute la database est fini d'importer
'=== create report file to put info on what is imported, with some details for double record (records with same master key)
 
'=== 2 = efface et refais, 8 = continue
'Set objOutputFile02 = objFileSystem.OpenTextFile(base_dir & "zz_csv_to_sql_log_fini_TOUT.txt", 8, true)
objOutputFile02.WriteLine date & " " & time & " " & db_main & "   " & nom_table & "          debut importation " & db_dir
'objOutputFile02.Close
'Set objFileSystem = Nothing
 
dim sql
'=== ouvre un fichier log et le crée si il est pas la (.txt)
'=== open a log file, create it if not there
Dim objFileSystem
dim objOutputFile
Set objFileSystem = CreateObject("Scripting.fileSystemObject")
Set objOutputFile = objFileSystem.OpenTextFile(base_dir & "z_csv_to_sql_log_" & nom_table & ".txt", 8, true)
 
'=== CSV connexion - input
'=== FUTUR: verifier si fichier existe avant de l'ouvrir
dim con_01
Set Con_01 = CreateObject("ADODB.Connection")
Con_01.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & db_Dir & ";Extensions=asc,csv,tab,txt;Persist Security Info=False"
Set RS_01 = CreateObject ("ADODB.Recordset")
 
'=== table de format sql à importer pour stas numérotés de 0 à 22 et +
'=== table containing the sql format as number for faster creation of "_schema" files
Set Con_03 = CreateObject("ADODB.Connection")
Con_03.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & base_dir & ";Extensions=asc,csv,tab,txt;Persist Security Info=False"
Set RS_03 = CreateObject ("ADODB.Recordset")
sql = "SELECT * from csv_to_sql_import_formats.csv"
set rs_03 = con_03.execute(sql)
 
'=== table de input pour les format à importer pour chaque colonne de chaque csv<
'=== "_schema" file, as the same number of column as the csv file to import, with numbers for format to input them as
Set Con_04 = CreateObject("ADODB.Connection")
Con_04.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & base_Dir & ";Extensions=asc,csv,tab,txt;Persist Security Info=False"
Set RS_04 = CreateObject ("ADODB.Recordset")
'=== CSV ouvre la table des types de colonnes à créer
 
sql = "SELECT * from " & nom_table & nom_table_suf & ".csv"
set rs_04 = con_04.execute(sql)
 
'=== SQL
'=== crée une database par rapport
'=== crée une table par fichier csv (voir csv_to_sql_liste_fichier_a_importer.txt, dans le dossier d'origine)
 
'=== will create one database for every directory to import
'=== will create one table for every file to import
 
Dim con_02
Set con_02 = CreateObject("ADODB.Connection")
'=== rs = record set = contient un record, une ligne de database
Set RS = CreateObject ("ADODB.Recordset")
Set SQLStmt = CreateObject("ADODB.Command")
set tag = CreateObject ("ADODB.Recordset")
 
'================================================================ sql ======================================
'=== connexion serveur sql, sans se connecter sur une database
'=== MAIN SQL DATABASE CONNECTION
con_02.ConnectionString = "Driver={SQL Server};Server=" & sql1 & ";Uid=sa;Pwd=Unista999"
con_02.Open
 
'=== DATABASE === cherche la database, pour voir si elle existe
 
'=== verify is database exist, in the table containing all database name for all sql server
'=== the name of this table might differ for a my_sql server
 
sql = "SELECT COUNT(*) As 'DBCount' FROM sysdatabases WHERE name = '" & nom_db & "'"
 
faisrequete10fois sql,ecmax
 
'if tag("DBCount") = 1 and nom_db = "M_Temp_ApprobFTemps" Then
'   objOutputFile.WriteLine date & " " & time & " cette base est effacée à chaque update EFFACEMENT: " & nom_db
'   sql = "DROP DATABASE " & nom_db
'   Set tag = Con_02.Execute(sql)
'   sql = "SELECT COUNT(*) As 'DBCount' FROM sysdatabases WHERE name = '" & nom_db & "'"
'   Set tag = Con_02.Execute(sql)
'end if
 
if tag("DBCount") = 0 Then
   '=== create the sql database
   sql = "CREATE DATABASE " & nom_db
   set tag = con_02.execute(sql)
   objOutputFile.WriteLine date & " " & time & " database CREATE on crée la database: " & nom_db
else
   'msgbox("la databse existe déjà, pas supposé")
   objOutputFile.WriteLine date & " " & time & " database UPDATE la database existais déjà " & nom_db
end if 
 
'=== activer une database
'=== be sure to "use" this database just in case i forgot to specifie database name in a command
sql = "USE " & nom_db
faisrequete10fois sql, ecmax
 
'=== CSV selectionne tous les CSV
'=== read the schema file that will tell me what format is each column, to import all column in the right format
sql = "SELECT * from " & nom_table & ".csv"
on error resume next
'=== colonne
 
set rs_01 = con_01.execute(sql)
 
If Err.number <> 0 Then
   'MsgBox Err.Source & "-->" & Err.Description, , "Error"
   objOutputFile.WriteLine date & " " & time & "  " & ii
   objOutputFile.WriteLine date & " " & time & "  " & db_main & "   " & nom_table & " " & sql
   objOutputFile.WriteLine date & " " & time & "  " & err.description
   objOutputFile.WriteLine date & " " & time & "  " & rs_01(table_clef).name
   objOutputFile02.WriteLine date & " " & time & " " & db_main & " csv vide de contenu "
   objOutputFile02.WriteLine date & " " & time & " le script est arrêté!!!"
   
   err.clear
   
   wscript.quit
End If
 
'=== crée une table avec une colonne de base qui est obligatoire
'=== la colonne de base est celle dont le type est >999 elle est la clef primaire
 
'=== create a column destined to be the primary key (format = to format +1000)
table_clef=-1
i=0
while i<RS_04.fields.count
   if rs_04(i)>999 then
      table_clef=i
      'msgbox(table_clef)
   end if
i=i+1
wend
 
if table_clef=-1 then
   '=== stop program is there is no master key
   a="vous avez oublié de spécifier une clef de base dans votre base de donnée soit un format +1000"
   'msgbox(a)
   objOutputFile.WriteLine date & " " & time & a & nom_db
   Wscript.quit   
end if
 
'=== TABLE === si table existe pas, crée la table et une colonne
'=== avec une clef de base (dont le code est +1000)
 
'=== clef primaire = 1
dummy = VER_CRE_db_TAB(nom_db, nom_table, rs_03(rs_04(table_clef)-1000), rs_04(table_clef).name, 1)
 
'======================== boucle principale ============================
'======================== MAIN LOOP         ============================
 
'=== vérifie si colonnes existes, si existe pas on les crée
 
if RS_04.fields.count>RS_01.fields.count then
   A="le nombre de colonnes dans le csv d'origine est < que celui du schema"
   'MSGBOX(A)
   objOutputFile.WriteLine date & " " & time & a
   a= RS_04.fields.count-RS_01.fields.count & " colonne(s) en trop sera(ont) crée(s) "
   objOutputFile.WriteLine date & " " & time & a & nom_db
   a=""
   for i=0 to RS_04.fields.count
      a = a + " " + rs_04(i).name
   next
   objOutputFile.WriteLine date & " " & time & "ORIGINE    " & a
   a=""
   for i=0 to RS_01.fields.count
      a = a + " " + rs_01(i).name
   next
   objOutputFile.WriteLine date & " " & time & "DESTINATION" & a   
   
   'wscript.quit
end if
if RS_04.fields.count<RS_01.fields.count then
   A = "le nombre de colonnes dans le csv d'origine est > que celui du schema"
   'MSGBOX(A)
   objOutputFile.WriteLine date & " " & time & a & nom_db
   a = "impossible de continuer car il vous manquera des données "
   objOutputFile.WriteLine date & " " & time & a & nom_db
   
   a=""
   for i=0 to RS_01.fields.count
      a = a + " " + rs_01(i).name
   next
   objOutputFile.WriteLine date & " " & time & "ORIGINE    " & a
   a=""
   for i=0 to RS_04.fields.count
      a = a + " " + rs_04(i).name
   next
   objOutputFile.WriteLine date & " " & time & "DESTINATION" & a   
   
   wscript.quit
end if
 
i=0
while i<RS_04.fields.count
   '=== clef primaire = 0 ou 1
   a=rs_04(i)
   dummy = VER_CRE_db_TAB(nom_db, nom_table, rs_03(a), rs_04(i).name, 0)
   i=i+1
wend
 
'objOutputFile.WriteLine date & " " & time & " database crée" & nom_db
'objOutputFile.WriteLine date & " " & time & " ce fichier update à tous les " & upd_ii & " record crée"
'=== LIGNES création
ii=0
 
While Not rs_01.EOF
   '=== nbr de lignes
   'msgbox(rs_01.recordcount) '=== retourne -1???
   '=== Replace(base64String, vbCrLf, "")
 
   '=== on insere la clef primaire en premier en tant que nouvelle ligne de données sql
   data_col = rs_01(table_clef)
   
   if instr(data_col,"'")<>0 then data_col = replace(data_col,"'","''")
   if IsNumeric(data_col) and instr(data_col,",")<>0 then data_col = replace(data_col,",",".")
   '=== on doit insérer que si elle existe pas...
 
   '=== compte le nombre de fois quon trouve cette ligne avec cette clef primaire de même valeur
   '=== chek if record exist (use primary key for that)
   sql = "SELECT COUNT(*) As tableCount FROm " & nom_db & ".dbo." & nom_table & " WHERE " & rs_01(table_clef).name & " = '" & data_col & "';"
   on error resume next
   
faisrequete10fois sql, ecmax
 
   'msgbox(tag("tableCount") & " " & sql)
 
   '=== la database sql avais deja un doublon DANGER
   if tag("tableCount") > 1 then
      objOutputFile.WriteLine date & " " & time & " --- ERREUR record exist *** MANY *** TIMES " & data_col & " UPDATING "
      'objOutputFile.WriteLine date & " " & time & " --- ERREUR DOUBLON " & nom_table & " . " & rs_01(table_clef).name & " . " & data_col
   end if
   '=== le csv a un doublon
   if tag("tableCount") = 1 then
      '=== this slow down the importation - removed - use for debugging only
      'objOutputFile.WriteLine date & " " & time & " --- ERREUR record exist 1 TIME " & data_col & " UPDATING "
   end if
 
   on error resume next  
   '=== all seems normal, we start the string that we will update, then send to sql server
   if tag("tableCount") = 0 then
      sql = "INSERT INTO " & nom_db & ".dbo." & nom_table & " (" & rs_01(table_clef).name & ") VALUES('" & data_col & "');"
      faisrequete10fois sql,ecmax
   end if
   
   i=0
   sql = "UPDATE " & nom_db & ".dbo." & nom_table & " SET "
   
   WHILE i<RS_01.fields.count
      '=== si c'est la clef primaire on ne l'insère pas on saute a la suivante
      data_col = rs_01(i)
            
      if instr(data_col,"'")<>0 then data_col = replace(data_col,"'","''")
      if IsNumeric(data_col) and instr(data_col,",")<>0 then data_col = replace(data_col,",",".")
      
      'if data_col1 <> "" then data_col = replace(data_col1,chr(10)," ")
      
      '=== add one operation to the string we will send to the sql server
      '=== this is faster then sending one operation for each column update (standard procedure)
      sql = sql + rs_01(i).name & "='" & data_col & "', "
      '=== adding special operation for clientu (new column)
      if lcase(nom_db)="magtest_projets" and lcase(nom_table)="documv3" and lcase(rs_01(i).name)="code" then
         if len(data_col)>2 then
            data_col=mid(data_col,1,3)
            sql = sql + "clientu='" & data_col & "', "
         end if
      end if
      
      'set tag = con_02.execute(sql)
      i=i+1
   WEND
   
   sql = mid(sql,1,len(sql)-2)
   sql = sql + " FROM " & nom_db & ".dbo." & nom_table & " WHERE " & rs_01(table_clef).name & "='" & rs_01(table_clef) & "';"
   
   on error resume next
   
   '=== send the record update (all columns) to sql server   
 
   faisrequete10fois sql,ecmax
   
   on error goto 0
 
   '=== code asp pour sortir le résultat en html
   'here we have stock price text file in format: Date High Low Close Volume
   'Response.Write "<TR bgcolor=lightblue><TD>" & rs_01(0) & "</TD><TD>" & rs_01(1) _
   '& "</TD><TD>" & rs(2) & "</TD><TD>" & rs(3) & "</TD><TD> " & rs(4) & "</TD></TR>"
 
   'enregistrement suivant
   rs_01.MoveNext
   ii=ii+1
 
   if ii/upd_ii =int(ii/upd_ii) then objOutputFile.WriteLine date & " " & time & " " & ii & " record"
 
Wend
 
'=========================== end of main loop ===========================================
 
If Err <> 0 Then
   MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
'   count=count+1
  
'loop
 
'=== efface elements
'sql = "DELETE FROM cds_cat WHERE catid = 'test'"
'set bine = con_02.Execute(sql)
 
  'readfile.close
  set readfile=nothing
  set fs=nothing
  'rs.close
  set rs=nothing
  con_02.close
  set con_02=nothing
  con_01.close
  set con_01=nothing
  con_03.close
  set con_03=nothing
  con_04.close
  set con_04=nothing
 
objOutputFile.WriteLine date & " " & time & " " & ii & " record(s). end of importations"
'=== ferme le fichier text de log file
objOutputFile.Close
Set objFileSystem = Nothing
 
objOutputFile02.WriteLine date & " " & time & " " & db_main & "   " & nom_table & "   " & ii & "   total des records "
 
Loop
 
objOutputFile02.WriteLine date & " " & time & " " & db_main & " FIN "
objOutputFile02.Close
 
'=== database finie === on crée un fichier bidon pour indiquer que toute la database est fini d'importer
Set objFileSystem = CreateObject("Scripting.fileSystemObject")
Set objOutputFile02 = objFileSystem.OpenTextFile(base_dir & "\zz_csv_to_sql_log_fini_TOUT.txt", 8, true)
objOutputFile02.WriteLine date & " " & time & " fini TOUTES LES TABLES "
objOutputFile02.Close
 
Set objFileSystem = Nothing
 
'==========================================================================================
'=== SUB fonction pour extraire une variable d'environnement du dos
Function Environ(VarName) 
    Dim wss, env 
    Set wss = CreateObject("WScript.Shell") 
    Set env = wss.environment("process") 
    Environ = env(VarName) 
    If Environ = "" Then 
        Set env = wss.environment("system") 
        Environ = env(VarName) 
    End If 
End Function
 
'===========================================================================================
'=== fonction verify if table exist, if not create it with a first column
function VER_CRE_db_TAB(db, table, col_clef_form, col_clef_name, primaire)
 
   sql = "SELECT COUNT(*) As 'tableCount' FROM " & db & ".dbo.sysObjects WHERE name = '" & table & "'"
 
   faisrequete10fois sql, ecmax
 
   '========== la table n'existe pas, on la crée avec une colonne de base
   if tag("tableCount") = 0 Then
     'msgbox(table_clef_name)
   
   '  sql = "CREATE TABLE " & nom_db & ".dbo." & nom_table & "(" & rs_04(table_clef).name & " " & rs_03(rs_04(table_clef)-1000) & ")"
     sql = "CREATE TABLE " & db & ".dbo." & table & "(" & col_clef_name & " " & col_clef_form & " PRIMARY KEY)"
 
     'msgbox(sql)
   
     faisrequete10fois sql, ecmax
 
     objOutputFile.WriteLine date & " " & time & " table CREATE table & clef de base" & db & " " & col_clef_name
     'msgbox("SQL TABLE ajoutée")
 
   '========== la table existe, on crée la colonne seulement si elle n'existe pas
   else
 
     '=== compte le nombre de colonne avec le nom qu'on cherche
     '=== count the number of columns that have a certain name in the database
     sql = "SELECT COUNT(*) As tableCount FROm " & db & ".dbo.syscolumns WHERE ID = (SELECT ID from " & db & ".dbo.sysobjects WHERE name='" & table & "') AND name = '" & col_clef_name & "';"
 
     faisrequete10fois sql, ecmax
   
     'msgbox(tag("tableCount") & " " & sql)
 
     if tag("tableCount") = 0 then
        '=== on ajoute une colonne de données
        '=== add a column in database
 
        on error resume next
        '=== colonne
        If Err.number <> 0 Then
           'MsgBox Err.Source & "-->" & Err.Description, , "Error"
           objOutputFile.WriteLine date & " " & time & "  " & ii
           objOutputFile.WriteLine date & " " & time & "  " & db & "   " & table & " " & sql
           objOutputFile.WriteLine date & " " & time & "  " & err.description
           objOutputFile.WriteLine date & " " & time & "  " & col_clef_name
           objOutputFile02.WriteLine date & " " & time & " " & db & " table manquante " & col_clef_name
           err.clear
           wscript.quit
        End If
        '=== ajoute colonne
        sql = "ALTER TABLE " & db & ".dbo." & table & " ADD " & col_clef_name & " " & col_clef_form
        '=== commentaire quand on ajoute une colonne
        objOutputFile.WriteLine date & " " & time & "  " & "ajout d'une colonne  " & sql
        on error resume next
        
        faisrequete10fois sql, ecmax
 
     end if
  end if
  
end function
 
'======================= function qui fais une requête 10 fois avant d'Abandonner
function faisrequete10fois(sql,ecmax)
      ec=0
      err.clear
      while ec<ecmax
         set tag = con_02.execute(sql)
 
         '=== if there is a null value on main key, error here
         If Err.number <> 0 Then
            'MsgBox Err.Source & "-->" & Err.Description, , "Error"
            objOutputFile.WriteLine date & " " & time & "  " & ii
            objOutputFile.WriteLine date & " " & time & "  " & db_main & "   " & nom_table & "   " & sql
            objOutputFile.WriteLine date & " " & time & "  " & err.description
            objOutputFile.WriteLine date & " " & time & "  " & rs_01(table_clef).name
            objOutputFile.WriteLine date & " " & time & "  " & tag("tablecount")
            objOutputFile02.WriteLine date & " " & time & " apres " & ec & " essaie la database ne repond toujours pas"
          
            ec2 = instr(err.description,"Ce serveur SQL n'existe pas ou son accès est refusé")
            if ec2 = 0 then 
               ec2 = instr(err.description,"Délai d'attente expiré")
            end if
            
            if ec2 = 0 or ec > ecmax-1 then
               if ec2 = 0 then
                  objOutputFile02.WriteLine date & " " & time & " erreur qui n'est pas une lenteur"
                  objOutputFile02.WriteLine date & " " & time & " " & err.description
                  objOutputFile02.WriteLine date & " " & time & " le script est arrêté!!!"
                  wscript.quit
               end if
               objOutputFile02.WriteLine date & " " & time & " apres " & ec & " essaie la database ne repond toujours pas"
               objOutputFile02.WriteLine date & " " & time & " nombre d'essai maximum " & ecmax-1
               objOutputFile02.WriteLine date & " " & time & " le script est arrêté!!!"
               wscript.quit
            else
            '=== erreur ou lenteur de communication possible, on compte une erreur et le while recommence
                ec = ec + 1
            end if
         else
            '=== pas d'Erreur du tout, on va sortir du while
            if ec<>0 then
               objOutputFile02.WriteLine date & " " & time & " database repartie"
            end if
            ec=1000
         End If
      wend
 
end function

Open in new window

0
 
LVL 12

Expert Comment

by:Serge Fournier
ID: 21836102
do not be fooled:
database chek if exist: (vbs)
   sql = "SELECT COUNT(*) As 'tableCount' FROM " & db & ".dbo.sysObjects WHERE name = '" & table & "'"

even if the variable is called "tablecount" it actually chek if the DATABASE exist

0
 
LVL 12

Expert Comment

by:Serge Fournier
ID: 21836117
will i finish theses post one day?

also, do not worry about the ID01 column (index), it will autoincrement itself when you insert a new element in the database

0
 
LVL 12

Expert Comment

by:Serge Fournier
ID: 21836119
now what you should do in sql query is:
creating the database first with the id01 index
alter database to insert the other columns

inform me what is the speed difference with just that modification (having an id01 autoincrement index)


0
 
LVL 10

Assisted Solution

by:Banthor
Banthor earned 250 total points
ID: 21836314
I DISAGREE COMPLETELY WITH THE ABOVE.
THE ISSUE IS MOST LIKEY THE UNCOMMITTED STATE OF THE TRANSACITONS AND LOGGING.

I would first make sure that My database was in simple recovery mode.
I would then Manually increase the file size in both my destination and the  tempdb to allow space for the process. This will prevent delays while the datbase resizes every 10% (Standard Default)
I would make sure that all files for the database and the tempdb are on drives unfettered by other activity if possible. (avoid putting SQL Database files on the same drive as LazyDudes Porn Videos)

Then I would backup my destination
DROP ALL INDEXES, CONSTRAINTS, and FORIEGN KEYS on the tables
Insert the data in as small MANAGEABLE bites.

Then run scripts to validate and or repair invalid data.
Then re-apply the Keys, Statistics, Indexes, Statistics, Constraints and then statistics again.
In That Order !

for instance simply adding the word
 GO
After every 10, 100, or  1000 transactions. Would cause the data to be committed.
saving both RAM, PAGE, and Log space.

The last database I optimized was accepting 240 inserts with 14 columns one of which is an 8K digital image of the current heart beat every minute for upto 12 leads on 300 beds. (only 2 leads could have the 8k binary, and it's only 8k at max sensitivity and heart rate)

that's 864000 Inserts a minute, "Hundreds of Thousands" of inserts.
That is on a server class box, not an underdesk pc.
But you should be able to do 10,000 Inserts a second without a sweat.

0
 
LVL 12

Expert Comment

by:Serge Fournier
ID: 21836337
well, i agree with banthor then

my experience is with updating table and inserting elements that does not exist
not with bulk import, without consideration for already existing data

sry



0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21837062
please try to change you list of x times insert

INSERT INTO VOUCHER VALUES (
'C0000042310','4503546','141381','5210','0','0','1','0','0',' ',' ','0','S','140','IC     ','25','838255','SPRING 2006   ','               ','20060713','20060718','20060717',     31620.28,          .00,     31620.28,        .00,     31620.28,'0140002100',          .00,'VPJ172','06','07','17','2006-07','06','          '
);
INSERT INTO VCHR VALUES (
'C0000042310','4509329','141381','5210','0','0','1','0','0',' ',' ','0','S','140','IC     ','25','840756','SUMMER 2006   ','               ','20060919','20060921','20060920',     25929.28,          .00,     25929.28,        .00,     25929.28,'0140002100',          .00,'VPJ201','06','09','20','2006-07','09','          '
);

into something like this:


INSERT INTO VOUCHER  SELECT
'C0000042310','4503546','141381','5210','0','0','1','0','0',' ',' ','0','S','140','IC     ','25','838255','SPRING 2006   ','               ','20060713','20060718','20060717',     31620.28,          .00,     31620.28,        .00,     31620.28,'0140002100',          .00,'VPJ172','06','07','17','2006-07','06','          '
UNION ALL SELECT
'C0000042310','4509329','141381','5210','0','0','1','0','0',' ',' ','0','S','140','IC     ','25','840756','SUMMER 2006   ','               ','20060919','20060921','20060920',     25929.28,          .00,     25929.28,        .00,     25929.28,'0140002100',          .00,'VPJ201','06','09','20','2006-07','09','          '
UNION ALL SELECT ... etc etc etc...
0
 

Accepted Solution

by:
roymene earned 0 total points
ID: 21848961
Thank you all for your suggestions.

Banthor: I have tried your suggestions and agree that the problem must be due to UNCOMMITTED STATE OF THE TRANSACITONS AND LOGGING.  I've inserted a 'GO' after every INSERT (because it was easiest for me to do it that way) and that has increased speed by 5x.  The other suggestions make a lot of sense and I will implement them, however at this writing they don't seem to make much of a difference.  

At this point I am able to process about 250 INSERTs per second -- still slower than I would have expected so I need to test further ...  

angellll: I have not yet had a chance to try your suggestion.

I will experiment further and report findings.

Thanks again to all of you.

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
convert in derived column 7 30
MS SQL / SQL Server Native Client -- how to prevent seeing other servers? 2 25
SSRS Enable Remote Errors 4 26
SQL server vNext 18 29
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question