Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2002
    Location
    Kyiv, Ukraine
    Posts
    77

    query on query - is it good?

    Wanted to ask about database concepts and main principals regarding creating querries which use dynasets from another querries instead of the direct connections to data (even if it's a bit difficult but possible!)..

    How much will it slower the overall calculation process or will any other way be visible when using dynaset from previous querry for getting needed information instead of actual data from tables (if possible both ways)?

    TIA.
    Yours faithfully,
    Yaroslav Zaremba

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dynasets? what are dynasets?

    general database concepts do not include dynasets, i believe those are proprietary thingies from one particular vendor

    general database concepts do include views, which are definitions that are stored in the database's sytem catalog

    querying a view is just as efficient as querying a table

    views do not involve a significant amount of overhead, although it is not zero

    rudy

  3. #3
    Join Date
    Sep 2002
    Location
    Kyiv, Ukraine
    Posts
    77

    Post

    Originally posted by r937
    dynasets? what are dynasets?

    general database concepts do not include dynasets, i believe those are proprietary thingies from one particular vendor
    Can't say anything concret because didn't meet any other DB except Microsoft Access yet. And Microsoft gave this name name to so called 'temporary' table which are created (formed) by the actual query ...

    general database concepts do include views, which are definitions that are stored in the database's sytem catalog

    querying a view is just as efficient as querying a table

    views do not involve a significant amount of overhead, although it is not zero
    Querring a dynaset, as far as I understand, makes DB launch another query to get this dynaset ... So the main difference is that we make so called 'two-SELECT'-query instead of using one.

    Yes, of course, you can meet with such reports where you will have two use even more connected SELECT-queries but I just wanted to ask if it is right to use as less of them for the query as possible?
    Yours faithfully,
    Yaroslav Zaremba

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, okay, in ms access, you can save a query and then query it

    as far as i know, this is resolved when the database engine parses the query, by "merging" it with the definition of the underlying query

    the result is then executed with no appreciable loss of performance as if it had been written as one combined query of a table

    i hope that makes sense

    rudy

  5. #5
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697

    Just a thought...

    It would behoove you to get a good book (or some proper classes!) on DBMS's so your world can one day expand beyond Access.

    <a href="http://www.dbdebunk.com/books.htm">Here's a few good ones.</a> A good introductory book would be, well, Introduction to Database Systems. (Disclaimer: I'm just a fan of the authors.)

  6. #6
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    I'm looking for a good in-depth book on database theory myself. Are you referring to the author CJ Date?

  7. #7
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Yes. Date's "Introduction to Database Systems" covers all the basics and introduces a good deal of advanced material. If all you've ever used is SQL, you'll find it a real eye-opener.

    I wouldn't say it's a book that focuses on theory so much as a book that provides thorough coverage of the state of the art. He does, for example, explain exactly what relational algebra is and how it works, but you won't see lots of equations and squiggles or a whole lot of proofs.

    Rather, he explains the fundamentals that make an RDBMS an RDBMS. Once you've covered that you should be able to learn a new DBMS in a day since you'll be able to tell what's relational and what's window dressing.

    I'm also curious about studying relational theory in a more formal manner but haven't had a chance to yet. Best guess would be Date's copius references listed in each chapter. You might also check your favorite bookstore for a textbook on Discrete Mathematics for background material. (Relational theory is based on set theory and predicate calculus, so those are the basic prerequisites. I've got a few textbooks that mention them, but couldn't really recommend them as their either not very good or not well suited for someone interested in relational theory.)

  8. #8
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    For those interested in the Mathematics, the following book is very good:

    Introductory Logic and Sets for Computer Scientists (ICSS)
    Nimal Nissanke; Addison Wesley; ISBN: 0201179571

  9. #9
    Join Date
    Oct 2002
    Location
    NZ
    Posts
    25

    Views -

    Aza,

    you said:

    *******
    Querring a dynaset, as far as I understand, makes DB launch another query to get this dynaset ... So the main difference is that we make so called 'two-SELECT'-query instead of using one.
    *******

    In terms of physical implementation in MS Access regarding "dynasets" vs views I'm not sure whats going on. I understand dynasets to be more analagous to (perhaps) cursors in procedural SQL. But then you in procedural situations you have to retrieve sets of data then work on them from within your code.

    However, in relational databases a view is simply a saved (named) query. The optimiser will convert any query on a view to a query on the base table. It does not do a double query as you suggest. The overhead is minimal and views are desirable for a number of reasons ;

    1. You can build security into your data by using views, ie give users access to the view only and hence hide data you don't want them to see.

    2. Good for saving often used complex queries

    3. Views can logically restructure data in perhaps a more convenient form

    4. Most importantly perhaps - logical data independance. Restructuring of the database may from time to time be necessary - such as expanding existing tables with new attributes, reallocation of attributes across tables - views allow you to hide this from users - or more importantly to applications that access the data.

    By the way if you are storing important data, (data that the organisation cannot afford to be without for any period of time) I recommend you move away from Access.

    Cheers
    s.

  10. #10
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697

    Re: Views -

    Originally posted by steve63
    By the way if you are storing important data, (data that the organisation cannot afford to be without for any period of time) I recommend you move away from Access.
    This is a good point. The natural move (which I believe Microsoft is pushing) would be to Microsoft Desktop Engine (MSDE), which is a scaled down version of MS SQL Server.

    If you own Visual Studio, you can redistribute MSDE with your applications, but read the license agreement and redistribution agreement very carefully.

    If you don't need to redistribute your application and you own Office 2000, then you can safely use MSDE instead of Access.

Posting Permissions

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