Performance Question ?

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.

Bob
Sun Apr 08 05:40:32 CDT 2007

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"

CP
Sun Apr 08 06:56:25 CDT 2007

On Apr 8, 3:40 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
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 -

Bob
Sun Apr 08 07:41:04 CDT 2007

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"

Mark
Sun Apr 08 23:23:55 CDT 2007


"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OcV%23dsdeHHA.4032@TK2MSFTNGP02.phx.gbl...
> 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"
>

Bob
Mon Apr 09 06:03:28 CDT 2007

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"