Solved

sql server 2005

Posted on 2010-09-22
6
392 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:shmz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33740354
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
 

Author Comment

by:shmz
ID: 33740404
Yes there is where clauses which is used for filtering basically
0
 

Author Comment

by:shmz
ID: 33740417
for example
where a.x = @clientID
and b.y between @StartDate and @EndDate
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 11

Expert Comment

by:JoeNuvo
ID: 33740475
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
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 250 total points
ID: 33741664
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
 
LVL 1

Assisted Solution

by:amarsale
amarsale earned 250 total points
ID: 33745537
put a join first on the tables with least no. of rows (from less to more no. of rows)
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question