Ten Centuries,

Is this something you're looking for?

I used sys.dm_exec_procedure_stats

object_id: 965578478

database_id: 55

proc name: UnitPricingTrans

cached_time: 2013-11-04 07:53:43.937

last_execution_time: 2013-11-04 08:08:29.953

total_elapsed_time: 541171953

avg_elapsed_time: 700999

last_elapsed_time: 161245222

execution_count: 772

Ten Centuries,

"...Have you looked at indices and stats before changing the code?..."

No I have not done this.

I am not a big guru when it comes to TSQL performance tuning.

Can you...

SSChampion,

How do I get execution plan in XML?

I would like to see what Gail is talking about in her article about parameter sniffing.

Especially this part - ColumnReference Column="@StringVar" ParameterCompiledValue="'zzz'"

SSChampion,

I re-compiled procedure using local variables.

But... first run with ( @P_UNIT_TYPE = 'VEH' )

took 11 seconds.

Next run ( @P_UNIT_TYPE = 'COM' )

is taking more than 2 min... and still running.

Still...

Guys, I am so thankful for your help.

I think parameter sniffing is definitely might be the root cause.

I am rewriting procedure now to use local variables.

Will let you know on...

This is procedure code.

The biggest table is prj_detail ( 53,000,000 records)

ALTER PROCEDURE UnitPricingTrans

(

@P_PER_START int,

@P_PER_END int,

@P_CO_CODE varchar(1000),

@P_UNIT_TYPE varchar(1000),

@P_UNIT varchar(1000)

)

AS

if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and...

Thank you for a quick response.

This is how I call procedure:

EXEC UnitPricingTrans

@P_PER_START = 201308,

@P_PER_END = 201309,

@P_CO_CODE = '7001',

@P_UNIT_TYPE = 'VEH',

@P_UNIT = 'AUSMV'

And I always run it with the same parameters.

What...

Dear Sean Lange.

You really are SSChampion !!!!!

It runs in 1 sec now !!!

Wow.

I just need to understand what is the trick

and what was wrong with my old code

because I use...

After some tests I think the problem is with this code:

('**all**' IN (@L_ORG) ....

As soon as I remove this part procedure runs in 1 sec.

getValuesAsTable is table-valued function

It works like this:

SELECT * FROM getValuesAsTable ('1,2,3',',')

---------------------------------------------

1

2

3

Thank you so much Tee Time !!!

You put me in the right direction.

I figured it out.

The trick was to create groups WITH HEADERS AND FOOTERS because later you will utilize those extra rows for group Titles/Subtotals.

Another trick is if you need this...

Hi Tee Time.

I tried your suggestion but did not get the results i need.

So I re-designed the report using this groups:

Parent group: Sales/Purchases

.... Child group: accounting_period (Month)

.... Child group: Tax...

That's what I was suspecting Scott !

Thanks. Now I need to think how I explain it to our Lead....hmm

Page size and margins have nothing to do with it.

I don't get a blank page in PDF problem or something like that.

It's a different type of problem.

Table fits perfectly on...