00900223-01_ID_OIS_SIS_17.0_R19-2_EN_DE - 第14页
ASM OIS/SIS Datab ases 17.0 (R19 -2) / Interf ace Description 11/2019 Edition 14 4.5.1 OIS Tables 4.5.1.1 Performance tips for que rying OIS Select by time not by station OIS tables include indexes for m ost usual querie…
ASM OIS/SIS Databases 17.0 (R19-2) / Interface Description 11/2019 Edition
13
4.5 Tables
The layout of the individual tables is illustrated below. The specified data types correspond to the
naming scheme in the MS-SQL-Server.
NOTICE
The sole purpose of the following tables is to clarify the views. No write access is
permitted to these tables. Backward compatibility cannot be guaranteed if you access
the tables directly. Use the provided views instead.
Legend for the following tables
CI
=
Clustered index
I
=
Index
P
=
Primary key
ASM OIS/SIS Databases 17.0 (R19-2) / Interface Description 11/2019 Edition
14
4.5.1 OIS Tables
4.5.1.1 Performance tips for querying OIS
Select by time not by station
OIS tables include indexes for most usual queries. There are clustered indexes as well as normal
indexes.
If a table contains a combined clustered index, try to adapt your query so that the where clause
includes at least the first part of the combined clustered index.
For example, the BOARD table includes a combined clustered table on (dtTime, lid).
Fast queries on the assigned V_BOARD_160 are:
select * from V_BOARD_160 where dtTime = ‘2019-02-25 13:13:47.417’
select * from V_BOARD_160 where dtTime > ‘2019-02-25 13:11:43.853’ and
dtTime < ‘2019-02-25 14:11:43.853’
select * from V_BOARD_160 where dtTime > ‘2019-02-25 13:11:43.853’ and
dtTime < ‘2019-02-25 14:11:43.853’and lid=1013
select * from V_BOARD_160 where lid=1013 and dtTime > ‘2019-02-25
13:11:43.853’ and dtTime < ‘2019-02-25 14:11:43.853’
Slow queries are all other columns, i. e.
select * from V_BOARD_160 where lid=1013
This means that queries for one time span and any station are executed very fast.
But this also means that queries for one station and any time span are executed slowly.
It is preferred to make time-based queries for the following views:
● V_BOARD*
● V_EVENT*
● V_PICKUPERROR*
● V_STATETRANSITION*
● V_USEDCOMPONENTS*
Use *_160 views (if exist)
With Monitoring Service 16.0 we converted several table columns from 32-bit to 64-bit. Only the
V*_160 views keep the original 64-bit values. All older views must convert the 64-bit values to
32-bit. This means:
1. if a table entry hits the max 32-bit value (2147483647), you no longer get the actual value but
either the converted 32-bit max value or in case of V_PANEL11 no row at all.
2. All views delivering the 32-bit max value cannot access the index for query optimization and
are very slow.
ASM OIS/SIS Databases 17.0 (R19-2) / Interface Description 11/2019 Edition
15
4.5.1.2 STATETEXT Table
STATETEXT
OIS name
Data type
Byte
CI
I
P
Identification number of the State
sState
smallint
2
Name of the State
strName
nvarchar(32)
64
4.5.1.3 FEEDERTYPE Table
FEEDERTYPE
OIS name
Data type
Byte
CI
I
P
SIPLACE Pro number for feeder type
(available with station software version 701
and higher)
lFeederType
int
4
SIPLACE Pro name for feeder type
strFeederTypeName
nvarchar(128)
256
SIPLACE Pro feeder code
IFeederCode
int
4
4.5.1.4 BOARD Table
BOARD
OIS name
Data type
Byte
CI
I
P
Identification number of the Station
(Reference to Table STATION)
lId
int
4
Date/time at which this event occurred. Local
station time
dtTime
datetime
8
Manufactured production lot
strRecipe
nvarchar(128)
256
Name of the setup
strSetup
nvarchar(128)
256
Name of the PCB
strBoard
nvarchar(128)
256
Station internal local number of manufactured
PCB
lBoardNumber
int
4
Conveyor belt 1 / 2
ucConveyor
tinyint
1
Cycle time of PCB
sDuration
smallint
2
Time when production of lot started
dtRecipeStart
datetime
8
Time when setup used for manufacture started
dtSetupStart
datetime
8
PCB barcode
strBarcode
nvarchar(256)
512
PCB status (see definition below)
ucStatus
tinyint
1
Time when the data is written. Local server
time
dtCreated
datetime
8
Number of panels within the board
lPanel
int
4
Conveyor sub-lane
lSubConveyor
int
4
Board side
ucBoardSide
tinyint
1
Unique ID for Line Job
strLineJobGuid
nvarchar(36)
72
Sequence ID (internal use)
lIdSequence
bigint
8
Internal ID to mark entries referring to a "Board
Station Composite" object (for internal use
only)
lBoardCompositeId
bigint
8
Internal GUID containing a unique GUID of the
strPCBGuid
nvarchar(256)
512