Re: Suggestion for Handling Very Large Tables (SQL 2000)
- From: "JT" <someone@xxxxxxxxxxxxx>
- Date: Wed, 22 Feb 2006 09:17:20 -0500
So you have very large tables in the operational environment that you
want to partition, but you still need to provide a convenient method for
developers to query against them? This is a common architectural issue, and
the standard answer is called [partitioned views].
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_06_17zr.asp
http://www.microsoft.com/sql/prodinfo/previousversions/scalability.mspx
Also, there are techniques for near real time refreshing of OLAP data,
but whether or not you choose to go this additional step should perhaps
depend on what impact would result from the users querying against the
tables directly from the operational system. If the data is not that rich
(just a log table with few attributes of interest to users), then it may not
be worth the expense and maintenance of setting up a server for Analysis
Services.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_real-timeolap.asp
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/anservog.mspx
"Christian Nunciato" <ChristianNunciato@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:5BF9AF00-3A74-4044-A203-2E5CAA48D18D@xxxxxxxxxxxxxxxx
Hi all:
I just learned today that a project I'm working on has a couple of very
large tables that need, somehow, to be optimized for searching. One table
has roughly 7 million rows, and another, its relative, has over 16
million.
At present, client applications only insert into these tables, but our
users
have expressed a desire to select from them as well, using a small set of
search criteria, so we need to come up with an appropriate approach. At
the
very least, to continue the application as it exists today, it seems to me
the two tables need to be trimmed down -- perhaps have their data archived
into separate tables, or databases, or some other approach -- but if users
also want to begin reporting on this data, allowing them to select against
it
could bring the server, which is already overburdened, to its knees.
I'm an application developer and not a DBA, so I'm looking to you all for
some best-practices suggestions, here. I've looked briefly into SQL
Analysis
Services, but I'm not sure thatt's the right approach for us, since it
appears Analysis Services is merely an interface for handling data
processing, and our users need real-time data (or as close to real-time as
possible -- big surprise, eh?). I'm wondering if simply segmenting the
data
(which is very simply stored -- user ID, thing ID, date/time stamp, and
that's pretty much it) into separate tables might be an acceptable
approach,
but that approach also seems a little crude to me. How do DBAs handle
very
large t ables like this in a way that's least cumbersome to the
client-side
developers and end users?
Thanks hugely in advance,
Chris
.
- Prev by Date: Re: Design opinion?
- Next by Date: Re: CRM - Warehouse integration
- Previous by thread: Re: Need an expert advice
- Next by thread: Re: Suggestion for Handling Very Large Tables (SQL 2000)
- Index(es):
Relevant Pages
|
Loading
