|
|
You're in the
sql server msde
group:Slow performance in adding records to msde table imported from dbase.
sql server msde:
I imported dbf files (dbase) into an msde database. I see the tables with the data in the msde database. The data is identical. The only change made to the code is in the DSN assignment in the Recordset's GetDefaultConnect(), return _T("ODBC;DSN=themsde"). Both DSN's are ODBC, the original uses Microsoft dBase driver (dbf), the msde is of course SQL Server. Using the SAME data and the SAME code I add a record (AddNew()) the Recordset.Open() takes about 4 or 5 times longer with the msde dsn the Recordset.Update() takes about 10 times longer (eg. 10 seconds with the msde dsn, less than 1 with dbase odbc dsn) Are there changes that have to be made in the code or in the data? Thanks.
Hi Charles,
Rather than focusing on the change in DSNs I think you should focus on a much more fundamental change namely the fact that you have changed the RDBMS. I would start by adding a few relevant indexes to your tables and if they already exist try updating your statistics. Jonathan [quoted text, click to view] CharlesC wrote:
> Hello, > > I imported dbf files (dbase) into an msde database. > > I see the tables with the data in the msde database. The data is identical. > > The only change made to the code is in the DSN assignment in the Recordset's > GetDefaultConnect(), > return _T("ODBC;DSN=themsde"). > > Both DSN's are ODBC, the original uses Microsoft dBase driver (dbf), > the msde is of course SQL Server. > > Using the SAME data and the SAME code I add a record (AddNew()) > the Recordset.Open() takes about 4 or 5 times longer with the msde dsn > the Recordset.Update() takes about 10 times longer (eg. 10 seconds with the > msde dsn, less than 1 with dbase odbc dsn) > > Are there changes that have to be made in the code or in the data? > > Thanks. >
Differences between odbc dbase and sql required adjustments to table design
and filters. Thanks anyway. [quoted text, click to view] "CharlesC" <Charles@bscinforma.com> wrote in message news:u$UMo7gyHHA.3772@TK2MSFTNGP06.phx.gbl... > Hello, > > I imported dbf files (dbase) into an msde database. > > I see the tables with the data in the msde database. The data is identical. > > The only change made to the code is in the DSN assignment in the Recordset's > GetDefaultConnect(), > return _T("ODBC;DSN=themsde"). > > Both DSN's are ODBC, the original uses Microsoft dBase driver (dbf), > the msde is of course SQL Server. > > Using the SAME data and the SAME code I add a record (AddNew()) > the Recordset.Open() takes about 4 or 5 times longer with the msde dsn > the Recordset.Update() takes about 10 times longer (eg. 10 seconds with the > msde dsn, less than 1 with dbase odbc dsn) > > Are there changes that have to be made in the code or in the data? > > Thanks. > >
Ah, using a Recordset or any ADO classic method to add bulk rows to a SQL
Server table will result in terrible performance. The correct approach when importing data is to use SSIS, DTS, Bulk Copy or the TSQL bulk copy methods. -- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant, Dad, Grandpa Microsoft MVP INETA Speaker www.betav.com www.betav.com/blog/billva Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ----------------------------------------------------------------------------------------------------------------------- [quoted text, click to view] "CharlesC" <Charles@bscinforma.com> wrote in message
news:eEfQXO4%23HHA.5948@TK2MSFTNGP04.phx.gbl... > Differences between odbc dbase and sql required adjustments to table > design > and filters. > Thanks anyway. > > "CharlesC" <Charles@bscinforma.com> wrote in message > news:u$UMo7gyHHA.3772@TK2MSFTNGP06.phx.gbl... >> Hello, >> >> I imported dbf files (dbase) into an msde database. >> >> I see the tables with the data in the msde database. The data is > identical. >> >> The only change made to the code is in the DSN assignment in the > Recordset's >> GetDefaultConnect(), >> return _T("ODBC;DSN=themsde"). >> >> Both DSN's are ODBC, the original uses Microsoft dBase driver (dbf), >> the msde is of course SQL Server. >> >> Using the SAME data and the SAME code I add a record (AddNew()) >> the Recordset.Open() takes about 4 or 5 times longer with the msde dsn >> the Recordset.Update() takes about 10 times longer (eg. 10 seconds with > the >> msde dsn, less than 1 with dbase odbc dsn) >> >> Are there changes that have to be made in the code or in the data? >> >> Thanks. >> >> > >
But that is not what I was doing, the data had already been imported.
Thanks. [quoted text, click to view] "William Vaughn" <billvaNoSPAM@betav.com> wrote in message news:ON0ma16%23HHA.4460@TK2MSFTNGP02.phx.gbl... > Ah, using a Recordset or any ADO classic method to add bulk rows to a SQL > Server table will result in terrible performance. The correct approach when > importing data is to use SSIS, DTS, Bulk Copy or the TSQL bulk copy methods. > > -- > ____________________________________ > William (Bill) Vaughn > Author, Mentor, Consultant, Dad, Grandpa > Microsoft MVP > INETA Speaker > www.betav.com > www.betav.com/blog/billva > Please reply only to the newsgroup so that others can benefit. > This posting is provided "AS IS" with no warranties, and confers no rights. > __________________________________ > Visit www.hitchhikerguides.net to get more information on my latest book: > Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) > and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) > -------------------------------------------------------------------------- --------------------------------------------- > > "CharlesC" <Charles@bscinforma.com> wrote in message > news:eEfQXO4%23HHA.5948@TK2MSFTNGP04.phx.gbl... > > Differences between odbc dbase and sql required adjustments to table > > design > > and filters. > > Thanks anyway. > > > > "CharlesC" <Charles@bscinforma.com> wrote in message > > news:u$UMo7gyHHA.3772@TK2MSFTNGP06.phx.gbl... > >> Hello, > >> > >> I imported dbf files (dbase) into an msde database. > >> > >> I see the tables with the data in the msde database. The data is > > identical. > >> > >> The only change made to the code is in the DSN assignment in the > > Recordset's > >> GetDefaultConnect(), > >> return _T("ODBC;DSN=themsde"). > >> > >> Both DSN's are ODBC, the original uses Microsoft dBase driver (dbf), > >> the msde is of course SQL Server. > >> > >> Using the SAME data and the SAME code I add a record (AddNew()) > >> the Recordset.Open() takes about 4 or 5 times longer with the msde dsn > >> the Recordset.Update() takes about 10 times longer (eg. 10 seconds with > > the > >> msde dsn, less than 1 with dbase odbc dsn) > >> > >> Are there changes that have to be made in the code or in the data? > >> > >> Thanks. > >> > >> > > > > >
So, with the ADO.NET 2.0 SqlBulkCopy method you can take an existing array,
DataTable or DataReader and export directly to SQL Server. This technique can move many hundreds of thousands or rows in no time. Even if you can't use ADO.NET, you can use any version of any language to write a delimited file. The BCP command-line utility can read this and import the data. -- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant, Dad, Grandpa Microsoft MVP INETA Speaker www.betav.com www.betav.com/blog/billva Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ----------------------------------------------------------------------------------------------------------------------- [quoted text, click to view] "CharlesC" <Charles@bscinforma.com> wrote in message
news:OxxlBv8%23HHA.3400@TK2MSFTNGP03.phx.gbl... > But that is not what I was doing, the data had already been imported. > Thanks. > > "William Vaughn" <billvaNoSPAM@betav.com> wrote in message > news:ON0ma16%23HHA.4460@TK2MSFTNGP02.phx.gbl... >> Ah, using a Recordset or any ADO classic method to add bulk rows to a SQL >> Server table will result in terrible performance. The correct approach > when >> importing data is to use SSIS, DTS, Bulk Copy or the TSQL bulk copy > methods. >> >> -- >> ____________________________________ >> William (Bill) Vaughn >> Author, Mentor, Consultant, Dad, Grandpa >> Microsoft MVP >> INETA Speaker >> www.betav.com >> www.betav.com/blog/billva >> Please reply only to the newsgroup so that others can benefit. >> This posting is provided "AS IS" with no warranties, and confers no > rights. >> __________________________________ >> Visit www.hitchhikerguides.net to get more information on my latest book: >> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) >> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) >> -------------------------------------------------------------------------- > --------------------------------------------- >> >> "CharlesC" <Charles@bscinforma.com> wrote in message >> news:eEfQXO4%23HHA.5948@TK2MSFTNGP04.phx.gbl... >> > Differences between odbc dbase and sql required adjustments to table >> > design >> > and filters. >> > Thanks anyway. >> > >> > "CharlesC" <Charles@bscinforma.com> wrote in message >> > news:u$UMo7gyHHA.3772@TK2MSFTNGP06.phx.gbl... >> >> Hello, >> >> >> >> I imported dbf files (dbase) into an msde database. >> >> >> >> I see the tables with the data in the msde database. The data is >> > identical. >> >> >> >> The only change made to the code is in the DSN assignment in the >> > Recordset's >> >> GetDefaultConnect(), >> >> return _T("ODBC;DSN=themsde"). >> >> >> >> Both DSN's are ODBC, the original uses Microsoft dBase driver (dbf), >> >> the msde is of course SQL Server. >> >> >> >> Using the SAME data and the SAME code I add a record (AddNew()) >> >> the Recordset.Open() takes about 4 or 5 times longer with the msde dsn >> >> the Recordset.Update() takes about 10 times longer (eg. 10 seconds >> >> with >> > the >> >> msde dsn, less than 1 with dbase odbc dsn) >> >> >> >> Are there changes that have to be made in the code or in the data? >> >> >> >> Thanks. >> >> >> >> >> > >> > >> > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |