Professional Web Applications Themes

querying a view of a lot tables - ASP Database

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

  1. #1

    Default querying a view of a lot tables

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


    Anthony Guest

  2. #2

    Default Re: querying a view of a lot tables

    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] Guest

  3. #3

    Default Re: querying a view of a lot tables

    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]" <reb01501NOyahoo.SPAMcom> wrote in message
    news:eV80qpAfEHA.2824TK2MSFTNGP10.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"
    >
    >

    Anthony Guest

  4. #4

    Default Re: querying a view of a lot tables

    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"


    Bob Barrows [MVP] Guest

  5. #5

    Default Re: querying a view of a lot tables

    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" <antgoodlifenospam.comcast.net> wrote in message
    news:Olsz1tAfEHA.720TK2MSFTNGP11.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]" <reb01501NOyahoo.SPAMcom> wrote in message
    > news:eV80qpAfEHA.2824TK2MSFTNGP10.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 [SQL Server MVP] Guest

  6. #6

    Default Re: querying a view of a lot tables

    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.xocdnartreb.noraa> wrote in message
    news:uvYDQzDfEHA.3632TK2MSFTNGP11.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" <antgoodlifenospam.comcast.net> wrote in message
    > news:Olsz1tAfEHA.720TK2MSFTNGP11.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]" <reb01501NOyahoo.SPAMcom> wrote in message
    > > news:eV80qpAfEHA.2824TK2MSFTNGP10.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"
    > >>
    > >>
    > >
    > >
    >
    >

    Guest

  7. #7

    Default Re: querying a view of a lot tables

    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]jasoncatamaranco.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.xocdnartreb.noraa> wrote in message
    > news:uvYDQzDfEHA.3632TK2MSFTNGP11.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" <antgoodlifenospam.comcast.net> wrote in message
    >> news:Olsz1tAfEHA.720TK2MSFTNGP11.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]" <reb01501NOyahoo.SPAMcom> wrote in message
    >>> news:eV80qpAfEHA.2824TK2MSFTNGP10.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"


    Bob Barrows [MVP] Guest

  8. #8

    Default Re: querying a view of a lot tables

    Thank you - makes sense.

    "Bob Barrows [MVP]" <reb01501NOyahoo.SPAMcom> wrote in message
    news:eo7DwOIfEHA.3632TK2MSFTNGP11.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]jasoncatamaranco.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.xocdnartreb.noraa> wrote in message
    > > news:uvYDQzDfEHA.3632TK2MSFTNGP11.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" <antgoodlifenospam.comcast.net> wrote in message
    > >> news:Olsz1tAfEHA.720TK2MSFTNGP11.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]" <reb01501NOyahoo.SPAMcom> wrote in message
    > >>> news:eV80qpAfEHA.2824TK2MSFTNGP10.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"
    >
    >

    Guest

Similar Threads

  1. Tables don't show in Design View
    By pwalters in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: January 23rd, 05:00 PM
  2. Querying related tables
    By c1natra in forum Coldfusion Database Access
    Replies: 4
    Last Post: November 11th, 08:49 PM
  3. Querying one-to-many tables
    By v_roma in forum Coldfusion Database Access
    Replies: 5
    Last Post: November 7th, 03:10 AM
  4. Querying data that matches in two different tables
    By weswhite7 in forum Coldfusion Database Access
    Replies: 3
    Last Post: July 28th, 03:53 PM
  5. Querying for unmatched records in two tables
    By OldNapkin in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 10th, 06:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139