Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Performance puzzle
Message
From
17/04/2020 12:06:59
Walter Meester
HoogkarspelNetherlands
 
 
To
17/04/2020 10:08:31
General information
Forum:
Microsoft SQL Server
Category:
Scripting
Miscellaneous
Thread ID:
01674076
Message ID:
01674082
Views:
54
I agree with Naomi,

the convert is unneccesary and might be causing performance issues.
Also, it the date ranges used in practice vary greatly, you might want to add "WITH RECOMPILE" to either the call or the stored procedure.
https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/recompile-a-stored-procedure?view=sql-server-ver15

This will ensure the execution plan is determined on the actually current values of the parameters at each individual call.

Walter,




>
>DECLARE @startdate DATETIME
>DECLARE @enddate DATETIME
>select @startdate = '2020-03-31'
>select @enddate = '2020-03-31'
>
>select inv_number, billingname, bill_store, sum(charged) as charged, sum(storetax) as tax, sum(charged + storetax) as totalcharge from (  select  IIF(right(b.bill_store,6) = SPACE(6), c.name, s.store_name) AS billingname, b.* from
>  (SELECT inv_number, dbo.gettaxrate(tax_area) AS taxrate, dbo.gettaxareaname(tax_area) AS taxareaname, bill_type, bill_store, tax_area, trancode, charged, storetax, storetxbl  FROM billing 
> where CONVERT(date,inv_date) >= @startdate AND CONVERT(date,inv_date) <= @enddate
> UNION ALL SELECT invno AS inv_number, dbo.GetTaxRate(1) as taxrate, dbo.gettaxareaname(1) AS taxareaname, SPACE(23) as bill_type, custno AS bill_store, 1, 'MI', invamt - tax AS charged, 
> tax as storetax, invamt - tax AS storetxble  from arinvoice  where left(invno,1) = SPACE(1) and CONVERT(date,invdte) >=(@startdate)  AND CONVERT(date,invdte) <= (@enddate)
> ) B INNER JOIN company C ON LEFT(B.bill_store,4) = c.comp_code LEFT JOIN store s ON LEFT(s.storecode,11) = b.bill_store ) i group by inv_number, billingname, bill_store order by inv_number 
>
>
>
>in an SSMQ query this code takes under a second.
>When I convert to code to a stored procedure and pass parameters, it gives the same results, but runs for 90 seconds.
>
>Any ideas?
Previous
Reply
Map
View

Click here to load this message in the networking platform