September 20, 2002 at 10:26 am
I have a batch of sql which populates three table variables and then runs a query joining the three table variables to two regular tables and unions the result with another query joining one of the table variables to another table. The query has an order by clause on the end that orders by columns from both the table variables and the other tables. When I run the query with the order by clause I get one result set and when I run the query without the order by I get a different result set.
To further complicate the matter the query works consistently on all but the largest of our client databases. On the database in question if I change the sql to use temp tables instead of table variables the query works consistently.
Has anyone every seen flaky behaviour like this caused by table variables? Or is there any logical reason why the order by clause would act as a filter on the data set being returned?
Thanks,
kirk
September 20, 2002 at 10:44 am
Well without seeing the code it is difficult to say much. When you specify the ORDER BY clause, are you qualifying the column names with the correct aliases?
I have found that using table variables can be a severe performance issue - have you tried a similar piece of code but using tables which you truncate each time the procedure is run?
Regards, Simon UK
PS Who is looking for a job if anyone knows anything...
September 20, 2002 at 12:57 pm
I am not aliasing the columns in the order by clause because it is being applied to the results of the union. I have tried adding aliases to all of the columns and it did not seem to make a difference.
Since using temporary tables seems to fix the problem I am currently testing with both temp tables and plain old tables to see which performs better. Both seem to perform about the same and both seem to be slower than the code executing with table variables.
I am including the sql below. It is unfortunately very long and hard to follow.
Thanks, kirk
SQL:
declare @valueArray table( do_object_id_co int, [id] int, Primary Key( do_object_id_co, [id] ) )
declare @pos int, @index int, @value int
set @value = 0 set @pos = 0 set @index = 0
while ( @pos <= 5 )
begin
set @index = charindex( ',', '39304', @pos )
if ( @index = 0 )
set @index = 5 + 1
set @value = substring( '39304', @pos, @index - @pos )
if not exists( select * from @valueArray where [id] = @value )
insert into @valueArray values ( 181, @value )
set @pos = @index + 1
end
declare @glbcosts table( sequence int, facility int, actid int, actcost float )
insert into @glbcosts
select dg.dg_sequence, 0, ar.ar_do_object_id_act,
case when ar.ar_actcost is NULL then 0 else ar.ar_actcost end
from vw_modeltimeperiodscenario vw
join dglobalassociation dg on vw.scenario_id = dg.dg_do_object_id_to
and vw.model_id = dg.dg_dm_model_id and dg.dg_sa_type = 155
join ActResults_1 ar on vw.scenario_id = ar.ar_do_object_id_scenario
where vw.scenario_id in (248005)
declare @glbtotals table ( sequence int, facility int, revenue float, directcost float, grossprofit float, indirectcost float, profit float )
insert into @glbtotals
select 33000, 0,
sum(case [base].[do_object_id_group] when 48 then ar.ar_actcost else NULL end) as Revenue,
sum(case [base].[do_object_id_group] when 47 then ar.ar_actcost else NULLend) as DirectCost,
sum(case [base].[do_object_id_group] when 48 then ar.ar_actcost when 47 then -ar.ar_actcost else NULL end) as GrossProfit,
sum(case [base].[do_object_id_group] when 46 then ar.ar_actcost else NULL end) as IndirectCost,
sum(case [base].[do_object_id_group] when 48 then ar.ar_actcost when 47 then -ar.ar_actcost when 46 then -ar.ar_actcost else NULL end) as Profit
from [dbo].[costbases2002_09_18T18_34_39_793] as [base]
inner join ActResults_1 ar on ar.ar_do_object_id_scenario = 248005
and ar.ar_do_object_id_act = base.do_object_id_act
declare @totals table( sequence int, scenario varchar(100), scid int, facid int,
facility varchar(50), co_identity int, revenue float, directcost float,
grossprofit float, indirectcost float, profit float )
insert into @totals
select t1.*
from ( select 33000 seq, '0908TopGroupModel v2' scen, 248005 scid, 0 facid, '0' facility,
fn.*
from ( select [CostObjectMap_248006].[mp_co_identity_master] as co_identity,
sum(case [base].[do_object_id_group] when 48 then res.[cr_actcost] else NULL end) as Revenues,
sum(case [base].[do_object_id_group] when 47 then res.[cr_actcost] else NULL end) as DirectCosts,
sum(case [base].[do_object_id_group] when 48 then res.[cr_actcost] when 47 then -res.[cr_actcost] else NULL end) as GrossProfit,
sum(case [base].[do_object_id_group] when 46 then res.[cr_actcost] else NULL end) as IndirectCosts,
sum(case [base].[do_object_id_group] when 48 then res.[cr_actcost] when 47 then -res.[cr_actcost] when 46 then -res.[cr_actcost] else NULL end) as Profit
from [dbo].[costbases2002_09_18T18_34_39_793] [base]
inner join [CostObjectMap_248006] on [CostObjectMap_248006].[mp_do_object_id_co_master] = 181 and [CostObjectMap_248006].[mp_do_object_id_co_detail] = [base].[do_object_id_co]
inner join (select distinct id, do_object_id_co from @valueArray) sort on sort.do_object_id_co = 181 and sort.[id] = [CostObjectMap_248006].[mp_co_identity_master]
inner join [COResults_248005] res on res.[cr_do_object_id_act] = [base].[do_object_id_act] and res.[cr_do_object_id_co] = [CostObjectMap_248006].[mp_do_object_id_co_detail] and res.[cr_co_identity] = [CostObjectMap_248006].[mp_co_identity_detail] group by [CostObjectMap_248006].[mp_co_identity_master] ) fn) t1
select res.*
from (
select 33000 seqid, '0908TopGroupModel v2' scenario, 248005 scid, '0' as facility,
fn.co_identity, co._ID [id], co._Name [name], fn.do_object_id_act actid,
dt.dt_value actname, case do.do_name when 'Revenue Group' then 101 when 'Direct Cost Group' then 201 when 'Indirect Cost Group' then 301 end as type,
case when fn.actcost is NULL then '0.0' else convert(varchar(50), cast(fn.actcost as money), 2) end actcost,
case when fn.acttime is NULL then '0.0' else fn.acttime end acttime,
case when tot.revenue is NULL then 0 when tot.revenue = 0 then 0 when fn.actcost is NULL then 0 else (fn.actcost/tot.revenue) end perrev,
case when gt.revenue is NULL then 0 when gt.revenue = 0 then 0 else (gc.actcost/gt.revenue) end compavg
from ( select [CostObjectMap_248006].[mp_co_identity_master] as co_identity,
[base].[do_object_id_act] as do_object_id_act,
sum([COResults_248005].[cr_actcost]) as actcost,
sum([COResults_248005].[cr_acttime]) as acttime
from [dbo].[costbases2002_09_18T18_34_39_793] as [base]
inner join [CostObjectMap_248006] on [CostObjectMap_248006].[mp_do_object_id_co_master] = 181
and [CostObjectMap_248006].[mp_do_object_id_co_detail] = [base].[do_object_id_co]
inner join (select distinct id, do_object_id_co from @valueArray) sort on sort.do_object_id_co = 181
and sort.[id] = [CostObjectMap_248006].[mp_co_identity_master]
inner join [COResults_248005] on [COResults_248005].[cr_do_object_id_act] = [base].[do_object_id_act]
and [COResults_248005].[cr_do_object_id_co] = [CostObjectMap_248006].[mp_do_object_id_co_detail]
and [COResults_248005].[cr_co_identity] = [CostObjectMap_248006].[mp_co_identity_detail]
group by [CostObjectMap_248006].[mp_co_identity_master], [base].[do_object_id_act] ) fn
inner join COCustomer_1 co on fn.co_identity = co.CO_IDENTITY
inner join dtext dt on dt.dt_dm_model_id = 1 and dt.dt_do_object_id = fn.do_object_id_act and dt.dt_sp_type = 146
inner join dversionassociation da on da.da_do_object_id_to = fn.do_object_id_act and da.da_sa_type = 112 and da.da_dv_version = 25
inner join dobject do on da.da_do_object_id_from = do.do_object_id and do_so_type = 135 and do_name in ( 'Revenue Group', 'Direct Cost Group', 'Indirect Cost Group') and do_dm_model_id = 1
inner join @totals tot on tot.sequence = 33000 and fn.co_identity = tot.co_identity
inner join @glbcosts gc on gc.sequence = tot.sequence and fn.do_object_id_act = gc.actid inner join @glbtotals gt on gt.sequence = gc.sequence
union all
select tot.sequence, tot.scenario, tot.scid, tot.facility, tot.co_identity, co._ID [id],
co._Name [name], -1, 'Total Revenue', 150 type,
case when tot.revenue is NULL then '0.0' else convert(varchar(50), cast(tot.revenue as money), 2) end,
0, case when tot.revenue is NULL then 0 when tot.revenue = 0 then 0 else 1 end perrev,
case when gt.revenue is NULL then 0 when gt.revenue = 0 then 0 else 1 end compavg
from @totals tot
inner join COCustomer_1 co on tot.co_identity = co.CO_IDENTITY
inner join @glbtotals gt on tot.sequence = gt.sequence
where tot.scid in (248005)
union all
select tot.sequence, tot.scenario, tot.scid, tot.facility, tot.co_identity, co._ID [id], co._Name [name], -2, 'Total Direct Cost', 250 type, case when tot.directcost is NULL then '0.0' else convert(varchar(50), cast(tot.directcost as money), 2) end, 0, case when tot.revenue is NULL then 0 when tot.revenue = 0 then 0 else (tot.directcost/tot.revenue) end perrev, case when gt.revenue is NULL then 0 when gt.revenue = 0 then 0 else (gt.directcost/gt.revenue) end compavg from @totals tot inner join COCustomer_1 co on tot.co_identity = co.CO_IDENTITY inner join @glbtotals gt on tot.sequence = gt.sequence where tot.scid in (248005)
union all
select tot.sequence, tot.scenario, tot.scid, tot.facility, tot.co_identity, co._ID [id], co._Name [name], -3, 'Gross Profit', 251 type, case when tot.grossprofit is NULL then '0.0' else convert(varchar(50), cast(tot.grossprofit as money), 2) end, 0, case when tot.revenue is NULL then 0 when tot.revenue = 0 then 0 else (tot.grossprofit/tot.revenue) end perrev, case when gt.revenue is NULL then 0 when gt.revenue = 0 then 0 else (gt.grossprofit/gt.revenue) end compavg from @totals tot inner join COCustomer_1 co on tot.co_identity = co.CO_IDENTITY inner join @glbtotals gt on tot.sequence = gt.sequence where tot.scid in (248005)
union all
select tot.sequence, tot.scenario, tot.scid, tot.facility, tot.co_identity, co._ID [id], co._Name [name], -4, 'Total Indirect Cost', 252 type, case when tot.indirectcost is NULL then '0.0' else convert(varchar(50), cast(tot.indirectcost as money), 2) end, 0, case when tot.revenue is NULL then 0 when tot.revenue = 0 then 0 else (tot.indirectcost/tot.revenue) end perrev, case when gt.revenue is NULL then 0 when gt.revenue = 0 then 0 else (gt.indirectcost/gt.revenue) end compavg from @totals tot inner join COCustomer_1 co on tot.co_identity = co.CO_IDENTITY inner join @glbtotals gt on tot.sequence = gt.sequence where tot.scid in (248005)
union all
select tot.sequence, tot.scenario, tot.scid, tot.facility, tot.co_identity, co._ID [id], co._Name [name], -5, 'Profit', 253 type, case when tot.profit is NULL then '0.0' else convert(varchar(50), cast(tot.profit as money), 2) end, 0, case when tot.revenue is NULL then 0 when tot.revenue = 0 then 0 else (tot.profit/tot.revenue) end perrev, case when gt.revenue is NULL then 0 when gt.revenue = 0 then 0 else (gt.profit/gt.revenue) end compavg from @totals tot inner join COCustomer_1 co on tot.co_identity = co.CO_IDENTITY inner join @glbtotals gt on tot.sequence = gt.sequence where tot.scid in (248005)
) res
order by facility, co_identity, type asc, actid, seqid
September 24, 2002 at 1:29 pm
One thing I can tell you from personnel experience as well as from Microsoft onsite premier support. Table variables reside in ram and ram only, temp tables as you know reside in temp db, so should your ram not be big enough to hold the results some flaky stuff can occur. Also the speed in which data returns is a result of this as well having data sitting in ram verses a table on disk obviously will be faster, however as stated you must make sure you have enough ram to hold your result sets.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply