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