January 11, 2011 at 5:49 am
Hello,
I'm working on a project for the control of DBs in ASP / SQL. I found a SQL procedure that gives me a list of tables for each DB (...) which works fine when I run in TSQL.
But I want to put this query in my ASP application without running the SQL myself, I tried to create a sp_procedure. A problem arose: the sp_procedure can not use temporary tables!
After some testing, I changed the SQL procedure to use physical table.
It was here that the problem arose!
Now I have two TSQL: one that only works as TSQL running manually using temporary tables:
--TSQL code 1---
use warehouse
declare @idint
declare @typecharacter(2)
declare@pagesint
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpagedec(15,0)
declare @pagesperMBdec(15,0)
--temporary table (don't work in sp_procedures)
create table #spt_space
(objidint null,
rowsint null,
reserveddec(15) null,
datadec(15) null,
indexpdec(15) null,
unuseddec(15) null,
)
set nocount on
-- Create a cursor to loop through the user tables
declare c_tables cursor for
selectid
fromsysobjects
wherextype = 'U'
open c_tables
fetch next from c_tables
into @id
while @@fetch_status = 0
begin
/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @id, (reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
select @pages = (dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull((used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
where objid = @id
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select (used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
where objid = @id
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select (used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
where objid = @id
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
and objid = @id
fetch next from c_tables
into @id
end
select
data = (select crdate from sysobjects where id = objid),
Table_Name = (select left(name,50) from sysobjects where id = objid),
rows = convert(char(11), rows),
reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0)),
data_KB = ltrim(str(data * d.low / 1024.,15,0)),
index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0)),
unused_KB = ltrim(str(unused * d.low / 1024.,15,0)),
case data
when 0 then '100%'
else ltrim(str(indexp*100 /data) + '%')
end as idx_data_ratio,
case reserved
when 0 then '100%'
else ltrim(str(unused * 100 /reserved) + '%')
end as unused_pct
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc
drop table #spt_space
close c_tables
deallocate c_tables
------for my Warehouse DB takes about 00:00:03 and shows 1051 rows----------
ex:
2008-07-29 12:11:35.077TblChequesDevolvidosH18979526 27117522711368803040%0%
2010-04-06 16:29:29.483TblReactivacaoClientes1046762 20298721976680528883043%0%
..................
---------------
and another that allows me to save as global sp_procedure to be called from the ASP application:
---TSQL code 2 ----
use warehouse
declare @idint
declare @typecharacter(2)
declare@pagesint
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpagedec(15,0)
declare @pagesperMBdec(15,0)
--table on disk (work with sp_procedures)
create table dbs_space
(objidint null,
rowsint null,
reserveddec(15) null,
datadec(15) null,
indexpdec(15) null,
unuseddec(15) null,
)
set nocount on
-- Create a cursor to loop through the user tables
declare c_tables cursor for
selectid
fromsysobjects
wherextype = 'U'
open c_tables
fetch next from c_tables
into @id
while @@fetch_status = 0
begin
/* Code from sp_spaceused */
insert into dbs_space (objid, reserved)
select objid = @id, (reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
select @pages = (dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull((used), 0)
from sysindexes
where indid = 255
and id = @id
update dbs_space
set data = @pages
where objid = @id
/* index: sum(used) where indid in (0, 1, 255) - data */
update dbs_space
set indexp = (select (used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
where objid = @id
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update dbs_space
set unused = reserved
- (select (used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
where objid = @id
update dbs_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
and objid = @id
fetch next from c_tables
into @id
end
select
data = (select crdate from sysobjects where id = objid),
Table_Name = (select left(name,50) from sysobjects where id = objid),
rows = convert(char(11), rows),
reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0)),
data_KB = ltrim(str(data * d.low / 1024.,15,0)),
index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0)),
unused_KB = ltrim(str(unused * d.low / 1024.,15,0)),
case data
when 0 then '100%'
else ltrim(str(indexp*100 /data) + '%')
end as idx_data_ratio,
case reserved
when 0 then '100%'
else ltrim(str(unused * 100 /reserved) + '%')
end as unused_pct
from dbs_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc
drop table dbs_space
close c_tables
deallocate c_tables
------for my Warehouse DB takes about 00:01:59 and shows the same 1051 rows----------
----------------
This second TSQL takes too long and originates "Time Out " in my ASP!
This is the problem!
Is it because of the use of physical tables?
Is it because of the use of cursors in physical tables?
Any ideas?
Thank you,
Jorge Gomes
January 11, 2011 at 7:09 am
I don't completely understand what you're trying to do there, but yes, cursors are absolutely going to cause performance to slow down in most circumstances, regardless of whether or not you're using a temporary or permanent table.
BTW, a temporary table, and a table variable, are both written to disk. There's no such thing as a memory only table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2011 at 7:46 am
hum ... ok, let's see:
I have an ASP page where I have a html table showing data about the tables of a certain DB: number of lines, size of memory used, etc ...
I have a TSQL code that lets me do that. But I want to run this code through my ASP page. To do this, I need to put the code as a TSQL stored procedure (the code in TSQL is too complicated to put in ASP).
But, to create a sp_procedure, the code can not use temporary tables (code 1).
So, I changed the code to use permanent tables (code 2). Now, i can use the code to create sp_procedure who is call through ASP page.
Now, the code with permanent tables takes too long to process! And because of that then gives error in the ASP page.
I use cursors in both code 1 and code 2. but code 2 is very slow!!!
do you understand better?
Thank you
JG
January 11, 2011 at 8:05 am
My confusion comes from two sources, I don't understand what an sp_procedure is. A stored procedure:
CREATE PROCEDURE MyProcName
(@MyVariable varchar(50))
AS
CREATE TABLE #MyTable (MyId int);
INSERT INTO #MyTable
(MyId)
VALUES
(1),(2),(3);
GO
Absolutely can support temporary tables, so I'm unclear where your error is coming from.
From what I see of your query, instead of a cursor, since you're putting disparate data sets into one, I'd look at using UNION or UNION ALL (probably the latter since you don't need to sweat duplicated data) to combine the information. Then you don't have to cursor through the tables updating a temporary table. You might need to add another column for table name, so that each of the queries shows which table they are returning for, then you can order by table name.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2011 at 8:06 am
There are a few things going on here. First your comment about the code being too complicated to be put in asp...you should not have any sql in your application. This will cause you untold headaches in the long run.
There is absolutely no reason that a stored procedure cannot use a temp table.
As Grant said, the performance issue here is the use of cursors. I am almost certain that whatever data you are trying to retrieve can be done without cursors. If you show us clearly what you are trying to get out the database we can help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 11, 2011 at 8:10 am
I'm stuck. I thought maybe you were trying to create a temporary global procedure, but I just tried it and those allow for temporary tables as well.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2011 at 8:13 am
The error he is getting is a timeout. His cursor use is making the response so slow that whatever the timeout is in the connection string (or the default) is exceeded.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 11, 2011 at 8:57 am
I see two issues:
1. You can re-write this procedure to not need any temp tables or cursors - just a single, set-based query.
2. You are referencing sysobjects, sysindexes, etc. You need to use the new system views instead of the depreciated ones: sys.objects, sys.indexes.
This procedure is broken down into many needless steps - all those updates should be, at the most, sub-queries in the original select that populates the temp table. Once you get it all down to one "insert into ... select" statement, then it becomes pretty easy to remove the cursor. It is then just a matter of getting the output in the correct column order, and (optionally) utilizing an order by on that select to return the rows in the proper order.
When you do this, you shouldn't have any issues at all.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 11, 2011 at 9:29 am
well ... I'm getting crazy!
I said that temporary tables in stored procedures did not work because when I tried to create a stored procedure with code 1 gave me a error: it said I could not use stored procedures with temporary tables (SQL 2005)!
Now I see here that you tell me that this is possible!
I went to my Management Studio and tried to change the System Stored Procedure that was created in DB Master (to be available for all DBs).
I changed the permanent table to temporary table again. I was mad when I did make the change!
Dahhhh ... I'm crazy!
I tested my ASP page, where i call the stored procedure overall, and the timing is perfect!
I think my problem is closed. Thanks everyone!
------
But the basic problem I had was the timing that took my TSQL:
- with temporary tables is perfect (2 sec.)
- with permanent tables takes a long time causing timeout (more than 1 minute in ASP).
... but since i can change the system stored procedure for temporary tables everything is perfect!
... anyway, I'll review the TSQL to change code and remove all that INSERTS.
Jorge Gomes
January 11, 2011 at 9:37 am
Jorge,
Might I suggest two things:
1. First, do not add things to master. Instead, create a database (named something like "Common"), and put all your special stuff there. Yes, you can't mark it as a system procedure, and thus be accessible to all databases by simply executing the name, but you can execute it by putting that db name in front for a 3-part naming convention.
2. I'd still take a serious look into removing that cursor. Your 2 seconds could very well go down to 2 microseconds.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 11, 2011 at 9:40 am
WayneS (1/11/2011)
Jorge,Might I suggest two things:
1. First, do not add things to master. Instead, create a database (named something like "Common"), and put all your special stuff there. Yes, you can't mark it as a system procedure, and thus be accessible to all databases by simply executing the name, but you can execute it by putting that db name in front for a 3-part naming convention.
2. I'd still take a serious look into removing that cursor. Your 2 seconds could very well go down to 2 microseconds.
Absolute agreement on both points.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2011 at 9:57 am
Waine,
I created the stored procedure in a DB "Performance ", with temporary tables (fine!!!), like you said.
Now when I call the stored procedure from this command line:
- exec performance.dbo.sp_BigTablesSP
the result only shows the tables of DB performance.
I need to call in my ASP:
- exec ''& DB & ". dbo.sp_BigTablesSP, (where DB is the name of my DBs)
so i see every tables from each DB.
This is why I created the system stored procedure to display the data for each DB.
Can i use my performance.dbo.sp_BigTablesSP to show every DBs? i don't want to create one stored procedure in every DB!!!!
thanks,
Jorge
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply