performance improvement query - 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)

performance improvement query

Author

Topic

zion99
Posting Yak Master

141 Posts

Posted - 2009-08-28 : 02:28:06

Hi ,

I have a select query wherein I have to use around 10 CASE statements.

I was wondering if moving this entire logic to a UDF would make sense or should i move this logic in my RDL file (i will be using VB.NET code in RDL file).

Please let me know what will be the best way in terms of performance improvement

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-28 : 02:38:45

If depends on what the CASE statements do.

N 56°04'39.26"
E 12°55'05.63"

zion99
Posting Yak Master

141 Posts

Posted - 2009-08-28 : 02:49:34

here's the sample code


,CASE WHEN (@RView = 'Jan' or @RView = 'Feb') THEN 
	CASE WHEN ProductTypeId = 1 THEN	
		CASE WHEN [CompVal] < 0 THEN 
			CONVERT(VARCHAR(2),DATEPART(hh,CONVERT(DATETIME,0) - CONVERT(DATETIME,[CompVal]))) + ':' +
			CONVERT(VARCHAR(2),DATEPART(mi,CONVERT(DATETIME,0) - CONVERT(DATETIME,[CompVal]))) + ':' + 
			CONVERT(VARCHAR(2),DATEPART(ss,CONVERT(DATETIME,0) - CONVERT(DATETIME,[CompVal]))) + 
			CASE WHEN (DATEPART(dd,CONVERT(DATETIME,0) - CONVERT(DATETIME,[CompVal])) > 1) THEN
				' -' + CONVERT(VARCHAR(4),DATEPART(dd,CONVERT(DATETIME,0) - CONVERT(DATETIME,[CompVal]))-1) + 'd' 
			ELSE '' --NOTHING
			END				
		ELSE 
			CONVERT(VARCHAR(2),DATEPART(hh,CONVERT(DATETIME,[CompVal]))) + ':' +
			CONVERT(VARCHAR(2),DATEPART(mi,CONVERT(DATETIME,[CompVal]))) + ':' +
			CONVERT(VARCHAR(2),DATEPART(ss,CONVERT(DATETIME,[CompVal]))) + ' +' +
			CONVERT(VARCHAR(4),DATEPART(dd,CONVERT(DATETIME,[CompVal]))-1) + 'd' 
		END					
	ELSE 
		CASE WHEN [CompVal] > 50000 THEN 
		--To avoid this conversion, convert to decimal first
			CONVERT(VARCHAR(255),CONVERT(DECIMAL(16,0),[CompVal]))
		ELSE CONVERT(VARCHAR(255),[CompVal])
		END					
	END						
ELSE CONVERT(VARCHAR(255),[Value]) 
END AS [CompVal]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-28 : 03:20:19

Can you give some example of the output from the code above?
It seems to me that this can be simplified.

N 56°04'39.26"
E 12°55'05.63"

zion99
Posting Yak Master

141 Posts

Posted - 2009-08-28 : 05:33:56

declare @RView varchar(5)
set @RView = 'Jan'

DECLARE @tbl1 TABLE
(
[Value] VARCHAR(255)
,ProductTypeId INT
,[CompVal] float
)

insert into @tbl1
select 'HELLO 1',2,333333333333333 UNION ALL
select 'HELLO 2',2,3333333 UNION ALL
select 'HELLO 3',3,NULL UNION ALL
select 'HELLO 4',3,NULL UNION ALL
select 'HELLO 5',1,0.950694405864198 UNION ALL
select 'HELLO 6',1,1.00833329475309 UNION ALL
select 'HELLO 7',1,-14463.2083333333 UNION ALL
select 'HELLO 8',1,-2.909027777777778

select CASE WHEN (@RView = 'Jan' or @RView = 'Feb') THEN
CASE WHEN ProductTypeId = 1 THEN
CASE WHEN [CompVal] < 0 THEN
CONVERT(VARCHAR(2),DATEPART(hh,CONVERT(DATETIME,0) - CONVERT(DATETIME,[CompVal]))) + ':' +
CONVERT(VARCHAR(2),DATEPART(mi,CONVERT(DATETIME,0) - CONVERT(DATETIME,[CompVal]))) + ':' +
CONVERT(VARCHAR(2),DATEPART(ss,CONVERT(DATETIME,0) - CONVERT(DATETIME,[CompVal]))) +
CASE WHEN (DATEPART(dd,CONVERT(DATETIME,0) - CONVERT(DATETIME,[CompVal])) > 1) THEN
' -' + CONVERT(VARCHAR(4),DATEPART(dd,CONVERT(DATETIME,0) - CONVERT(DATETIME,[CompVal]))-1) + 'd'
ELSE '' --NOTHING
END
ELSE
CONVERT(VARCHAR(2),DATEPART(hh,CONVERT(DATETIME,[CompVal]))) + ':' +
CONVERT(VARCHAR(2),DATEPART(mi,CONVERT(DATETIME,[CompVal]))) + ':' +
CONVERT(VARCHAR(2),DATEPART(ss,CONVERT(DATETIME,[CompVal]))) +
CASE WHEN (DATEPART(dd,CONVERT(DATETIME,[CompVal])) > 1) THEN
' +' + CONVERT(VARCHAR(4),DATEPART(dd,CONVERT(DATETIME,[CompVal]))-1) + 'd'
ELSE '' --NOTHING
END
END
WHEN ProductTypeId = 2 THEN
CASE WHEN [CompVal] > 50000 THEN
--Large values appear in exponential format, convert to decimal
CONVERT(VARCHAR(255),CONVERT(DECIMAL(16,0),[CompVal]))
ELSE CONVERT(VARCHAR(255),[CompVal])
END
ELSE
CONVERT(VARCHAR(255),[Value])
END
ELSE CONVERT(VARCHAR(255),[Value])
END AS [CompVal]
FROM @tbl1

zion99
Posting Yak Master

141 Posts

Posted - 2009-08-28 : 05:45:29

just to add....based on the ProductTypeId, display needs to be customised..
i am also working on adding preceding Zero's for datetime values...
e.g. 0:11:59 +1d will be shown as 00:11:59 +1d
4:59:59 -7d will be shown as 04:59:59 -7d

zion99
Posting Yak Master

141 Posts

Posted - 2009-09-01 : 07:10:28

any suggestions plz. ???

zion99
Posting Yak Master

141 Posts

Posted - 2009-09-01 : 07:15:24

here's the updated query with padding zeros included.


declare @RView varchar(5)
set @RView = 'Jan'

DECLARE @tbl1 TABLE 
(
[Value] VARCHAR(255) 
,ProductTypeId INT 
,[CompVal] float
) 

insert into @tbl1
select 'HELLO 1',2,333333333333333 UNION ALL
select 'HELLO 2',2,3333333 UNION ALL
select 'HELLO 3',3,NULL UNION ALL
select 'HELLO 4',3,NULL UNION ALL
select 'HELLO 5',1,0.950694405864198 UNION ALL
select 'HELLO 6',1,1.00833329475309 UNION ALL
select 'HELLO 7',1,-14463.2083333333 UNION ALL
select 'HELLO 8',1,-2.909027777777778 

select CASE WHEN (@RView = 'Jan' or @RView = 'Feb') THEN 
CASE WHEN ProductTypeId = 1 THEN 
CASE WHEN [CompVal] < 0 THEN 
RIGHT(REPLICATE('0',2) + CAST(DATEPART(hh,CONVERT(DATETIME,0) - CONVERT(DATETIME,[CompVal])) as VARCHAR(2)),2) + ':' +
RIGHT(REPLICATE('0',2) + CAST(DATEPART(mi,CONVERT(DATETIME,0) - CONVERT(DATETIME,[CompVal])) as VARCHAR(2)),2) + ':' +
RIGHT(REPLICATE('0',2) + CAST(DATEPART(ss,CONVERT(DATETIME,0) - CONVERT(DATETIME,[CompVal])) as VARCHAR(2)),2) + 
CASE WHEN (DATEPART(dd,CONVERT(DATETIME,0) - CONVERT(DATETIME,[CompVal])) > 1) THEN
' -' + CONVERT(VARCHAR(4),DATEPART(dd,CONVERT(DATETIME,0) - CONVERT(DATETIME,[CompVal]))-1) + 'd' 
ELSE '' --NOTHING
END 
ELSE 
RIGHT(REPLICATE('0',2) + CAST(DATEPART(hh,CONVERT(DATETIME,[CompVal])) as VARCHAR(2)),2) + ':' +
RIGHT(REPLICATE('0',2) + CAST(DATEPART(mi,CONVERT(DATETIME,[CompVal])) as VARCHAR(2)),2) + ':' +
RIGHT(REPLICATE('0',2) + CAST(DATEPART(ss,CONVERT(DATETIME,[CompVal])) as VARCHAR(2)),2) + 
CASE WHEN (DATEPART(dd,CONVERT(DATETIME,[CompVal])) > 1) THEN
' +' + CONVERT(VARCHAR(4),DATEPART(dd,CONVERT(DATETIME,[CompVal]))-1) + 'd' 
ELSE '' --NOTHING
END 
END 
WHEN ProductTypeId = 2 THEN 
CASE WHEN [CompVal] > 50000 THEN 
--Large values appear in exponential format, convert to decimal
CONVERT(VARCHAR(255),CONVERT(DECIMAL(16,0),[CompVal]))
ELSE CONVERT(VARCHAR(255),[CompVal])
END 
ELSE 
CONVERT(VARCHAR(255),[Value]) 
END 
ELSE CONVERT(VARCHAR(255),[Value]) 
END AS [CompVal] 
FROM @tbl1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-01 : 16:04:58

Something like this?

DECLARE @tbl1 TABLE 
	(
		[Value] VARCHAR(255), 
		ProductTypeId INT,
		[CompVal] float
	) 

insert into @tbl1
select 'HELLO 1',2,333333333333333 UNION ALL
select 'HELLO 2',2,3333333 UNION ALL
select 'HELLO 3',3,NULL UNION ALL
select 'HELLO 4',3,NULL UNION ALL
select 'HELLO 5',1,0.950694405864198 UNION ALL
select 'HELLO 6',1,1.00833329475309 UNION ALL
select 'HELLO 7',1,-14463.2083333333 UNION ALL
select 'HELLO 8',1,-2.909027777777778 

SELECT	CASE
		WHEN CompVal > 50000 THEN LTRIM(STR(CompVal, 16, 0))
		WHEN CompVal IS NULL THEN Value
		WHEN CompVal >= 1 THEN CONVERT(CHAR(8), CAST(CompVal AS DATETIME), 108) + ' +' + LTRIM(STR(FLOOR(CompVal), 16, 0)) + 'd'
		WHEN CompVal >= 0 THEN CONVERT(CHAR(8), CAST(CompVal AS DATETIME), 108)
		WHEN CompVal < 0 THEN CONVERT(CHAR(8), CAST(CompVal AS DATETIME), 108) + ' -' + LTRIM(STR(FLOOR(CompVal), 16, 0)) + 'd'
	END	
FROM	@Tbl1

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources