473,407 Members | 2,306 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,407 software developers and data experts.

Performance Question ?

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.

Apr 8 '07 #1
5 1395
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"
Apr 8 '07 #2
CP
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 -

Apr 8 '07 #3
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"
Apr 8 '07 #4

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:Oc****************@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"

Apr 9 '07 #5
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"
Apr 9 '07 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

25
by: Brian Patterson | last post by:
I have noticed in the book of words that hasattr works by calling getattr and raising an exception if no such attribute exists. If I need the value in any case, am I better off using getattr...
12
by: Fred | last post by:
Has anyone a link or any information comparing c and c++ as far as execution speed is concerned? Signal Processing algorithms would be welcome... Thanks Fred
12
by: serge | last post by:
I have an SP that is big, huge, 700-800 lines. I am not an expert but I need to figure out every possible way that I can improve the performance speed of this SP. In the next couple of weeks I...
6
by: teedilo | last post by:
We have an application with a SQL Server 2000 back end that is fairly database intensive -- lots of fairly frequent queries, inserts, updates -- the gamut. The application does not make use of...
5
by: Scott | last post by:
I have a customer that had developed an Access97 application to track their business information. The application grew significantly and they used the Upsizing Wizard to move the tables to SQL...
115
by: Mark Shelor | last post by:
I've encountered a troublesome inconsistency in the C-language Perl extension I've written for CPAN (Digest::SHA). The problem involves the use of a static array within a performance-critical...
13
by: bjarne | last post by:
Willy Denoyette wrote; > ... it > was not the intention of StrousTrup to the achieve the level of efficiency > of C when he invented C++, ... Ahmmm. It was my aim to match the performance...
13
by: Bern McCarty | last post by:
I have run an experiment to try to learn some things about floating point performance in managed C++. I am using Visual Studio 2003. I was hoping to get a feel for whether or not it would make...
7
by: Michael D. Ober | last post by:
When calling Enqueue, the internal array may need to be reallocated. My question is by how much? In the old MFC array classes, you could tell MFC how many additional elements to add to the array...
1
by: jvn | last post by:
I am experiencing a particular problem with performance counters. I have created a set of classes, that uses System.Diagnostics.PerformanceCounter to increment custom performance counters (using...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.