Solved

SQL script running extremely slowly

Posted on 2008-06-20
12
1,228 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 11

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 11

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 11

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
 
LVL 11

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 11

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 11

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 11

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 11

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now