query faster - 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

query faster

Author

Topic

kshitizgp
Starting Member

31 Posts

Posted - 2012-01-23 : 05:49:45

USE [db_1234]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[AddLeaveForm]

@Employee_Id numeric(18,0),

@Leave_Status varchar(50),
@Created_By numeric(18,0) ,
@Is_Active BIT= null ,

@Modified_By numeric(18,0) = NULL,
@Modified_Date datetime = NULL,
@start_Date datetime,
@end_Date DATETIME

AS

BEGIN
declare @flag bit
set @flag = 0
set nocount off;
while(@start_Date <= @end_Date)

IF NOT EXISTS (SELECT FD.Employee_Id from dbo.Form_Details FD where FD.Employee_Id = @Employee_Id AND work_date = @start_Date group by FD.Employee_id having SUM(WORK_hours)>0)
BEGIN

IF NOT EXISTS (select Leave_Id from Employee_Leave where Employee_Id = @Employee_Id and Leave_Date=@start_Date )
begin
INSERT INTO
[Employee_Leave]
(
[Employee_Id],
[Leave_Date]
,[Leave_Status]
,[Created_By]
,[Is_Active]
,[Created_Date]
)
VALUES
(
@Employee_Id ,
@start_Date,
@Leave_Status,
@Created_By,
1,
CURRENT_TIMESTAMP
)
end

ELSE
BEGIN
set @flag = 1
END
set @start_Date = DATEADD(DD, 1, @start_Date);
END -- End While Loop

if @flag = 1
select -1
else

SELECT @@IDENTITY

END

i have made it like this ..its working properly but taking too much time if sum is greater than zero ....else if sum= 0 its quickly inserting..

how can i make this sp run fast ..coz if client wait for 20-30 secs for leave to process he will get frustrated ..any ideas!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-23 : 06:03:52

Yeah, get rid of the while loop and do a single insert of the required rows. You can use a tally table (numbers table) or a calendar table as the source for the dates, and filter out the dates that shouldn't be inserted in the where clause

--
Gail Shaw
SQL Server MVP

kshitizgp
Starting Member

31 Posts

Posted - 2012-01-24 : 01:41:16

@gilamonster ..
i asked my ML he said no to tally table ... he said make changes in sp :(((

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-24 : 03:49:56

Why does he not want a tally table? Any good reason?
You can always create a temp table with sequential numbers or dates and use that instead of a permanent table)

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources