VERIFIED SOLUTION i
X

How to increase MapXtreme Performance

UPDATED: September 19, 2017


Regarding SQLServer 2008 key selects and MIDataReader performance, see the following:

When creating an MIDataReader from a SQL command statement built on a key select, PB recommends using the pseudo key column name MI_KEY in the MapXtreme select statements, regardless of the real column name.

 
MapXtreme interrogates the SQLServer2008 table schema to determine the key, based available information, such as:
PRIMARY KEY index
IDENTITY column
UNIQUE identifier
MI_PRINX column name.

This evaluated key is interpreted as "MI_KEY"

Since MapXtreme's SQL key SELECT behavior is optimized for MI_KEY use, we recommend that customers use MI_KEY in
their MapXtreme select statements, regardless of the real column name.

For example, if the table is defined with a PrimaryKey column 'MI_PRINX', the MIDataReader command should nonetheless reference MI_KEY for key select statements, otherwise,MapXtreme's SELECT behavior doesn't make use of the evaluated key column, so it potentially skips around the key select optimization.
 
In this example scenario, where MI_PRINX is defined as the key column, the following select statement exhibits
performance issues over a large data set:
SELECT * from linkar WHERE MI_PRINX in (22924,22925,22926,22927,22928)
Whereas the following statement utilizes the key select to advantage to get acceptable performance, using the
MapXtreme pseudo key column name, MI_KEY:
SELECT * from linkar WHERE MI_KEY in ('22924','22925','22926','22927','22928')
Note, MI_KEY is evaluated as a string during the select statement parsing, so you need to enclose the values in '''
marks.

Environment Details

Products affected: MapXtreme™

Downloads

  • No Downloads