Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107

    Unanswered: Access Combo Boxes/SQL Server

    I have a form bound to a table in SQL server using ODBC. The performance of the form is fine until I add a couple of combo boxes and then it slows down considerably. I have tried binding the combo boxes to Pass-through queries when the form opnes but the improvement in performance is negligible. I would now like to bind the combo boxes to a stored procedure to see if it makes any difference.

    Does anyone have some example code that binds a combo box (RowSource) to the results of a stored procedure?

    Thanks
    Last edited by mmcdonald; 03-26-04 at 09:30.

  2. #2
    Join Date
    Feb 2004
    Posts
    142
    Binding comboxes to a server table can cause a performance issue, especially if this is a large table. If you are displaying all the records in the table then a server proc will not yield much more improvement from a passthrough query. A table scan is a table scan.

    Usually, it is best to find a way around this. Some examples are running a local table containing just enough info to run the combobox and using a passthrough to pull results for display or for form navigation. Then developing a process to keep the local table in sync with the server table.

    If you are not displaying all the records from the server table in the combobox then you can use indexes to improve performance. Create an index that includes all the fields needed for the combox operation, including the field for the where clause. Then create a passthrough query to bind to the table again with the where clause. This can allow the server to do an index read instead of hitting the table and this can up the performance by reducing i/os and limiting the rows returned.

    You can also try to set the clustered sort on the server to the order the combobox displays records and sometimes that can help.
    KC

  3. #3
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    Thanks. I'll try creating an index and see how that goes. If that doesn't offer much improvement I'll just use local tables in the font end and synchronize them when the DB opens. I shoudl really be using at least and ADP file but I wnt to keep the Jet security feature which isn't available in an ADP file.

    Originally posted by AZ KC
    Binding comboxes to a server table can cause a performance issue, especially if this is a large table. If you are displaying all the records in the table then a server proc will not yield much more improvement from a passthrough query. A table scan is a table scan.

    Usually, it is best to find a way around this. Some examples are running a local table containing just enough info to run the combobox and using a passthrough to pull results for display or for form navigation. Then developing a process to keep the local table in sync with the server table.

    If you are not displaying all the records from the server table in the combobox then you can use indexes to improve performance. Create an index that includes all the fields needed for the combox operation, including the field for the where clause. Then create a passthrough query to bind to the table again with the where clause. This can allow the server to do an index read instead of hitting the table and this can up the performance by reducing i/os and limiting the rows returned.

    You can also try to set the clustered sort on the server to the order the combobox displays records and sometimes that can help.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The security features you lose by switching ti an adp are nothing compared to the security you can implement on the sq server itself. Are you not in a position to do as such?

  5. #5
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    I am, but there are other issues (as usual)...many thanks for your help.

    Originally posted by Teddy
    The security features you lose by switching ti an adp are nothing compared to the security you can implement on the sq server itself. Are you not in a position to do as such?

Posting Permissions

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