December 9, 2009 at 9:39 am
Hello Everyone,
I have recorded the time it takes to populate the temporary table inside a stored procedure and in a dynamic query. It is found that population inside stored procedure takes around 1:20 seconds and that of dynamic query takes only 40 seconds approx.
Eg
Dynamic --- 40 secs
create table #temp
(
order_ID varchar(40) not null,
date datetime ,
invoice int
)
select getdate()
INSERT INTO #temp (order_ID,date,invoice_number)
select distinct top 10 r.order_ID,r.date,r.invoice from dbo.Order r
join
orderline ri
on
r.order_ID = ri.order_ID
where
ISNULL(ri.batch_ID,0) = 0
and
ISNULL(ri.c_ID,0) = 0
and
r.order_status_ID!=6 and r.order_status_ID !=3
order by
r.date_entered desc
select getdate()
Stored Procedure - 1min:20 sec
create procedure XXX
as
create table #temp
(
order_ID varchar(40) not null,
date datetime ,
invoice int
)
select getdate()
INSERT INTO #temp (order_ID,date,invoice_number)
select distinct top 10 r.order_ID,r.date,r.invoice from dbo.Order r
join
orderline ri
on
r.order_ID = ri.order_ID
where
ISNULL(ri.batch_ID,0) = 0
and
ISNULL(ri.c_ID,0) = 0
and
r.order_status_ID!=6 and r.order_status_ID !=3
order by
r.date_entered desc
select getdate()
end
Any suggestion?
December 9, 2009 at 10:42 am
using a function on a column in the WHERE statement slows things down because it's not SARGable...that is a searchable argument that can be take advantage of the indexes on the table.
You can search SSC or even Wiki for more details on SARGability.
this would slow down your query by forcing a table scan of every record:
where
ISNULL(ri.batch_ID,0) = 0 --column wrapped with function = not SARGable
and
ISNULL(ri.c_ID,0) = 0 --column wrapped with function = not SARGable
let's change the SELECT portion to something that would hopefully take better advantage of indexes.
i would think this will return the same results faster:
select distinct top 10
r.order_ID,
r.date,
r.invoice
from dbo.Order r
inner join orderline ri
on r.order_ID = ri.order_ID
where ri.batch_ID is not null
and ri.c_ID IS NOT NULL
and r.order_status_ID NOT IN(3,6)
order by
r.date_entered desc
Lowell
December 9, 2009 at 11:17 am
I'd want to see execution plans to determine that you're reallly looking at two different sets of behavior. Does the connection calling the store procedure have the same ANSI settings as the connection running the ad hoc SQL?
"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 9, 2009 at 11:44 am
Are there any parameters on the real stored procedure? If so, could you show that to 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]
December 9, 2009 at 1:39 pm
Lowell,Grant,Barry,
I will try lowell script tomorrow with same ansi setting for stored procedure ( has some parameters)and dynamic query. I will aslo try adding index to the temporary table and see if that boost the performance.
Ta
December 10, 2009 at 5:56 am
Hello,
I have tried executing stored procedure and dynamic query under the same ansi setting. Execution time is 1min:05 seconds for stored procedure and 30 seconds for dynamic query.
Im querying the view not the tables. Execution plan is large to post here. I have analysed the plan and can say the table scan on order view takes 28% with estimated rows to be 490295 and sorting based on date take about 27% and expected rows is 431214.
Ta
December 10, 2009 at 6:37 am
sqllearner-339367 (12/10/2009)
Hello,I have tried executing stored procedure and dynamic query under the same ansi setting. Execution time is 1min:05 seconds for stored procedure and 30 seconds for dynamic query.
Im querying the view not the tables. Execution plan is large to post here. I have analysed the plan and can say the table scan on order view takes 28% with estimated rows to be 490295 and sorting based on date take about 27% and expected rows is 431214.
Ta
Since we're not there and we can't see anything, it's really hard to help you. How big are the execution plans? If they're very large, and right there is a possible indication of why you're getting problems, zip them up. They're just XML and should compress nicely.
Do the two queries get different execution plans? If so, post both plans. If not... something else is going on. There's contention or something.
Also, are you always running them in the same order, procedure then ad hoc sql? You might be seeing data caching changing the performance of the ad hoc sql.
You're joining views together? That's generally not considered to be a good practice. While the query looks very simple, the execution plans generated can be extremely large and you'll be moving all kinds of data that you don't necessarily need. For example, you're only referencing, what, four columns. How many tables are in the views that are not included in the four columns you're selecting? All that processing still has to occur.
"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 10, 2009 at 7:25 am
Hi Grant,
Reason we are using view is to achieve data synchronization between old and new database.
Order view contains 3 tables order,shopper,user and total of 30 columns.
order table contains 490325, shopper contains 635610 , and user contains 642848. These three tables will be growing atleast 1% every month.
Im checkign the post http://www.simple-talk.com/sql/performance/execution-plan-basics/ which contains 1 chapter from your books.
Ta
December 10, 2009 at 7:52 am
Just so you know, you can save the graphical plan as a .sqlplan file and attach that file to the posts. It makes the whole process for all of us a lot easier.
"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 10, 2009 at 8:11 am
Hi Grant,
I have attached the execution plan now. Im very new to performance analysis and unaware about saving execution plan. Appreciate your smartness in communication
Thanks
December 10, 2009 at 8:40 am
View is defined as below
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Order]
AS
(SELECT Convert(VARCHAR(40),rs.order_id) AS order_id,
ss.userid AS user_id,
Convert(VARCHAR(60),Isnull(rs.email,'xx@yy.com')) AS email_address,
Isnull(rs.gross_total,0) AS gross_order_total,
Isnull(rs.total,0) AS gross_product_total,
Isnull(rs.shipping,0) AS gross_shipping_total,
0 AS is_email,
case
when rs.shipping = 0 or rs.shipping = 399 then 0
when rs.shipping >= 599 then 1
else 0
end AS is_express_delivery,
0 AS is_fraud,
'' AS order_notes,
Isnull(rs.vat_total,0) AS vat_order_total,
0 AS vat_product_total,
0 AS is_held,
rs.friendly_order_id AS invoice_number,
rs.date_entered,
CASE rs.payment_method
WHEN 1
THEN 1
WHEN 2
THEN 3
WHEN 4
THEN 2
ELSE 0
END AS mop_id,
CASE ISNULL(rs.reserve_accept,0)
WHEN 0 THEN 'false'
ELSE 'true'
END AS is_out_of_stock_accepted,
rs.discount AS vocher_id,
rs.address1 AS delivery_address1,
rs.address2 AS delivery_address2,
rs.address3 AS delivery_address3,
rs.address4 AS delivery_address4,
u.address AS invoice_address1,
u.address2 AS invoice_address2,
u.town AS invoice_address3,
u.postcode AS invoice_address4,
u.country AS invoice_address5 ,
ISNULL(ir.number_of_packages,1) AS number_of_packages,
ir.notes_updates_time,
ir.delivery_instruction,
ir.fraud_reason,
ir.vat_shipping_total,
ir.discount_vat,
ir.discount_gross,
isnull(ir.order_status_ID,2)AS order_status_ID
FROM sscs.dbo.order rs
INNER JOIN sscs.dbo.shopper ss
ON rs.shopper_id = ss.shopper_id
INNER JOIN sscs.dbo.users u
ON ss.userid = u.userid
LEFT OUTER JOIN IReceipt ir
ON rs.order_ID = ir.Order_ID
)
As Lowell pointed usage of SArding ISnull() will have performance hit while view is queried. We have no choice but to use those SARding...
Idea is to have mutual field between new and old table renamed in the view and use supporting table with left outer join to create whole new set of fields in view which shadows the new table structure. ie creating a version of new table
Ta
December 10, 2009 at 8:53 am
sqllearner-339367 (12/10/2009)
Hi Grant,Please find the xml version of execution plan for stored procedure followed by dynamic query below
Stored Procedure
...
FYI: these are for completely different SQL queries'
The first (stored procedure) is for the SQL code:
select * from #Processing
And the second is for the SQL code:
INSERT INTO #temp (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
Naturally, I woulod expect that these would perform differently.
[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]
December 10, 2009 at 8:57 am
And the two sqlplans in the attached ZIP appear to be for yet two more SQL queries, both completely different from each other and from the two queries above.
[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]
December 10, 2009 at 9:02 am
Barry,
I have just named the temp tables differently. I have now include the execution plan for both adhoc and stored procedure, please take a look when you next visit this post.
After looking at you reply I have tested the queries again. It is still behaving same.
Ta
December 10, 2009 at 9:16 am
sqllearner-339367 (12/10/2009)
Barry,I have just named the temp tables differently. I have now include the execution plan for both adhoc and stored procedure, please take a look when you next visit this post.
After looking at you reply I have tested the queries again. It is still behaving same.
Ta
The problem remains. And this has nothing to do with changed table names.
The AdHoc query has this SQL statement:
set ansi_nulls on
and the other has this (abbreviated):
CREATE PROCEDURE [dbo].[GetOrders](@PageIndex int, @PageSize int, @CountOnly bit ,@TotalRows int output,@Error varchar(500) output) AS BEGIN IF OBJECT_ID ('tempdb..#OrderSku') IS NOT NULL
...
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
...
[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 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply