Database Collation Change - Unusably slow Import/Export Wizard
in [Server Configuration]
|
Prev: What's the next release after SQL Server 2008 R2 & when?
Next: How to CONCATENATE >50 fields in Excel table into SQL Insert State
From: JimLad on 21 Jun 2010 13:34 Hi, I am trying to change the database collation (+ all defaulting columns). I have created a new database with the new collation and have tried to use Import/Export Wizard to transfer the data and create the new tables. IT IS UNUSABLY SLOW... ~5000 records every half hour, or possibly even worse. The old db is in compatibilty mode 2000. Would that be making any difference? Anyone got any ideas? Cheers, James
From: Erland Sommarskog on 21 Jun 2010 17:11 JimLad (jamesdbirch(a)yahoo.co.uk) writes: > I am trying to change the database collation (+ all defaulting > columns). > > I have created a new database with the new collation and have tried to > use Import/Export Wizard to transfer the data and create the new > tables. > > IT IS UNUSABLY SLOW... ~5000 records every half hour, or possibly even > worse. Ouch! > The old db is in compatibilty mode 2000. Would that be making any > difference? Anyone got any ideas? I can't directly see that the compat level of the old table would matter. I don't use the wizard much, but I made a quick test, and it seems to use BULK INSERT which is the fastest method, so that looks good. It also seemed that it loaded the tables in parallel which is even better. More exactly how did you run the wizard? In my quick test, I just clicked Next - and I found that the views were copied as tables! How big is your database? How many tables are there? How many big tables are there? And which version of SQL Server do you use? -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: JimLad on 23 Jun 2010 06:42 On 21 June, 22:11, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > JimLad (jamesdbi...(a)yahoo.co.uk) writes: > > I am trying to change the database collation (+ all defaulting > > columns). > > > I have created a new database with the new collation and have tried to > > use Import/Export Wizard to transfer the data and create the new > > tables. > > > IT IS UNUSABLY SLOW... ~5000 records every half hour, or possibly even > > worse. > > Ouch! > > > The old db is in compatibilty mode 2000. Would that be making any > > difference? Anyone got any ideas? > > I can't directly see that the compat level of the old table would matter. > > I don't use the wizard much, but I made a quick test, and it seems to > use BULK INSERT which is the fastest method, so that looks good. It also > seemed that it loaded the tables in parallel which is even better. > > More exactly how did you run the wizard? In my quick test, I just clicked > Next - and I found that the views were copied as tables! > > How big is your database? How many tables are there? How many big tables > are there? > > And which version of SQL Server do you use? > > -- > Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Hi Erland, Sorry for the delay - I was off yesterday. I am using SQL Server 2005. Database is about 5GB. Original db and new db are both on same disk. I just selected all tables in the wizard, I selected the multiple table optimisations, but not the transactions button. Then ran immediately. There is a very slow connection between my client workstation and the server - I assume that wouldn't slow down an operation like this which would all happen server side? I will report back on this thread if I get anywhere with this. I was just wondering if there were any known issues. I will try again after converting to 2005 compatibility and see if it makes any difference. Cheers, James
From: Erland Sommarskog on 24 Jun 2010 17:39 JimLad (jamesdbirch(a)yahoo.co.uk) writes: > Sorry for the delay - I was off yesterday. I am using SQL Server 2005. > Database is about 5GB. Original db and new db are both on same disk. I > just selected all tables in the wizard, I selected the multiple table > optimisations, but not the transactions button. Then ran immediately. > > There is a very slow connection between my client workstation and the > server - I assume that wouldn't slow down an operation like this which > would all happen server side? Au contraire, the slow connection is surely the culprit. I ran a test where I ran the Import wizard from my laptop and copied a database on my desktop. As the wizard was running, I watched Process Explorer on both machines. I could see no action on the desktop, but DTSWizard.exe more or less pegged one core on the other machine. I also noticed that the copying took a lot longer time that it when tried running the wizard on the same server as the databases are on. (Although in that test I used the SQL 2008 wizard.) I see that the wizard uses bulk which is a fast way to do it. But it has to bulk load from somewhere outside SQL Server, or more particularly: it has to extract the data to disk (or memory), and there has to be a different process than SQL Server for the task. And apparently this is process runs on the machine where you initiated the operation. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: JimLad on 28 Jun 2010 09:36
On 24 June, 22:39, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > JimLad (jamesdbi...(a)yahoo.co.uk) writes: > > Sorry for the delay - I was off yesterday. I am using SQL Server 2005. > > Database is about 5GB. Original db and new db are both on same disk. I > > just selected all tables in the wizard, I selected the multiple table > > optimisations, but not the transactions button. Then ran immediately. > > > There is a very slow connection between my client workstation and the > > server - I assume that wouldn't slow down an operation like this which > > would all happen server side? > > Au contraire, the slow connection is surely the culprit. I ran a test > where I ran the Import wizard from my laptop and copied a database on > my desktop. As the wizard was running, I watched Process Explorer on > both machines. I could see no action on the desktop, but DTSWizard.exe > more or less pegged one core on the other machine. > > I also noticed that the copying took a lot longer time that it when tried > running the wizard on the same server as the databases are on. (Although > in that test I used the SQL 2008 wizard.) > > I see that the wizard uses bulk which is a fast way to do it. But it > has to bulk load from somewhere outside SQL Server, or more particularly: > it has to extract the data to disk (or memory), and there has to > be a different process than SQL Server for the task. And apparently > this is process runs on the machine where you initiated the operation. > > -- > Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Hi Erland, Many thanks. Not much to say is there... that's very bad. I expect the workaround is simply to save as a package and then run the package, rather than running immediately. Thanks again, James |