 | |
Transform/transfer 50Gb - how to do it fast?
comp.databases.ms-sqlserver comp.databases.ms-sqlserver
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: Transform/transfer 50Gb - how to do it fast? -
04-29-2007
, 03:04 PM
|
I assumed that it is not possible to create a partitioned tables from existing ones. But I have not worked much with partitioned tables, so I could be wrong. |
into a partitioned table with ALTER TABLE...SWITCH PARTITION. The
source/target table must have the same schema (including indexes) and
table/indexes must reside on the same , filegroup(s). Also, the source
table must have a check constraint on the partitioning column to ensure data
is within the target partition boundaries.
One caveat is that the index stats are not updated when data is switched
into the partitioned table so it's probably a good idea to update stats
after SWITCH.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote
Quote:
|
B D Jensen (bjorn.d.jensen (AT) gmail (DOT) com) writes: Functions are written in T-SQL (i also wrote them in CLR, but in this case they were slower). The original columns have incorrect datatypes, that uses too much storage, so the functions check that values are in correct domain and if not they return null - what is a correct result, because the values then are physical impossible. I would recommend that you have the expressions inline, at least if you desire to cut down execution time. I wondered why you only wrote that I can't use "select into" for patitioned tables. I assumed that it is not possible to create a partitioned tables from existing ones. But I have not worked much with partitioned tables, so I could be wrong. -- Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx |
#12
| |||
| |||
|
|
It is possible to move a non-partitioned table (actually a single partition) into a partitioned table with ALTER TABLE...SWITCH PARTITION. The source/target table must have the same schema (including indexes) and table/indexes must reside on the same , filegroup(s). Also, the source table must have a check constraint on the partitioning column to ensure data is within the target partition boundaries. |
Ah, that's great. That would it would be possible for Bjørn to create his
partitions with SELECT INTO, add the required index and constraints nd
then glue them together.
Thanks Dan for the information. ... I really need to start playing with
partitioning some day.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
#13
| |||
| |||
|
#14
| |||
| |||
|
|
Thanks for the details about requirements. But I'm afraid I then must create the newtbl first, because the old table is in the Primary filegroup. And as I see there is no way for saying: select id, func1(col1) into newtbl MYNEWFG from oldtbl. So I think I have look at unload/load now.... |
the default filegroup? You could do that, and then your SELECT INTO tables
should end up there. At least that is what I would expect.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
#15
| |||
| |||
|
|
B D Jensen (bjorn.d.jen... (AT) gmail (DOT) com) writes: Thanks for the details about requirements. But I'm afraid I then must create the newtbl first, because the old table is in the Primary filegroup. And as I see there is no way for saying: select id, func1(col1) into newtbl MYNEWFG from oldtbl. So I think I have look at unload/load now.... But doesn't ALTER DATABASE permit you to specify a different filegroup as the default filegroup? You could do that, and then your SELECT INTO tables should end up there. At least that is what I would expect. -- Erland Sommarskog, SQL Server MVP, esq... (AT) sommarskog (DOT) se Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books... Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |
/Bjorn
#16
| |||
| |||
|
|
B D Jensen (bjorn.d.jen... (AT) gmail (DOT) com) writes: I didn't understand the last part about "ditch" (what means that?). To ditch = slänga, kasta, göra sig av med. Will the use of functions make the select into very slow? Slower. I cannot say how much slower, but I would never use functions for this situation. Since this appears to be a one-off, code maintainability does not seem to be important. -- Erland Sommarskog, SQL Server MVP, esq... (AT) sommarskog (DOT) se Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books... Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |
and wrote a loop going from -1mio to +1mio
using TSQL-function: 72 seconds
using directly between (not in a function): 70 seconds ; it's in
between ;^)
using directly <= and >= : 67seconds
(and CLR-function: 2min 27seconds)
So you are right not writing it in seperate function is faster (in
this case),
so it depends (... ;^) on the situation if the difference is too
costly.
Maybe it's one time only, but if you think you can reuse it, then at
all there is less typing and more
important: your code is much more readable, because it becomes shorter
and much more
natural too read. And if one finds an better implementation, you can
just replace it without affecting depending code.
Again: it depends ;^)
Best regards
Bjørn
#17
| ||||
| ||||
|
|
I made a comparison for the case of converting to tinyint and wrote a loop going from -1mio to +1mio using TSQL-function: 72 seconds using directly between (not in a function): 70 seconds ; it's in between ;^) using directly <= and >= : 67seconds So you are right not writing it in seperate function is faster (in this case), |
T-SQL UDF would be faster.
Then again, with the numbers you present it's dubious whether you actually
have found a significant difference.
Quote:
|
(and CLR-function: 2min 27seconds) |
I once did a test where I had to convert zoned numbers with fixed
decimal from an AS400 system. In that case a CLR function was faster
than all T-SQL solutions. I think I have heard that when you have more
than four operations, the CLR pays off.
Quote:
|
Maybe it's one time only, but if you think you can reuse it, then at all there is less typing and more important: your code is much more readable, because it becomes shorter and much more natural too read. |
Quote:
|
Again: it depends ;^) |
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
#18
| |||
| |||
|
|
This is not correct. INSERT SELECT FROM is a fully-logged operation. You are thinking of SELECT INTO which is a minimally logged operation. That is, all that is logged are the extent allocations. There are no write operations in SQL Server that are entirely non-logged. |
meant.
Regarding the statement that it is a "non logged" operation -
obviously all operations write to the transaction log in some way, I
was just using the term that is in general use that was erroneously
started by Books Online. In any case, as the only records that are
written are merely to log a table's creation the difference is
somewhat irrelevant in this context.
#19
| |||
| |||
|
|
Regarding the statement that it is a "non logged" operation - obviously all operations write to the transaction log in some way, I was just using the term that is in general use that was erroneously started by Books Online. |
talk about minimally logged. I looked in Books Online for SQL 6.5, which
indeed talks about non-logged, but that was loooong ago. And the
architecture was different way backk then.
Quote:
|
In any case, as the only records that are written are merely to log a table's creation. |
the operation failed on illegal convert operation half-way through, you
would be left with a table that would have a couple of rows in it.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.