jwcolby
jwcolby at colbyconsulting.com
Fri Jun 22 20:51:08 CDT 2007
NOLOCK makes no apparent difference. It also says in books online that nolock is the default for a select so it really might not make any difference. 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 David McAfee Sent: Friday, June 22, 2007 6:34 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] VB.Net / SQl Server 2005 - Export file generator 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 > > _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com