Performance Question ?

Discussion in 'ASP General' started by CP, Apr 8, 2007.

  1. CP

    CP Guest

    Hello:

    I have a database with 300,000 records. I have two "DATE" columns and
    I need to calcluate the difference and display the number of days in
    one of the reports. I was wondering if this calculation of days should
    be done on the fly or is it OK to have a "Difference Date"
    column[Contains Number of Days] and retrieve the same ?

    Their will be not more than 50 concurrent users accesing it. However,
    in the next 6 months the record size is expected to reach 800,000. The
    database will be hosted on a shared hosting on the internet.

    Kindly share your thoughts.

    Thanks.
    CP, Apr 8, 2007
    #1
    1. Advertising

  2. CP wrote:
    > Hello:
    >
    > I have a database with 300,000 records. I have two "DATE" columns and
    > I need to calcluate the difference and display the number of days in
    > one of the reports. I was wondering if this calculation of days should
    > be done on the fly or is it OK to have a "Difference Date"
    > column[Contains Number of Days] and retrieve the same ?
    >
    > Their will be not more than 50 concurrent users accesing it. However,
    > in the next 6 months the record size is expected to reach 800,000. The
    > database will be hosted on a shared hosting on the internet.
    >
    > Kindly share your thoughts.
    >
    > Thanks.


    What database type and version please?
    I guess the answer depends: will ALL the records be displayed in the report?
    Or a subset? Will the difference be aggregated (max difference, avg
    difference, etc.)? What reporting software? should you decide to store the
    difference, are you prepared to handle the recalculation of the difference
    when either of the dates changes?

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Apr 8, 2007
    #2
    1. Advertising

  3. CP

    CP Guest

    On Apr 8, 3:40 pm, "Bob Barrows [MVP]" <>
    wrote:
    > CP wrote:
    > > Hello:

    >
    > > I have a database with 300,000 records. I have two "DATE" columns and
    > > I need to calcluate the difference and display the number of days in
    > > one of the reports. I was wondering if this calculation of days should
    > > be done on the fly or is it OK to have a "Difference Date"
    > > column[Contains Number of Days] and retrieve the same ?

    >
    > > Their will be not more than 50 concurrent users accesing it. However,
    > > in the next 6 months the record size is expected to reach 800,000. The
    > > database will be hosted on a shared hosting on the internet.

    >
    > > Kindly share your thoughts.

    >
    > > Thanks.

    >
    > What database type and version please?
    > I guess the answer depends: will ALL the records be displayed in the report?
    > Or a subset? Will the difference be aggregated (max difference, avg
    > difference, etc.)? What reporting software? should you decide to store the
    > difference, are you prepared to handle the recalculation of the difference
    > when either of the dates changes?
    >


    1. SQL Server 8.0 with SP2 on Windows Advanced Server 2000

    2. Not more than 100 recorrds per report

    3.No the difference will not be aggregated. Just display the
    difference.

    4.Reports will be generated by user query through a ASP page.

    Thanks.

    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"- Hide quoted text -
    >
    > - Show quoted text -
    CP, Apr 8, 2007
    #3
  4. CP wrote:
    >>
    >> What database type and version please?
    >> I guess the answer depends: will ALL the records be displayed in the
    >> report? Or a subset? Will the difference be aggregated (max
    >> difference, avg difference, etc.)? What reporting software? should
    >> you decide to store the difference, are you prepared to handle the
    >> recalculation of the difference when either of the dates changes?
    >>

    >
    > 1. SQL Server 8.0 with SP2 on Windows Advanced Server 2000
    >
    > 2. Not more than 100 recorrds per report
    >
    > 3.No the difference will not be aggregated. Just display the
    > difference.
    >
    > 4.Reports will be generated by user query through a ASP page.
    >


    Then I would calculate them on the fly, probably in the query used to
    retrieve the resultset.

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Apr 8, 2007
    #4
  5. "Bob Barrows [MVP]" <> wrote in message
    news:OcV%...
    > CP wrote:
    >>>
    >>> What database type and version please?
    >>> I guess the answer depends: will ALL the records be displayed in the
    >>> report? Or a subset? Will the difference be aggregated (max
    >>> difference, avg difference, etc.)? What reporting software? should
    >>> you decide to store the difference, are you prepared to handle the
    >>> recalculation of the difference when either of the dates changes?
    >>>

    >>
    >> 1. SQL Server 8.0 with SP2 on Windows Advanced Server 2000
    >>
    >> 2. Not more than 100 recorrds per report
    >>
    >> 3.No the difference will not be aggregated. Just display the
    >> difference.
    >>
    >> 4.Reports will be generated by user query through a ASP page.
    >>

    >
    > Then I would calculate them on the fly, probably in the query used to
    > retrieve the resultset.


    Actually, in this case, you can have your cake and eat it too, a computed
    column is the best of both worlds: it's value is automatically maintained so
    its always as current as a derived column in a view or ad hoc SQL, but
    internally a computed column is only recalculated if/when one of its
    operands changes. (On the fly calcs must be performed every time the data
    is queried.)

    Further, as long as no sub-expression in the formula is non-deterministic,
    computed columns can be indexed, and further still, computed columns do not
    count against the 8060 row size limit, when inserting/updating rows in the
    table. (Their size does count in a select statement, but that's
    unavoidable.)


    -Mark





    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    Mark J. McGinty, Apr 9, 2007
    #5
  6. Mark J. McGinty wrote:
    >
    > Actually, in this case, you can have your cake and eat it too, a
    > computed column is the best of both worlds: it's value is
    > automatically maintained so its always as current as a derived column
    > in a view or ad hoc SQL, but internally a computed column is only
    > recalculated if/when one of its operands changes. (On the fly calcs
    > must be performed every time the data is queried.)
    >
    > Further, as long as no sub-expression in the formula is
    > non-deterministic, computed columns can be indexed, and further
    > still, computed columns do not count against the 8060 row size limit,
    > when inserting/updating rows in the table. (Their size does count in
    > a select statement, but that's unavoidable.)
    >
    >

    Thanks. I wasn't sure if computed columns were available in SQL2000. I just
    looked it up in BOL and confirmed it.

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Apr 9, 2007
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Don Beal
    Replies:
    13
    Views:
    839
    Richard Grimes [MVP]
    Sep 29, 2003
  2. jm
    Replies:
    1
    Views:
    507
    alien2_51
    Dec 12, 2003
  3. Cris Rock

    Performance related Question.....

    Cris Rock, Feb 12, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    312
    Stefano Mostarda
    Feb 12, 2004
  4. cjl
    Replies:
    3
    Views:
    986
    John Nagle
    May 21, 2007
  5. Software Engineer
    Replies:
    0
    Views:
    324
    Software Engineer
    Jun 10, 2011
Loading...

Share This Page