Is this stored procedure efficient ? well coded ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Is this stored procedure efficient ? well coded ?

Author

Topic

johnstern
Yak Posting Veteran

67 Posts

Posted - 2007-04-26 : 18:04:44

Hello,

I have a stored procedured below that will be used by 3 different websites, so I need check which site is calling it in order to return the correct results.

currently this is my approach but I am little bit nervous about performance( numbers of rows is about 10k), and even the actual logic I am following.

This is the first time that I made this type of multi website stored procedured so I would like to get some opinions. Is this the right approach?, am I overlooking something simpler?.

the below code is only an example, the select * statement is actually 20 lines long ( so that is why I did not just make 3 if else statements with their own queries. otherwise I was going to have near identical 20 lines code, 3 times)

thank you for your input

CREATE PROCEDURE dbo.GetContent

@SiteFlag varchar(10)
AS

Declare @IsCNN int,
@IsGOOGLE int,
@IsMSN int

set @ IsCNN = 0
set @ IsGOOGLE = 0
set @ IsMSN =0

IF @SiteFlag = 'CNN'
Set @ IsCNN = 1

IF @SiteFlag = GOOGLE'
Set @ IsGOOGLE = 1

IF @SiteFlag = 'MSN'
Set @ IsMSN = 1

SELECT *

FROM mytable

WHERE IsCNN = @IsCNN and IsGOOGLE = @IsGOOGLE and IsMSN = @IsMSN

Mytable

Id | content | IsCNN| IsGOOGLE| IsMSN|

1 | my text | 1 | 1 | 0|
2 | my other text | 0 | 0 | 1|
3 | my very other text | 0 | 1 | 1|

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 18:20:31

[code]CREATE PROCEDURE dbo.uspGetContent
(
@SiteFlag VARCHAR(10)
)
AS

SET NOCOUNT ON

SELECT ID,
Content
FROM MyTable
WHERE IsCNN = 1 AND @SiteFlag = 'CNN'
OR IsMSN = 1 AND @SiteFlag = 'MSN'
OR IsGOOGLE = 1 AND @SiteFlag = 'Google'[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 18:22:51

You would be better off normalizing your database, and use something like this

CREATE PROCEDURE dbo.uspGetContent
(
	@SiteFlag VARCHAR(10) 
)
AS

SET NOCOUNT ON

SELECT	ID,
	Content
FROM	MyTable
WHERE	Site = @SiteFlag

Peter Larsson
Helsingborg, Sweden

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-26 : 18:25:39

1. select * in a proc is a no-no. if you add or remove a column to the table, the proc continues to work but clients will break.
2. I would probably use a single bitmask column instead of 3 separate bit columns (so you can easily add another provider without adding another column)

www.elsasoft.org

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 18:28:01

Or even better

MyTable

ID	CONTENT
--	------------------
1	my text
2	my other text
3	my very other text


MyContentSites

ID	ContentID	Site
--	---------	-------
1	1		CNN
2	1		GOOGLE
3	2		MSN
4	3		GOOGLE
5	3		MSN


SELECT		t.Content
FROM		MyTable AS t
INNER JOIN	MyContentSites AS s ON s.ContentID = t.ID
WHERE		s.Site = @SiteFlag

You could even expand the Site column to a new table MySites and change the Site column to a INT instead of VARCHAR as a foreign key to the new MySites table.

Peter Larsson
Helsingborg, Sweden

johnstern
Yak Posting Veteran

67 Posts

Posted - 2007-04-26 : 18:30:24

-I would very much want to normalize that database, but that is the way my client has it, but I will suggest to him
- I am not using * , it was just for sample purposes ( but good to know :))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 18:31:33

Fully normalized.

[MyContents table]

ContentID	ContentText
--		------------------
1		My text
2		My other text
3		My very other text


[MySites table]

SiteID	SiteName
--	------------------
1	CNN
2	GOOGLE
3	MSN


[MyContentSites table]

ID	ContentID	SiteID
--	---------	-------
1	1		1
2	1		2
3	2		3
4	3		2
5	3		3


SELECT		c.ContentText
FROM		MyContents AS c
INNER JOIN	MyContentSites AS cs ON cs.ContentID = c.ContentID
INNER JOIN	MySites AS s ON s.SiteID = cs.SiteID
WHERE		s.SiteName = @SiteFlag

This allows you to add any new Site without changing code!
The MyContentSites table is the "bridge" between you content and your sites.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 18:34:34

quote:
Originally posted by johnstern

-I would very much want to normalize that database, but that is the way my client has it, but I will suggest to him

If it is not possible to change table layouts, use my first suggestion.

Peter Larsson
Helsingborg, Sweden

johnstern
Yak Posting Veteran

67 Posts

Posted - 2007-04-26 : 18:37:23

thank you very much for the fast responses, this is incredible, first time using this forum, I got answers within minutes to the questions I been breaking my head this whole afternoon

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 18:41:15

http://www.datamodel.org/NormalizationRules.html

Peter Larsson
Helsingborg, Sweden

johnstern
Yak Posting Veteran

67 Posts

Posted - 2007-04-26 : 18:50:21

quote:
Originally posted by jezemine

2. I would probably use a single bitmask column instead of 3 separate bit columns (so you can easily add another provider without adding another column)

www.elsasoft.org

Since you mentioned single bitmask column, I seached google for it but I am still confused on what a bitmask column, is. would you mind briefly explaining or pointing me to a site with some information.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 19:06:41

A bitmask column is this:

Rightmost bit has the value of 1 and means CNN when used.
Second rightmost bit has the value of 2 and means GOOGLE when used.
Third rightmost bit has the value of 4 and means MSN when used.

If the value for whole column is 5, that mean both CNN and MSN is used. This approach make you hardcode the columns and you have to document them or at least remember the position used for each and every new Site.

Bitmasked columns are a little harder to use than normalization as described above.

Peter Larsson
Helsingborg, Sweden

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-26 : 19:20:14

you don't have to hardcode the bit values, they could be in a lookup table.

Peso's normalized solution is better than the bitmask idea.

www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources