[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to optimize this View IN SQL Server 2005

Posted on 2009-12-29
23
Medium Priority
?
266 Views
Last Modified: 2012-05-08
How do I optimize this view

SELECT DISTINCT TOP (100) PERCENT dbo.X.id AS Id, dbo.Y.Name AS Name
FROM         dbo.X WITH (NOLOCK) INNER JOIN
                      dbo.Y WITH (NOLOCK) ON dbo.X.id = dbo.Y.id INNER JOIN
                      dbo.Z WITH (NOLOCK) ON dbo.Z.it_id = dbo.Y.it_id INNER JOIN
            ---- Have around 10 INNER JOINS
      
WHERE     (dbo.X.ID IS NOT NULL) AND (dbo.Y.EMPID <> '' N/A'')
                      ---- have around 100 conditions

How do it optimize the above view? Well i know Distinct Top (100) percents is essentially asking to have all the rows returned, which it would do anyway.

Thanks,
Rithesh
0
Comment
Question by:Star79
  • 9
  • 5
  • 4
  • +3
23 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26141401
do you have indexes on all the columns participated in the join
0
 
LVL 26

Expert Comment

by:tigin44
ID: 26141412
can you provide the full query
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 26141414
first, post all of your code,   2nd, make sure there are indexes on the fields in the joins.  NEXT, see if you can get rid of the <>'N\A' statement.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

Author Comment

by:Star79
ID: 26141461
i see most of the columns participating in the join is a primary key and i see some clustered as well non clustered indexes on other columns as well.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 26141475
hmmm...can you post the visual actual execution plan screenshot up on here?
0
 
LVL 10

Accepted Solution

by:
lof earned 1000 total points
ID: 26141495
indexes are important but other things you may check include:

 - is it possible to narrow data from participating tables before joining them together?
 - can you reorder conditions to have the most selective first, so if you have a field that will narrow the results down to 1% of the dataset it should be one of the first in your WHERE clause. it should probably indexed as well.

more than that we will definitely need full WHERE as it may be very important for optimisation. Also sizes of the tables will be helpful
0
 

Author Comment

by:Star79
ID: 26141497
I have only read only access to it, not sure but can you suggest me how to get the visual actual execution plan....
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 26141507
right click in ssms query window, select include actual execution plan.  run the view select.  there will be a new tab on the screen that shows you the plan.  I need to see a screenshot of it.
0
 

Author Comment

by:Star79
ID: 26141576
hey I see some timeout issues at our end, i will send the snapshot once i resolve it. Working on it, not sure i will be today...heavy probs with server
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 26141584
ok
0
 

Author Comment

by:Star79
ID: 26149172
Hi, Sorry for the delay, some server issues, please find the files for execution plan. couldnt upload the file with extension .sqlplan... had to split this into 5 files.
ExecPlan1.JPG
ExecPlan2.JPG
ExecPlan3.JPG
ExecPlan4.JPG
ExecPlan5.JPG
0
 

Author Comment

by:Star79
ID: 26149258
i think the above screenshots are not complete, i do have a .sqlplan. Can you please suggest me how to send.
0
 
LVL 10

Expert Comment

by:lof
ID: 26149271
put it on rapidshare, or something like that

the screenshots will not help as all visible items have cost of 0% which means the problem is in somewhere down the bottom.

You may try to find items where cost>0, or where the cost is highest and screen shot that, it may help a little.
0
 
LVL 10

Expert Comment

by:lof
ID: 26149283
or even better, when you find a node with high cost, hover with mouse over it so a balloon will pup up, than do a screen shot
0
 
LVL 26

Expert Comment

by:tigin44
ID: 26149291
you can do that by right clicking on the execution plan and choosing save execution plan as or show execution plan xml and saving the new output.
0
 

Author Comment

by:Star79
ID: 26149326
your suggestions are good, Then i will put it on rapidshare and send the link. coz i want to send the entire plan. Thank you.
0
 

Author Comment

by:Star79
ID: 26154017
Not sure i can access rapishare from here but sendin the image for node with high cost. please find images attached and plz lemme know if you need anything else.
Cost8-.JPG
Cost7-.JPG
Cost6-.JPG
Cost6--1.JPG
Cost5-.JPG
Cost4-.JPG
Cost1--2.JPG
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 26154073
and you do have indexes on your itenerary_id and trip_id columns?
0
 

Author Comment

by:Star79
ID: 26154186
I do some non clustered indexes created on iternary_id or trip_id columns, since these columns also share a foriegn key relationship with both tables eitherways and both are primary key for itenary and trip tables.
0
 
LVL 10

Expert Comment

by:lof
ID: 26155822
From the screenshots it looks like the problem is with joins with Trip table.
If as you say you've got the indexes on iternary_id and trip_id columns you should look into reorganizing joins and moving some conditions from WHERE to ON if possible.

Now to be more specific I would need all Joins and full WHERE so I can advise how to reorganise it
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 1000 total points
ID: 26165427
OK,

Would appear by naming conventions that ID is going to uniquely identify something, and because of the name being used (and suggested for trip_id in your post above), if it is primary key, then the first check for NULL might also be redundant. Would be good to clarify

If that is the case, then both the DISTINCT and the TOP 100 PERCENT might be redundant - unless there is a join condition needed for selection, but not included in the selected results (hence giving rise to potential duplication).

I also see encapsulation single quotes for 'N/A' - does that mean it is part of some dynamic SQL ?

Anyway, from what we can see, might be able to start with:

SELECT dbo.X.id AS Id, dbo.Y.Name AS Name
FROM       dbo.X WITH (NOLOCK)
INNER JOIN dbo.Y WITH (NOLOCK) ON dbo.X.id = dbo.Y.id AND (dbo.Y.EMPID <> '' N/A'')
INNER JOIN dbo.Z WITH (NOLOCK) ON dbo.Z.it_id = dbo.Y.it_id


Doing things like <> 'N/A' normally results in table scans du to the exclusive nature and the the query optimisoer might well say that a full table scan will make it quicker, so, where ever possible, try to give the optimiser as much information as possible so that it can use an index. Maybe some of those non clustered indexes might benefit from covering indexes (ie using the INCLUDE qualifier).

But the above is clearly hugely presumptive, and need to know a LOT more about the data relationships, and even then, might also need to know a bit about how the data is used / business models / table relationships / etc...  

So, what about the request of the Experts above ? Is it possible to see the whole query ?

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 26165434
Oh, and is it really a view or a stored procedure ? How are you selecting from it ?
0
 

Author Comment

by:Star79
ID: 26325036
its a view i was able to optimize it with above suggestions
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

829 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