querying a view of a lot tables

[Anthony]

I have setup a database to record our IP Account from ALL of our routers in
a form. It's very slow and sometimes times out.. which is NO GOOD.. I was
looking for a better way to manage this data. See below :

Sample from one router table data looks like this :
source destination packets bytes date/time
172.25.40.172 10.30.50.50 10 748.0 2004-07-04 04:00:00.000 rda01
172.25.40.52 10.10.42.230 4 1088.0 2004-07-04 06:00:00.000 rda01
172.25.40.52 172.25.16.83 4 384.0 2004-07-04 11:00:00.000 rda01
172.25.40.151 172.29.5.50 12 756.0 2004-07-04 12:00:00.000 rda01
172.25.49.50 172.25.46.255 118 55224.0 2004-07-04 12:00:00.000
.... but there are thousands of rows in about 15 different tables...

then I have a view (called RALL) which joins (Or unions not sure the
difference) all the router tables like so :
CREATE VIEW dbo.RALL
AS
SELECT *
FROM dbo.RC01
UNION
SELECT *
FROM dbo.RD01
UNION
....
and there are about 15 different tables...

when I run a query against all of the tables it takes a LONG LONG time to
run...(About 45 seconds max) anyway.. the query I am running is like so :
SELECT top 20 source,destination,sum(packets) as 'PACKETS',sum(bytes) as
'BYTES',sum(bytes) / sum(packets) as 'Avg Byt/Pkt' from RALL WHERE datetime
BETWEEN '8/6/2004 00:00' and '8/6/2004 23:00' group by source,destination
order by sum(bytes) desc

is there a faster way to get what I am looking for... (Maybe indexing but I
don't think that will help) or at least a better way to get my data out of
it? The timeouts on the webpage are killing me and I don't think increasing
the timeout will help too much....

Thank you in advance..

[Bob Barrows [MVP]]

Anthony wrote:

>
> is there a faster way to get what I am looking for...

Yes. Put all the data into a single table with the proper index. That is the
only way to speed this up.

Bob Barrows
--
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"

[Anthony]

I can do that... but is it ok for this table to have about 30,000 rows? And
I am amazed that it would be faster but I will trust you...

Thank you.

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eV80qpAfEHA.2824@TK2MSFTNGP10.phx.gbl...

> Anthony wrote:

> >
> > is there a faster way to get what I am looking for...

>
> Yes. Put all the data into a single table with the proper index. That is

the

> only way to speed this up.
>
> Bob Barrows
> --
> 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"
>
>

[Bob Barrows [MVP]]

Anthony wrote:

> I can do that... but is it ok for this table to have about 30,000
> rows? And I am amazed that it would be faster but I will trust you...

I get good response from properly indexed tables containing millions of
rows.

Bob Barrows
--
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"

[Aaron [SQL Server MVP]]

Yes, 30,000 rows is laughably easy for SQL Server to handle. This sounds
like a properties table to me, ready to serve semantics to queries against
*real* data (millions and millions of rows).

However, keeping all this like data in 15 distinct tables is going to be a
maintenance and performance nightmare. Do yourself a favor and design it
right.

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)





"Anthony" <antgoodlife@nospam.comcast.net> wrote in message
news:Olsz1tAfEHA.720@TK2MSFTNGP11.phx.gbl...

>I can do that... but is it ok for this table to have about 30,000 rows?
>And
> I am amazed that it would be faster but I will trust you...
>
> Thank you.
>
> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:eV80qpAfEHA.2824@TK2MSFTNGP10.phx.gbl...

>> Anthony wrote:

>> >
>> > is there a faster way to get what I am looking for...

>>
>> Yes. Put all the data into a single table with the proper index. That is

> the

>> only way to speed this up.
>>
>> Bob Barrows
>> --
>> 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"
>>
>>

>
>

Aaron, would the same hold true for a login system that captures user
personal information but also had foreign keys....

I currently have 3 foreign keys in the Master Propsects table as I wish to
correctly assign information like States, Countries and Source of Call to
Control Tables....

I also have a UserPath table to capture their page requests when they
login.......

Can you advise?

Thanks
Jason
"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:uvYDQzDfEHA.3632@TK2MSFTNGP11.phx.gbl...

> Yes, 30,000 rows is laughably easy for SQL Server to handle. This sounds
> like a properties table to me, ready to serve semantics to queries against
> *real* data (millions and millions of rows).
>
> However, keeping all this like data in 15 distinct tables is going to be a
> maintenance and performance nightmare. Do yourself a favor and design it
> right.
>
> --
> [url]http://www.aspfaq.com/[/url]
> (Reverse address to reply.)
>
>
>
>
>
> "Anthony" <antgoodlife@nospam.comcast.net> wrote in message
> news:Olsz1tAfEHA.720@TK2MSFTNGP11.phx.gbl...

> >I can do that... but is it ok for this table to have about 30,000 rows?
> >And
> > I am amazed that it would be faster but I will trust you...
> >
> > Thank you.
> >
> > "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> > news:eV80qpAfEHA.2824@TK2MSFTNGP10.phx.gbl...

> >> Anthony wrote:
> >> >
> >> > is there a faster way to get what I am looking for...
> >>
> >> Yes. Put all the data into a single table with the proper index. That

is

> > the

> >> only way to speed this up.
> >>
> >> Bob Barrows
> >> --
> >> 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"
> >>
> >>

> >
> >

>
>

[Bob Barrows [MVP]]

Joining tables is much different from UNIONing them. The qeury engine can
easily optimize JOIN queries, especially when the tables involved in the
JOINs are properly indexed.

The task of UNIONing tables is much more labor-intensive for the query
engine.

Bob Barrows

[email]jason@catamaranco.com[/email] wrote:

> Aaron, would the same hold true for a login system that captures user
> personal information but also had foreign keys....
>
> I currently have 3 foreign keys in the Master Propsects table as I
> wish to correctly assign information like States, Countries and
> Source of Call to Control Tables....
>
> I also have a UserPath table to capture their page requests when they
> login.......
>
> Can you advise?
>
> Thanks
> Jason
> "Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
> news:uvYDQzDfEHA.3632@TK2MSFTNGP11.phx.gbl...

>> Yes, 30,000 rows is laughably easy for SQL Server to handle. This
>> sounds like a properties table to me, ready to serve semantics to
>> queries against *real* data (millions and millions of rows).
>>
>> However, keeping all this like data in 15 distinct tables is going
>> to be a maintenance and performance nightmare. Do yourself a favor
>> and design it right.
>>
>> --
>> [url]http://www.aspfaq.com/[/url]
>> (Reverse address to reply.)
>>
>>
>>
>>
>>
>> "Anthony" <antgoodlife@nospam.comcast.net> wrote in message
>> news:Olsz1tAfEHA.720@TK2MSFTNGP11.phx.gbl...

>>> I can do that... but is it ok for this table to have about 30,000
>>> rows? And
>>> I am amazed that it would be faster but I will trust you...
>>>
>>> Thank you.
>>>
>>> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
>>> news:eV80qpAfEHA.2824@TK2MSFTNGP10.phx.gbl...
>>>> Anthony wrote:
>>>>>
>>>>> is there a faster way to get what I am looking for...
>>>>
>>>> Yes. Put all the data into a single table with the proper index.
>>>> That is the only way to speed this up.
>>>>
>>>> Bob Barrows
>>>> --
>>>> 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"

--
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"

Thank you - makes sense.

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eo7DwOIfEHA.3632@TK2MSFTNGP11.phx.gbl...

> Joining tables is much different from UNIONing them. The qeury engine can
> easily optimize JOIN queries, especially when the tables involved in the
> JOINs are properly indexed.
>
> The task of UNIONing tables is much more labor-intensive for the query
> engine.
>
> Bob Barrows
>
> [email]jason@catamaranco.com[/email] wrote:

> > Aaron, would the same hold true for a login system that captures user
> > personal information but also had foreign keys....
> >
> > I currently have 3 foreign keys in the Master Propsects table as I
> > wish to correctly assign information like States, Countries and
> > Source of Call to Control Tables....
> >
> > I also have a UserPath table to capture their page requests when they
> > login.......
> >
> > Can you advise?
> >
> > Thanks
> > Jason
> > "Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
> > news:uvYDQzDfEHA.3632@TK2MSFTNGP11.phx.gbl...

> >> Yes, 30,000 rows is laughably easy for SQL Server to handle. This
> >> sounds like a properties table to me, ready to serve semantics to
> >> queries against *real* data (millions and millions of rows).
> >>
> >> However, keeping all this like data in 15 distinct tables is going
> >> to be a maintenance and performance nightmare. Do yourself a favor
> >> and design it right.
> >>
> >> --
> >> [url]http://www.aspfaq.com/[/url]
> >> (Reverse address to reply.)
> >>
> >>
> >>
> >>
> >>
> >> "Anthony" <antgoodlife@nospam.comcast.net> wrote in message
> >> news:Olsz1tAfEHA.720@TK2MSFTNGP11.phx.gbl...
> >>> I can do that... but is it ok for this table to have about 30,000
> >>> rows? And
> >>> I am amazed that it would be faster but I will trust you...
> >>>
> >>> Thank you.
> >>>
> >>> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> >>> news:eV80qpAfEHA.2824@TK2MSFTNGP10.phx.gbl...
> >>>> Anthony wrote:
> >>>>>
> >>>>> is there a faster way to get what I am looking for...
> >>>>
> >>>> Yes. Put all the data into a single table with the proper index.
> >>>> That is the only way to speed this up.
> >>>>
> >>>> Bob Barrows
> >>>> --
> >>>> 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"

>
> --
> 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"
>
>