Solved

sql server 2005

Posted on 2010-09-22
6
381 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 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

680 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