November 6, 2007 at 5:43 am
Hi,
I am currently building a system to upload cruise ship holiday offers from flat files to SQL Server 2005.
I have uploaded data from a flat file (which was 26MB) into SQL Server 2005 to a table called "Princess1Tmp" for the cruise ship holiday offers, and have achieved this no problem using SSIS.
The next step required me to obtain the cruise ship ID from the "amCruiseShip" table and insert the cruise offers from "Princess1Tmp" into the "cruiseOffers" table with their respective Cruise Ship ID.
I have achieved this part of the process using VB Script and although it works, I was wandering if anyone new any way to speed up the second step.
I have listed my code below, if anyone has any ideas or could point me in the right direction it would be greatly appreciated.
Many Thanks Mark
'##################################################
'########## Princess sub-procedure 1. ######################
'##################################################
sub princessInsert1()
'SQL statement to select data from "Princess1Tmp table".
sql = "SELECT Princess1Tmp.Cruise_Number, Princess1Tmp.Embark_Date, Princess1Tmp.Air_City, Princess1Tmp.Air_Indicator, " &_
"Princess1Tmp.category, Princess1Tmp.Category_Status, Princess1Tmp.Best_Price, " &_
"Princess1Tmp.Brochure_Price, Princess1Tmp.Duration, " &_
"CarnivalGroupVoyageDataTmp.ship_code, CarnivalGroupVoyageDataTmp.brand " &_
"FROM Princess1Tmp " &_
"INNER JOIN CarnivalGroupVoyageDataTmp ON CarnivalGroupVoyageDataTmp.voyage_code = Princess1Tmp.Cruise_Number " &_
"WHERE CarnivalGroupVoyageDataTmp.brand = 'PC'"
set conn = createObject("ADODB.Connection") 'Assign the database connection object to variable.
conn.Open CONNECT 'Open the database connection using the CONNECT variable.
set rs = createObject("ADODB.Recordset") 'Assign the database recordset object to variable.
rs.Open sql, conn 'Open the recordset with the values of the SQL statement.
WScript.StdOut.WriteLine("Princess 1 Script begin") 'Script processing indicator.
if rs("Cruise_Number") <> "" or rs("Best_Price") <> "" or rs("Brochure_Price") <> "" then
while not rs.EOF 'Loop while there are records to process.
strShipCode = rs("ship_code") 'The Ship_Code recordset is in the correct format.
sql1 = "SELECT ID FROM amCruiseShips WHERE cruiseLineID = '13' AND code = '" & strShipCode & "'" 'SQL statement to retrieve ship ID.
set rs1 = createObject("ADODB.Recordset") 'Assign the database recordset object to variable.
rs1.Open sql1, conn 'Open the recordset with the values of the SQL statement.
if not rs1.EOF then 'Loop while there are records to process.
cruiseShipID = rs1("ID") 'Assign Ship ID to variable.
end if
rs1.Close 'Close the recordset object variable.
set rs1 = nothing 'Set the recordset object variable to nothing.
sql = "INSERT INTO CruiseOffersTmp (voyageNumber, departureAirport, duration, departureDate, availability, cabinGrade, " &_
"cruiselineID, cruiseshipID, bestPrice, brochurePrice, uploadEntryDate, flyCruise) " &_
"VALUES " &_
"('" & rs("Cruise_Number") & "', '" & rs("Air_City") & "', '" & rs("Duration") & "', '" & rs("Embark_Date") & "', " &_
"'" & rs("Category_Status") & "', '" & rs("category") & "', '13', '" & cruiseShipID & "', " &_
"convert(money,'" & rs("Best_Price") & "'), convert(money,'" & rs("Brochure_Price") & "'), '" & now() & "', "&_
"'" & rs("Air_Indicator") & "')"
conn.Execute(sql) 'Open the database connection using the sql statement
rs.moveNext() 'Move to the next recordset item.
wend
end if
WScript.StdOut.WriteLine("Princess 1 Script complete") 'Script comlete indicator.
rs.Close 'Close the recordset object variable.
set rs = nothing 'Set the recordset object variable to nothing.
conn.Close 'Close the database connection.
set conn = nothing 'Set the connection object variable to nothing.
end sub
November 6, 2007 at 7:36 am
Heh... you've basically pulled the wings off of any chance of performance by using outside code to do something inside the database... you said that you want to import 26MB and then do that post processing of using a cruise ship ID to insert into another table...
If you think you simply must use SSIS for the import instead of using really high speed functionality like BULK INSERT, I supposed that's ok (not for me 😉 ) but using VBS to copy rows one at a time from one table to another is RBAR on steroids... why not write a nice little stored procedure to do it for you and call the sproc?
By the way, I don't know what your data actually looks like, but if it's trully a flat file, BULK INSERT will import 26MB of data in the proverbial blink of an eye as will the query you need.
--Jeff Moden
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply