What else do I need to get my 2005 instance down ?

  • my dev: "Because reporting server cannot handle a sproc and tsql isn't

    suitable to writer our state of the art functions .... bla, bla, bla"

    I'm sorry I got a bit agitated :doze:

    The actual clr function is just building a "dymanic" sql - the dynamic part is

    the where clause and an extra join-predicate (wich could also be added to the where because it contains only inner joins .

    The dynamic sql actualy selects from 3 local views wich refer to 3

    different tables that reside at a linked server :sick:

    Then, based on a number value, it will actualy perform a loop, build a result array and flink that one back to the caller.

    With the current data load it will pull over some 56Mb if the opting "select all" is used.

    Imagine these reports being refreshed # times in parallel :hehe:

    Any other suggestions regarding what else we can do to bring our server down ?

    and please don't mention the doublebarrel shotgun 😉

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • ..please don't forget to turn the Array into an XML data stream as the output....

    or

    ...be sure to do all of your data conversions (to wide Char(400) strings of course) FIRST before sending back to client...

    or (my favorite)

    ... Use a While clause and handle the increments yourself (you don't need no nasty "exit clause")

    Public Function bob() As IEnumerable

    Dim cmd As SqlCommand

    Dim rdr As SqlDataReader

    Dim junk As New Collection

    Dim i As Integer = 1

    Using conn As New SqlConnection("context connection=true")

    conn.Open()

    cmd = New SqlCommand("Select rownum,accountid,date,amount from jbmtestmerry ORDER by accountid,date", conn)

    rdr = cmd.ExecuteReader()

    While (i < 500)

    i = i + 1

    junk.Add(rdr(i).ToString)

    i = i Mod 50

    End While

    End Using

    Return junk

    End Function

    And don't forget error handling (courtesy of GSquared as I recalll:))

    Create Procedure PleaseMeltMyServer(@sql varchar(200))

    as

    Begin

    Begin Try

    Exec(@sql)

    End Try

    Begin Catch

    Print 'Oops - I did it again'

    Exec PleaseMeltMyServer @sql

    End Catch

    End

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Dang... I was loading up the double-barreled pork-chop sling-shot. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Well, it turns out that the tsql-storedprocedure-version of that clr function actualy ....

    Here are the results from the simple test jury 😉

    TheVersion ExecCount AvgElapsMs MinElapsMs MaxElapsMs

    clrfn 10 11802 8610 18076

    spc 10 11766 8453 19030

    spc2 10 11411 9080 14796

    The sproc:

    CREATE procedure [dbo].[usp_GetCalibrationRoutings]

    @periodID uniqueidentifier -- varchar(128)

    , @cipSubFamily varchar(128) = '(Select All)'

    as

    begin

    set nocount on

    if isnull(@cipSubFamily, '(Select All)') = '(Select All)'

    begin

    select CR.MaterialOrderID

    , CR.OccurrenceNr

    , CR.ProdLineID

    , PL.Description

    , CTS.Value

    , Rmax.RoutingNr

    , COI.OrderNr

    , COI.OrderItem

    , COI.OrderDate

    , PL.NameProductionLine

    , CR.PeriodID

    , CTS.PeriodID as CTS_PeriodID

    from dbo.CalibrationRouting CR

    inner join dbo.CalibrationOrderitem COI

    ON COI.MaterialOrderID = CR.MaterialOrderID

    inner join dbo.CalibrationTechnicalSpecification CTS

    ON CTS.MaterialOrderID = COI.MaterialOrderID

    and CTS.PeriodID = @PeriodID

    and CTS.TechElement = 'YRDPSUBFA'

    inner join ProductionLine PL

    ON PL.ProdLineID = CR.ProdLineID

    INNER JOIN (select MaterialOrderID, max(OccurrenceNr) as RoutingNr

    from CalibrationRouting

    group by MaterialOrderID) Rmax

    on CR.MaterialOrderID = Rmax.MaterialOrderID

    order by CTS.Value

    , CR.MaterialOrderID

    , CR.OccurrenceNr

    -- order by 5, 1, 2

    end

    else

    begin

    select CR.MaterialOrderID

    , CR.OccurrenceNr

    , CR.ProdLineID

    , PL.Description

    , CTS.Value

    , COI.OrderNr

    , COI.OrderItem

    , COI.OrderDate

    , PL.NameProductionLine

    , CR.PeriodID

    from dbo.CalibrationRouting CR

    inner join dbo.CalibrationOrderitem COI

    ON COI.MaterialOrderID = CR.MaterialOrderID

    inner join dbo.CalibrationTechnicalSpecification CTS

    ON CTS.MaterialOrderID = COI.MaterialOrderID

    and CTS.PeriodID = @PeriodID

    and CTS.TechElement = 'YRDPSUBFA'

    and CTS.Value = @cipSubFamily

    inner join ProductionLine PL

    ON PL.ProdLineID = CR.ProdLineID

    INNER JOIN (select MaterialOrderID, max(OccurrenceNr) as RoutingNr

    from CalibrationRouting

    group by MaterialOrderID) Rmax

    on CR.MaterialOrderID = Rmax.MaterialOrderID

    where CR.PeriodID = @periodID

    order by CTS.Value

    , CR.MaterialOrderID

    , CR.OccurrenceNr

    -- order by 5, 1, 2

    end

    end

    the spc2 in the result is the same spc, but replaced

    max(OccurrenceNr) as RoutingNr

    with

    count(*) as RoutingNr

    The target sqlserver is a heavy duty DWH IA64 and

    results vary from +- 8 seconds up to 71 seconds :crazy:

    And the big reason is I/O bound (very fragmented sql2000 tables (avg bytes free / page +3500))

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • ALZDBA (2/20/2008)


    Any other suggestions regarding what else we can do to bring our server down ?

    Oh my, yes. Unrestricted ad-hoc query facility for all app users. :w00t:

    It delivers in so many ways: poor performance, unreliability, insecurity, etc.

    The old ways are still the best.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply