SQL Performance Problem - 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

SQL Performance Problem

Author

Topic

hemant_ec48
Starting Member

7 Posts

Posted - 2010-04-19 : 08:17:51

Hi Friends,

I have some performance problem.

I have two table.

IssueLog & IssueLogRegion Detail.

Both are connected by Foreign key IssueLogID. Each Issue may or may not have region but it contains multiple region.

So now my requirement is there..

While fetching Issues..if that issue is assigned to mutiple region i need to show Multiple & if it is assign to single than it should display that region name. & if it is not assigned that I need to display "No region".

I have written case statement as below...is there any other way to achieve this task...

Likee...
CASE(SELECT COUNT(IssueLogRegionDetailId) FROM IssueLogRegionDetail WHERE IssueLogId=IssueLog.IssueLogID AND Deleted=0)
WHEN 0 THEN 'No Region'
When 1 THEN (SELECT
RegionTitle
FROM
IssueLogRegionDetail
INNER JOIN IssueLogRegion
ON
IssueLogRegion.IssueLogRegionID=IssueLogRegionDetail.IssueLogRegionId AND
IssueLogRegionDetail.Deleted=0 AND
IssueLogId=IssueLog.IssueLogID
)
ELSE 'Multiple'
END AS Region

************************

here i like to tell you that....IssueLog table is same way connected to other 8 different table same as IssueLogregion.

Hemant Patel
Contact me : 09726242864

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-04-19 : 09:15:34

Your sample CASE deal with three tables but your introduction to it mentioned two tables so either you fix CASE sample or provide more detailed information regarding your tables

Kristen
Test

22859 Posts

Posted - 2010-04-19 : 09:17:39

Not sure what columns join to which in your JOINs, so you'll have to fix any mis-understnadings I've made, but does this help?


SELECT	L.Col1, L.Col2,	...,
	CASE COUNT(IssueLogRegionDetailId)
		WHEN 0 THEN 'No Region'
		WHEN 1 THEN MIN(RegionTitle)
		ELSE 'Multiple'
		END AS Region
FROM	IssueLog AS L
	LEFT OUTER JOIN
	(
		IssueLogRegion AS R
		JOIN IssueLogRegionDetail AS RD
			 ON RD.IssueLogRegionId = R.IssueLogRegionID
			AND RD.IssueLogId = R.IssueLogID
			AND RD.Deleted = 0
	)
		 ON R.IssueLogId = L.IssueLogID
WHERE	...
GROUP BY L.Col1, L.Col2, ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources