How to optimize this View IN SQL Server 2005

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
Star79Asked:
Who is Participating?
 
lofCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
do you have indexes on all the columns participated in the join
0
 
tigin44Commented:
can you provide the full query
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
chapmandewCommented:
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
 
Star79Author Commented:
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
 
chapmandewCommented:
hmmm...can you post the visual actual execution plan screenshot up on here?
0
 
Star79Author Commented:
I have only read only access to it, not sure but can you suggest me how to get the visual actual execution plan....
0
 
chapmandewCommented:
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
 
Star79Author Commented:
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
 
chapmandewCommented:
ok
0
 
Star79Author Commented:
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
 
Star79Author Commented:
i think the above screenshots are not complete, i do have a .sqlplan. Can you please suggest me how to send.
0
 
lofCommented:
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
 
lofCommented:
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
 
tigin44Commented:
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
 
Star79Author Commented:
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
 
Star79Author Commented:
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
 
chapmandewCommented:
and you do have indexes on your itenerary_id and trip_id columns?
0
 
Star79Author Commented:
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
 
lofCommented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
Mark WillsTopic AdvisorCommented:
Oh, and is it really a view or a stored procedure ? How are you selecting from it ?
0
 
Star79Author Commented:
its a view i was able to optimize it with above suggestions
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.