roymene
asked on
SQL script running extremely slowly
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\sqlcm d" -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?
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\sqlcm
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',' '
);
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
fltime is the column name that will be aligned with hard disk sector at the next sql defragmenting database
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)
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)
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
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
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
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
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
also, do not worry about the ID01 column (index), it will autoincrement itself when you insert a new element in the database
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)
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)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
my experience is with updating table and inserting elements that does not exist
not with bulk import, without consideration for already existing data
sry
please try to change you list of x times insert
INSERT INTO VOUCHER VALUES (
'C0000042310','4503546','1 41381','52 10','0','0 ','1','0', '0',' ',' ','0','S','140','IC ','25','838255','SPRING 2006 ',' ','20060713','20060718','2 0060717', 31620.28, .00, 31620.28, .00, 31620.28,'0140002100', .00,'VPJ172','06','07','17 ','2006-07 ','06',' '
);
INSERT INTO VCHR VALUES (
'C0000042310','4509329','1 41381','52 10','0','0 ','1','0', '0',' ',' ','0','S','140','IC ','25','840756','SUMMER 2006 ',' ','20060919','20060921','2 0060920', 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','1 41381','52 10','0','0 ','1','0', '0',' ',' ','0','S','140','IC ','25','838255','SPRING 2006 ',' ','20060713','20060718','2 0060717', 31620.28, .00, 31620.28, .00, 31620.28,'0140002100', .00,'VPJ172','06','07','17 ','2006-07 ','06',' '
UNION ALL SELECT
'C0000042310','4509329','1 41381','52 10','0','0 ','1','0', '0',' ',' ','0','S','140','IC ','25','840756','SUMMER 2006 ',' ','20060919','20060921','2 0060920', 25929.28, .00, 25929.28, .00, 25929.28,'0140002100', .00,'VPJ201','06','09','20 ','2006-07 ','09',' '
UNION ALL SELECT ... etc etc etc...
INSERT INTO VOUCHER VALUES (
'C0000042310','4503546','1
);
INSERT INTO VCHR VALUES (
'C0000042310','4509329','1
);
into something like this:
INSERT INTO VOUCHER SELECT
'C0000042310','4503546','1
UNION ALL SELECT
'C0000042310','4509329','1
UNION ALL SELECT ... etc etc etc...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Open in new window