Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql server 2005

Posted on 2010-09-22
6
Medium Priority
?
413 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
6 Comments
 
LVL 93

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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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 1000 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 1000 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
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…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Loops Section Overview

916 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