Re: Designing a Reporting Database
Date: Sat, 21 Jul 2001 23:26:11 GMT
Message-ID: <3b0e8257_1_at_news3.prserv.net>
Todd,
Microsoft, when they introduced SQL Server 2000, had some metrics ... obviously, the increase in performance is very dependent upon the data ... but you should be able to get an idea via a simple experiment:
create a table with an enormous number of rows .. at least a million. I suggest an algorithm something like:
create table Todd (c1 int default 1, c2 char (8000) default ' ')
insert into Todd default values
insert into Todd select * from Todd
insert into Todd select * from Todd
repeat the insert into .. select .. from about 20 times, and that should give you more than million rows ... incidentally, the reason for this particular table schema is so that each row will take a full page ... thereby using a lot of disk space and thus a lot physical I/O.
Run the query "select count(*) from Todd" and time it.
Then create as an indexed view:
select count(*) from Todd
and run this, and time it.
BTW, to do this you need either the Developer Edition or the Enterprise Edition of SQL Server 2000 ... any edition of SQL Server 2000 supports creating indexed views, but only these two will actually make use of indexed views !!
Second BTW, a very nifty feature of SQL Server is that the query optimizer is intelligent enough to use an indexed view (if it exists) even without an explicit reference to it ... so once you create the indexed view over "select count(*) from Todd", just running the query (without an explicit reference to the view) should still cause the indexed view to be used.
Performance gains, as I said, are very data dependent, but one can easily get two order of magnitudes gain in performance.
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which can be cut and pasted into Query Analyzer is appreciated.
"Todd Rubel" <trubel_at_oakgroup.com> wrote in message
news:d272bee7.0105250715.41e718a1_at_posting.google.com...
> I'm wondering about the speed of an indexed view as compared to an
> indexed table. I am already leaning towards the indexed view (just from
> a maintenance/development standpoint) but I'd like to see the actual
> numbers. If you have an idea or know where I could find out, I'd really
> appreciate it.
>
> "BP Margolin" <bpmargo_at_attglobal.net> wrote in message
news:<3b0d425f_1_at_news3.prserv.net>...
>
> > Todd,
> >
> > When I mentioned OLAP I wasn't specifically talking about Analysis
Services,
> > although I can certainly see the confusion. OLAP applications have been
> > implemented on relational platforms, so I was asking whether the
application
> > itself was originally OLTP, and now the same set of tables are being
> > considered for DSS / OLAP purposes.
> >
> > It this is indeed the case, then I would certainly suggest, as I
mentioned
> > in the earlier post, seriously considering not hosting both OLTP and DSS
/
> > OLAP applications on the same server in the same database.
> >
> > SQL Server 2000 does indeed support indexed views.
> >
> > -------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.
Received on Sun Jul 22 2001 - 01:26:11 CEST