Bad Performance Result Sets up 100,000 rows

[former_member236501]

Hello everybody, I am migrating a database from MS SQL Server 2012 to Sybase IQ 16 SP 11 but I am facing the next issue, when I run a query with a big results set for example 5 millions of rows, Sybase IQ  response time is slow. For example when I run the query on MS SQL Server it takes 5:01 minutes but when I run the same query on Sybase IQ 16 SP 11 it takes 10 minutes. I don’t know why MS SQL Server is faster than Sybase IQ 16 SP 11 with big result sets.

Here some information about my Sybase IQ

SO: Windows Server 2012 X64

CPU: Intel Xeon E7-8890 with 36 cores x 2

RAM: 160 GB

HD: 2 TB SSD

Here some information about my Sybase IQ

SO: Windows Server 2012 X64

CPU: Intel Xeon E7-8890 with 36 cores x 2

RAM: 160 GB

HD: 2 TB SSD

Here some information about my Sybase IQ

SO: Windows Server 2012 X64

CPU: Intel Xeon E7-8890 with 36 cores x 2

RAM: 160 GB

HD: 2 TB SSD

The current configuration

IQ Information

Dbspaces

CFG file

# The following parameters are also found in the configuration file

# C:\SAP\IQ-16_0\scripts\default.cfg.  Any parameters not specified below

# and not in the start up parameter list, will be added by start_iq

# using default.cfg as a guide.

-c 32M

-gtc 4

-gc 20

-gd all

-gl all

-gm 100

-gn 150

-gp 4096

-iqnumbercpus 16

-iqgovern 154

-p 65535

-iqmc 40960

-iqtc 40960

Thanks

IQ Information

Dbspaces

CFG file

# The following parameters are also found in the configuration file

# C:\SAP\IQ-16_0\scripts\default.cfg.  Any parameters not specified below

# and not in the start up parameter list, will be added by start_iq

# using default.cfg as a guide.

-c 32M

-gtc 4

-gc 20

-gd all

-gl all

-gm 100

-gn 150

-gp 4096

-iqnumbercpus 16

-iqgovern 154

-p 65535

-iqmc 40960

-iqtc 40960

IQ Information

Dbspaces

CFG file

# The following parameters are also found in the configuration file

# C:\SAP\IQ-16_0\scripts\default.cfg.  Any parameters not specified below

# and not in the start up parameter list, will be added by start_iq

# using default.cfg as a guide.

-c 32M

-gtc 4

-gc 20

-gd all

-gl all

-gm 100

-gn 150

-gp 4096

-iqnumbercpus 16

-iqgovern 154

-p 65535

-iqmc 40960

-iqtc 40960

[c_baker]

I had this happen many years ago with a customer who could not see the real need for IQ after a 'performance' test between MSSQL and IQ - MSSQL time 6 minutes, IQ time 4.5 minutes - until I suggested eliminating all the network communications between their servers and clients (they had about 20 hops and QOS in their environment that effectively slowed down all non-IP phone communications - it took 20 minutes just for Sybase Central to connect to IQ!).

When the queries were rewritten to just select into #temp tables, the time went to MSSQL - 1.5 minutes, IQ - 1 second.  Therefore the actual network transfer time was ~4.5 minutes for both, but making IQ actually 90x faster than MSSQL.

Along with the suggestions above, you may want to perform a similar test and just take out the network completely. Also, keep in mind, that to get correct query plan timing from dbisql, you must issue a 'commit' immediately after your last result set is returned - otherwise the timing will not only include network transfer time for the result set, but the 'think' time it took to review the results and issue that 'commit'.

The other thing that comes to mind is the actual indexes used in IQ.  I would strongly suggest reviewing your index configurations and use index advisor to confirm that you are effectively indexed.  e.g. using composite indexes to cover queries may work great in an MSSQL DDL design, but is effectively useless for IQ.  Check that you have the correct indexes.

Another issue that comes to mind is the fact that you do not have -iqlm defined.  You are running IQ 16, so should also have -iqlm configured, not just -iqmc and -iqtc.  For now, to use the same memory, split your -iqtc and -iqmc total configuration into equal thirds between -iqmc, -iqtc and -iqlm.  -iqlm is used for the nBitFP indexes lookup tables, and as you have too little -iqlm configured (I think the default is only 2GB) there is a definite possibility that the tokenized indexes were rolled over to flat too early during loading, causing performance issues for loading and querying.  You may have to rebuild the FP indexes after increasing this memory as well (sp_iqrebuildindex) to re-tokenize the FP indexes.

Some of the above can be mitigated, however, if you ensure that you are using other indexes effectively - e.g. HG indexes on join keys - (see indexadvisor again).

For now, please ensure that you increase -iqlm and check your indexing.

Indexing Tips - SAP IQ Performance and Tuning - SAP Library

Chris

[former_member194571]

Hi Josefina,

A few comments out of a situation I've experienced at a customer's site which looks similar to what you describe:

VL result sets are a pain. Period. It doesn't matter which RDBMS you use. Iterating through the result set is the expansive part. I admit that this doesn't explain the difference between MSS and IQ.

For baseline evaluations:

The fastest way to process a VL result set is to do a server side unload. I found little difference between an actual file and the null device as the unload destination, but this may vary. In doubt, the null device should be at least as fast as any real file.

The next step is to unload the result set on client side. Here, network bandwidth, throughput and other characteristics have an impact.

The slowest of these three alternatives is to transfer the result set to the client using the relational API / cursor functionality (like with the dbisql OUTPUT statement). Even when optimizing row batches, prefetch etc. I found it to take some two to three times as long as the client side unload (if I remember correctly). This is due to the handshake which returns control to the client application after the transfer of each row or batch of rows (as compared to the stream in the unload which can only be interrupted in case of an error).

One of the particular strengths of IQ is the computation of a small or moderate sized result set out of an almost arbitrary number of base records through aggregation.

The usual way to enhance the performance of the client / server communication is to increase network packet size and row prefetch settings.

Is the result set made of raw records or aggregated values? Are few / many / all columns of the table(s) involved part of the result set?

HTH

Volker

[tayeb_hadjou]

Hi Josefina,

Which sql tool/application used for query?
Was the test local to IQ server or from remote client machine?

Have you tested same query with dbisql (native)?

Is the execution time (10 minutes) match the one reported by IQ query plan?

For next tests, I suggest enable also

Query_Plan_As_Html

Query_Plan_After_Run

Querry_timing

Use dbisql to isolate the problem (client application, network or IQ optimizer).

Regards,