Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Query perfomance in large tables.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
chandraBE

New User


Joined: 02 Feb 2010
Posts: 40
Location: Bangalore

PostPosted: Fri Jan 14, 2011 5:16 pm    Post subject: Query perfomance in large tables.
Reply with quote

Hi,

We have one Table with huge data(around 80 Millions of Records) in my Project. We are planning to split the data of this table into two different Tables.

After Splitting, we will have to search the data in both of the tables in place of one table.

I am not sure about the difference in the performance of the query on this table before splitting and after splitting.

Can anyone let me know the impact of splitting the table into two tables. After splitting, perfromance of select query will improve, degrade or it will be neutral?

I have tried this in testing environment but could not significant results.

Thanks,
Chandra
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Fri Jan 14, 2011 6:24 pm    Post subject:
Reply with quote

Why don't you partition? Let DB2 do the work for you and (maybe) benefit of parallel access.
If you do it yourself , you'll need to maintain the code to decide where which row has to go.

A lot depends on what kind of access you do.
For direct random access via an index you could benefit if the levels of the indexes are lower than the nlevel of the big index.

I suspect that as soon as you need to 'union all' the 2 tables or always query both tables, performance will be worse.

Cutting it in 2 won't do much. If you can cut it in 5 or more and application-wise know that you don't need to search all 5 "partitions" you could benefit more.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jan 14, 2011 9:10 pm    Post subject:
Reply with quote

Hello,

You need to consider the SELECTs that will be used (the current ones and the ones that are projected to be needed later) whether the table is split, partitioned, or left alone.

Several of the larger systems i've built and/or supported have tables with several hundred million rows and the online query response time is sub-second. The trick is to only access a few rows in a transaction and access those rows directly (proper design/implementation of the database/tables/keys).

One problem i see more and more is that organizations throw together some kind of table definitions and code and test with a few hundred or a few thousand rows. Of course this will perform wonderfully. I believe that high-volume testing should be done even in unit test - to provent ugly surprises later . . .
Back to top
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Sat Jan 15, 2011 9:11 pm    Post subject:
Reply with quote

You haven't mentioned what kind of data you are storing.

Fx. splitting into separate tables might be feasible if you have historical rows and you want the most current records in one table (those most frequently accessed), and the historical seldom accessed row in another.

If all rows are retrived on equal frequency, I will definitely recommend partioning, with a proper hashing algoritm to distribute rows evenly across the partitions.
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2429
Location: Netherlands, Amstelveen

PostPosted: Sun Jan 16, 2011 2:28 pm    Post subject:
Reply with quote

http://www.ibm.com/developerworks/data/library/techarticle/dm-0605ahuja2/index.html
Back to top
View user's profile Send private message
chandraBE

New User


Joined: 02 Feb 2010
Posts: 40
Location: Bangalore

PostPosted: Mon Jan 17, 2011 5:22 pm    Post subject:
Reply with quote

Thanks you for your quick responses. They were very useful to me.

Since we had historical data in the tables, we wanted to move the old data into a seperate tables.

Thanks & Regards,
Chandra
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am
No new posts To know activities on db2 tables ashek15 DB2 4 Fri Mar 03, 2017 11:23 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts ERM-Diagram of SysIBM-Catalog-Tables Auryn DB2 2 Fri Feb 17, 2017 6:22 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us