Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2003
    Posts
    4

    Unanswered: Extending query timeouts?

    There are certain queries that are timing out on me.
    For example:
    select count(*) from log
    where log has 50-60 million entries
    (unfortunately it seems Sybase doesn't shortcircuit this common query to just returning the number of rows in the table)

    I've been looking through the documentation for query timeouts, but don't see any.

    ODBC has SQL_ATTR_QUERY_TIMEOUT but defaults to infinite unless overridden by the data source.

    I was wondering if there was a Sybase parameter which was causing these queries or TCP connections to time out.

    Thanks,
    nemo

  2. #2
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159

    Re: Extending query timeouts?

    Do you have an index on this table?

    The following provides and "estimate" of the number of rows. I personally have never seen a wrong number produced using this method, but potentially the number returned could be out of date.

    Running update statistics on the table first will provide the correct figure.

    select name, rowcnt(sysindexes.doampg)
    from sysindexes
    where name in
    (select name from sysobjects
    where type = "U")

  3. #3
    Join Date
    Apr 2003
    Posts
    4
    That solves a specific problem of getting a row count. And unfortunately, we need to do it in a cross-database fashion, so this is unfeasible.

    It doesn't solve other problems though.
    Like a:
    select distinct field from log

    with again, 50 million + rows

    So. Any theories on what could be causing the timeout?

  4. #4
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159
    Sorry for the delay in getting back to you, but I've been away for a while.

    As far as I know there is no "default" timeout for open client. Open client applications may supply a timeout as part of their connection and/or query processing.

    Depending on how your client is written, it may be sending asynchronous or synchronous SQL. Synchronous is the simpler to write. Your application sends the SQL and waits for the SQL to return. Asynchronous is when you send the SQL and a callback function is called when the query returns. For the sake of simplicity I'm assuming you're writing a synchronous program.

    CS_INT timeout;
    CS_CONTEXT* p_ctx ;

    // Connect to database using context and connection allocation routine

    timeout = 5 ; // This is the number of seconds to wait before timing out
    ct_config = ( p_ctx, CS_SET, CS_TIMEOUT, (CS_VOID*)&timeout, CS_UNUSED, NULL) ;

    Login timeouts can be undertaken using specifying CS_LOGIN_TIMEOUT. Obviously this needs to be setup before connecting to the database.

    Have a look at the examples here:
    http://www.sybase.com/detail?id=20373

  5. #5
    Join Date
    Apr 2003
    Posts
    4
    Quite alright, I rewrote things so the need to fix this is less pressing (with only slight loss in reliability).
    I certainly appreciate any response I can get.

    Unfortunately, we are only using at the moment Microsoft's ODBC lib to communicate with the database.

    That's why I referred to SQL_ATTR_QUERY_TIMEOUT which is set using SQLSetStmtAttr.

    Since you say no timeout is provided by default, I assume that means that whether I am connecting using the ODBC layer or this Sybase library you gave, if I don't specify a timeout, it should wait indefinitely.
    This definitely puzzles me since looking through the code, I don't see any place where a timeout is set.

    As for synch vs asynch, you're correct that all threads that do SQL queries do them synchronously.

  6. #6
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159
    I've always assumed that ODBC uses open client at some point, but it is conceivable that connects directly to the ip address and port in the same way as JDBC. I have never seen an ODBC connection to ASE that didn't have Open Client installed on the machine.

    Having something installed does not necessarilly mean that it is used by an application. I tend to install Wordpad, but that doesn't mean I use it

  7. #7
    Join Date
    Apr 2003
    Posts
    4
    Could be. I may have an overly simplistic understanding of the underlying mechanism.
    Presumably the Sybase ODBC layer is written using open client then.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •