David McAfee
davidmcafee at gmail.com
Fri Jun 22 17:34:29 CDT 2007
John, are you using "NOLOCK" on your selects? SELECT * FROM tblPeople WITH (NOLOCK) and yes, try to use stored procedures whenever possible. On 6/22/07, jwcolby <jwcolby at colbyconsulting.com> wrote: > > 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-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >