Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2007
    Posts
    5

    Unanswered: SQL Server Performacne...

    Records are added to a table which can grow in millions with date of the creation. Views (also cascade of views) is used to calculate the grouped data:

    for example:

    1) here is a view to calculate aggregate data:

    CREATE VIEW dbo.All_Campaign_Calls AS
    SELECT TOP 100 PERCENT NUMCAMPAGNE AS Campaign_Num, OWNERNAME AS owner_name, STATUTPROSPECT AS STATUS, CAST(CONVERT(varchar(10), DATESELECT, 103) AS
    datetime) AS call_date
    FROM dbo.ProspectCampagne
    WHERE (APPELE = '1')


    this view shows from a prospect table for a given campagne, by date and by person used to call

    2) here a view that uses the previous view to aggregate data: number of calls by date:

    SELECT TOP 100 PERCENT Campaign_Num, call_date, SUM(nb_Total_Calls) AS nb_Total_Calls_day FROM dbo.Calls_Per_Campaign_Date_Status
    GROUP BY Campaign_Num, call_date
    ORDER BY Campaign_Num, call_date


    3) here is a view that uses other views to agrregate data by data AND by person called:

    SELECT TOP 100 PERCENT Campaign_Num, call_date, STATUS, SUM(Nb_Calls) AS nb_Total_Calls FROM dbo.Calls_Per_Campaign_Dt_Own_Stat
    GROUP BY Campaign_Num, call_date, STATUS ORDER BY Campaign_Num, call_date, STATUS


    etc.....

    In SQL server DB; do the calculations:

    We have views that gather, agregate data...

    To know the performance of the server with 100k, 500k, 1M 2, 3, 4 million records

    How to find that performacne. ????



    Give me your advice.

    Thanks

    Babu B
    Last edited by babu.knb; 03-16-07 at 07:40. Reason: Important Changes done

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Step 1: Drop all the TOP 100 PERCENT statements.

    In order to find out how well this performs, ultimately you will have to test it with the various numbers of records. I would advise against cascading views, however, as this tends to introduce extra joins to tables that may not have anything to do with the query at hand.

  4. #4
    Join Date
    Feb 2007
    Posts
    62
    Also there is no point in ORDER BY in a view. You should do that last in your query - Although you might get away with it, there's no telling what will come out. The problem would become more obvious if your query joined another table to the view that's for sure.

  5. #5
    Join Date
    Mar 2007
    Posts
    5

    Need Better Performance

    Dear friends,

    I am having 10GB for my database. In my table having 10 Millian records per day. I want to select records per date and by product status. So,

    1. How can I design my Database initialization paramet ??
    2. How can I tune my database for better performance.

    My Machine Configuration :

    Windows 2003 Server OS,1 GB Ram, 80 GB Hard Disk, 3.99 GHz

    Regards

    Babu B
    Last edited by babu.knb; 03-19-07 at 08:56. Reason: Some Changes

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

    You appear to be in the habit (http://www.dbforums.com/showthread.php?p=6262070) of cross posting across boards (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80792).

    If you do this please post a link in your thread so that respondants do not waste time asking questions already answered or do not miss pertinent information from the other thread.

    SQLTeam may appreciate the same courtesy.

    Thank you
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Your RAM sounds inadequate for your transaction rate (10 mill records per day?).

    Impossible to know how to answer the question. We can help on database design (logical & physical) and SQL syntax for specific questions but cannot give you something for such a broad question.

    The question suggests you don't really have much of a grounding in this topic - you would do well to read many of the articles on http://www.sql-server-performance.com/ and come back with more sepcific questions.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Mar 2007
    Posts
    5
    Hi HTH,

    Your correct, But here we have 10 M rec. in my table. I need to tune better performacne.

    Like, I need time difference per 1 Lk, 5 lk and 10 Millian records in select query., that is why i asking you.

    Give me your clarfication. How to set parameter ??

    Babu

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by babu.knb
    How to set parameter ??
    I don't know what this means.

    Do you want to stress test\ bench mark? Does Lk mean 1000 in Urdu?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2005
    Posts
    28
    Hi,

    You can create your search procedure with RECOMPILE hint.

    Perhaps a creation of a Cluster Index on date field will also be helpful.

    Eralper
    http://www.kodyaz.com

  11. #11
    Join Date
    Mar 2007
    Posts
    5
    Lk is nothing - Laks, Give me solution.

  12. #12
    Join Date
    Mar 2007
    Posts
    5
    Hi Eralper,

    I can't understand. Can you explain me ?

    Babu

  13. #13
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    the reason he's got TOP 100 PERCENT is because of the ORDER BYs.

    Here's why you shouldn't be doing that:

    http://blogs.msdn.com/sqltips/archiv...20/441053.aspx

  14. #14
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am not going to follow your link but I thought I read top 100 percent and order by used together are ignored in views in 2005.
    “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.

  15. #15
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    They are not always ignored. sometimes they are.

    as discussed at the link, the optimizer reserves the right to ignore them if it thinks can produce a more efficient plan by ignoring them.

Posting Permissions

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