Performance tunning - OR - 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)

Performance tunning - OR

Author

Topic

chih
Posting Yak Master

154 Posts

Posted - 2009-03-15 : 20:38:29

Hi all,

We have performance issue when we want to get result from many OR conditions. here is the simple codes

create table project (id int,staff int)
create index ix_project on project (id)

insert project values (1,1)
insert project values (2,1)
insert project values (3,1)
insert project values (1,2)
insert project values (2,2)
insert project values (3,2)
insert project values (1,3)
insert project values (2,3)

Result:
id
1
2

What we want to get is unique project id that staff 1,2 and 3 work on. unfortunatly I cannot redesgin the table and it currently have
36,882,768 rows.

The below query is what we currently use, but it does not peformance well if we have more OR conditions.
select id from project where staff=1 or staff=2 or staff=3
group by id having count(*)=3

we have been thinking to use intersect but it is even worse.
select id from project where staff=1
intersect
select id from project where staff=2
intersect
select id from project where staff=3

anyone has ideas how to tune this?
The original code has more conditions, e.g.
where (staff=1 and XXXXXX and XXXXX and XXXXX)
or (staff=2 and XXXXXX and XXXXX and XXXXX)
or (staff=3 and XXXXXX and XXXXX and XXXXX)

Thank you in advance.

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-15 : 20:59:54

Try one of these two. If you don't already have a clustered index, I would suggest staff. If you do then create your index on staff with an include for the ID to save it having to go back to the table.

Create clustered index ix_project on project(staff)
OR(NOT BOTH)
Create index ix_project on project(staff) include(id)

Also instead of writing multiple or clauses you can use this:
select ID from project where staff in(1,2,3)
group by id having count(*)=3

Let me know how you make out with one of those index options.

Mike
"oh, that monkey is going to pay"

chih
Posting Yak Master

154 Posts

Posted - 2009-03-15 : 21:36:43

Thank you for the reply. I have added the index and peformance did improve from 25sec to 24sec.

It is just a simple code. The original code actually have more conditions
where (staff=1 and XXXXXX and XXXXX and XXXXX)
or (staff=2 and XXXXXX and XXXXX and XXXXX)
or (staff=3 and XXXXXX and XXXXX and XXXXX)

so I cannot use in clause.
Is there any ways to rewrite the query?

quote:
Originally posted by mfemenel

Try one of these two. If you don't already have a clustered index, I would suggest staff. If you do then create your index on staff with an include for the ID to save it having to go back to the table.

Create clustered index ix_project on project(staff)
OR(NOT BOTH)
Create index ix_project on project(staff) include(id)

Also instead of writing multiple or clauses you can use this:
select ID from project where staff in(1,2,3)
group by id having count(*)=3

Let me know how you make out with one of those index options.

Mike
"oh, that monkey is going to pay"

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-03-15 : 23:56:03

If you want to collect the Unique Project id

Try this

select Distinct ID from project where staff in(1,2,3)
group by id having count(*)=3

Regards

Senthil.C
Willing to update...

chih
Posting Yak Master

154 Posts

Posted - 2009-03-16 : 00:02:39

Thank you for the reply

If we use group by, I do not think we need to have distinct statment.

quote:
Originally posted by senthil_nagore

If you want to collect the Unique Project id

Try this

select Distinct ID from project where staff in(1,2,3)
group by id having count(*)=3

Regards

Senthil.C
Willing to update...

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-03-16 : 00:06:32

Ya...

Distinct is Better then Group by(in your case), because u did't use ant aggregate function.

Regards

Senthil.C
Willing to update...

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-16 : 05:35:42

In order to help you further you need to be specific about your where clause and what data is returned. Your indexing all depends on it.

Mike
"oh, that monkey is going to pay"

chih
Posting Yak Master

154 Posts

Posted - 2009-03-16 : 18:16:30

Thank you. Adding an index can definitly improve the performance, but the main reason I opened this topic is to find an alternative (better query) to get the same result.

quote:
Originally posted by mfemenel

In order to help you further you need to be specific about your where clause and what data is returned. Your indexing all depends on it.

Mike
"oh, that monkey is going to pay"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources