|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Help please with updating large amounts of data on SQL Server 2000Hi all,
I am writing an application to look through an Interbase back office database, detect any changes (by looking at a timestamp) since the last 'synch' and write them to the SQL Server 2000 DB. In general it works fine with smaller quantities of data, but when I get into the 10,000s of record it all grinds to a halt. I'm assuming this is a transaction log problem? I've tried batching the records into transactions (typically of 1000 updates) but this doesn't seem to speed things up much. To do the initial load of the database I used a DTS package - and that was astoundingly fast. But in live use I need to trickle changes through rather than truncate the table and bulk copy it. Any ideas where I'm going wrong? Any help greatly appreciated! Can you tell us how you are updating in batches of 10000
using cursors, from the application calling stored procedures? try backing up the log before you do this mass update. Hi
In VB.Net I'm opening the Interbase and getting a DataReader where LastModified >= LastSynchDate I'm then opening a connection to SQL Server, looping through the records and running a stored procedure to insert/update the record by primary key value. I have a configurable transaction size and every time the counter MOD transaction size = 0 I'm doing a commit on the transaction and opening a new transaction. The stored procedure is the format (pseudo code): IF (SELECT PK FROM tablename WHERE PK = @PK) = @PK UPDATE ELSE INSERT I figured this would save two trips to the server (ie one trip to see if the record already exists, and another to Insert or Update) I am running this format on another table and it flies through - 65k records in about 20 seconds. Yet 33k records on another table and it grinds to a halt. The 33k records are substantially bigger with large column sizes, etc. Once it gets past about 3000 inserts it's down to around 1 per second, and on a serious powerful server! I've checked the tables have primary keys / indexes assigned correctly, so can only assume it's the size of each record that's tipping it over the edge. Show quote "Omnibuzz" wrote: > Can you tell us how you are updating in batches of 10000 > using cursors, from the application calling stored procedures? > try backing up the log before you do this mass update. If you can send an XML from VB code.
Then you can send it across as a XML text to the SP.. in batches containing 1000 rows or so. and use OPEN_XML in the SP to do bulk update. That will be faster. Hope this helps -- Show quote"GeorgieBR" wrote: > Hi > > In VB.Net I'm opening the Interbase and getting a DataReader where > LastModified >= LastSynchDate > > I'm then opening a connection to SQL Server, looping through the records and > running a stored procedure to insert/update the record by primary key value. > I have a configurable transaction size and every time the counter MOD > transaction size = 0 I'm doing a commit on the transaction and opening a new > transaction. > > The stored procedure is the format (pseudo code): > IF (SELECT PK FROM tablename WHERE PK = @PK) = @PK > UPDATE > ELSE > INSERT > > I figured this would save two trips to the server (ie one trip to see if the > record already exists, and another to Insert or Update) > > I am running this format on another table and it flies through - 65k records > in about 20 seconds. Yet 33k records on another table and it grinds to a > halt. The 33k records are substantially bigger with large column sizes, etc. > Once it gets past about 3000 inserts it's down to around 1 per second, and on > a serious powerful server! I've checked the tables have primary keys / > indexes assigned correctly, so can only assume it's the size of each record > that's tipping it over the edge. > > > "Omnibuzz" wrote: > > > Can you tell us how you are updating in batches of 10000 > > using cursors, from the application calling stored procedures? > > try backing up the log before you do this mass update. Thanks Omnibuzz, that's well outside my experience range though!
I'm going to explore DTS to see if I can find a way of doing it from there, but reading the last_synch_date from the database so I don't do a Truncate like when I initially load the tables. But thanks for your help! G Show quote "Omnibuzz" wrote: > If you can send an XML from VB code. > Then you can send it across as a XML text to the SP.. in batches containing > 1000 rows or so. > and use OPEN_XML in the SP to do bulk update. > That will be faster. Hope this helps > -- > > > > > "GeorgieBR" wrote: > > > Hi > > > > In VB.Net I'm opening the Interbase and getting a DataReader where > > LastModified >= LastSynchDate > > > > I'm then opening a connection to SQL Server, looping through the records and > > running a stored procedure to insert/update the record by primary key value. > > I have a configurable transaction size and every time the counter MOD > > transaction size = 0 I'm doing a commit on the transaction and opening a new > > transaction. > > > > The stored procedure is the format (pseudo code): > > IF (SELECT PK FROM tablename WHERE PK = @PK) = @PK > > UPDATE > > ELSE > > INSERT > > > > I figured this would save two trips to the server (ie one trip to see if the > > record already exists, and another to Insert or Update) > > > > I am running this format on another table and it flies through - 65k records > > in about 20 seconds. Yet 33k records on another table and it grinds to a > > halt. The 33k records are substantially bigger with large column sizes, etc. > > Once it gets past about 3000 inserts it's down to around 1 per second, and on > > a serious powerful server! I've checked the tables have primary keys / > > indexes assigned correctly, so can only assume it's the size of each record > > that's tipping it over the edge. > > > > > > "Omnibuzz" wrote: > > > > > Can you tell us how you are updating in batches of 10000 > > > using cursors, from the application calling stored procedures? > > > try backing up the log before you do this mass update. The fastest way to get large volumes of data into SQL Server is using a bulk
insert technique. DTS uses SQLOLEDB IRowsetFastLoad. That API can be used in C++ (and perhaps Delphi). Other bulk insert methods: Sql Server BCP command-line utility BULK INSERT Transact-SQL statement ODBC BCP API SqlBulkCopy (.Net 2.0) -- Show quoteHope this helps. Dan Guzman SQL Server MVP "GeorgieBR" <Georgi***@discussions.microsoft.com> wrote in message news:6ABE4A87-963B-4C6D-9BFE-82C2306A0388@microsoft.com... > Hi all, > > I am writing an application to look through an Interbase back office > database, detect any changes (by looking at a timestamp) since the last > 'synch' and write them to the SQL Server 2000 DB. In general it works fine > with smaller quantities of data, but when I get into the 10,000s of record > it > all grinds to a halt. > > I'm assuming this is a transaction log problem? I've tried batching the > records into transactions (typically of 1000 updates) but this doesn't > seem > to speed things up much. > > To do the initial load of the database I used a DTS package - and that was > astoundingly fast. But in live use I need to trickle changes through > rather > than truncate the table and bulk copy it. > > Any ideas where I'm going wrong? Any help greatly appreciated! I'm in VB.Net.
I've used DTS to load the data initially and I loved it - it was blindingly fast. But I couldn't see a way to determine if the record already existed. Perhaps I should be putting this application into a DTS package and letting SQL Server manage how often it runs etc. Show quote "Dan Guzman" wrote: > The fastest way to get large volumes of data into SQL Server is using a bulk > insert technique. DTS uses SQLOLEDB IRowsetFastLoad. That API can be used > in C++ (and perhaps Delphi). > > Other bulk insert methods: > > Sql Server BCP command-line utility > BULK INSERT Transact-SQL statement > ODBC BCP API > SqlBulkCopy (.Net 2.0) > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "GeorgieBR" <Georgi***@discussions.microsoft.com> wrote in message > news:6ABE4A87-963B-4C6D-9BFE-82C2306A0388@microsoft.com... > > Hi all, > > > > I am writing an application to look through an Interbase back office > > database, detect any changes (by looking at a timestamp) since the last > > 'synch' and write them to the SQL Server 2000 DB. In general it works fine > > with smaller quantities of data, but when I get into the 10,000s of record > > it > > all grinds to a halt. > > > > I'm assuming this is a transaction log problem? I've tried batching the > > records into transactions (typically of 1000 updates) but this doesn't > > seem > > to speed things up much. > > > > To do the initial load of the database I used a DTS package - and that was > > astoundingly fast. But in live use I need to trickle changes through > > rather > > than truncate the table and bulk copy it. > > > > Any ideas where I'm going wrong? Any help greatly appreciated! > > > If you need to update and insert, consider a hybrid DTS solution where you
bulk insert into a staging table and then run a proc to insert or update data as needed. Similarly, in .Net 2.0, you have the option to use the SqlBulkCopy object in your VB.Net app to bulk insert data directly. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "GeorgieBR" <Georgi***@discussions.microsoft.com> wrote in message news:710AD140-4368-443F-BCE9-A39446918A27@microsoft.com... > I'm in VB.Net. > > I've used DTS to load the data initially and I loved it - it was > blindingly > fast. But I couldn't see a way to determine if the record already existed. > Perhaps I should be putting this application into a DTS package and > letting > SQL Server manage how often it runs etc. > > > "Dan Guzman" wrote: > >> The fastest way to get large volumes of data into SQL Server is using a >> bulk >> insert technique. DTS uses SQLOLEDB IRowsetFastLoad. That API can be >> used >> in C++ (and perhaps Delphi). >> >> Other bulk insert methods: >> >> Sql Server BCP command-line utility >> BULK INSERT Transact-SQL statement >> ODBC BCP API >> SqlBulkCopy (.Net 2.0) >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> >> "GeorgieBR" <Georgi***@discussions.microsoft.com> wrote in message >> news:6ABE4A87-963B-4C6D-9BFE-82C2306A0388@microsoft.com... >> > Hi all, >> > >> > I am writing an application to look through an Interbase back office >> > database, detect any changes (by looking at a timestamp) since the last >> > 'synch' and write them to the SQL Server 2000 DB. In general it works >> > fine >> > with smaller quantities of data, but when I get into the 10,000s of >> > record >> > it >> > all grinds to a halt. >> > >> > I'm assuming this is a transaction log problem? I've tried batching the >> > records into transactions (typically of 1000 updates) but this doesn't >> > seem >> > to speed things up much. >> > >> > To do the initial load of the database I used a DTS package - and that >> > was >> > astoundingly fast. But in live use I need to trickle changes through >> > rather >> > than truncate the table and bulk copy it. >> > >> > Any ideas where I'm going wrong? Any help greatly appreciated! >> >> >>  |
|||||||||||||||||||||||
Other interesting topics