Results 1 to 2 of 2

Thread: optimising odbc

  1. #1
    Join Date
    May 2002
    Dublin, Ireland

    Unanswered: optimising odbc

    Apologies if this issue has been dealt with before but I'm under pressure with deadlines.

    I'm connecting to a remote sql server via odbc to download among other things a text field which needs to be converted to a number before adding it to a local table. I am currently using a function to achieve this in the query itself i.e.

    select Left(RemoveAlphas([fieldname]),9) AS Expr1

    This is a mission critical system which is subject to very busy periods and I keep on reading that to optimise odbc queries you need to avoid any operation that can't be performed on the remote server.
    I am very keen to minimise the amount of time spent connected to the server.

    To achieve this would it be better to download the field to a temp table and then perform the conversion in an update query - or is this just what Access97 does anyway so the performance gain would be neglible? Again the goal is to minimise the time spent connected to the remote server.



  2. #2
    Join Date
    Apr 2002
    Importing the whole recordset and then running the function on the local table would probably be faster as Access retrieves every record 1-by-1 from the server when you have any expressions in the sql the server can't process itself. A bulk insert into from a linked table should be considerably faster and then running an update on it locally would not suffer from the overhead odbc does when fetching single rows.

Posting Permissions

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