April 25, 2008 at 4:24 am
Dear Gurus,
Please suggest me to Speed up this Sp. I checked it is taking around 15 mili sec. Still my boss need to fine tune it.
I have to use the function as of now I have no other alternative to use. Checked the index it is ok.
Please see the attachment. I know If I remove nolock hint it will take more time.. Wht to do..
awaiting your reply.
Thanks in advance !!
Minaz
"More Green More Oxygen !! Plant a tree today"
April 25, 2008 at 6:10 am
I have only taken a short look at it. I see in the WHERE clause you often use:
value IN (SELECT xxx FROM yyy WHERE zzz) OR value NOT IN (SELECT xxx FROM yyy)
Maybe you can rewrite this to a LEFT JOIN??
April 25, 2008 at 6:34 am
Agreed on using JOIN statements instead of IN statements.
Do you really need the DISTINCT? That's usually added when the data is bad, the structure is bad, or the where clauses are written inapropriately. It adds a lot of processing time to any query you run.
You're getting a lot of scans against PK_execution_window. But that's not surprising when you have something like this:
...
item.sales_item_id IN
( SELECT sales.execution_window.sales_item_id FROM sales.execution_window, (SELECT departure FROM inventory.sailing WITH(NOLOCK) WHERE sailing_code = @p_sailing_code) journey
WHERE sales.execution_window.day_range_id IN
( SELECT sales.day_range.day_range_id FROM sales.day_range WITH(NOLOCK)
WHERE DATEPART(WEEKDAY,journey.departure) BETWEEN from_dow AND to_dow
)
You do realize that you've got an undefined join between sales.execution_window and inventory.sailing? That's automatically a scan. Add to that it's doing an other IN clause, inside of an IN clause... You really need to break all these down into appropriate joins.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 25, 2008 at 7:01 am
I started looking at your query, and honestly, I gave up. You have a lot of subqueries that could be eliminated using joins in your FROM clause that would then allow you to simplify your WHERE clause.
Start by looking at all of your subqueries using the IN statement. These point to tables that you should be able to join to in the FROM clause. Then start looking at the criteria for selecting records. Something that will help you there is a truth table, build one.
Without your DDL for the tables, some sample data, expected results based on the sample data, and more time than I have today (I might be able to do something over the weekend, but with soccer and yard work that needs to be done, maybe not)
Make a start, if you have problems or get stuck just post on this thread what you have done and where you are having a problem. I'm sure someone will jump in and assist.
One thing, I would look at eliminating the NOLOCK hint unless the possible inaccurate data is acceptable.
April 26, 2008 at 1:35 am
Thank you all very much. I had a sleepless night thinking how should I tune?
As you all are there to help me out Iam quite sure I will try using Join in FROM clause.
Try to elliminate the Distict clause definately a bad design. Also I will consider about NO LOCK hint.
once again Iam thanking you all for spending time for the query and helping me out.
🙂
"More Green More Oxygen !! Plant a tree today"
April 27, 2008 at 11:48 pm
Also, suggest you post the Query Plan XML instead, which has better info for us.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply