Querying From A Linked Server On An Indexed Date Range Seems To Force Table Scan

pinne65

Member
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.
 

ForEachInvoiceDelete

Active Member
If the same query runs fine in ABL, try running "UPDATE STATISTICS" from your odbc SQL editor.

OpenEdge 11.7 Documentation

You can specify a table or just run as is to do your entire database.

Might be your SQL-92 needs to re-optimize itself after you have added index's etc.

You will notice some slowdown running remote instead of puttying into the box depending on your network, but shouldn't be too bad.
 

pinne65

Member
Thanks for the info.

Swapping the order of the dates around or using the BETWEEN syntax didn't seem to have any effect.

I will turn on logging and see if it provides any clues.

I've previously been nervous about running UPDATE STATISTICS since I've heard about possible performance problems on OE 10 after doing that.

We are however upgrading to 11.6 this weekend. With 11.7 to follow soon thereafter. Then I won't have to be worried about running UPDATE STATS any more.
 

TomBascom

Curmudgeon
Isn't there some sort of issue around needing to define your queries properly with linked servers? The term "pass through query" sounds relevant to me. If I recall correctly doing it wrong turns all of your linked server queries into table scans. And I seem to recall that the default is "doing it wrong".
 
Top