February 20, 2008 at 11:26 am
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
February 20, 2008 at 12:23 pm
..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?
February 20, 2008 at 1:25 pm
Dang... I was loading up the double-barreled pork-chop sling-shot. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 21, 2008 at 8:31 am
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
March 8, 2008 at 12:44 pm
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