Conference ulysse::rdb_vms_competition

1315.0. "odbc / sql services very slow" by WARNUT::BALMFORTHK (Say goodbye to the blues...) Mon Nov 22 1993 13:38

  Hi,

  Rdb 5.1/6.0 , ODBC Driver 1.0/1.1 MSACCESS 1.1

  A customer is saying he has a problem that the time to retrieve 
the data from an Rdb database and put it on the screen using MSACCESS is 
unacceptably slow. He also did some experiments using DECquery and was able to
make that go faster by setting the FETCH_COUNT parameter within DECquery. He
cannot do this with MSACCESS. What we think is happening here is that by
setting the FETCH_COUNT to a value greater than 1, DECquery is doing calls to 
fetch_many but that MSACCESS could not be made to do calls to fetch_many 
(via the SQLExtendedFetch in the ODBC driver). From what I can glean from the 
limited documentation I have, to get many rows at a time using ODBC I could, 
in my application (if I were to write one) do calls to SQLExtendedFetch 
in the ODBC driver, which would result in calls to fetch_many in sql/services 
etc. I understand that this is not done by MSACCESS. I.e. it does calls to 
fetch but not fetch_many. 

  The problem is further compounded by the customer comparing MSACCESS/DEC ODBC
Driver/SQLservices to an Rdb database against MSACCESS/Microsoft's ODBC driver
/ Sequel Server to a Sybase database.
  He says that the second route is much, much faster than the first. As I have 
absolutely no knowledge of the differences between the two nor how the second
route works, I have no way of knowing if what he is telling me is true. 

  

  They are comparing the two for evaluation purposes. They will be using one or
the other in the future for data access. If we cannot drastically improve the
performance of  MSACCESS/DEC ODBC Driver/SQLservices, we will lose a sale. 
Lilian suggested the difference in performance may be at the network connection
level i.e. in out case the SQL/Services level but I, and my colleagues, do not
have the knowledge or experience of SQL/Services to know how to find out exactly
in which layer of the software the problem is. 

  Initially he was using Rdb 5.1 ODBC Driver 1.0. but he tried it with Rdb 
6.0FT and ODBC 1.1 but with no noticeable improvement. 

  Does anyone have knowledge of the difference between the two access paths I 
outlined earlier ? Does anyone have any pointers about how to investigate this 
problem ? I will be putting this note in the SQL_SERVICES, ODBC and the Rdb
competition notes files,

  Thanks in anticipation,

  Regards,

  Kevin.

    

  We recently completed a series of tests to compare the performance between 
  using SQL/Services APIs and ODBC APIs.  We did not find any significant 
  performance degradation between these two, less than 2% performance 
  degradation.

  We did find something interesting.  The ODBC have two modes of transaction,
  which is auto-commit and manual-commit.  The default behavior of ODBC is
  to auto-commit.  Which means each SQL statement is followed automatically by
  a commit statement to conform to the ODBC spec.  This creates significant 
  performance degradation unless auto-commit is explicitly disabled.  

  In the case of MS/Access, you don't have that option.  Compound that with 
  the problem of not using FetchMany, I think that's why you have serious 
  performance problem.  

  You can certainly write your own code to use the ODBC driver, but keep the
  above two points in mind.  You should be able to see a much better 
    performance.

  Does anyone know how Sybase does this with their ODBC and MS/Access?  

  Yongmin

    Hi,
    
      I am not sure you got my point. Maybe I did not explain it very well.
    The customer is using MSACCESS to query an Rdb database and a SYBASE
    database. In the first instance, he is using :-
    
      MSACCESS/DEC ODBC Driver/ SQL Services / Rdb
    
    and in the second he is using :-
    
      MSACCESS/ MS ODBC Driver/ Sequel Server / Sybase
    
      The MSACCESS is used in both cases, the ODBC layer should be very
    similar as they conform to a standard, so it looks like the differences
    *may be* in the network transport layer i.e. SQL Services or  Sequel
    Server. 
    
      Can anyone confirm this or has any other ideas ?
    
      Thanks,
    
      Kevin.