Tweaking the SQL Script
-
Hi Guys,
There are two DATABASEs (workshop..same name) on two SQL SERVER 1.BOREDEV and 2.SQLDEV.
BOREDEV( SQL Server 2000 )is in a server name BOREDEV(win 2003..same name) and SQLDEV(Sql Server 2000) is on server SQLDEV(win 2003..same name)
This code is run in BOREDEV.workshop.Both the db WORKSHOP on SQLDEV and BOREDEV have the same set of tables.this script basically counts all the rows of all the tables of WORKSHOP in both the databases and gives out the comparison as output.
There are are around 88 Tables which matches the query and the rows in these tables can vary from 0 to 104320935.
This takes typically 30+ minutes to run.Is there any way (tweaking the code ) thorugh which we can get the output quickly.
SET NOCOUNT ON
DECLARE @TableName sysname,
@TableCount INT,
@sql varchar(400)
-- Create the table
CREATE TABLE #tablelist (
TableName varchar(100))
CREATE TABLE #tablecountlist (
TableName varchar(100),
BoreDev int,
Sqldev int)
Insert #tablelist (TableName)
SELECT a.name
FROM sysobjects a
WHERE xtype = 'U'
AND (a.name LIKE 'OBN01%'
or a.name like 'DEV01%'
or a.name like 'calndr%'
or a.name like 'gl04%'
or a.name like 'GL0%'
or a.name like 'hc0%'
or a.name like 'hr0%'
or a.name like 'prod03%'
or a.name like 'kcst01%'
or a.name like 'kdmn01%'
or a.name like 'mtl01%'
or a.name like 'exrate%')
order by a.name
-- Open the cursor
-- Declare cursor for list of indexes to be defragged
DECLARE TableCursor CURSOR FOR
SELECT RTRIM(TableName)
FROM #tablelist
-- Open the cursor
OPEN TableCursor
-- loop through the indexes
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0 BEGIN
set @sql = 'insert #tablecountlist select ''' + @TableName + ''', (select count(*) from ' + @TableName + '), (select count(*) from sqldev.workshop.dbo.' + @TableName + ')'
exec (@SQL)
FETCH NEXT FROM TableCursor INTO @TableName
END
-- Close and deallocate the cursor
CLOSE TableCursor
DEALLOCATE TableCursor
--select * from #fraglist
-- Delete the temporary table
DROP TABLE #tablelist
select * from #tablecountlist
drop table #tablecountlist
-
I have just written a simple piece of code which suffices the purpose specified by you...you can enhance it if u need it to do more ...It takes less than a minute for me to execute on my DB..which is not very huge (just a few tables with a couple of million rows and around 400+ tables). Pls try it on yours !!
Create Table #t
(rowid int identity(1,1),
DBName varchar(150),
TblName varchar(50),
Rowcnt int
)
Insert into #t (DBName, TblName)
select Host_Name() + '.' + db_name(), name from sysobjects where type = 'u'
Declare @cnt int,
@i int,
@sql nvarchar(200),
@tblname sysname
Select @cnt = @@rowcount
Select @i = 1
While @i <= @cnt
Begin
Select @tblname = TblName From #t where rowid = @i
Set @sql = 'Update #t Set Rowcnt = A.cnt From (Select Count(*) AS cnt from ' + @tblname + ') A Where tblname = ' + ''''+@tblname+''''
Execute (@sql)
-- select @sql
Set @i = @i + 1
End
Select * from #t
Drop table #t
I prefer to use the While Loop instead of a Cursor coz its usually faster than the Cursor.
Hope it helps!
Cheers,
Arvind -
You don't need to use count(*) to achieve this the number of rows in a table is stored in sysindexes.
e.g
SELECT rows
FROM DBName..sysindexes
WHERE OBJECT_ID('DBName..'+@tablename)= id and (indid = 0 or indid = 1)
This should be faster.
Dave
-
Awesome !! It worked.
Earlier It used to take 35 minutes.
Arvind - Your suggestion brought it down to 25 minutes !!!
David - Your suggestion brought it down to whopping 30 Seconds !!!
Thanks a million
.I really appreciate that.Debjit
-
Thats really cool Debjit.
Dave - Thx for the excellent suggestion...Never thot abt using the rows from sysindexes...
Out of curiosity...i am sure this will work fine for tables with clustered index but wud the count be rebuilt or updated in case of broken indexes or chaining issues with tables having only non clustered indexes.?
Cheers,
Arvind -
Arvind
I had the same queries when i started using this method and have never yet got an incorrect number of rows whether it has been a clustered or non-clustered index or even no index.
But if you do find any issues let me know.
Dave
-
Guys,
1. if there is no key defined in a table , in that case will sysindexes contain the rowcount.
2.One more thing how to convert a row from Null to 0,if that row has a formula.ROWCNT column has a formula and i am getting NUll , no matter how much i try it,if the field is 0.
If i run the query in the particular server it returns 0 but when run from a different server it is coming as NULL.
I have tried CAST,CONVERT,ISNULL,SET_ANSI_NULLS (ON/OFF) but nothing worked.
Debjit
-
1. SysIndexes system table will contain the number of rows for each table in the db. Existence of indexes is not a factor.
rows will be populated against the row with indid = 0 for a heap (table without indexes)
rows will be populated against the row with indid = 1 for a Clustered Index. Thats why the "(indid = 0 or indid = 1)" in the where clause.
Always indid = 0 contains the value for a Heap and indid = 1 refers to the Clustered Index and from there on indid 2 - 250 refer to Non Clustered Indexes. indid = 255 refers to columns with text, ntext, image data types etc.
2. I am sure you must have tried ISNULL() but in case you havent you better try that. In case you have tried that...need some more info on the data type and the query if possible.
Cheers!
Arvind -
Sorry a Heap is a table without a CLUSTERED Index. missed that important detail.
Cheers!
Arvind -
Hi Arvind,
here is the script..
It is run on boredev.workshop
SET NOCOUNT ON
DECLARE @TableName sysname,
@TableCount INT,
@sql varchar(400),
@cnt INT,
@i int
-- Create the table
CREATE TABLE #tablelist (
rowid int identity(1,1),
TableName varchar(100))
CREATE TABLE #tablecountlist (
TableName varchar(100),
BoreDev int,
Sqldev int)
Insert #tablelist (TableName)
SELECT a.name
FROM sysobjects a
WHERE xtype = 'U'
AND (a.name LIKE 'OBN01%'
or a.name like 'DEV01%'
or a.name like 'calndr%'
or a.name like 'gl04%'
or a.name like 'GL0%'
or a.name like 'hc0%'
or a.name like 'hr0%'
or a.name like 'prod03%'
or a.name like 'kcst01%'
or a.name like 'kdmn01%'
or a.name like 'mtl01%'
or a.name like 'exrate%')
order by a.name
Select @cnt = @@rowcount
Select @i = 1
While @i <= @cnt
Begin
Select @TableName = TblName From #tablelist where rowid = @i
set @sql = 'insert #tablecountlist select ''' + @TableName + ''',
(SELECT rows FROM workshop..sysindexes
WHERE OBJECT_ID('+ ''''+ ' workshop..' + @TableName + ''''+ ')= id'+ ' and '
+ '(indid = 0 or indid = 1)'+ ')
, (SELECT rows FROM sqldev.workshop.dbo.sysindexes
WHERE OBJECT_ID('+ ''''+ ' workshop..' + @TableName + ''''+ ')= id'+ ' and '
+ '(indid = 0 or indid = 1)' + ')'
exec (@SQL)
Set @i = @i + 1
End
DROP TABLE #tablelist
select * from #tablecountlist
drop table #tablecountlist
-> Infact if run this query in SQLDEV it gives(ex. of one table)
SELECT rows FROM sqldev.workshop.dbo.sysindexes WHERE OBJECT_ID('workshop..HC01Alpha')= id and (indid = 0 or indid = 1)
output :
rows
---
0
If i run the same query from BOREDEV ,it gives me blank space
rows
----
ANd when i run it as a part of the script it gives me NULL.
I tried ISNULL it didnt work.
Thanks,
Deb
-
Hi Deb,
I was getting NULL for some of the tables when i ran the script on my DB. But when i tried this
ISNULL( (SELECT rows FROM sqldev.workshop.dbo.sysindexes WHERE OBJECT_ID('+ ''''+ ' workshop..' + @TableName + ''''+ ')= id'+ ' and ' + '(indid = 0 or indid = 1)' + ') , 0) '
ISNULL for the whole select statement itself...Then i got 0 instead of NULL...Not sure if you tried this...Lemme know if this helps.
Cheers!
Arvind -
even i tried of doing that !!!.but there was a confusion in the quotes sign so i didnt tried it finally
Thanks anyways...
Deb
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply