Question
· Dec 2, 2016

What are best ways to query large tables?

We don't often use SQL within our org, which is mostly due to the performance issue we experience due to the quantity of data we are reviewing.

Aside from the standard performance measures for non-Caché databases, are there any recommended approaches when querying large tables?

The table would have roughly 50M records, but there are not a finite amount of sub-nodes.

Discussion (7)1
Log in or sign up to continue

The class referencing the account global doesn't even finish a COUNT (ex. SELECT COUNT(acctID) FROM namespace.account). The global itself looks like the following, which have multiple parent and child nodes as well as varying numbers of positions for each. I've posted the structure below.

global(acctID,parent):positions|child:positions|...

account(acctID,10):25|1:12|2:37|3:35|4:30|5:7|6:28|7:21|8:17
account(acctID,12):71|1:9|2:18|3:4|4:8|5:18|6:2|7:2|8:8|9:16|10:12
account(acctID,16):28|1:13|2:46|3:7|4:8
account(acctID,20):7|1:85|2:49|3:28|4:8|5:14|6:4|7:13|8:35|9:2
account(acctID,27):45|1:13|2:40|3:40|4:1
account(acctID,30):13|1:5|2:22|3:22|4:22|6:28|7:12|8:10|9:3|10:3|11:5|12:22
account(acctID,31):16|1:2|2:2|3:38|4:35|5:7|6:1|7:45|8:10|9:10
account(acctID,40):42|1:44|2:11|3:12
account(acctID,50):79|1:50|2:14|3:48|4:20
account(acctID,60):19|1:24|2:10|3:20|4:24|5:24
account(acctID,70):7|1:32|2:9|3:9|4:23|5:15|6:10|7:8|8:6
account(acctID,90):1|1:9|2:9|3:10|4:10|5:15|6:12|7:5|8:4|9:4|10:4|11:13|12:3|13:5|14:22|15:22|16:22|17:5|18:13
 

The first question for any SQL performance issue is:  Have you run TuneTable?

It is very important to have correct values for ExtentSize, Selectivity and Block Count, whether the info comes from a developer manually entering it or by running TuneTable.  With out these values the Query Optimizer can only guess what the right plan might be.

The next step is to provide proper indices to support the queries you are going to write.  Without have a lot more info it is impossible to tell you what those might be or what type of index would be best (bitmap or standard).