December 11, 2009 at 3:08 am
Lowell,
I have replaced the script to
INSERT INTO #Processing (order_ID,date_entered,invoice_number)
select distinct top 10 r.order_ID,r.date_entered,r.invoice_number from receipt r
join receipt_item ri on
r.order_ID = ri.order_ID
where ri.batch_ID is not null and ri.consignment_ID is not null and r.is_express_delivery =0 and
r.order_status_ID!=6 and r.order_status_ID !=3
order by date_entered desc
execution resulted in no rows.
If the condition is specified like
where isnull(ri.batch_ID,0) =0 and isnull(ri.consignment_ID,0) = 0 it bring up 10 rows as requested.
This is due to how view has been structured. In previous reply I have included the script for the view.
we might need to consider improving that.
December 11, 2009 at 3:26 am
Hi Grant,
Neither Ctrl + M or clicking Include Actual Execution plan is not bringing the execution plan.
Not suer why this is happening.
Ta
December 11, 2009 at 4:22 am
Lowell (12/10/2009)
i'm thinking that an index on date_entered DESC at a minimum is needed, and i would try an index on date_entered ,friendly_order_id,order_id,shopper_id,shipping first.
I have now created a non-cluster index on friendly_order_id,shopper_id which realy seems to work out finally.
Procedure executed in exact time of adhoc query. There is also a significant improvement on another procedure which does all stock checking,courier assginment etc. It is fairly large procedure which was exectuing at 8 mins. Now after creating clustered and non clustered indexes it executed in 1 min:18 seconds. I will now try to introduce non-cluster index on date entered and see if that boost the performance better.
My sincere thanks to Lowell,Grant,Barry.... absolutely overwhelmed.. i would probably buy the book written by barry and learn more..
Ta
December 11, 2009 at 4:30 am
Lowell,Grant,Barry,
I now have clustered index on order_Id and non-clustered index on date_entered,shipping,friendly_order_id,shopper_ID. With introduction of date_entered I have gained 5 mins in exectuion. It is execting much faster....
ta
December 11, 2009 at 5:34 am
sqllearner-339367 (12/11/2009)
Hi Grant,Neither Ctrl + M or clicking Include Actual Execution plan is not bringing the execution plan.
Not suer why this is happening.
Ta
You have to then execute the query. It will then display an execution plan. It's not like the estimated plan where it pops up immediately.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 11, 2009 at 5:56 am
Grant,
Actual estimated plan attached. (Wake up call to update my skills)
Ta
December 11, 2009 at 6:29 am
sqllearner-339367 (12/11/2009)
Grant,Actual estimated plan attached. (Wake up call to update my skills)
Ta
Actual "estimated" plan.... Is that like honest politician? Kidding.
Plans are either "estimated" meaning they're the plan generated by the query optimizer either as part of an execution, stored in the plan cache, or by request, or they're "actual", a result of capturing the plan used by the query engine during the execution of a plan.
More often than not, when tuning queries, the preferred plan is the actual. It has more information that will help you do more with your tuning.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 11, 2009 at 6:37 am
sqllearner-339367 (12/11/2009)
Grant,Actual estimated plan attached. (Wake up call to update my skills)
Ta
Looking at the plan, you're still getting scans. Is this taken after you added the index that improved performance (and eliminated the difference between the ad hoc & procedure)?
I'm seeing some disparity between estimated and actual rows. Not a lot, but it does suggest you might need to update your statistics, maybe with a full scan.
You might want to consider adding an index to the UserID column on your users table. I'm assuming that's not the key on the PK. That table scan is killing this query, especially since it's part of a loop join and it's executing twice.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 11, 2009 at 7:30 am
Grant,
Yes, the plan is taken after introducing the new indexes on order table. Im waiting for the site maintenance schedule to update the indexes and statistics. Hopefully after that I will see few improvements. i have learnt so much from this posting. Thank you all.
Ta
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply