need help to create an index - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

need help to create an index

Author

Topic

ramu143
Yak Posting Veteran

64 Posts

Posted - 2008-05-15 : 06:22:23

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.carr_summary_Datewise Script Date: 5/15/2008 10:20:37 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[carr_summary_Datewise]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[carr_summary_Datewise]
GO

CREATE proc carr_summary_Datewise --2007,9,27,30,'COMPUTERTEL'

@pYear int,
@pMonth int,
@pDay1 int,
@pDay2 int,
@pOperator varchar(32) -- DisplayName

as
begin

declare @sql varchar(4000)
declare @Service varchar(32)
declare @Operator varchar(32)
declare @tDiff int
declare @pDate1 varchar(32)
declare @pDate2 varchar(32)
declare @pD1 datetime
declare @pD2 datetime
declare @sD varchar(2)
declare @eD varchar(2)
declare @eM varchar(2)
declare @tb1 varchar (32)

set @pDate1 = dbo.AsString(@pYear,@pMonth,@pDay1,0,0,0)
set @pDate2= dbo.AsString(@pYear,@pMonth,@pDay2,23,59,59)
--print @pDate1
--print @pDate2

select @Operator=SystemName,@tDiff=timeDiff from Report..Carriers where DisplayName=@pOperator
select @Service=serviceName from Report..Carriers where SystemName=@Operator
--print @tDiff

set @pD1=dateadd(hh,@tDiff,(cast(@pDate1 as datetime)))
set @pD2=dateadd(hh,@tDiff,(cast(@pDate2 as datetime)))
--print @pD1
--print @pD2

/*set @sD=datepart(dd,@pD1)
set @eD=datepart(dd,@pD2)
set @eM=datepart(mm,@pD1)*/
--print @eM

if @pDay1=1
begin
set @sD=1
end
else
begin
set @sD=datepart(dd,@pD1)
end

if @pDay2=30
begin
set @eD=30
end
else
begin
set @eD=datepart(dd,@pD2)
end

if @pDay2=31
begin
set @eD=31
end
--else
-- begin
-- set @eD=datepart(dd,@pD2)
-- end

--print @sD
--print @eD
--print 'Operator=' + @pOperator
--print 'Service=' + @Service

--set @tb1='ob_sep07'

if @pMonth=1
begin
set @tb1='ob_Jan08'
end
if @pMonth=2
begin
set @tb1='ob_Feb08'
end
if @pMonth=3
begin
set @tb1='ob_Mar08'
end
if @pMonth=4
begin
set @tb1='ob_Apr08'
end
if @pMonth=5
begin
set @tb1='ob_May08'
end
if @pMonth=6
begin
set @tb1='ob_Jun08'
end
if @pMonth=7
begin
set @tb1='ob_Jul08'
end
if @pMonth=8
begin
set @tb1='ob_Aug08'
end
if @pMonth=9
begin
set @tb1='ob_Sep08'
end
if @pMonth=10
begin
set @tb1='ob_Oct08'
end
if @pMonth=11
begin
set @tb1='ob_Nov08'
end
if @pMonth=12
begin
set @tb1='ob_Dec08'
end

set @sql='

select Callyy,Callmm,Calldd,CallDate,dbo.Acc_NearestZone_Tracks(Operatorout,routepfx,CallDate) zone,
routepfx,Talktime,RefUploader.dbo.WhichTimeClass(''' + @pOperator + ''',CallDate) TimeCls,Cost
from
(

select Callyy,Callmm,Calldd,dbo.asString(Callyy,Callmm,Calldd,Callhh,0,0) CallDate,Routepfx,Operatorout,
cast(sum(Talktime/60.) as decimal(10,2)) Talktime,Cost
from Report.dbo.'+@tb1+' (nolock)
where Operatorout=''' + @Operator + '''
and Callyy=' + ltrim(str(@pYear)) + '
and Callmm=' + ltrim(str(@pMonth)) + '
and calldd between ' + ltrim(str(@sD)) + ' and ' + ltrim(str(@eD)) + '
group by Callyy,Callmm,Calldd,dbo.asString(Callyy,Callmm,Calldd,Callhh,0,0),
Routepfx,Operatorout,Cost
)x
where CallDate between ''' + convert(varchar(20),@pD1,120) + ''' and ''' + convert(varchar(20),@pD2,120) + '''
'
--print(@sql)
--exec (@sql)

set @sql=
'select Callyy,Callmm,Calldd,Routepfx Prefix,case when zone is not null then zone else ''--NOT FOUND--'' end zone,
case when TimeCls is not null then TimeCls else ''--NOT FOUND--'' end TimeCls,
''' + @Operator+ ''' Operatorout,Sum(Talktime) Talktime,Cost
from ('+@sql+') x
group by Callyy,Callmm,Calldd,Routepfx,zone,TimeCls,Cost
order by Callyy,Callmm,Calldd,Routepfx,zone,TimeCls
'
--print (@sql)
exec (@sql)
end

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

this procedure takes more time to run

we need make it fastly

what should i need

if i create one index how it works

where should i create index please write one index for me

jason7655
Starting Member

24 Posts

Posted - 2008-05-15 : 12:43:16

So have you verified that you have no indexes presently?

I would first see what indexes I have, and then go from there.

If you have no indexes then you would probably want to start by determining the column of the table that is queried often and used in joins. It doesn't have to be completely unique but you don't want to add a clustered index on a column that only has 2 possible values like male/female.

Have a look at indexes. Then have a look at your table. Then have a look at this query. Then think about any other queries that hit the same table. Would they benefit from this index or would it hurt them. Then test it.

That's not exactly "writing one index" for you but it should at least point you in the right direction. I could go through your stored procedure and find a possible candidate for an index but it's hard to say without knowing more info.

Run the following code in Query Analyzer (taken from http://www.sqlservercentral.com/scripts/Index+Management/31800/)


-- Select all table names, their individual indexes with keys, description
-- and disk size in MB into a temporary table. 

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @TabName varchar(100)

CREATE TABLE #T (TabName varchar(200), IndexName varchar(200), IndexDescr varchar(200), IndexKeys varchar(200), IndexSize int)

DECLARE TCursor CURSOR FAST_FORWARD LOCAL FOR
	SELECT name FROM sysobjects WHERE xtype = 'U'
OPEN TCursor

FETCH NEXT FROM TCursor INTO @TabName
WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO #T (IndexName, IndexDescr, IndexKeys)
		EXEC sp_helpindex @TabName
		
		UPDATE #T SET TabName = @TabName WHERE TabName IS NULL
		
		FETCH NEXT FROM TCursor INTO @TabName
	END

CLOSE TCursor
DEALLOCATE TCursor
GO

DECLARE @ValueCoef int
SELECT @ValueCoef = low FROM Master.dbo.spt_values WHERE number = 1 AND type = N'E'

UPDATE #T SET IndexSize = 
	((CAST(sysindexes.used AS bigint) * @ValueCoef)/1024)/1024
		FROM sysobjects INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
			INNER JOIN #T T ON T.TabName = sysobjects.name AND T.IndexName = sysindexes.name

SELECT * FROM #T
--WHERE IndexDescr LIKE '%nonclustered%'  --Here various filters can be applied
ORDER BY TabName, IndexName 
GO

DROP TABLE #T
GO

That code should show you what indexes you have on the Report..Carriers table.
If you don't have an index then you know you need one.

Once you determine where you need to put it you can do:


USE [database] 
go

SET QUOTED_IDENTIFIER ON 
SET ARITHABORT ON 
SET CONCAT_NULL_YIELDS_NULL ON 
SET ANSI_NULLS ON 
SET ANSI_PADDING ON 
SET ANSI_WARNINGS ON 
SET NUMERIC_ROUNDABORT OFF 
go

DECLARE @bErrors as bit

BEGIN TRANSACTION
SET @bErrors = 0

--DROP INDEX [dbo].[table].[index] --if you need to drop one later
CREATE CLUSTERED INDEX [index_name] ON [dbo].[table] ([column] ASC)
IF( @@error <> 0 ) SET @bErrors = 1

IF( @bErrors = 0 )
  COMMIT TRANSACTION
ELSE
  ROLLBACK TRANSACTION

That should be enough to get you started.

X002548
Not Just a Number

15586 Posts

Posted - 2008-05-15 : 12:48:19

Did you do a show plan on the query?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-05-15 : 22:48:23

It's virtually impossible to say with this crappy data model. With all the monthly tables and dynamic SQL, dates as strings etc god only knows what you would need to do. I'd say creating an index was the least of your worries!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources