Performance issue with temporary table
-
Also, *please* stop going back and editing you previous posts and attachments, it's just making a confusing situation worse.
Instead, when you have new or corrected information or files, then make a new post with the new attachments so that we can keep things straight.
[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] -
Hi Barry,
For clarity I have posted the exact code of stored procedure and adhoc query below
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[GetOrders](@PageIndex int, @PageSize int, @CountOnly bit ,@TotalRows int output,@Error varchar(500) output)
AS
BEGIN
IF OBJECT_ID ('tempdb..#Processing') IS NOT NULL
BEGIN
DROP TABLE #Processing
END
)
CREATE TABLE #Processing
(
order_ID varchar(40) not null,
date_entered datetime ,
invoice_number int
)
print 'Initializing #processing population'
select getdate()
INSERT INTO #Processing (order_ID,date_entered,invoice_number)
select distinct top (@PageSize) r.order_ID,r.date_entered,r.invoice_number from receipt r
join receipt_item ri on
r.order_ID = ri.order_ID
where ISNULL(ri.batch_ID,0) = 0 and ISNULL(ri.consignment_ID,0) = 0 and r.is_express_delivery =0 and
r.order_status_ID!=6 and r.order_status_ID !=3
order by date_entered desc
select getdate()
print 'Population #Processing completed'
end
Adhoc query
set ansi_nulls on
go
SET QUOTED_IDENTIFIER ON
go
create table #Processing
(
order_ID varchar(40) not null,
date_entered datetime ,
invoice_number int
)
select getdate()
INSERT INTO #Processing (order_ID,date_entered,invoice_number)
select distinct top 10 r.order_ID,r.date_entered,r.invoice_number from dbo.receipt r
join receipt_item ri on
r.order_ID = ri.order_ID
where ISNULL(ri.batch_ID,0) = 0 and ISNULL(ri.consignment_ID,0) = 0 and r.is_express_delivery=0 and
r.order_status_ID!=6 and r.order_status_ID !=3
order by r.date_entered desc
select getdate()
select * from #Processing
From the above code we can come to know that queries are same in stored procedure and adhoc.
Ta
-
Going blind or something. I can't find the attachments. Where are they?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
altering a procedure is NOT the same as EXECUTE ing the procedure.
run something like this statement and capture the query plan :
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
EXECUTE GetOrders @params.....
then run the second statement you pasted, also capturing the query plan (you were already doing that correctly)
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell,Grant,Barry,
As lowell suggested I executed stored procedure as following and attaching new execution plan.
set ansi_nulls on
go
SET QUOTED_IDENTIFIER ON
go
exec getorders 1,10,0,0,''
go
-
sqllearner-339367 (12/10/2009)
Hi Barry,For clarity I have posted the exact code of stored procedure and adhoc query below
...
From the above code we can come to know that queries are same in stored procedure and adhoc.
My point is that the SQLPLANS that you posted are not for the same queries. you are catching the wrong parts of the queries in the plans.
[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] -
sqllearner-339367 (12/10/2009)
Lowell,Grant,Barry,As lowell suggested I executed stored procedure as following and attaching new execution plan.
set ansi_nulls on
go
SET QUOTED_IDENTIFIER ON
go
exec getorders 1,10,0,0,''
go
Much better.
[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] -
ok I'm no expert, but I'm not a country bumpkin either.
it looks to me like this is the costliest item: a table SCAN of almost 500,000 rows, which then requires a SORT:
http://www.stormrage.com/SQLStuff/sql_learner_table_scan.gif%5B/img%5D
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 would also stick to my guns and avoid the isnulls that i identified earlier; i'd like to see what difference that makes.
am i interpreting this poorly?
where ISNULL(ri.batch_ID,0) = 0 and ISNULL(ri.consignment_ID,0) = 0
this is clearly just setting nulls to zeros....i'l give you a dollar for every batch_id that REALLY equals zero, as that's a safe bet...it's an idneitity PK, there's no zeros, so no need to try and make them null to zero.
same for consignment_id.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Right. The estimated execution plans are identical. That means the same query should be run for both processes. However, to be really sure, you'd need to see actual execution plans, not estimates. Just to help out, can you capture the actual (to include the actual plan, hit CTL-M in the query window or select the icon, then run the query).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Lowell (12/10/2009)
ok I'm no expert, but I'm not a country bumpkin either.it looks to me like this is the costliest item: a table SCAN of almost 500,000 rows, which then requires a SORT:
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 would also stick to my guns and avoid the isnulls that i identified earlier; i'd like to see what difference that makes.
am i interpreting this poorly?
No, you're right on. I'd say this is the primary problem for performance as well. I'm still trying to figure out why he's getting different performance between the two processes. Odd when they both have the same plan. Makes me think something else is being done within the proc... or, possibly, the proc is staying as a parallel execution and the ad hoc sQL is not (that determination is made by the query engine, not the optimizer).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Grant, would this be a correct assumption?
if the index for date_entered was in place, would we expect an INDEX SEEK and an estimated 10 rows(because of the TOP 10) instead of the huge table scan?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Are they both running on the same server/instance and the same disks?
[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] -
Lowell (12/10/2009)
Grant, would this be a correct assumption?if the index for date_entered was in place, would we expect an INDEX SEEK and an estimated 10 rows(because of the TOP 10) instead of the huge table scan?
Probably a seek, but not necessarily 10 rows. The top requires the rows to be ordered. The index might provide that order or it might require a partial scan (still satisfied as a seek) which means you might see more rows processed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
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.
Currently index exists only on order_ID field. No other index exists on this table. If I introduce new index on date_entered,frindly_order_id,shopper_ID it might increase performance. However, I have learnt (only theoritical) having too many index created on table will also cause performance hit.
It is worth giving a try anyway.
[Quote]
i would also stick to my guns and avoid the isnulls that i identified earlier; i'd like to see what difference that makes.
[/quote]
I will try replacing this as you suggested earlier tomorrow on view scripts and will get back to you.
-
Yes , Im running on the same server/instance and same disk. We dont have raid disks even in Live server 🙁 .
Ta
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply