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
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
Aneesh
do you have indexes on all the columns participated in the join
can you provide the full query
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.
ASKER
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.
hmmm...can you post the visual actual execution plan screenshot up on here?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have only read only access to it, not sure but can you suggest me how to get the visual actual execution plan....
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.
ASKER
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
ok
ASKER
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
ExecPlan1.JPG
ExecPlan2.JPG
ExecPlan3.JPG
ExecPlan4.JPG
ExecPlan5.JPG
ASKER
i think the above screenshots are not complete, i do have a .sqlplan. Can you please suggest me how to send.
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.
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.
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
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.
ASKER
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.
ASKER
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
Cost8-.JPG
Cost7-.JPG
Cost6-.JPG
Cost6--1.JPG
Cost5-.JPG
Cost4-.JPG
Cost1--2.JPG
and you do have indexes on your itenerary_id and trip_id columns?
ASKER
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.
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oh, and is it really a view or a stored procedure ? How are you selecting from it ?
ASKER
its a view i was able to optimize it with above suggestions