Performance Question ?

C

CP

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.
 
B

Bob Barrows [MVP]

CP said:
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?
 
C

CP

CP said:
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.

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.
 
B

Bob Barrows [MVP]

CP said:
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.
 
M

Mark J. McGinty

Bob Barrows said:
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
 
B

Bob Barrows [MVP]

Mark said:
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.
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top