September 14, 2010 at 11:21 pm
hi,
When i execute the query it takes 6-10 seconds some times more then that while it return 3122 records only.
declare @fromdt varchar(10),@todt varchar(10)
select @fromdt ='01-12-2007',@todt ='31-12-2007'
Select D.FLTNBR,D.STARTTIME,D.DEP,SUM(D.PAX) AS PAX,SUM(D.PAX_BOOKED) AS PAX_BOOKED FROM
(Select Distinct FLTNBR,STARTTIME,DEP,
CASE WHEN D.PAXCLASSSTATUS='ACTUAL' THEN D.PAXFCM ELSE 0 END AS PAX,
CASE WHEN D.PAXCLASSSTATUS='BOOKING' THEN D.PAXFCM ELSE 0 END AS PAX_BOOKED
from t_loadinfo D where dbo.FnISTDt(d.starttime)>=convert(datetime,@fromdt,103)
and dbo.FnISTDt(d.starttime)<=convert(datetime,@todt,103) ) D
GROUP BY D.FLTNBR,D.STARTTIME,D.DEP
and the table structure is
CREATE TABLE [dbo].[T_LoadInfo](
[AIRLINE] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FLTNBR] [int] NULL,
[SUFFIX] [nvarchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[STARTTIME] [datetime] NULL,
[DEP] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PAXADULTS] [int] NULL,
[PAXCHILDREN] [int] NULL,
[PAXINFANTS] [int] NULL,
[PAXFCM] [int] NULL,
[PAX_F] [int] NULL,
[PAX_C] [int] NULL,
[PAX_M] [int] NULL,
[PAXCLASSSTATUS] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PAXTYPESTATUS] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PAXACI] [int] NULL,
[CREATED_DTM] [datetime] NULL CONSTRAINT [DF_T_LoadInfo_CREATED_DTM] DEFAULT (getdate())
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ALL CLASSES (FCM)' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'T_LoadInfo', @level2type=N'COLUMN', @level2name=N'PAXFCM'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'CLASS F' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'T_LoadInfo', @level2type=N'COLUMN', @level2name=N'PAX_F'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'CLASS C' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'T_LoadInfo', @level2type=N'COLUMN', @level2name=N'PAX_C'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'CLASS M' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'T_LoadInfo', @level2type=N'COLUMN', @level2name=N'PAX_M'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ACTUAL, BOOKING' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'T_LoadInfo', @level2type=N'COLUMN', @level2name=N'PAXCLASSSTATUS'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ACTUAL, BOOKING,PROGNOSIS OR NONE' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'T_LoadInfo', @level2type=N'COLUMN', @level2name=N'PAXTYPESTATUS'
Please help
September 14, 2010 at 11:28 pm
Select Distinct FLTNBR,STARTTI
:w00t:
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
September 15, 2010 at 1:19 am
where dbo.FnISTDt(d.starttime)>=convert(datetime,@fromdt,103)and dbo.FnISTDt(d.starttime)<=convert(datetime,@todt,103) ) D
:w00t:
This is probably the root cause.
Two links that will help you...
http://www.karaszi.com/SQLServer/info_datetime.asp <--- Required reading IMO
and check out Rob Farley's "Sargability" Session from this link
http://appdev.sqlpass.org/MeetingArchive/tabid/2005/Default.aspx
September 15, 2010 at 1:55 am
The functions on the columns will force at least an index scan, possibly even a table scan.
What do those UDFs do?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 15, 2010 at 2:51 am
GilaMonster (9/15/2010)
The functions on the columns will force at least an index scan, possibly even a table scan.What do those UDFs do?
here is the function details................
ALTER Function [dbo].[Fn_HHMM](@DT DATETIME)
Returns VARCHAR(5)
As
Begin
Declare @hh varchar(2)
Declare @mm varchar(2)
Declare @hhmm varchar(5)
set @hh=''
set @mm=''
set @hhmm=''
-- set @hh=CONVERT(VARCHAR,DATEPART(hh,DATEADD(mi,-330,@dt))) -- UTC
-- set @mm=CONVERT(VARCHAR,DATEPART(mi,DATEADD(mi,-330,@dt))) -- UTC
--set @dt=dateadd(mi,330,@DT) --Making this Date according to IST time
set @hh=CONVERT(VARCHAR,DATEPART(hh,@dt))
set @mm=CONVERT(VARCHAR,DATEPART(mi,@dt))
if len(@hh)<2
begin
set @hh='0' +@hh
end
if len(@mm)<2
begin
end
set @hhmm=@hh +':'+@mm
RETURN @hhmm
End
-----------------
ALTER Function [dbo].[Fn_HHMM_To_Mins](@str varchar(10))
Returns int
As
Begin
Declare @mins int
Declare @np varchar(5)
set @mins=0
set @np=''
set @STR=ltrim(rtrim(@str))
if @STR is null or @STR='' -- or CHARINDEX(':',@str,1)=0
begin
set @mins=0
end
else
begin
set @np=(SUBSTRING(@str,CHARINDEX(':',@str,1)-2,2))
if left(ltrim(@np),1)='-'
Begin
set @mins=(((SUBSTRING(@str,CHARINDEX(':',@str,1)-2,2))*60) + ((SUBSTRING(@str,CHARINDEX(':',@str,1)+1,2))%60))
set @mins=@mins*-1
end
else
Begin
set @mins=(((SUBSTRING(@str,CHARINDEX(':',@str,1)-2,2))*60) + ((SUBSTRING(@str,CHARINDEX(':',@str,1)+1,2))%60))
end
set @np=''
end
RETURN @mins
End
----------------
ALTER FUNCTION [dbo].[FN_HM](@DT int)
Returns VARCHAR(8)
As
Begin
Declare @hh varchar(5)
Declare @mm varchar(5)
Declare @hhmm varchar(8)
declare @Q int
Declare @r int
Declare @DD int
set @DD=@DT
set @hh=''
set @mm=''
set @hhmm=''
--For positive Vaues
if(@DD<60 and @dd>0)
begin
-- print 'positive only minutes'
set @hh='00'
set @mm=convert(varchar,@DD)
end
else if (@DD>=60)
begin
-- print 'positive hours and minutes'
set @Q=@DD/60
set @r=@DD%60
set @hh=convert(varchar,@Q)
set @mm=convert(varchar,@R)
end
--For Negative Vaues
if(@DD<0 and @dd>-60)
begin
-- print 'negative only minutes'
set @hh='00'
set @mm=convert(varchar,@DD*-1)
end
else if (@DD<-60)
begin
-- print 'negative hours and minutes'
set @Q=(@DD*-1)/60
set @r=(@DD*-1)%60
set @hh=convert(varchar,@Q)
set @mm=convert(varchar,@R)
end
if (@dt)>0--For Positive Vaues
begin
if len(@hh)<2
begin
set @hh='0' +@hh
end
if len(@mm)<2
begin
end
-- This function takes care of 24 hour clock
if Convert(int,@hh) > 23
begin
if (Convert(int,@hh)-24)>= 0
begin
if len(Convert(int,@hh)-24)<2
begin
set @hh='0' + Convert(varchar,(Convert(int,@hh)-24))
end
else
begin
set @hh=Convert(varchar,(Convert(int,@hh)-24))
end
end
end
set @hhmm=@hh +@mm
end
if (@dt)<0--For Negative Vaues
begin
if len(@hh)<2
begin
set @hh='0' +@hh
end
if len(@mm)<2
begin
end
set @hhmm='-'+ @hh + @mm
end
RETURN @hhmm
End
----------------------------
ALTER FUNCTION [dbo].[FN_INT_HHMM](@DT int)
Returns VARCHAR(8)
As
Begin
Declare @hh varchar(5)
Declare @mm varchar(5)
Declare @hhmm varchar(8)
declare @Q int
Declare @r int
Declare @DD int
set @DD=@DT
set @hh=''
set @mm=''
set @hhmm=''
--For positive Vaues
if(@DD<60 and @dd>0)
begin
-- print 'positive only minutes'
set @hh='00'
set @mm=convert(varchar,@DD)
end
else if (@DD>=60)
begin
-- print 'positive hours and minutes'
set @Q=@DD/60
set @r=@DD%60
set @hh=convert(varchar,@Q)
set @mm=convert(varchar,@R)
end
--For Negative Vaues
if(@DD<0 and @dd>-60)
begin
-- print 'negative only minutes'
set @hh='00'
set @mm=convert(varchar,@DD*-1)
end
else if (@DD<-60)
begin
-- print 'negative hours and minutes'
set @Q=(@DD*-1)/60
set @r=(@DD*-1)%60
set @hh=convert(varchar,@Q)
set @mm=convert(varchar,@R)
end
if (@dt)>0--For Positive Vaues
begin
if len(@hh)<2
begin
set @hh='0' +@hh
end
if len(@mm)<2
begin
end
set @hhmm=@hh +@mm
end
if (@dt)<0--For Negative Vaues
begin
if len(@hh)<2
begin
set @hh='0' +@hh
end
if len(@mm)<2
begin
end
set @hhmm='-'+ @hh + @mm
end
RETURN @hhmm
End
----------------
ALTER Function [dbo].[FnDT_HHMM](@DT DATETIME)
Returns VARCHAR(5)
As
Begin
Declare @hh varchar(2)
Declare @mm varchar(2)
Declare @hhmm varchar(5)
set @hh=''
set @mm=''
set @hhmm=''
set @hh=CONVERT(VARCHAR,DATEPART(hh,@dt))
set @mm=CONVERT(VARCHAR,DATEPART(mi,@dt))
if len(@hh)<2
begin
set @hh='0' +@hh
end
if len(@mm)<2
begin
end
set @hhmm=@hh +':'+@mm
RETURN @hhmm
End
-------------------------------
ALTER Function [dbo].[FnIST](@DT DATETIME)
Returns Datetime
As
Begin
set @dt=dateadd(mi,330,@DT)
RETURN @dt
End
-----------------
ALTER Function [dbo].[FnISTDt](@DT DATETIME)
Returns Datetime
As
--This function convert UTC into IST and removes time.
Begin
Declare @dtstr varchar(10)
set @dt=dateadd(mi,330,@DT)
set @DTstr=convert(varchar(10),@dt,120)
set @dt=convert(datetime,@DTstr)
RETURN @dt
End
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply