| Partitioned table takes longer to load than ordinary table [message #249350] |
Wed, 04 July 2007 03:01  |
kpremsagar
Messages: 26
Registered: June 2007
|
Junior Member |
|
|
Hi,
Recently I have been test loading a partition table (Datawarehouse fact table). However, I notice almost 2 to 2.5 jump in timing compared to the non-partitioned table.
The partition is on the TIME Key on the basis of monthly range. Since this key is not entirely consistent (less than 1% cases) and can change sometimes, I have enabled row movement.
Is there anything that you can do to improve timing when everything else remains same?
Regards
Prem
|
|
|
|
| Re: Partitioned table takes longer to load than ordinary table [message #249956 is a reply to message #249350] |
Fri, 06 July 2007 10:44  |
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
|
Senior Member |
|
|
| kpremsagar wrote on Wed, 04 July 2007 02:01 | Hi,
Recently I have been test loading a partition table (Datawarehouse fact table). However, I notice almost 2 to 2.5 jump in timing compared to the non-partitioned table.
The partition is on the TIME Key on the basis of monthly range. Since this key is not entirely consistent (less than 1% cases) and can change sometimes, I have enabled row movement.
Is there anything that you can do to improve timing when everything else remains same?
Regards
Prem
|
Are you talking about DML or select?
|
|
|
|
| Re: Partitioned table takes longer to load than ordinary table [message #250206 is a reply to message #249956] |
Mon, 09 July 2007 02:32 |
kpremsagar
Messages: 26
Registered: June 2007
|
Junior Member |
|
|
Hi,
I am talking abt update/insert statements here.
(Though, it will be handled by an ETL tool)
Regards
Prem
One more thing: I tried a manual SQL query on the tables.
The update query takes 10 mins for the partitioned table against 30 secs for the normal table.
The insert too takes 3-4 times longer than normal table.
Any suggestions?
Thanks
Prem
|
|
|
|
| Re: Partitioned table takes longer to load than ordinary table [message #250216 is a reply to message #250206] |
Mon, 09 July 2007 03:02 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
This came up once before in the forum here, but we never got feedback from the OP. There is no reason I know why - other than row movement - it should be so much slower just because the table is partitioned.
You should benchmark both jobs with the same data in the target table: one with a partitioned table, one non-partitioned. Trace both and run the results through TK*Prof.
Try it with inserts only and separately with updates only. For the updates, try separate runs with row-movement and without row-movement.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
