Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2002
    Posts
    37

    Question view performance

    sorry for this simple question, but it's important to me.
    we use db2 as database.
    i retrieve data from a table using the query below:

    select kod1, kod2, kod3, ad from t_hizmetler where rtrim(kod1) || rtrim(char(kod2)) || rtrim(char(kod3)) in ("+strbuffer.toString()+")

    would the performance increase if i would make a view with "rtrim(kod1) || rtrim(char(kod2)) || rtrim(char(kod3))" as a column and get the data from it instead of querying the table itself ? if yes, could someone explain the reason in short ?
    thanks in advance
    Mehmet

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: view performance

    Originally posted by msegmx
    sorry for this simple question, but it's important to me.
    we use db2 as database.
    i retrieve data from a table using the query below:

    select kod1, kod2, kod3, ad from t_hizmetler where rtrim(kod1) || rtrim(char(kod2)) || rtrim(char(kod3)) in ("+strbuffer.toString()+")

    would the performance increase if i would make a view with "rtrim(kod1) || rtrim(char(kod2)) || rtrim(char(kod3))" as a column and get the data from it instead of querying the table itself ? if yes, could someone explain the reason in short ?
    thanks in advance
    Mehmet
    Well, I don't know DB2, but I would be very surprised if it worked the way you suggest. A view is not a stored table with pre-calculated values like rtrim(kod1) || rtrim(char(kod2)) || rtrim(char(kod3)) stored in it. Rather it is a stored QUERY, so when you say:

    "SELECT concat_column FROM myview"

    it translates that to something like:

    "SELECT concat_column FROM ( SELECT rtrim(kod1) || rtrim(char(kod2)) || rtrim(char(kod3)) FROM mytable )"

    So no, it won't be quicker. It will just be easier to write and understand the query, which can be a good thing if it is a common requirement.

    Hopefully someone with DB2 knowledge can back me up (or not!)

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    absolutely right, tony, views are like that in all databases

    sql/server, i think, has something called a "materialized view" in recent releases, which, as you can imagine, is more than just the column definitions

    rudy

  4. #4
    Join Date
    Oct 2002
    Posts
    37
    Originally posted by r937
    absolutely right, tony, views are like that in all databases

    sql/server, i think, has something called a "materialized view" in recent releases, which, as you can imagine, is more than just the column definitions

    rudy
    That helped.
    Thanks for sharing your knowledge.

    Mehmet

Posting Permissions

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