Skip to Content
avatar image
Former Member

Slow remote request on 7.6.03.15 on Windows server 2008

Hello all,

I have install MAXDB on Windows server 2008.

When I made request from a remote computer on the database with SQLStudio requests are very slow.

For example I request an empty table :

SELECT * FROM <TABLENAME> the request seem fast.

but this one is very slow, I only replace * with all columns

SELECT ArcState, Comp_date_time, ContFlag, Deferred, SndAccount, SndAddress, SndCompany, SndName, SndType, RcpAddress, Msn, Notif, OwnerID, OwnerPB, PreviewMessage, PreviewVisible, Priority, Purged, DIST_ABORTED, DIST_ABORTEDCOUNT, DIST_OWNER, DIST_NUM, DIST_VERSION, Status_str, State, Subject, Sub_date_time, SATID, RcpCompany, RcpName, Viewed FROM <TABLENAME>

This request takes 0.3s, and the table is empty. Everything work find if sqlstudio is running on local computer even if I use XSERVER

How could you explain this behavior ?

To avoid DNS issues I put IP adress in ETC/host, I also disable Windows2008 firewall.

If you have any idea that could help me, let me know.

Thanks for help.

Yann.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • May 16, 2008 at 12:36 PM

    > I have install MAXDB on Windows server 2008.

    Oh 😊

    > When I made request from a remote computer on the database with SQLStudio requests are very slow.

    >

    > For example I request an empty table :

    >

    > SELECT * FROM <TABLENAME> the request seem fast.

    > but this one is very slow, I only replace * with all columns

    >

    > SELECT ArcState, Comp_date_time, ContFlag, Deferred, SndAccount, SndAddress, SndCompany, SndName, SndType, RcpAddress, Msn, Notif, OwnerID, OwnerPB, PreviewMessage, PreviewVisible, Priority, Purged, DIST_ABORTED, DIST_ABORTEDCOUNT, DIST_OWNER, DIST_NUM, DIST_VERSION, Status_str, State, Subject, Sub_date_time, SATID, RcpCompany, RcpName, Viewed FROM <TABLENAME>

    >

    > This request takes 0.3s, and the table is empty. Everything work find if sqlstudio is running on local computer even if I use XSERVER

    "select *" is known to be not the fastest 😊

    What happens is:

    the system does a table scan on the first statement. All the data will be in the cache then. The second statement then access exact that data, and since it doesn't need to be read from disk but out of the memory it's very fast. That's what happens.

    To really compare those two statements - stop and start the database between them - and you will see that both of them take the same amount of time.

    Markus

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 16, 2008 at 12:49 PM

    Hi Markus,

    Here is the result of explain.

    ESKDBADM DBM350_AC AC_SUB_DATE_TIMED INDEX SCAN 1

    RESULT IS NOT COPIED , COSTVALUE IS 2

    QUERYREWRITE - APPLIED RULES:

    DistinctPullUp 1

    Keep in mind that the table is empty,

    If I excute this request on remote computer.

    SELECT ArcState, Comp_date_time, ContFlag, Deferred, SndAccount, SndAddress, SndCompany, SndName, SndType, RcpAddress, Msn, Notif, OwnerID, OwnerPB, PreviewMessage, PreviewVisible, Priority, Purged, DIST_ABORTED, DIST_ABORTEDCOUNT, DIST_OWNER, DIST_NUM, DIST_VERSION, Status_str, State, Subject, Sub_date_time, SATID, RcpCompany, RcpName, Viewed FROM DBM350_AC WHERE (((ArcState IS NULL OR ArcState != 4) AND (PreviewMessage IS NULL OR PreviewMessage = 0 OR PreviewVisible = 1))) ORDER BY Sub_date_time DESC WITH LOCK ISOLATION LEVEL 0

    on an empty table, it always take 0,608s, I can type several time on F8, It always take 0,608s.

    You said

    "To really compare those two statements - stop and start the database between them - and you will see that both of them take the same amount of time."

    I agree with you if the second call is faster than the first call, because of datacache , but in my case it's always slow.

    How to explain that a select on an empty table can take 0.608s ?

    I think there is something wrong in Windows2008, but I don't kwon what.

    Yann.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 05, 2008 at 10:04 AM

    Hello,

    I find a workaround, on windows 2008. we had to set the registry key to TcpAckFrequency to 1.

    My understanding is that there is a bug in driver ODBC and Serv.exe.

    When driver ODBC open a connection, it sets SO_SNDBUF, and SO_RCVBUF to 0x140 before connect.

    and Serv sets SO_SNDBUF and SO_RCVBUF to 0x1fe48. So when serv receive 0x140 size frame from ODBC driver, it waits 200ms before sending acknolegment for each frame of 0x140 bytes.

    I think that Driver and Serv, should call setsockopt with same parameters and call getsockopt after connection to check the current value .

    Another strange thing in driver is that setsockopt is called after connect with the value 0x20000, this call is ignored by TCP Stack.

    Is it possible to have a patch for this issue ?

    Yann.

    Add comment
    10|10000 characters needed characters exceeded