What are best ways to query large tables?

Question

Question

Mack Altman · Dec 2, 2016

#Databases #ObjectScript #SQL #Performance #Caché

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

Brendan Bannon · Dec 2, 2016

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).

0 0

Brendan Bannon · Dec 6, 2016

Mark

Running TuneTable is safe to do at any time. It will be CPU intensive so you might not want to run it at peak workload times, other than that it is fine to run at any time.

It is important that all your tables have this info so it is great that you are running it on all your tables.

0 0

score 0 · Answer 1 · 2016-12-02T14:05:34-05:00

Well, if you want good performance in SQL Queries, you need indexes. What do your tables look like? We can definitely get fast performance on a 50M row table, it might just take some work (which we're happy to help with).

score 0 · Answer 2 · 2016-12-02T20:42:50-05:00

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

score 0 · Answer 3 · 2016-12-02T20:55:52-05:00

I ran TuneTable on one table and it seemed rather quick. Following that I used the 'TuneTables' option and its been running in the background for about 2 hours now.

There isn't much available in the 2010.2 docbook about it. Are there any best practices to running it?

score 0 · Answer 4 · 2016-12-02T15:49:31-05:00

If you have more recent versions of Cache you will likely benefit by using %PARALLEL especially if you have a large number of cores for your environment.

score 0 · Answer 5 · 2016-12-02T20:56:22-05:00

Mack Altman · Dec 2, 2016

Unfortunately, we are on 2010.1.

0 0