[dba-SQLServer] [dba-VB] VB.Net / SQl Server 2005 - Export filegenerator

jwcolby jwcolby at colbyconsulting.com
Fri Jun 22 20:28:49 CDT 2007


>Are you using OleDB or the native SQL client driver to interface with SQL
server? 

That is a good question...

"Provider=SQLOLEDB;Data Source = Azul;Initial Catalog = PSM;Integrated
Security=SSPI"

I assume that is OLEDB.
 


John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Eric Barro
Sent: Friday, June 22, 2007 6:56 PM
To: dba-vb at databaseadvisors.com; dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] [dba-VB] VB.Net / SQl Server 2005 - Export
filegenerator

Are you using OleDB or the native SQL client driver to interface with SQL
server? 

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, June 22, 2007 3:10 PM
To: dba-sqlserver at databaseadvisors.com; dba-vb at databaseadvisors.com
Subject: [dba-VB] VB.Net / SQl Server 2005 - Export file generator

I need to export all of the records from my list tables out for address
validation, periodically.  Thus I need to be able to specify the source
table, the size (number of records) each file will contain, the number of
records exported etc.

In order to accomplish this I am building a VB.Net application to allow me
to export specific fields of a specific table into files of size X records.
All of the parameters are in tables that I create.  I have it working now,
and it is now dumping the name / address fields from 99 million records, out
to 1 million record files.  It is interesting to note (and the reason for
this email) that getting the data from SQL Server is the slowest part.  I am
building up a query dynamically (yea, I know I should be using a stored
procedure with parameters, that will follow) with the name of the table, the
field names to pull, and the start PKID and end PKIDs.  VB.Net is having
waitlock issues if the time to get the records is too large so I broke it
down to 100K record sets, then export 10 of those recordsets into a single
file.  In average, 100K records is taking about 20-25 seconds to return the
datasets, and then the VB.Net code that writes the file can write the data
out to the file in about 2 seconds.  So on average I am getting a million
record file built in about 100-120 seconds.  My program creates a unique
name for each file and dumps them in a directory specified in the an export
spec table.

This is the second piece of the big picture where a set of data files is
imported in to sql server, then immediately dumped back out for address
validation (this piece) then imported back in to SQL Server for use.  This
same piece will later be used to periodically dump the data tables that have
already been address validated back out to be validated again (NCOAs).  When
finished, this process will run automatically every week (or other time
period I specify) to dump every one of my tables out for a periodic address
(NCOA) validation.

Without automation this process is immensely time intensive, but with
automation it will be... Well... Automatic, with little or no manual
intervention.  So part two is complete, just a few tweaks left (logging
results and such).

John W. Colby
Colby Consulting
www.ColbyConsulting.com 

_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com


 

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list