optimize 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 2008 Forums

Transact-SQL (2008)

optimize query

Author

Topic

Mahavirjadhav
Starting Member

18 Posts

Posted - 2012-04-11 : 01:43:24

Dear all,

I am having following set of queries in S.P


    DECLARE @CNT INT
    SELECT @CNT = COUNT(USER_ID) FROM RECO_PRCNT_INFO WITH (NOLOCK)  WHERE USER_ID = @Userid AND RECO_SRVC= @RcmdSrvc AND (@Date1 between FROM_DT AND TO_DT )
    IF @CNT = 0
        BEGIN            
            SELECT  RECO_PRCNT FROM RECO_PRCNT_INFO WITH (NOLOCK)  WHERE USER_ID= 0 AND RECO_SRVC=@RcmdSrvc   AND (@Date1 between FROM_DT AND TO_DT )
        END
    ELSE
        BEGIN
            SELECT  RECO_PRCNT  FROM RECO_PRCNT_INFO  WITH (NOLOCK) WHERE USER_ID= @Userid AND RECO_SRVC=@RcmdSrvc  AND (@Date1 between FROM_DT AND TO_DT )
        END

How can I optimize obove quries in a single query.

Thanks and Regards,

Mahavir

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-04-11 : 02:17:22

Hi,

What is the problem when you use like this

SELECT RECO_PRCNT FROM RECO_PRCNT_INFO WITH (NOLOCK) WHERE USER_ID= @Userid AND RECO_SRVC=@RcmdSrvc AND (@Date1 between FROM_DT AND TO_DT )

Why you mention 0 (zero) for USER_ID if the @CNT is 0(Zero).

Can you explain more about your fubnctionality.

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Mahavirjadhav
Starting Member

18 Posts

Posted - 2012-04-11 : 02:45:42

Hi,

SELECT @CNT = COUNT(USER_ID) FROM RECO_PRCNT_INFO WITH (NOLOCK) WHERE USER_ID = @Userid AND RECO_SRVC= @RcmdSrvc AND (@Date1 between FROM_DT AND TO_DT )

In this query I am checking existance of given user_id

if count> 0 then get the value of RECO_PRCNT from table for given user_id

else
get the value for global user(user_id=0)

Mahavir

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-11 : 03:28:11

[code]SELECT RECO_PRCNT
FROM RECO_PRCNT_INFO WITH (NOLOCK)
WHERE USER_ID = @Userid
AND RECO_SRVC = @RcmdSrvc
AND @Date1 BETWEEN FROM_DT AND TO_DT

IF @@ROWCOUNT = 0
SELECT RECO_PRCNT
FROM RECO_PRCNT_INFO WITH (NOLOCK)
WHERE USER_ID = 0
AND RECO_SRVC = @RcmdSrvc
AND @Date1 BETWEEN FROM_DT AND TO_DT[/code]

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