0

I am trying to execute this query :

ALTER  PROCEDURE [dbo].[SPViewMTO] 
AS
BEGIN
SELECT      *,isnull(dbo.ReturnShortageByItemCodeLinePackage(LineId,TestPackageId,MaterialDescriptionId),0) As Shortage

           ,isnull(dbo.WarehouseByMaterialdesciptionId(MaterialDescriptionId),0) As Warehouse
           ,isnull(dbo.ReturnTotalIMIVByLineIdAndTestPackIdAndMaterialDescriptionId(LineId,TestPackageId,MaterialDescriptionId),0) as totalIMIV
,isnull(dbo.ReturnTotalMIVByLineIdAndTestPackIdAndMaterialDescriptionId(LineId,TestPackageId,MaterialDescriptionId),0) as TotalMIV
,isnull(dbo.ReturnTotalMRCByLineIdAndTestPackIdAndMaterialDesriptionId(LineId,TestPackageId,MaterialDescriptionId),0) as TotalMRC
 from dbo.ViewMTO

END

I run this query on this computer :

enter image description here

with 2:13 execution time ,it is a little slow : enter image description here

So our company decieded to buy a proliant dl380 hp gen 9 with this config enter image description here

And i run the query again and i hope to my query execute more faster : enter image description here

but the execution time is 4:10.Why?!!! Another thing that i should add is the cpu usage in my local pc is :

enter image description here

And the server enter image description here

Ehsan Akbar
  • 6,977
  • 19
  • 96
  • 180
  • 1
    This query is very simple, you don't have your problem here but on some or all these functions : ReturnShortageByItemCodeLinePackage WarehouseByMaterialdesciptionId ReturnTotalIMIVByLineIdAndTestPackIdAndMaterialDescriptionId ReturnTotalMRCByLineIdAndTestPackIdAndMaterialDesriptionId Or the view : ViewMTO You need to proportionate the code of those funcions and view if you want further opinions. – Marc Guillot Oct 03 '16 at 09:18
  • Those UDFs could be killing your query. See http://stackoverflow.com/a/1179894/130352 which explains some of it. – Chris J Oct 03 '16 at 09:21
  • @ChrisJ So how can i remove this function ?Can i bring the function code directly inside my storeprocedure? – Ehsan Akbar Oct 03 '16 at 09:26
  • @EhsanAkbar - depending on the contents of the functions, yes. Or rewrite the functions to be table-valued rather than single-valued. But the only way to know is to profile. – Chris J Oct 03 '16 at 10:43
  • 2
    You've got 5 UDFs that we can see, plus it looks like you're querying a view which could contain anything (including more UDFs, joins on non-indexed columns, non-sargable WHERE clauses, the list goes on). You're almost certainly in 'complete rewrite of the whole query' territory. But without seeing the contents of those UDFs and the view it's hard to give concrete suggestions. – MartW Oct 03 '16 at 11:24

0 Answers0