Search 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 2005 Forums

Transact-SQL (2005)

Search query

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-07-23 : 07:05:49

Hi,
I write search query in which one parentid i passes
create proc test
@parentid int = 0
as
begin
select col1 from tablename where
( parentid = @parentid or @parentid = 0)
end

-- ABOVE query takes toooooo much time to fetch records from DB table.
AND IF I WRITE QUERY
select col1 from tablename where
( parentid = @parentid)
JUST REMOVE OR CONDITION THEN IT gives results in a second..
There is a default Parameter i need to pass.. If i dont pass it then all RECORDS should come..

What cause of this problem please help ??
What is wrong with that or condition for dynamic search with multiple SP parameters..

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-07-23 : 08:47:22

create proc test
@parentid int
as
begin
Select col1 from tablename where
parent_id=@parentid or @parentid is null
end

if you Pass parent_id all the child will come, if you pass NULL all records will come.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2010-07-23 : 09:12:06

Try this


create proc test
@parentid int = 0
as
begin
select col1 from tablename where 
(parentid = @parentid and@parentid = 0)
end

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-07-24 : 02:16:01

Hi senthil.. i tried that way.. but still performance issue occurs
because of that or condition for id.

There are 37 indexes on that table with lacs of records..
so i think may be cause is because of that OR and index.
as i heard.. OR discards index..
As it works fast in simple condition without or using dynamic query that is parentid = @parentid

IT CREATES PROBLEM ONLY WHEN i add condition.. parentid = @parentid OR @parentid = 0

Please correct if i am wrong..
thanks,

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-07-24 : 07:01:11

Try this:


CREATE PROC test
   @parentid INT = 0
AS
BEGIN
   IF @parentid = 0
      SELECT col1
        FROM tablename
   ELSE
      SELECT col1
        FROM tablename
       WHERE parentid = @parentid
END

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-07-24 : 15:24:02

Hi malpashaa i already used this way .. also did with dynamic query too..

My main concern is .. why it runs too slow just due to that or condition.. is there any problem with indexs with or condition or not...

thanks to you

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-07-24 : 23:11:15

create proc test
@parentid int = 0
as
begin
select col1 from tablename where
( parentid = @parentid or @parentid = 0)
end

is there any problem with indexs with or condition or not...

No, the problem is about parameter, "or @parentid = 0".

For the engine, parameter is unpredictable, like column without index, so it prefers to scan entire table rather than using index (if any on parentid). That is why it is slow, does'nt matter @parentid = 0 or not .

If you force the engine to use index on parentid as query below, in case @parentid !=0, it may be better than your query, but in case @parentid =0, it is much worse than your query.

select col1 from tablename with(index(indexParentidName) where
( parentid = @parentid or @parentid = 0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources