'IF statement' better way of doing this? - 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)

'IF statement' better way of doing this?

Author

Topic

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2009-12-02 : 06:01:57

I have the following stored procedure and I noticed performance issues over it compared to when I just had a select statement. I am using it with .NET and my connection and command timeouts are both set to 90 seconds, the execution times in management studio are always really fast but real life performance is not:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	@page int = 0
AS
BEGIN

	SET NOCOUNT ON;

if @page = '0'

	select top 15 * from articles
	order by article_date desc

else

	select * from articles
	order by article_date desc
END

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-02 : 06:46:01

In your SP declare a variable.
Move @page to that variable.
Then use that variable in your statement instead of using your parameter.

It is worth to try.

For more info look for parameter sniffing.

No, you're never too old to Yak'n'Roll if you're too young to die.

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2009-12-02 : 09:17:05

Could you post an example, I have looked at some examples but I can get them to integrate with my query

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources