many DISTINCT queries

Hi,

I have a table that contains log data, usually around a million records. The
table has about 10 columns with various attributes of the logged data,
nothing special. We're using SQL Server 2000.

Some of the columns (for example "category") have duplicate values
throughout the records. We have a web page that queries the table to show
all the unique columns, for example:

select distinct CATEGORY from table TEST

Obviously the server has to scan all rows in order to get all unique columns
which takes quite a while, especially since that web page contains several
of these types of queries. We also have a MAX(DATE) and MIN(DATE) query that
also add to the load.

I already created indexes on the CATEGORY (actually on all categories)
column which might help a little but I'm pretty sure that there has got to
be a better way.

I also create a view (select distinct CATEGORY from table TEST) and tried to
index it, but it won't let me index a query that contains a DISTINCT
statement.

Isn't there a way to create an index that contains only the distinct values?
Is there another way to speed this up?

Thanks for any hints!"Florian" <REMOVEUPPERCASEwizard_oz@.gmx.net> wrote in message
news:_cOUb.13574$F23.3296@.newsread2.news.pas.earth link.net...
> Hi,
> I have a table that contains log data, usually around a million records.
The
> table has about 10 columns with various attributes of the logged data,
> nothing special. We're using SQL Server 2000.
> Some of the columns (for example "category") have duplicate values
> throughout the records. We have a web page that queries the table to show
> all the unique columns, for example:
> select distinct CATEGORY from table TEST
> Obviously the server has to scan all rows in order to get all unique
columns
> which takes quite a while, especially since that web page contains several
> of these types of queries. We also have a MAX(DATE) and MIN(DATE) query
that
> also add to the load.
> I already created indexes on the CATEGORY (actually on all categories)
> column which might help a little but I'm pretty sure that there has got to
> be a better way.
> I also create a view (select distinct CATEGORY from table TEST) and tried
to
> index it, but it won't let me index a query that contains a DISTINCT
> statement.
> Isn't there a way to create an index that contains only the distinct
values?
> Is there another way to speed this up?
>
> Thanks for any hints!

If only you load/update the data relatively infrequently, then you could
create a 'lookup' table for each attribute, and populate them from the main
table after loading it (rather like the dimensions in a star schema):

insert into dbo.Categories (Category)
select distinct Category
from dbo.Test

Your client code could then query the lookup tables instead of the log
table. If you want to use indexed views, then you could create a view like
this:

create view dbo.Categories
with schemabinding
as
select Category, count_big(*) as 'Occurrences'
from dbo.Test
group by Category

That should be indexable, although there are quite a few other restrictions,
so you would need to check them out. But having multiple indexed views on
the table would make data modifications much slower, so if the data changes
frequently you might have to use some sort of lookup table approach anyway.

Simon|||> select distinct CATEGORY from table TEST
Try experimenting with group by instead of distinct. Also in query
analyzer, enable view execution plan, to get an idea what mssql is
doing. "select category from mytable group by category"|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:4023d837$1_1@.news.bluewin.ch...
> "Florian" <REMOVEUPPERCASEwizard_oz@.gmx.net> wrote in message
> news:_cOUb.13574$F23.3296@.newsread2.news.pas.earth link.net...
> > Hi,
> > I have a table that contains log data, usually around a million records.
> The
> > table has about 10 columns with various attributes of the logged data,
> > nothing special. We're using SQL Server 2000.
> > Some of the columns (for example "category") have duplicate values
> > throughout the records. We have a web page that queries the table to
show
> > all the unique columns, for example:
> > select distinct CATEGORY from table TEST
> > Obviously the server has to scan all rows in order to get all unique
> columns
> > which takes quite a while, especially since that web page contains
several
> > of these types of queries. We also have a MAX(DATE) and MIN(DATE) query
> that
> > also add to the load.
> > I already created indexes on the CATEGORY (actually on all categories)
> > column which might help a little but I'm pretty sure that there has got
to
> > be a better way.
> > I also create a view (select distinct CATEGORY from table TEST) and
tried
> to
> > index it, but it won't let me index a query that contains a DISTINCT
> > statement.
> > Isn't there a way to create an index that contains only the distinct
> values?
> > Is there another way to speed this up?
> > Thanks for any hints!
> If only you load/update the data relatively infrequently, then you could
> create a 'lookup' table for each attribute, and populate them from the
main
> table after loading it (rather like the dimensions in a star schema):
> insert into dbo.Categories (Category)
> select distinct Category
> from dbo.Test
> Your client code could then query the lookup tables instead of the log
> table. If you want to use indexed views, then you could create a view like
> this:
> create view dbo.Categories
> with schemabinding
> as
> select Category, count_big(*) as 'Occurrences'
> from dbo.Test
> group by Category
> That should be indexable, although there are quite a few other
restrictions,
> so you would need to check them out. But having multiple indexed views on
> the table would make data modifications much slower, so if the data
changes
> frequently you might have to use some sort of lookup table approach
anyway.

Thanks, I tried the indexed view and that seems to work OK now, pretty
fast - can't complain. Data shouldn't be updated that often so that should
be OK. Otherwise I might have to go with a lookup table - but it's not a
real good solution for our scenario for reasons I'm not going to bore
anybody with :)

Thanks!|||"louis nguyen" <louisducnguyen@.hotmail.com> wrote in message
news:b0e9d53.0402061216.5d4f6e56@.posting.google.co m...
> > select distinct CATEGORY from table TEST
> Try experimenting with group by instead of distinct. Also in query
> analyzer, enable view execution plan, to get an idea what mssql is
> doing. "select category from mytable group by category"

Yes, the group thing worked great - I also analyzed the execution plan and
now it's only returning the actual number of rows I'm getting - not the
whole table anymore.

Thanks.

Managing Very Large number of rows in SQL Server

Hi,
We have a need to manage about 900 million rows/ year of data of one
kind. I am looking for suggestions on how best to design a database table(s)
to handle this.
These data is essentially information - across various markets and time. We
need the ability to search across markets/ time and update across markets
and time.
I did some basic tests and extrapolating the data would lead me to believe
that if I were have this as a single table the table size will be over 100
GB.
Thanks
* Get a fast machine with a lot of RAM.
* Have much patience!
* Look into partitioned tables.
* Look into OLAP cubes.
Which is only to say, as the scale of your app challenges the hardware, be
very sure you know what your real requirements are. A minor glitch in design
can cost you big, when your data is big, but what's a glitch and what's a
feature depends on the situation.
Sounds like fun anyway, good luck!
Josh
"shikarishambu" wrote:

> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database table(s)
> to handle this.
> These data is essentially information - across various markets and time. We
> need the ability to search across markets/ time and update across markets
> and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
>
>
|||Hire a pro to do the design and spec work. To do anything other is to set
yourself up for extreme disappointment. The cost now will be a LOT less
than in the future when your system is live and non-performant!! :-)
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
news:E4D50946-48C9-4264-B344-E35F8E757FAF@.microsoft.com...[vbcol=seagreen]
>* Get a fast machine with a lot of RAM.
> * Have much patience!
> * Look into partitioned tables.
> * Look into OLAP cubes.
> Which is only to say, as the scale of your app challenges the hardware, be
> very sure you know what your real requirements are. A minor glitch in
> design
> can cost you big, when your data is big, but what's a glitch and what's a
> feature depends on the situation.
> Sounds like fun anyway, good luck!
> Josh
>
> "shikarishambu" wrote:
|||Indexing and partitioning will be your friend with a huge table such as
this. Also if you find a need to update large amounts of data consider
doing it in batches to avoid lock escalation issues.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"shikarishambu" <shikarishambu70@.hotmail.com> wrote in message
news:%23hJvuDOPIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database
> table(s) to handle this.
> These data is essentially information - across various markets and time.
> We need the ability to search across markets/ time and update across
> markets and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
>
|||On Wed, 12 Dec 2007 14:49:29 -0600, "TheSQLGuru"
<kgboles@.earthlink.net> wrote:

>Hire a pro to do the design and spec work. To do anything other is to set
>yourself up for extreme disappointment. The cost now will be a LOT less
>than in the future when your system is live and non-performant!! :-)
Never time to do it right, always time to do it over.
J.
|||Well, up to the point where it stops functioning or meeting SLAs. Then they
call for me (or another performance consultant) in a panic!! ;)
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:sls1m3liavtoscl3l9doi5ajrg2ak2rs7j@.4ax.com...
> On Wed, 12 Dec 2007 14:49:29 -0600, "TheSQLGuru"
> <kgboles@.earthlink.net> wrote:
>
> Never time to do it right, always time to do it over.
> J.
>
|||On Dec 12, 11:03 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database table(s)
> to handle this.
> These data is essentially information - across various markets and time. We
> need the ability to search across markets/ time and update across markets
> and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
Maybe you can design your database so it vertically partitions your
data into 90 tables with 10 milion records per table.

Managing Very Large number of rows in SQL Server

Hi,
We have a need to manage about 900 million rows/ year of data of one
kind. I am looking for suggestions on how best to design a database table(s)
to handle this.
These data is essentially information - across various markets and time. We
need the ability to search across markets/ time and update across markets
and time.
I did some basic tests and extrapolating the data would lead me to believe
that if I were have this as a single table the table size will be over 100
GB.
Thanks* Get a fast machine with a lot of RAM.
* Have much patience!
* Look into partitioned tables.
* Look into OLAP cubes.
Which is only to say, as the scale of your app challenges the hardware, be
very sure you know what your real requirements are. A minor glitch in desig
n
can cost you big, when your data is big, but what's a glitch and what's a
feature depends on the situation.
Sounds like fun anyway, good luck!
Josh
"shikarishambu" wrote:

> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database table(
s)
> to handle this.
> These data is essentially information - across various markets and time. W
e
> need the ability to search across markets/ time and update across markets
> and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
>
>|||Hire a pro to do the design and spec work. To do anything other is to set
yourself up for extreme disappointment. The cost now will be a LOT less
than in the future when your system is live and non-performant!! :-)
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
news:E4D50946-48C9-4264-B344-E35F8E757FAF@.microsoft.com...[vbcol=seagreen]
>* Get a fast machine with a lot of RAM.
> * Have much patience!
> * Look into partitioned tables.
> * Look into OLAP cubes.
> Which is only to say, as the scale of your app challenges the hardware, be
> very sure you know what your real requirements are. A minor glitch in
> design
> can cost you big, when your data is big, but what's a glitch and what's a
> feature depends on the situation.
> Sounds like fun anyway, good luck!
> Josh
>
> "shikarishambu" wrote:
>|||Indexing and partitioning will be your friend with a huge table such as
this. Also if you find a need to update large amounts of data consider
doing it in batches to avoid lock escalation issues.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"shikarishambu" <shikarishambu70@.hotmail.com> wrote in message
news:%23hJvuDOPIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database
> table(s) to handle this.
> These data is essentially information - across various markets and time.
> We need the ability to search across markets/ time and update across
> markets and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
>|||On Wed, 12 Dec 2007 14:49:29 -0600, "TheSQLGuru"
<kgboles@.earthlink.net> wrote:

>Hire a pro to do the design and spec work. To do anything other is to set
>yourself up for extreme disappointment. The cost now will be a LOT less
>than in the future when your system is live and non-performant!! :-)
Never time to do it right, always time to do it over.
J.|||Well, up to the point where it stops functioning or meeting SLAs. Then they
call for me (or another performance consultant) in a panic!! ;)
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:sls1m3liavtoscl3l9doi5ajrg2ak2rs7j@.
4ax.com...
> On Wed, 12 Dec 2007 14:49:29 -0600, "TheSQLGuru"
> <kgboles@.earthlink.net> wrote:
>
> Never time to do it right, always time to do it over.
> J.
>|||On Dec 12, 11:03 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database table(
s)
> to handle this.
> These data is essentially information - across various markets and time. W
e
> need the ability to search across markets/ time and update across markets
> and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
Maybe you can design your database so it vertically partitions your
data into 90 tables with 10 milion records per table.

Managing Very Large number of rows in SQL Server

Hi,
We have a need to manage about 900 million rows/ year of data of one
kind. I am looking for suggestions on how best to design a database table(s)
to handle this.
These data is essentially information - across various markets and time. We
need the ability to search across markets/ time and update across markets
and time.
I did some basic tests and extrapolating the data would lead me to believe
that if I were have this as a single table the table size will be over 100
GB.
Thanks* Get a fast machine with a lot of RAM.
* Have much patience!
* Look into partitioned tables.
* Look into OLAP cubes.
Which is only to say, as the scale of your app challenges the hardware, be
very sure you know what your real requirements are. A minor glitch in design
can cost you big, when your data is big, but what's a glitch and what's a
feature depends on the situation.
Sounds like fun anyway, good luck!
Josh
"shikarishambu" wrote:
> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database table(s)
> to handle this.
> These data is essentially information - across various markets and time. We
> need the ability to search across markets/ time and update across markets
> and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
>
>|||Hire a pro to do the design and spec work. To do anything other is to set
yourself up for extreme disappointment. The cost now will be a LOT less
than in the future when your system is live and non-performant!! :-)
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
news:E4D50946-48C9-4264-B344-E35F8E757FAF@.microsoft.com...
>* Get a fast machine with a lot of RAM.
> * Have much patience!
> * Look into partitioned tables.
> * Look into OLAP cubes.
> Which is only to say, as the scale of your app challenges the hardware, be
> very sure you know what your real requirements are. A minor glitch in
> design
> can cost you big, when your data is big, but what's a glitch and what's a
> feature depends on the situation.
> Sounds like fun anyway, good luck!
> Josh
>
> "shikarishambu" wrote:
>> Hi,
>> We have a need to manage about 900 million rows/ year of data of one
>> kind. I am looking for suggestions on how best to design a database
>> table(s)
>> to handle this.
>> These data is essentially information - across various markets and time.
>> We
>> need the ability to search across markets/ time and update across markets
>> and time.
>> I did some basic tests and extrapolating the data would lead me to
>> believe
>> that if I were have this as a single table the table size will be over
>> 100
>> GB.
>> Thanks
>>|||Indexing and partitioning will be your friend with a huge table such as
this. Also if you find a need to update large amounts of data consider
doing it in batches to avoid lock escalation issues.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"shikarishambu" <shikarishambu70@.hotmail.com> wrote in message
news:%23hJvuDOPIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database
> table(s) to handle this.
> These data is essentially information - across various markets and time.
> We need the ability to search across markets/ time and update across
> markets and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
>|||On Wed, 12 Dec 2007 14:49:29 -0600, "TheSQLGuru"
<kgboles@.earthlink.net> wrote:
>Hire a pro to do the design and spec work. To do anything other is to set
>yourself up for extreme disappointment. The cost now will be a LOT less
>than in the future when your system is live and non-performant!! :-)
Never time to do it right, always time to do it over.
J.|||Well, up to the point where it stops functioning or meeting SLAs. Then they
call for me (or another performance consultant) in a panic!! ;)
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:sls1m3liavtoscl3l9doi5ajrg2ak2rs7j@.4ax.com...
> On Wed, 12 Dec 2007 14:49:29 -0600, "TheSQLGuru"
> <kgboles@.earthlink.net> wrote:
>>Hire a pro to do the design and spec work. To do anything other is to set
>>yourself up for extreme disappointment. The cost now will be a LOT less
>>than in the future when your system is live and non-performant!! :-)
> Never time to do it right, always time to do it over.
> J.
>|||On Dec 12, 11:03 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database table(s)
> to handle this.
> These data is essentially information - across various markets and time. We
> need the ability to search across markets/ time and update across markets
> and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
Maybe you can design your database so it vertically partitions your
data into 90 tables with 10 milion records per table.