1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'DataServers and ODBC' started by pinne65, May 2, 2017.

  1. pinne65

    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.
     
  2.  
  3. TheMadDBA

    TheMadDBA Active Member

    Is your where clause literally WHERE 04/23/17 <= tranDate AND tranDate <= 04/25/17?

    If so... don't do that. Use tranDate between startdate and endate or (tranDate >= startdate and tranDate <= enddate).

    Also see this KB to see how to enable/disable SQL query plan logging ---> Progress KB - SQL-92: How to turn SQL statement logging on and off?
     
  4. ForEachInvoiceDelete

    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.
     
  5. pinne65

    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.
     
  6. TomBascom

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

Share This Page