Home » RDBMS Server » Performance Tuning » Partitioned table takes longer to load than ordinary table
Partitioned table takes longer to load than ordinary table [message #249350] Wed, 04 July 2007 03:01 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: How to use optimizer hint in a recursivly called procedure
Next Topic: Index created is not using(I dont want to use hints)
Goto Forum:
  


Current Time: Fri Dec 06 23:41:41 CST 2024