I'm querying an OE 10.2B database running on RHEL 5.11 over as a linked server odbc. Specifically a on a date range being part of a composite index.
If I run this SQL query from the OE development on the server it finishes in a couple of seconds. However, if I run it from SQL management studio, I.e. as a linked server over odbc, it never finishes. I'm suspecting the index is not being used. Using thw WITH INDEX phrase didn't help.
============================= Table: TheTable =============================
Field Name Format
-------------------------------- -----------------------------
empCode X(5)
desc X(50)
lineNum >>>>9
tranNum >>>>>>>9
search X(60)
sequence >>>>>>9
traceExpr x(68)
timeStamp ->,>>>,>>9
transDate 99/99/99
type X(8)
userId X(5)
Index:
Flags Index Name Cnt Field Name
----- -------------------------------- --- ---------------------------------
dateIndex 2 + tranDate
+ timeStamp
Query:
SELECT desc
,lineNum
,search
,sequence
,timeStamp
,tranDate
,type
,userId
FROM TheTable
WHERE 04/23/17 <= tranDate
AND tranDate <= 04/25/17
Linked query:
SELECT *
FROM OPENQUERY([TheDatabase],'SELECT desc
,lineNum
,search
,sequence
,timeStamp
,tranDate
,type
,userId
FROM PUB.TheTable
WHERE "2017-04-23" tranDate <= tranDate <= ''2017-04-25'' ')
If anyone can shed some light on this it'd be great.
If I run this SQL query from the OE development on the server it finishes in a couple of seconds. However, if I run it from SQL management studio, I.e. as a linked server over odbc, it never finishes. I'm suspecting the index is not being used. Using thw WITH INDEX phrase didn't help.
============================= Table: TheTable =============================
Field Name Format
-------------------------------- -----------------------------
empCode X(5)
desc X(50)
lineNum >>>>9
tranNum >>>>>>>9
search X(60)
sequence >>>>>>9
traceExpr x(68)
timeStamp ->,>>>,>>9
transDate 99/99/99
type X(8)
userId X(5)
Index:
Flags Index Name Cnt Field Name
----- -------------------------------- --- ---------------------------------
dateIndex 2 + tranDate
+ timeStamp
Query:
SELECT desc
,lineNum
,search
,sequence
,timeStamp
,tranDate
,type
,userId
FROM TheTable
WHERE 04/23/17 <= tranDate
AND tranDate <= 04/25/17
Linked query:
SELECT *
FROM OPENQUERY([TheDatabase],'SELECT desc
,lineNum
,search
,sequence
,timeStamp
,tranDate
,type
,userId
FROM PUB.TheTable
WHERE "2017-04-23" tranDate <= tranDate <= ''2017-04-25'' ')
If anyone can shed some light on this it'd be great.