• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 498
  • Last Modified:

tempdb cache on MSDE( or IIS/ADO)?

hello everyone,

i have a procedure that uses temporary tables, global and also local and several exec('this really must be dynamic, because column names are dynamic') statements generated from configuration tables.
on dev. machine everything works fine, final result are two temporary tables, displayed on web page
table1 -- display numbers
-------
dt        |        col1        |        col2        |        col3
1.1.05        |        100        |        150        |        100
1.2.05        |        100        |        120        |        120
1.3.05        |        90        |        150        |        120

table2 --displays user defined substitutes for each numbers. it has the same structure as table1, just float(53) became varchar(20)
-------
dt        |        col1        |        col2        |        col3
1.1.05        |        A1        |        A2        |        A3
1.2.05        |        A1        |        B2        |        B3
1.3.05        |        B1        |        A2        |        B3

on cust. machine. i get the same result, wher running proc from QA
When running from Web client, result is only table1. twice.
i tried to drop/recreate/restart...
 

dev. machine
Microsoft SQL Server  2000 - 8.00.760 (Intel X86)  Standard Edition
on Windows NT 5.0 (Build 2195: Service Pack 4)  version:server
Athlon 2.2XP/ 1GB
cust. machine
Microsoft SQL Server  2000 - 8.00.760 (Intel X86)   Desktop Engine
on Windows NT 5.0 (Build 2195: Service Pack 4)  version:professional
P Xeon3.0/ 1GB
settings of iis/sql is exactly the same on both machines.
0
ill
Asked:
ill
  • 4
  • 3
1 Solution
 
arbertCommented:
Do you have SET NO COUNT at the top of your proc?

Post your proc and your ASP code....
0
 
illAuthor Commented:
server settings are the same, including no-count, quoted ident, etc...
connection from iis set no count on, too.
procedure returns 4 resultset and code works on my dev. machine and also from QA by client.

code won't be of any help, imo, there are a lot of udf's and exec's in it. there is definitely no problem in T-sql part.
0
 
arbertCommented:
Well, if you can't post the code, we probably can't help.

Since you're using Dynamic SQL, I would double check your permissions closely as well....
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
illAuthor Commented:
/*
permissions are setted correctly.
SP code is here.
*/
create procedure oo_diag_getComplete
@userID bigint
, @oid bigint
, @dt varchar(32)
, @sortby varchar(8000)
, @showAs varchar(20)= 'userDefMarks'
AS
set nocount on
declare @curOrder int set @curOrder=0
declare @reid bigint
declare @pausal varchar(8000) , @for1 varchar(800), @1s varchar(8000) , @h int, @list varchar(8000), @relist varchar(8000), @upoint varchar(20), @rpoint varchar(20), @dpoint varchar(20)
select  @list=''
select @relist= oo_mode_conf from dbo.w2f_oo_mode ( 7, @reid, -1, '')

select "ord"= ord.val, d.*, t.*
into #x1
from dbo.objectTreeByName( @userID, 232, '') t
join dbo.w2f_oo_diag_det ( @userID, -1, -1, '') d on ( d.id= t.xslave)
left join dbo.strSPlit ( @sortBy, ',') ord on  ord.val=d.id
order by ord.rank asc
select top 1 @h= oo_diag_start , @upoint= oo_user_point, @rpoint= oo_rezim_point, @dpoint= oo_diag_point, @pausal= oo_diag_pausal, @for1= oo_diag_for1, @1s= oo_diag_1s from #x1

create table #xt ( dtsk varchar(32) , dtplus datetime , dth int , dtm int , dt datetime , dtsys datetime, dtsysh int , dtsysm int , n int, val float(53) , rezim float(53) )
declare @i int, @col varchar(20), @text varchar(2000), @seltext varchar(8000), @uptext varchar(8000)
select @pausal, @for1, @1s

select dd.id, dd.name, dd.oo_diag_point, ord.val from dbo.w2f_oo_diag_det( @userID, -1, -1 , 'oo_diag_point') dd
left join dbo.strSPlit ( @sortBy, ',') ord  on  ord.val=dd.oo_diag_point
where isNumeric( ord.val)=1 and ord.val is not null
order by ord.rank asc

select @list= @list+ ',' +oo_diag_point from dbo.w2f_oo_diag_det( @userID, -1, -1 , 'oo_diag_point') dd
left join dbo.strSPlit ( @sortBy, ',') ord on  ord.val=dd.oo_diag_point
where isNumeric( ord.val)=1 and ord.val is not null
order by ord.rank asc
set @list= substring( @list, 2, 8000)

declare @listtext varchar(8000), @listtextChar varchar(8000)
if exists ( select * from tempdb..sysobjects where name='##x')      drop table ##x
if exists ( select * from tempdb..sysobjects where name='##CharX')      drop table ##CharX

set @listtext= 'create table ##x ( dtsk varchar(32) , dtplus datetime , dth int , dtm int , dt datetime , dtsys datetime, dtsysh int , dtsysm int , n int, p' + replace ( @list, ',', '  float(53),p')+' float(53) )'  
set @listtextChar= 'create table ##CharX ( dtsk varchar(32) , dtplus datetime , dth int , dtm int , dt datetime , dtsys datetime, dtsysh int , dtsysm int , n int, p' + replace ( @list, ',', '  varchar(5),p')+' varchar(53) )'  
exec( @listtext)
exec( @listtextChar)
insert into ##x
exec dataS  @list, @dt, @h, 0, 900, '-2', 1
delete from ##x where dtm<>0
insert into ##CharX select * from ##x
if @showAs<> 'userDefMarks'
 select *,  "cas"= right( '00'+convert( varchar(2), r1. dth), 2)+'- '+ right( '00'+convert( varchar(2), (r1. dth+1)%24), 2) from ##x r1

-- now comes translation of numerik values tu user defined marks
declare @simpleCur_text varchar(8000), @simpleCur_col varchar(200), @simpleCur_ID varchar(20), @simpleCur_joinText varchar(8000)
select id, name, oo_HmodeH_val, oo_diag_det
      into #modes from dbo.w2f_oo_hmode ( 7, -1, -1, '')
select *
      into #diags from dbo.w2f_oo_diag_det ( 7, -1, -1, '')
select *
      into #oo_modes from dbo.w2f_oo_mode ( 7, -1, -1, '')
select modes.name, modes.oo_HmodeH_val, oo_diag_point
into #recalc
from #diags diags
left join #modes modes on ( modes.oo_diag_det= diags.id)
set @simpleCur_text= 'update CharX set dtm=dtm'
set @simpleCur_joinText= ' from ##CharX CharX'
declare simpleCur insensitive cursor for select column_name, substring(column_name, 2, 20) from tempdb.information_schema.columns where table_name='##x' and substring( column_name, 1, 1)='p' order by ordinal_position -- just to be sure
open simpleCur
fetch next from simplecur into @simpleCur_col, @simpleCur_ID
while @@fetch_status=0 begin
      set @simpleCur_text= @simpleCur_text +','+ @simpleCur_col+ '=t'+ @simpleCur_ID+ '.name'
      set @simpleCur_joinText= @simpleCur_joinText +'
             left merge join #recalc t'+ @simpleCur_ID+ ' on( t'+ @simpleCur_ID+ '.oo_diag_point='+ @simpleCur_ID+ ' and t'+ @simpleCur_ID+ '.oo_HmodeH_val=CharX.'+@simpleCur_col+')'
      fetch next from simplecur into @simpleCur_col, @simpleCur_ID
end
close simpleCur deallocate simpleCur
exec ( @simpleCur_text +@simpleCur_joinText)
if @showAs= 'userDefMarks'
 select *,  "cas"= right( '00'+convert( varchar(2), r1. dth), 2)+'- '+ right( '00'+convert( varchar(2), (r1. dth+1)%24), 2), "cacheCol"='no-cache'  from ##CharX r1

select "rank"= cast(ord.rank as int), "val"=ord.val, "name"=mode.name, "oo_mode_conf"=mode.oo_mode_conf, "modeID"= isnull(mode.id, 0)
into #trans
from dbo.strSplit( @sortBy, ',') ord
left merge join dbo.w2f_oo_diag_det ( 7, -1, -1, 'oo_diag_point') det on ( det.oo_diag_point= ord.val)
left merge join dbo.w2f_oo_mode ( 7, -1, -1, '') mode on ( mode.oo_diag_det= det.id)

-- translation of corresponding saved modes to numeric
declare @transID bigint, @transval varchar(8000), @transhlp varchar(8000)
declare transCur insensitive cursor for select distinct val, oo_mode_conf from #trans
open transCur
fetch next from transCur into @transID, @transval
while @@fetch_status= 0 begin
      set @transHlp=''
      select @transHlp= @transHlp+ ','+ isnull(mode.name, '')
      from dbo.strSplit (@transval, ',') fullstr
      left join #diags diags on ( diags.oo_diag_point= @transID)
      left join #modes mode on ( mode.oo_HmodeH_val= fullstr.val  and mode.oo_diag_det= diags.id)
      set @transHlp= substring( @transHlp, 2, 8000)
      update #trans set oo_mode_conf= @transHlp where val= @transID and oo_mode_conf= @transval

      fetch next from transCur into @transID, @transval
end
close transCur deallocate transCur
select * from #trans order by rank
drop table ##x
drop table ##CharX
GO
0
 
illAuthor Commented:
after several more installations on different computers, it's p4 parallel queries problem, imho.

0
 
arbertCommented:
It's very possible.  Add the MAXDOP,1 option to the queries and see what happens.  You can also up the threshhold that causes a parallel plan to be generated so you don't have to disable parallel support.

Another thing to consider--a parallel plan can also cause your DISKIO to bottleneck since more IO happens at once--check perfmon.

However, this really wouldn't describe why tables are "missing" on the output.
0
 
illAuthor Commented:
could it be, queuries are processed in "wrong" order, so that one of EXEC clauses executes null string and doesn't return any resultset?
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now