Page 1 of 4 123 ... LastLast
Results 1 to 15 of 59
  1. #1
    Join Date
    Jul 2007
    Posts
    25

    Red face Unanswered: Problem With a Very Big Tables in SQL Server 2000

    Hi, every one
    I have a big big problem in SQL Sever 2000 working with a very very big problem

    This is My Story
    ----------------
    I have a very big table (36,000,000 row and every day we add 400,000 new row) and i have a page ASP Classic that run Stored Procedural in Database that make ajoins betwen that table and other small tables (100,000 roww),
    in this page i display some summery information that back from this Stored Procedural.
    The problem is runung any query on this Stored Procedural Take from 1 hour to 3
    how can i optmaize this table ? to make query run faster

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Please can you post the relevent DML and DDL? Please see the sticky at the top of the forum for mor info.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sounds like the tables aren't indexed properly...

    Read the hint link at the top of the forum, or read this

    http://weblogs.sqlteam.com/brettk/ar...5/25/5276.aspx

    And post what the instructions tell you to post
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jul 2007
    Posts
    25
    sorry, it`s my first time

    but what DML or DDL i have to post

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by mgsn
    sorry, it`s my first time

    but what DML or DDL i have to post
    DML = Data Manipulation Language (SELECT, INSERT, UPDATE, DELETE statements)

    DDL = Data Definition Language (CREATE <object>, DROP <object>, etc)

    Regards,

    hmscott
    Have you hugged your backup today?

  6. #6
    Join Date
    Jul 2007
    Posts
    25
    i know waht DML & DDL mean but in my case it`s not imortant

  7. #7
    Join Date
    Jul 2007
    Posts
    25
    i can`t use index because i have to insert about 6000 row every 10min
    and index will slow it down

  8. #8
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by mgsn
    i can`t use index because i have to insert about 6000 row every 10min
    and index will slow it down
    WTF??? So you are willing to sacrifice your application/business logic query for the sake of speedy inserts? I would put forth the thought that if your inserts are speedy as hell, it really doesn't matter if you can't access the data once it has been inserted.

    In any case, this thread smells like another of our favorite types. You know, the "I know what you are telling me to do is correct, but I can't do it. Can you wave your magic wands instead?" kind of thread.

    And yes, in your case the DDL and DML are certainly important.

    Well, unless you are shooting for a "I know what you are telling me to do is correct, but I can't do it. Can you wave your magic wands instead?, and BTW, I am not going to tell you anything about my database, so you will need your magic x-ray glasses as well" thread.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by TallCowboy0614
    I know what you are telling me to do is correct, but I can't do it. Can you wave your magic wands instead?
    that's called hardware. lots of it of the expensive nature. who needs efficient design and coding when you can just buy a bigger box?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Quote Originally Posted by mgsn
    i can`t use index because i have to insert about 6000 row every 10min
    and index will slow it down
    Do tell. Have you quantified how much the slowdown is?

  11. #11
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Thrasymachus
    that's called hardware. lots of it of the expensive nature. who needs efficient design and coding when you can just buy a bigger box?
    We have one of those right now. The vendor wants a dedicated 8-way server running SQL 2005 to support 1,000 concurrent users and a 17 GB database.

    The vendor insists that this is the only way to achieve 1,000 concurrent users and that this is "standard practice" (a dedicated database server).



    Regards,

    hmscott
    Have you hugged your backup today?

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by mgsn
    i can`t use index because i have to insert about 6000 row every 10min
    and index will slow it down
    you funny


    You have an architechture problem

    Do the inserts into a stage table, then have a separate process (job) that inserts the rows from stage every x minutes or so

    You need an index, or you happy with mutli-hour queries?

    Tell you what, I'm leaving for the weekend, and you put a big simle on my face

    Thanks
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Oh, and lets see

    6,000 rows every 10 minutes...which is
    600 row every minute....which is
    10 rows a second

    And you think that's a lot?


    I doubt sql server would even blink
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mgsn
    i can`t use index because i have to insert about 6000 row every 10min
    and index will slow it down
    If you don't have indexes then you have a heap. You can get better insert performance with an appropriate clustered index than a heap.

    Quote Originally Posted by mgsn
    how can i optmaize this table ?
    .......
    i know waht DML & DDL mean but in my case it`s not imortant
    What you are saying is that you have a query that acts on a table and it runs like a dog. But the query and the table are irrelevent? If you go to a mechanic and tell him your car is not running properly and he asks to look under the bonnet (hood if you are American) do you think that it is irrelevant?

    Another question - how many rows do you return to the ASP page?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Jul 2007
    Posts
    25
    number of row depend on the user selection filters
    i have number of dropdown list that the user will select from them,
    ex: i have the date(From - to) if the user select the range 1 day it will return about 40 row, if more it more

Posting Permissions

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