Post reply

SQL 2000 Table Variables & UDFunctions

  • November 17, 2006 at 3:41 am

    #368070

    Hi Guys,

    Novice Access SQL programmer here.

    SQL 2000 Enterprise DB with Access 2000 mdb front end.

    I have been looking at improving speed for some of my subforms. One form in particular is based on an Access Query with loads & loads of expressions (including some VBA functions) which is in turn based on a View.

    So far I have converted the Access query with all its expressions to a stored procedure. This has a hugely positive impact on performance.

    Thing is I would like to retain the flexibility of the View and Ive been experimenting with a UDF returning a table to the calling View. This seems to work great. Too Great!

    I need to be able to update/insert/delete records from the View which all seems possible but Im a little apprehensive about rolling this out to production.

    Should I be using this technique for updating base tables? Are there any side effects of this method I should be wary of?

    BASE TABLE > UDF > TABLE VARIABLE > VIEW

    I havent used UDFs or Table variables at all before

    Any advice will be gratefully accepted.

    Thanks.

    JK

  • November 17, 2006 at 10:27 am

    #672689

    you will have to update the base tables;

    a view is really just a saved SELECT statement. this article might help a little bit:

    http://www.sql-server-performance.com/nn_views.asp

     

    you'd most likely want to create a stored procedure , which would be passed some of the values you want to change(parameters), and that would in turn update the base tables.

    Of course, you can just use a SQL Statement to update the table directly, but with a stored proc, you get an additional performance boost because of saved code and execution plans.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply