sql server 2005

Hi,

I have got a SQL Query to improve it's performance.

Here is simple format of how it is structured:

Select x,y,z from table a
inner join table b on a.id = b.id
inner jon table c on b.id = c.id
inner join table d on a.id = d.id

left join table w.........
left join  table y.........
left jon table z.........
left join table q.........
left join table p.........

how can this change to produce a faster query...

thanks in advance
shmzAsked:
Who is Participating?
 
Bhavesh ShahLead AnalysistCommented:
1stly u can use "Database Engine Tuning Advisor" for improving performance by creating and using necessary index.
secondly, if u r calling this query very frequenctly then create view on
Select x,y,z from table a
inner join table b on a.id = b.id
inner jon table c on b.id = c.id
inner join table d on a.id = d.id

with proper indexing as Mathew Sir said.

then put left join with other table

CREATE VIEW viewData as
Select x,y,z from table a
inner join table b on a.id = b.id
inner jon table c on b.id = c.id
inner join table d on a.id = d.id


Select x,y,z from viewData
left join table w.........
left join  table y.........
left jon table z.........
left join table q.........
left join table p.........
0
 
Patrick MatthewsCommented:
The very first thing to check: do you have indexes on all of the columns used in joins and in your WHERE clause?If you are using a WHERE clause, what is it?
0
 
shmzAuthor Commented:
Yes there is where clauses which is used for filtering basically
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
shmzAuthor Commented:
for example
where a.x = @clientID
and b.y between @StartDate and @EndDate
0
 
JoeNuvoCommented:
can you provide your real query?
(you may change table name/field list to something else to hide it real name, if u want to)
so we can see if some table can be remove or not

since now, we can't see which field need to be retrieve, which field need to be filter etc.
0
 
amarsaleCommented:
put a join first on the tables with least no. of rows (from less to more no. of rows)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.