TPC-H test tools for SAP IQ multiplex

[Former Member]

Hello，

    Is there any TPC-H test tool recommended for SAP IQ multiplex？ I am looking for a tool to do an internal TPC-H test for IQ multiplex.

I wonder if DBGen is suitable for IQ multiplex or not.

Thanks,

Cole Huang

[c_baker · Accepted solution]

We used an internally sourced TPC-H test harness (to comply with TPC-H standards for TPC-H testing) when we did the work for the following whitepapers (which were done on multiplex):

http://scn.sap.com/docs/DOC-42439

http://scn.sap.com/docs/DOC-58998

I am not sure if we can release it publicly however.  (I am looking and adjusting it for the 2.17 anyway).

That being said, you can use dbgen to generate the tests no problem.  Just compile with the following flags set in the makefile (assuming you are on Linux):

DATABASE= SYBASE

MACHINE = LINUX

WORKLOAD = TPCH

The .tbl files generated easily work with LOAD TABLE.  I have attached my 'load_tables.sql' script used in some of my ISUG and TechEd labs that use the dbgen-generated .tbl files to help.

When I developed the workshops, I did notice more compression in IQ16 vs. IQ15.4 (52% vs. 82%) for a 1GB TPC-H input set (thanks to the n-bitFP indexes) for the default, TPC-H allowed indexes (keys and dates).

For multiplex, it also depends on what you want to accomplish.  We did the loads of the data on the co-ordinator node and the power and throughput tests on co-ordinator and reader/writer nodes to show DQP.  You may want to run loads of separate tables on different nodes.  I think in our test harness, we also had the option to have a separate thread run the LINEITEM table load to improve load timings parallelism, even on the co-ordinator node, instead of running the loads serially.

For DQP, we saw only about 2-3 of the queries run DQP in IQ15.4, whereas all but 2 or 3 ran DQP in IQ16.

You may also want to test the difference in using SHARED_TEMP vs. interconnect as your mileage will vary depending on your shared disk configuration and interconnect speed.  (I would strongly suggest reviewing the sizing guide as well - http://scn.sap.com/docs/DOC-46166 to help you in your configuration).

In other words, there are a lot of variables you can still play with to improve or alter TPC-H performance.

HTH

Chris

load_tables.sql.txt.zip

[Former Member · Accepted solution]

Thanks Chris for sharing, it is  very helpful to me.

Cole Huang