IQ vs MSSQL in POC.

[former_member199543]

Hello, IQ gurus

At the moment i'm involved in POC project where we compare SQL server 2014 Enterprise and SAP IQ 16.1 . We are testing these 2 DB with sample efashion DW which is shipped with SAP BO, fact table has approx 250m rows. Both are on

the same non virtual windows 2012 R2 server, 1 CPU with 8 cores, 128GB of memory, SSD drives.

Here are the startup parameters for IQ:
-c 256m -gc 300 -gd DBA -gl all -iqmc 10000 -iqtc 15000 -iqrlvmem 600 -gm 50 -iqgovern 26 -cl 500 -ch 500 -m -gn 100 -gt 8 -gtc 8
all tables has HG index on column which is used in joins and default indexes on aggregate columns (ones with sum). Index advisor also does not return any suggestion. Iq options has been attached.

In SQL server I have one non-clustered column-store index set on fact table and primary key on dimension key columns. Even if I replace column-store index with multiple independent
non-clustered indexes on join columns of the fact table, even then SQL runs much faster. SQL has 12gb of memory assigned.

Results:
In SQL it takes 0.2 sec to execute the query and return 12 rows , while in IQ 18 seconds. Not good, because i prefer IQ more than SQL Server.

Here is one of SQL, which we use in comparison. Very simple, is not it:
SELECT
efashion.Calendar_year_lookup.Yr,
efashion.Calendar_year_lookup.Mth,
efashion.Outlet_Lookup.State,
efashion.Outlet_Lookup.City,
sum(efashion.Shop_facts.Amount_sold),
sum(efashion.Shop_facts.Quantity_sold)
FROM
efashion.Calendar_year_lookup,
efashion.Outlet_Lookup,
efashion.Shop_facts
WHERE
(efashion.Outlet_Lookup.Shop_id=efashion.Shop_facts.Shop_id )
AND ( efashion.Shop_facts.Week_id=efashion.Calendar_year_lookup.Week_id )
AND ( efashion.Calendar_year_lookup.Yr IN ( '2006' )
AND efashion.Outlet_Lookup.State IN ( 'Colorado' ))
GROUP BY
efashion.Calendar_year_lookup.Yr,
efashion.Calendar_year_lookup.Mth,
efashion.Outlet_Lookup.State,
efashion.Outlet_Lookup.City

[former_member199543]

One thing that concerns me a bit is the CPU usage, as you can see it consumes where little of CPU when SQL is executed, memory usually grows when we start IQ server. What can be the cause that it consumes that little of CPU, in other cases IQ uses much more, SQL complexity is approx the same

Here is an extract from iqmsg file:

SAP IQ
Version 16.1
(64bit mode)
Copyright 1992-2017 by SAP AG or an SAP affiliate company. All rights reserved
Copyright © 2017 SAP SE or an SAP affiliate company.
All rights reserved.
Use of this software is governed by the SAP Software Use Rights Agreement.
Refer to http://global.sap.com/corporate-en/our-company/agreements/index.epx.

Processors detected: 8 logical processor(s) on 4 core(s) on 1 physical processor(s)
This server is licensed to use: all logical processors in the system
Physical processor limit (-gt option): 8
Processor core limit (-gtc option): 8
Processors in use by server: 8 logical processor(s) on 4 core(s) on 1 physical processor(s)
Running Windows 2012R2 Build 9600 on X86_64
Server built for X86_64 processor architecture
16616K of memory used for caching
Minimum cache size: 608K, maximum cache size: 65536K
Using a maximum page size of 4096 bytes
Multiprogramming level: 100
Automatic tuning of multiprogramming level is disabled
Starting database "iqdwh" (G:\IQDWH\iqdwh.db) at Tue Apr 03 2018 14:04

[harpreet_chaudhary]

Few things :

1. you should have -iqlm (large memory cache essential in 16.x) set along with -iqmc and -iqtc.
2. If you are not using RLV enabled tables then remove -iqrlvmem and give that memory -iqlm
3. Generate query plans for the query and attach that here, it will help us figure out where the slowness is occurring.
4. What is the exact version of IQ 16.1, there is a known performance issue, I am wondering if you may be running into it. Please make the above changes and get the latest Patch 16.1 SP 02 PL05 for windows and then try your query.

Regards

Harpreet

[former_member199543]

Hello. Thank you for these suggestions

1. I've added -iqlm 10240

2. I removed rlv, indeed i do not use rlv tables at the moment.

4. I have the latest version, @@version returns SAP IQ/16.1.020.659/10770/P/sp02.05/MS/Windows 2012 R2/64bit/2018-01-31 02:52:32

Query plan is attached HTML file . this one executes in 28sec, while in M$ SQL it took 1.2 seconds. Index structure is the same, set only on Columns that are used in joins. SDN do not allow to attach html files, if you cannot access that site, i can send to mail, maybe?

I read the file, i'm not an expert, but join settings and method used seems correct, no index suggestions.