?
Solved

tempdb cache on MSDE( or IIS/ADO)?

Posted on 2005-03-15
7
Medium Priority
?
485 Views
Last Modified: 2012-06-21
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
Comment
Question by:ill
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 13544686
Do you have SET NO COUNT at the top of your proc?

Post your proc and your ASP code....
0
 
LVL 12

Author Comment

by:ill
ID: 13546100
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
 
LVL 34

Expert Comment

by:arbert
ID: 13546209
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 12

Author Comment

by:ill
ID: 13546986
/*
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
 
LVL 12

Author Comment

by:ill
ID: 13558648
after several more installations on different computers, it's p4 parallel queries problem, imho.

0
 
LVL 34

Accepted Solution

by:
arbert earned 2000 total points
ID: 13559195
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
 
LVL 12

Author Comment

by:ill
ID: 13562484
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question