Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Aug 2003
    Location
    Germany
    Posts
    20

    Question Unanswered: urgent help needed with query and view

    As i posted before in another thread i am trying to update a table through a view. Its a distributed partitioned view.
    When i try to update the view with just something like this:

    ---------------------------------------
    update V set somecolumn=@value
    where V.pk1=@pk1 and V.pk2=@pk2
    ---------------------------------------

    it works fine, but when i try to update and select :

    ---------------------------------------
    update V set somecolumn=@value
    from R where .....
    ---------------------------------------

    it takes a long time to finish (sql server does a remote scan).

    V and R are both views, the tables they point to are on a different sql machine.
    I also notices, that if i use a local table instead of R (view) i get the same (bad) result.


    thx in advance

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: urgent help needed with query and view

    Originally posted by AticAtac
    As i posted before in another thread i am trying to update a table through a view. Its a distributed partitioned view.
    When i try to update the view with just something like this:

    ---------------------------------------
    update V set somecolumn=@value
    where V.pk1=@pk1 and V.pk2=@pk2
    ---------------------------------------

    it works fine, but when i try to update and select :

    ---------------------------------------
    update V set somecolumn=@value
    from R where .....
    ---------------------------------------

    it takes a long time to finish (sql server does a remote scan).

    V and R are both views, the tables they point to are on a different sql machine.
    I also notices, that if i use a local table instead of R (view) i get the same (bad) result.


    thx in advance
    Yeah, and I asked you to post the DDL and DML...

    Why aren't you going against the tables directly...

    You could also try building a temp table, store the results of the join on the remote server, then apply the results...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Aug 2003
    Location
    Germany
    Posts
    20

    Re: urgent help needed with query and view

    Originally posted by Brett Kaiser
    Yeah, and I asked you to post the DDL and DML...

    Why aren't you going against the tables directly...

    You could also try building a temp table, store the results of the join on the remote server, then apply the results...
    I am not going against the tables directly ! Thats why i use partitioned view. My applications only know the "tables" (views) R and V.
    I don't want to put extra handling on the "application" side.

    Posting DDL and DML ? Sorry, not sure what you exactly mean with that.
    You want the execution plan ?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    SQL server allows you to do many things that are not necessarily good ideas. One of these is updating views. Good programming practice is to update data using stored procedures. This doesn't necessitate extra "handling" in your application. The logic is put in the stored procedure and your application calls the stored procedure rather than executing an update against the view.

    blindman

  5. #5
    Join Date
    Aug 2003
    Location
    Germany
    Posts
    20
    Originally posted by blindman
    SQL server allows you to do many things that are not necessarily good ideas. One of these is updating views. Good programming practice is to update data using stored procedures. This doesn't necessitate extra "handling" in your application. The logic is put in the stored procedure and your application calls the stored procedure rather than executing an update against the view.

    blindman
    So you mean, i should do "extra" handling in the stored procedure and
    call update from there but still use the view ?
    Do you have any examples ?
    What i try to do is in my opinion the simplest things you can do with a
    partitioned view. Should i still go with the view ? Should put all the different server/table handling in the sp ?
    In my case the partitioned tables grow from month to month.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by AticAtac
    In my case the partitioned tables grow from month to month.
    Why do I get a sneaking feeling by when you mean "Grow" you mean new tables are added to your partitioned view...right?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Other people on this forum may disagree with me, and I'd like to hear their thoughts on the subject, but I think front-end applications are better off communicating with databases through procedure than views, both for getting data and for modifying data. Not only are stored procedures more efficient, but they allow you to do security or parameter related processing that is not easily done through views.

    Create procedures that return the recordsets your application needs to display, and create procedures that your application can call in order to insert, update or delete data.

    The other advantage to using stored procedures is that you only need to write your logic once, and then you can write as many different interfaces as you want to connect to the database. The alternative is to write the business logic in each interface and do all the admin necessary to make sure they all use identical logic as the application evolves. I much prefer the stored procedure method.

    blindman

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    My developers are pure presentation layer...

    All code resides in sprocs...

    [shiver]
    thought of developers writing their own sql
    [/shiver]

    O..the humanity....

    Still want to know what "grow" means to your view...

    Gotta be a new table a month....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I frequently have to fight with developers who think a database server is just a huge filing cabinet. Why they can't see that it is better to implement complex business logic once in the database and return only the results over the network than it is to write the same logic multiple times for every interface and return streams of unprocessed data over a network is beyond me. The only answer I can see is that to a man with a hammer, every problem is a nail.

    Processing in the database. Presentation in the interface. Simple.

    blindman

  10. #10
    Join Date
    Aug 2003
    Location
    Germany
    Posts
    20
    Originally posted by Brett Kaiser
    Why do I get a sneaking feeling by when you mean "Grow" you mean new tables are added to your partitioned view...right?
    Correct, i meant number of tables grow ...

    Well, just to sum a little bit, blindman, you suggest that i put
    all the "logic" i have in the view (accessing different servers and tables based on check constraints) into stored procedure ?
    That means that the partitioned view mechanism ms provides for the sql server is in case of insert and update useless.
    I would like to "hear" more opinions on this.
    One more thing, do you have any examples on doing this in the sp ?

    thanks so far for all the answers here ...

  11. #11
    Join Date
    Aug 2003
    Location
    Germany
    Posts
    20
    Originally posted by blindman
    Other people on this forum may disagree with me, and I'd like to hear their thoughts on the subject, but I think front-end applications are better off communicating with databases through procedure than views, both for getting data and for modifying data. Not only are stored procedures more efficient, but they allow you to do security or parameter related processing that is not easily done through views.

    Create procedures that return the recordsets your application needs to display, and create procedures that your application can call in order to insert, update or delete data.

    The other advantage to using stored procedures is that you only need to write your logic once, and then you can write as many different interfaces as you want to connect to the database. The alternative is to write the business logic in each interface and do all the admin necessary to make sure they all use identical logic as the application evolves. I much prefer the stored procedure method.

    blindman
    Doing insert, update, delete and even select in stored procedures may not be always a good choice. In my case, i have front-end applications
    (interfacing through ado written in c++) for them sp could be "good" method to do insert/update jobs. But i have also many other "applications" done in T-SQL and executed on the server. For them
    i think using sp could be difficult.
    I#ve also heard about "instead of ...." triggers for views. Maybe that could be a solution, what you guys think about that ?
    As you see, i am sort of unsure about which way to go, i don't have much production experiences with sql server.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by AticAtac
    Correct, i meant number of tables grow ...
    Yup....

    I would say that is a bad design...

    How much admin do you have to do?

    Partition the tables 1 by month..

    second, the reason sprocs are prefered is because they can be compiled, and have an access plan stored..

    If you do it dynamically, the optimizer needs to figure it out (the best path) every time....

    PLUS....adding a new table every month, means you have to change the view, EVERY month...

    you getting paid by the hour?

    Never mind that...but objects should not change because of the data...

    and what types of updates/inserts are we talking about...

    betcha we could figure a way to optimize the process...

    Are we talkining singleton or mass processes?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and how many records do you have each month, that you feel you need to partition the tables? This can be justified if the database is very large (VERY large), but otherwise it just increases complexity and impacts performance.

    blindman

  14. #14
    Join Date
    Aug 2003
    Location
    Germany
    Posts
    20
    I get about ~10 million records each month.
    For each "delivery" i create a month table.
    I think it was a right decision to split data into multipile month
    tables and let the tables be on different machines. While the number
    of tables grow i can add new machines etc.

    As for altering the views each month, its done all automatically.
    I have a table containing all the information about the existing month tables and from this table i create all the views.

    As for accessing/using data, the client application written in c++, uses ado to access the data on the lowest layer.
    Besides the client application i have many sql scripts which do different "jobs" and also need to access the views for selecting/updaing/inserting.

    I am still not sure how to "replace" the views with stored procedures.
    I mean can you simply uses sp in sql statements like

    update myview set something=xxx
    from anotherview where ......

    ?

    Also, by talking about stored procedures do you mean also user defined functions ?

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your decision to split the data may be justified based on the number of rows you expect.

    I don't think you should reference the view in the stored procedure. That wouldn't gain you much.

    What you need is a stored proc that accepts the primary key of one or more of your virtual tables along with the new or modified data. The proc would then look up which table holds the data and would modify or add the appropriate record. This way your update is only accessing the one table required, and isn't trying to search across all the tables on multiple servers.

    blindman

Posting Permissions

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