suggestion for optimizing the cost of this query

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)

suggestion for optimizing the cost of this query

Author

Topic

winman
Starting Member

26 Posts

Posted - 2013-04-15 : 03:33:50

hi I'm writting the below given query to retrive info about task for abc from the table reminder in reminder database....is there any way to reduce the cost of this query

SELECT [Reminder].* FROM [Reminder].dbo.[Reminder]
WHERE ( Charindex('abc',CAST([Reminder].dbo.[Reminder].[AllotedTo] AS VARCHAR(MAX)))>0
AND CAST([Reminder].dbo.[Reminder].[Date] as smalldatetime) <= '23-Aug-2012'
AND (Charindex('~!',CAST([Reminder].dbo.[Reminder].[F6Row] AS VARCHAR(MAX)))=0 or [Reminder].dbo.[Reminder].[F6Row] Is NULL ) )
OR
( Charindex('abc',CAST([Reminder].dbo.[Reminder].[DiscussWith] AS VARCHAR(MAX)))>0
AND (CAST([Reminder].dbo.[Reminder].[FollowUpDate] as smalldatetime) Is Null OR CAST([Reminder].dbo.[Reminder].[FollowUpDate] as smalldatetime) = '' )
AND CAST([Reminder].dbo.[Reminder].[Date] as smalldatetime) <= '23-Aug-2012'
AND (Charindex('~!',CAST([Reminder].dbo.[Reminder].[F6Row] AS VARCHAR(MAX)))=0 or [Reminder].dbo.[Reminder].[F6Row] Is NULL ) )
OR
( Charindex('abc',CAST([Reminder].dbo.[Reminder].[DiscussWith] AS VARCHAR(MAX)))>0
AND CAST([Reminder].dbo.[Reminder].[FollowUpDate] as smalldatetime) <= '23-Aug-2012'
AND (Charindex('~!',CAST([Reminder].dbo.[Reminder].[F6Row] AS VARCHAR(MAX)))=0 or [Reminder].dbo.[Reminder].[F6Row] Is NULL ) )

ORDER BY cast([Reminder].[Date] as smalldatetime) Desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-15 : 06:36:22

there are lots of redundant conditions.
try like below


SELECT [Reminder].* FROM [Reminder].dbo.[Reminder] 
WHERE ( Charindex('abc',CAST([Reminder].dbo.[Reminder].[AllotedTo] AS VARCHAR(MAX)))>0 
OR Charindex('abc',CAST([Reminder].dbo.[Reminder].[DiscussWith] AS VARCHAR(MAX)))>0 )
AND ((CAST([Reminder].dbo.[Reminder].[Date] as smalldatetime) <= '20120823'  AND (CAST([Reminder].dbo.[Reminder].[FollowUpDate] as smalldatetime) Is Null OR CAST([Reminder].dbo.[Reminder].[FollowUpDate] as smalldatetime) = '' )) 
OR CAST([Reminder].dbo.[Reminder].[FollowUpDate] as smalldatetime) <= '20120823' )
AND (Charindex('~!',CAST([Reminder].dbo.[Reminder].[F6Row] AS VARCHAR(MAX)))=0 or [Reminder].dbo.[Reminder].[F6Row] Is NULL ) )

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources