SQL Server Performance

Querying and inserting/updating from VB

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by TheGios, Oct 28, 2004.

  1. TheGios New Member

    QUERYING
    --------
    I have a big table (1,5 millions of rows, increasing...) with 30 columns, joined with 5 or 6 lookup tables. I have to select only 6 colums from the above join, filtering on a specific value of the big table's PK:

    I know I have 4 options:

    1. Use the ADO command object to execute a query on the tables

    2. Create a join VIEW containing only the columns I need and query the view using the ADO command object

    3. Write a SP accepting the value to filter on and returning a one-row recordset, then use the ADO command object to call the SP and store the value in a client-side disconnected cursor

    4. Write a SP accepting the value to filter on and returning a delimited string, then use the ADO command object to call the SP and store the value in a string to be stripped on the client

    Which is the best way?

    Consider that I have tried already option 2 and 3 and they take both less than 1 second.
    Consider also that I am not very good in T-SQL but I know of course SQL.

    INSERTING/UPDATING
    ------------------
    Inserts and updates are done instead on one table with hunreds of row and 40 colums.
    It is a denormalised table, with some NULL values (but I'm planning to use "fake" nulls).

    Is it better to run the inserts/updates from the ADO command object, or should I create a SP with input parameters to be called from the ADO command object?

    In the second case, sould (in case of an insert) the SP have 40 input parameters or is there another way to pass an entire row?


    THANKS A LOT
  2. FrankKalis Moderator

    IMHO, the use of a SP is generally preferable. Not only that it is precompiled and optimized, but it also gives you controlled access to the underlying data. I would choose 3 or 4 depending on what exactly your SP is supposed to do.
    As for INSERT/UPDATE:
    Same goes. Use a SP to do this along with the Command object.
    And since in SQL Server 2000 you can have as much as 1,024 input or output parameters for a SP, that might be some PITA to write, but AFAIK the only way to pass the values to your SP.


    -----------------------
    --Frank
    http://www.insidesql.de
    -----------------------

Share This Page