Importing Text to SQL Server Waaaaaayyyy Too Slow! 
Author Message
 Importing Text to SQL Server Waaaaaayyyy Too Slow!

Greetings, ADO gurus.  I am trying to import a HUGE text file (500,000+
lines) of fixed column format data into a SQL Server table, using ADO.  The
text data is dirty, so I parse it out line by line, and clean it up.  I am
using a Recordset.UpdateBatch method inside of a Connection.BeginTrans,
.CommitTrans wrapper. A snippet of my code follows. Everything works fine
within the .Addnew loop, but as soon as the code gets to the
'rsJobDetail.UpdateBatch' line, the SQL Server hangs for hours.

There are probably much better, faster ways to do this.  Would anyone care
to share some wisdom?  Thanks in advance!

'Open the import file
Open "\\ECNSQL\Invoicing\DFILE.txt" For Input As #1 Len = 32767  ' Open
Dfile.

'open the now empty Job_Detail table via a stored proc
cmdStoredProc.CommandText = "sp_select_all_Job_Detail"
rsJobDetail.Open cmdStoredProc, , adOpenStatic, adLockBatchOptimistic

'use transactions to batch update, so that
'the entire operation must succeed or be rolled back
conn.BeginTrans

Do Until EOF(1)
   'fetch a line, parse into variables
   Line Input #9, sLine
   job_number = Mid(sLine, 1, 10)
   list_name = Mid(sLine, 11, 32)
   delivery_time = Mid(sLine, 43, 14)
   delivery_status = Mid(sLine, 57, 1)

   If delivery_status = "Y" Then
      'we don't import 'not sends'

      raw_duration = Mid$(sLine, 58, 5)
      'make sure duration is a number
      If IsNumeric(raw_duration) Then
         duration = CLng(raw_duration)
      Else
         duration = 0
      End If

      raw_country_code = Mid$(sLine, 63, 5)
      'make sure country_code is a number
      If IsNumeric(raw_country_code) Then
         country_code = CLng(raw_country_code)
      Else
         country_code = 0
      End If

      delivery_type = Mid(sLine, 118, 1)
      raw_fax_number = Mid(sLine, 68, 20)

      If delivery_type = "F" Then
         'format fax number, remove everything except digits
         fax_number = ParseDigits(raw_fax_number)
      Else
         'leave email address as-is
         fax_number = raw_fax_number
      End If

      detail_ref = Mid(sLine, 88, 20)

      raw_detail_cost = Mid$(sLine, 108, 10)
      'make sure detail_cost is currency
      If IsNumeric(raw_detail_cost) Then
         detail_cost = CCur(raw_detail_cost)
      Else
         detail_cost = 0
      End If

      ' now load into job_detail table
      With rsJobDetail
      .AddNew
         .Fields.Item(1).Value = job_number
         .Fields.Item(2).Value = list_name
         .Fields.Item(3).Value = delivery_time
         .Fields.Item(4).Value = delivery_status
         .Fields.Item(5).Value = duration
         .Fields.Item(6).Value = fax_number
         .Fields.Item(7).Value = country_code
         .Fields.Item(8).Value = detail_ref
         .Fields.Item(9).Value = detail_cost
         .Fields.Item(10).Value = delivery_type
         .Fields.Item(11).Value = process_date
      End With

   End If   'test for job_delivery_status = 'Y'

Loop  'fetch next line from Dfile

'all done, commit new records to database
'******next line just bursts its little brain....
rsJobDetail.UpdateBatch
conn.CommitTrans

'close recordset and connection
rsJobDetail.Close
conn.Close



Tue, 08 Apr 2003 03:00:00 GMT  
 Importing Text to SQL Server Waaaaaayyyy Too Slow!
 Did you consider coding a 'reformatter' that will do the cleaning and format
the out put file so that SQLO Server bcp can read it, and executing bulk
procedure on SQL Server to import the file as a bulk copy operation?  It should
be faster than creating record one at a time.

Quote:

> Greetings, ADO gurus.  I am trying to import a HUGE text file (500,000+
> lines) of fixed column format data into a SQL Server table, using ADO.  The
> text data is dirty, so I parse it out line by line, and clean it up.  I am
> using a Recordset.UpdateBatch method inside of a Connection.BeginTrans,
> .CommitTrans wrapper. A snippet of my code follows. Everything works fine
> within the .Addnew loop, but as soon as the code gets to the
> 'rsJobDetail.UpdateBatch' line, the SQL Server hangs for hours.

> There are probably much better, faster ways to do this.  Would anyone care
> to share some wisdom?  Thanks in advance!

> 'Open the import file
> Open "\\ECNSQL\Invoicing\DFILE.txt" For Input As #1 Len = 32767  ' Open
> Dfile.

> 'open the now empty Job_Detail table via a stored proc
> cmdStoredProc.CommandText = "sp_select_all_Job_Detail"
> rsJobDetail.Open cmdStoredProc, , adOpenStatic, adLockBatchOptimistic

> 'use transactions to batch update, so that
> 'the entire operation must succeed or be rolled back
> conn.BeginTrans

> Do Until EOF(1)
>    'fetch a line, parse into variables
>    Line Input #9, sLine
>    job_number = Mid(sLine, 1, 10)
>    list_name = Mid(sLine, 11, 32)
>    delivery_time = Mid(sLine, 43, 14)
>    delivery_status = Mid(sLine, 57, 1)

>    If delivery_status = "Y" Then
>       'we don't import 'not sends'

>       raw_duration = Mid$(sLine, 58, 5)
>       'make sure duration is a number
>       If IsNumeric(raw_duration) Then
>          duration = CLng(raw_duration)
>       Else
>          duration = 0
>       End If

>       raw_country_code = Mid$(sLine, 63, 5)
>       'make sure country_code is a number
>       If IsNumeric(raw_country_code) Then
>          country_code = CLng(raw_country_code)
>       Else
>          country_code = 0
>       End If

>       delivery_type = Mid(sLine, 118, 1)
>       raw_fax_number = Mid(sLine, 68, 20)

>       If delivery_type = "F" Then
>          'format fax number, remove everything except digits
>          fax_number = ParseDigits(raw_fax_number)
>       Else
>          'leave email address as-is
>          fax_number = raw_fax_number
>       End If

>       detail_ref = Mid(sLine, 88, 20)

>       raw_detail_cost = Mid$(sLine, 108, 10)
>       'make sure detail_cost is currency
>       If IsNumeric(raw_detail_cost) Then
>          detail_cost = CCur(raw_detail_cost)
>       Else
>          detail_cost = 0
>       End If

>       ' now load into job_detail table
>       With rsJobDetail
>       .AddNew
>          .Fields.Item(1).Value = job_number
>          .Fields.Item(2).Value = list_name
>          .Fields.Item(3).Value = delivery_time
>          .Fields.Item(4).Value = delivery_status
>          .Fields.Item(5).Value = duration
>          .Fields.Item(6).Value = fax_number
>          .Fields.Item(7).Value = country_code
>          .Fields.Item(8).Value = detail_ref
>          .Fields.Item(9).Value = detail_cost
>          .Fields.Item(10).Value = delivery_type
>          .Fields.Item(11).Value = process_date
>       End With

>    End If   'test for job_delivery_status = 'Y'

> Loop  'fetch next line from Dfile

> 'all done, commit new records to database
> '******next line just bursts its little brain....
> rsJobDetail.UpdateBatch
> conn.CommitTrans

> 'close recordset and connection
> rsJobDetail.Close
> conn.Close

--
Laura Meyerovich
Magnet Systems



Wed, 09 Apr 2003 10:11:19 GMT  
 Importing Text to SQL Server Waaaaaayyyy Too Slow!
On Fri, 20 Oct 2000 11:37:41 -0700, "Jonathan Currie"

microsoft.public.vb.database:

Quote:
>Greetings, ADO gurus.  I am trying to import a HUGE text file (500,000+
>lines) of fixed column format data into a SQL Server table, using ADO.  The
>text data is dirty, so I parse it out line by line, and clean it up.  I am
>using a Recordset.UpdateBatch method inside of a Connection.BeginTrans,
>.CommitTrans wrapper. A snippet of my code follows. Everything works fine
>within the .Addnew loop, but as soon as the code gets to the
>'rsJobDetail.UpdateBatch' line, the SQL Server hangs for hours.

<snip>

        If you are using SQL7 you might want to take a look at
DTS(Data Transformation Services).  Your file seems to be fixed width
which is not a problem for DTS.  You also might want to import the
data into a "Staged" table then use SQL to do validation against the
"Staged" data.  When the validation is done move the data from the
staged area to the live table(s).  One good thing about this is you
can do all of this from the DTS package you create to do this job.
Plus the package can be run from inside vb or via the command prompt.
One thing is to make sure you have at least sp1 on the SQL server.

        If you have SQL 6.5 you could Bulk Copy the data using the
bcp.exe command line app.  You can find alot of useful examples if the
BOL(Books on Line).

Hope this helps



Wed, 09 Apr 2003 03:00:00 GMT  
 Importing Text to SQL Server Waaaaaayyyy Too Slow!
It doesn't say if there are any indexes setup on the destination table but
another item to be aware of is if you are doing massive imports it is far
more efficient to do it on a table that has no indexes defined.  If the
server has to maintain the indexes as it is loading data the processing will
slow to a crawl.

It is better to do a bulk load and then create the indexes afterwards.

Louis Salas
MuniLogic Systems


Quote:
> Greetings, ADO gurus.  I am trying to import a HUGE text file (500,000+
> lines) of fixed column format data into a SQL Server table, using ADO.
The
> text data is dirty, so I parse it out line by line, and clean it up.  I am
> using a Recordset.UpdateBatch method inside of a Connection.BeginTrans,
> .CommitTrans wrapper. A snippet of my code follows. Everything works fine
> within the .Addnew loop, but as soon as the code gets to the
> 'rsJobDetail.UpdateBatch' line, the SQL Server hangs for hours.

> There are probably much better, faster ways to do this.  Would anyone care
> to share some wisdom?  Thanks in advance!

> 'Open the import file
> Open "\\ECNSQL\Invoicing\DFILE.txt" For Input As #1 Len = 32767  ' Open
> Dfile.

> 'open the now empty Job_Detail table via a stored proc
> cmdStoredProc.CommandText = "sp_select_all_Job_Detail"
> rsJobDetail.Open cmdStoredProc, , adOpenStatic, adLockBatchOptimistic

> 'use transactions to batch update, so that
> 'the entire operation must succeed or be rolled back
> conn.BeginTrans

> Do Until EOF(1)
>    'fetch a line, parse into variables
>    Line Input #9, sLine
>    job_number = Mid(sLine, 1, 10)
>    list_name = Mid(sLine, 11, 32)
>    delivery_time = Mid(sLine, 43, 14)
>    delivery_status = Mid(sLine, 57, 1)

>    If delivery_status = "Y" Then
>       'we don't import 'not sends'

>       raw_duration = Mid$(sLine, 58, 5)
>       'make sure duration is a number
>       If IsNumeric(raw_duration) Then
>          duration = CLng(raw_duration)
>       Else
>          duration = 0
>       End If

>       raw_country_code = Mid$(sLine, 63, 5)
>       'make sure country_code is a number
>       If IsNumeric(raw_country_code) Then
>          country_code = CLng(raw_country_code)
>       Else
>          country_code = 0
>       End If

>       delivery_type = Mid(sLine, 118, 1)
>       raw_fax_number = Mid(sLine, 68, 20)

>       If delivery_type = "F" Then
>          'format fax number, remove everything except digits
>          fax_number = ParseDigits(raw_fax_number)
>       Else
>          'leave email address as-is
>          fax_number = raw_fax_number
>       End If

>       detail_ref = Mid(sLine, 88, 20)

>       raw_detail_cost = Mid$(sLine, 108, 10)
>       'make sure detail_cost is currency
>       If IsNumeric(raw_detail_cost) Then
>          detail_cost = CCur(raw_detail_cost)
>       Else
>          detail_cost = 0
>       End If

>       ' now load into job_detail table
>       With rsJobDetail
>       .AddNew
>          .Fields.Item(1).Value = job_number
>          .Fields.Item(2).Value = list_name
>          .Fields.Item(3).Value = delivery_time
>          .Fields.Item(4).Value = delivery_status
>          .Fields.Item(5).Value = duration
>          .Fields.Item(6).Value = fax_number
>          .Fields.Item(7).Value = country_code
>          .Fields.Item(8).Value = detail_ref
>          .Fields.Item(9).Value = detail_cost
>          .Fields.Item(10).Value = delivery_type
>          .Fields.Item(11).Value = process_date
>       End With

>    End If   'test for job_delivery_status = 'Y'

> Loop  'fetch next line from Dfile

> 'all done, commit new records to database
> '******next line just bursts its little brain....
> rsJobDetail.UpdateBatch
> conn.CommitTrans

> 'close recordset and connection
> rsJobDetail.Close
> conn.Close



Wed, 09 Apr 2003 03:00:00 GMT  
 Importing Text to SQL Server Waaaaaayyyy Too Slow!
Yes, I considered that, but could not figure out how to run BCP from within
my VB code.  (hey, no laughing back there!).  My end users' abilities are
pretty much limited to posting the text files into some directory and
clicking on the 'go' button.  This is my first blush with ADO against SQL
server, so my abilities are somewhat limited too <g>. Thanks for your input!



Quote:
> Did you consider coding a 'reformatter' that will do the cleaning and
format
> the out put file so that SQLO Server bcp can read it, and executing bulk
> procedure on SQL Server to import the file as a bulk copy operation?  It
should
> be faster than creating record one at a time.


> > Greetings, ADO gurus.  I am trying to import a HUGE text file (500,000+
> > lines) of fixed column format data into a SQL Server table, using ADO.
The
> > text data is dirty, so I parse it out line by line, and clean it up.  I
am
> > using a Recordset.UpdateBatch method inside of a Connection.BeginTrans,
> > .CommitTrans wrapper. A snippet of my code follows. Everything works
fine
> > within the .Addnew loop, but as soon as the code gets to the
> > 'rsJobDetail.UpdateBatch' line, the SQL Server hangs for hours.

snip


Fri, 11 Apr 2003 03:00:00 GMT  
 Importing Text to SQL Server Waaaaaayyyy Too Slow!
Yes, I am using SQL7, should have mentioned that.  I like your DTS idea.  Do
you (or anyone else out there) have an example of how to run a DTS package
from within VB?  I fear I am still a bit of a SQL Server / ADO novice.
Thanks all, great feedback!
Quote:

> If you are using SQL7 you might want to take a look at
> DTS(Data Transformation Services).  Your file seems to be fixed width
> which is not a problem for DTS.  You also might want to import the
> data into a "Staged" table then use SQL to do validation against the
> "Staged" data.  When the validation is done move the data from the
> staged area to the live table(s).  One good thing about this is you
> can do all of this from the DTS package you create to do this job.
> Plus the package can be run from inside vb or via the command prompt.
> One thing is to make sure you have at least sp1 on the SQL server.

> If you have SQL 6.5 you could Bulk Copy the data using the
> bcp.exe command line app.  You can find alot of useful examples if the
> BOL(Books on Line).

> Hope this helps



Fri, 11 Apr 2003 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Insert into SQL Server slow, slow, slow...

2. Import text file in SQL Server 6.5 via SQL in Visual Basic

3. Import text file in SQL Server 6.5 via SQL in Visual Basic

4. Import Text(Tab delimited) to SQL Server Table

5. using DTS to import a text file in SQL Server 7.0 with VB6

6. VB Codes to Import Text Files to SQL Server

7. How can I import a text file into a sql server using Visual basic5

8. Import text file int sql server

9. Import xls file into SQL Server using VBscript and exe function on remote server

10. Importing text file slower with VB6???

11. Importing Text File into DataBase Slow !

12. Importing Text File into DataBase Slow !

 

 
Powered by phpBB® Forum Software