Stored Proc. Optimization - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Proc. Optimization

Author

Topic

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-05-29 : 05:05:46

I have a table tblBadgeData,
it has following columns
Gen_Time, Status, Reader, Card_Number, Name, Facility
Eg Data:

2009-04-07 14:18:00.000 Permanant Wing A 3rd In 08451790 zyx(3665), abcd FT
2009-04-08 14:18:00.000 Permanant Wing A 4rd In 08451790 zyx(3665), abcd CT
like above row, there are 5 lakhs rows are there now.

if i pass 2 dates as inputs, it needs to give the worked hours in a facility under those dates.
eg: if i pass 5/1/2009, 5/3/2009 (dates are in mm/dd/yyyy format)
then the output should be like, that means dates has to become columns, and if he spend a day in 2 building it has to show in comma seperated format.

EmpName EmpId Company 5/1/2009 5/2/2009
xyz 123 test FT-2hrs15min;CT-3hrs 15min 1hr 30 min;

Note:EMpName,EmpId are not directlt there. There are in Name column in table. I extracted those by using substrings.
I implemented the below query. it is taking 90 seconds for 5 days duration. but we need normally output for 1 month. it is not giving output after 10 min, when we give inputs btn 1 month duration.

CREATE proc [dbo].[usp_getWorkedHoursCount_ByDate_Facility] -- usp_getWorkedHoursCount_ByDate_Facility '5/1/2009','5/5/2009'
(

@StartDate datetime = null,
@EndDate datetime = null

)
as
begin
declare @dates nvarchar(max)
declare @tempdates table
(
myDate datetime
)
;with cte as
(
select @StartDate as StartDate
union all
select StartDate + 1 from cte
where StartDate < @EndDate
)
insert into @tempdates
select * from cte
OPTION (MAXRECURSION 10000);
delete from @tempdates where mydate in (select HolidayDate from Holidays)
set @dates = stuff( replace( (select ':' + Convert(varchar,myDate,107) from @tempdates for xml path('')),':','],['),1,2,'') + ']'
declare @sql nvarchar(max)
declare @datesinputs varchar(max)
set @datesinputs = (select stuff( (select ','+'coalesce([' + Convert(varchar,myDate,107) + '],''0'') as [' + Substring(Convert(varchar,myDate,101),0,6) + ']' from @tempdates for xml path('')),1,1,''))

set @sql = 'declare @StartDate datetime declare @enddate datetime set @StartDate = ''' + convert(varchar,@StartDate,101) + ''' set @EndDate = ''' + convert(varchar,@EndDate,101) + ''';with cte as
(
select distinct Substring([Name],charindex('','',[Name])+ 2,len([Name])) as [Name],
substring([Name],charindex(''('',[Name])+1,charindex('')'',[Name])-(charindex(''('',[Name])+1)) as [EmpID],
Gen_Time ,
Case When [Name] like ''Iso%'' Then ''iSoft'' When [Name] like ''CSC%'' then ''CSC'' Else ''NA'' ENd
as Company ,Facility
from tblBadgedata
where gen_time >= @StartDate and Gen_Time < @EndDate + 1 and
Substring([Name],0,charindex(''('',[Name])) in (''ISO'',''CSC'') AND [Name] not like ''ISO (,%''
)
,
cte1 as
(
select Facility,Name,EmpId,convert(varchar,Gen_time,107) as Date,datediff(mi, Min(Gen_time),max(Gen_time)) as Duration,Company
from cte group by Name,EmpId,Company,convert(varchar,Gen_time,107),Facility
)
select Name,EmpId,Company,' + @datesinputs + ' from
(
select Name,EmpId,Company,Date,
(select Facility + ''-'' + cast( Duration/60 as varchar) + '' hrs '' + cast( Duration%60 as varchar) + '' Mins;''
from cte1 where cte1.Name = c1.Name and cte1.EmpId = c1.EmpId and cte1.Company = c1.Company and cte1.Date = c1.Date
for xml path('''')
)
as TimeSpent
from cte1 c1
)p
pivot (max(TimeSpent) for Date in (' + @dates + '))pvt '

exec(@sql)
end

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-05-29 : 09:12:05

any help?

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-05-30 : 07:58:23

In above stored proc, to get the comma seperated values i used subquery concept, is there any better way that that?
suppose if a tabe is like
Id Name
1 ram
1 reddy
2 abcd
2 def
required o/p : 1 ram;reddy;
2 abcd;def;

I used the query of type,
select distinct Id,(select Name + ';' from TableName where Id = t.Id for xml path('')) as Names from TableName t

But this query causing me trouble when there are lakhs of records, as its a sub query, is there any other way to get this type of output?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-30 : 08:59:12

Try one of the methods
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Madhivanan

Failing to plan is Planning to fail

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-05-31 : 01:45:05

quote:
Originally posted by madhivanan

Try one of the methods
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Madhivanan

Failing to plan is Planning to fail

Thanks Madhivanan, Great article. I will try that techniques in article.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources