September 13, 2012 at 5:10 am
Hi Everyone,
Wondered if anyone can make any suggestions as to the areas i need to look into next to solve a slow report issue. Firstly can i just say that i am an "accidental DBA" so my SQL knowledge has been building very slowly over the past few years, so please excuse any ignorance on my part.
We have a piece of stock control software that has the option to add reports into its functionality by referencing stored procedures that i add to our database. There is one report that is taking a lot longer to return the results than others, even though it isn't much more complicated. There are two points about this that make this problem unusual, the large time lag doesn't happen with other reports run from the software, and if i run the stored procedure from Management Studio (either directly on the server or from my laptop) the query results appear in under a second, rather than a minute and a half as it does going through the software.
Im assuming a few things here, firstly that as the procedure runs quickly in its own right, the problem cannot be in the sql, although i'm sure my "noddy" sql could easily be improved! Secondly, as the other reports were created in exactly the same way and do not suffer from this lag, there isn't a problem with the communication between server and client or the software itself that fires the report off.
The only idea i have about the problem is that is something to do with the way SQL Server 2008 r2 runs, as we recently migrated to it from SQL2000 on SBS2000. Whilst writing this i have just resurrected the old server and checked the running speed of the report, and it is as fast as it should be.
The query uses user selected dates and a varchar name to define the result set, and fires off a couple of UDFs when running, but as i stated earlier, the results are almost instant on the server itself. The same permissions and properties are set on all queries, i have added the query below in case it helps. Help!
[dbo].[MISQRY0000026]
@sSDate varchar(17),
@sEDate varchar(17),
@Manufacturer varchar(17)
AS
select mfr.mfrname,md.title,cil.modelid,mc.mcdesc,sum (qty) Sold,
isnull(dbo.DisplayStock (cil.modelid),0) 'Display To Sell' ,
dbo.BoxedStock ( cil.modelid) 'Boxed To Sell',
soo.onorder-soo.allocated OnOrder,md.is_discontinued Discontinued
from customerinvoiceline cil (nolock)
left join manufacturer mfr (nolock) on mfr.mfrid=cil.mfrid
left join model md (nolock) on cil.modelid=md.mdid
left join customerinvoice ci (nolock) on cil.custinvid=ci.ciid
left join modelcategory mc (nolock) on md.mcatid=mc.mcid
left join stockonorder soo (nolock) on cil.modelid=soo.modelid
where cil.standard=1 and mfr.mfrcode = @Manufacturer and cil.qtycancelled=0 and soo.gradeid=1 and
cil.creationdate between @sSDate and @sEDate
group by mfr.mfrname,md.title,mc.mcdesc,cil.modelid,soo.onorder,soo.allocated,md.is_discontinued
order by mfr.mfrname,md.title,mc.mcdesc,cil.modelid,soo.onorder,soo.allocated,md.is_discontinued
Functions.
[dbo].[BoxedStock] ( @lModelId int)
RETURNS int
AS
BEGIN
DECLARE @lItemCountint
SELECT @lItemCount = sum(qtyavforsale) FROM StockLocationCohort (NOLOCK)
WHERE modelid = @lModelId AND gradeid=1 and locid in (2,5,8,10,14,17,24,36)
RETURN @lItemCount
END
ALTER FUNCTION [dbo].[DisplayStock] ( @lModelId int)
RETURNS int
AS
BEGIN
DECLARE @lItemCountint
SELECT @lItemCount = sum(qtyavforsale) FROM StockLocationCohort (NOLOCK)
WHERE modelid = @lModelId AND gradeid=1 and locid in (1,4,7,13,16,23,35)
RETURN @lItemCount
END
September 13, 2012 at 5:15 am
When you migrated, did you rebuild all indexes and ensured all statistics where up to date?
Also could you please read through the 4th link in my signature on posting performance problems?
September 13, 2012 at 5:29 am
Hi,
Sorry, have read through the link now. I think i was getting sidetracked by my thoughts that the query itself is not the problem, so didnt cover it enough.
Have attached the execution plan file
Number of rows, under 10,000
Indexes rebuilt when moved over to new server, the report was originally written after the software develpoer created the database and indexes etc, none have been added since.
Statistics, dont know much about this sorry.
Hope this helps.
Martin.
September 13, 2012 at 5:50 am
Most of the cost is with a scan on CustomerInvoiceLine, which when looking at the plan is bringing back 13898 rows, but the inner join to Manufacturer is filtering that down to 78 rows, so would be worth trying to turn that into a seek, looking at the predicated the below index should suffice.
CREATE INDEX ..... ON CustomerInvoiceLine (Standard, QtyCancelled, CreationDate) INLCUDE (MRFid, ModelID, Qty)
The plan is then saying that it thinks there is a missing index on StockOnOrder, which would coincide with the scan on that table, the index it details could help
CREATE INDEX ..... ON StockOnOrder (GradeId) INCLUDE (ModelId, OnOrder, Allocated)
With that then out the way you might want to have a look how big the model table is and put a clustered index on it to get away from the RID lookup
Also create a copy with the nolock hints removed and retest as with nolock your leaving yourself wide open for dirty reads. Nolock is not a go faster switch
Article by Gail - Search for the Looking for Go-faster Hints heading[/url]
September 13, 2012 at 7:07 am
Thanks for your time, will get those on and have a play around with it.
Cheers,
Martin.
September 13, 2012 at 7:21 am
m.henly (9/13/2012)
Thanks for your time, will get those on and have a play around with it.Cheers,
Martin.
Martin, you might also benefit from reading this article [/url]about parameter sniffing.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 13, 2012 at 7:23 am
Thanks Chris will have a look at that this afternoon.
Martin.
September 13, 2012 at 10:41 am
Because as you say you are an "Accidental DBA" and your query is full of nolock hints you might want to read up on the hint some more.
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/%5B/url%5D
If you can explain why they are needed in this query (and performance is not a reason) then they are probably ok. Otherwise, read up and understand what is happening when you use that hint.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 13, 2012 at 11:04 am
If you have a test or development server where you can do some playing with the code, give the following a try:
drop function [dbo].[BoxedStock];
go
create function [dbo].[BoxedStock] ( @lModelId int)
RETURNS table
AS
return(
SELECT
sum(qtyavforsale) ItemCount
FROM
StockLocationCohort (NOLOCK)
WHERE
modelid = @lModelId AND
gradeid=1 and
locid in (2,5,8,10,14,17,24,36)
);
go
drop function [dbo].[DisplayStock];
go
CREATE FUNCTION [dbo].[DisplayStock] ( @lModelId int)
RETURNS table
AS
return
(
SELECT
sum(qtyavforsale) ItemCount
FROM
StockLocationCohort (NOLOCK)
WHERE
modelid = @lModelId AND
gradeid = 1 and
locid in (1,4,7,13,16,23,35)
);
go
alter procedure [dbo].[MISQRY0000026]
@sSDate varchar(17),
@sEDate varchar(17),
@Manufacturer varchar(17)
AS
begin
select
mfr.mfrname,
md.title,
cil.modelid,
mc.mcdesc,
sum(qty) Sold,
isnull(ds.ItemCount,0) 'Display To Sell',
bs.ItemCount 'Boxed To Sell',
soo.onorder - soo.allocated OnOrder,
md.is_discontinued Discontinued
from
customerinvoiceline cil (nolock)
--left join manufacturer mfr (nolock)
left join manufacturer mfr (nolock) -- mfr.mfrcode = @Manufacturer makes this an inner join
on mfr.mfrid = cil.mfrid
left join model md (nolock)
on cil.modelid = md.mdid
left join customerinvoice ci (nolock)
on cil.custinvid = ci.ciid
left join modelcategory mc (nolock)
on md.mcatid = mc.mcid
--left join stockonorder soo (nolock)
inner join stockonorder soo (nolock) -- soo.gradeid = 1 in where clause makes this an inner join
on cil.modelid = soo.modelid
outer apply [dbo].[BoxedStock](cil.modelid) bs(ItemCount)
outer apply [dbo].[DisplayStock](cil.modelid) ds(ItemCount)
where
cil.standard = 1 and
mfr.mfrcode = @Manufacturer and
cil.qtycancelled = 0 and
soo.gradeid = 1 and
cil.creationdate between @sSDate and @sEDate
group by
mfr.mfrname,
md.title,
mc.mcdesc,
cil.modelid,
soo.onorder,
soo.allocated,
md.is_discontinued
order by
mfr.mfrname,
md.title,
mc.mcdesc,
cil.modelid,
soo.onorder,
soo.allocated,
md.is_discontinued;
end
go
I'm sure you will have questions after you look at it, so please ask.
September 13, 2012 at 11:12 am
Hi Sean,
When the original developer put it all together (about ten years ago) and started to show me the basics of Sql, he told me to always use the (no lock) so that the query would not hold up any other reads/updates and start building up any locks. As most of the stored procedures i write are for reporting purposes, its a habit i've never shook (like putting all updates etc in a transaction). I do need to learn more about different locking options though, as some of the stuff i am having to work on now needs 100% data accuracy, so thanks for the link.
Martin.
September 13, 2012 at 11:14 am
Hi Lynn,
Thanks for the post, off now till friday afternon (gmt) so will have a look and respond after i have abrorbed.
Cheers,
Martin.
September 13, 2012 at 11:31 am
m.henly (9/13/2012)
Hi Lynn,Thanks for the post, off now till friday afternon (gmt) so will have a look and respond after i have abrorbed.
Cheers,
Martin.
Saw a slight error in my code above. I just changed an ALTER FUNCTION to CREATE FUNCTION.
September 13, 2012 at 12:39 pm
m.henly (9/13/2012)
Hi Sean,When the original developer put it all together (about ten years ago) and started to show me the basics of Sql, he told me to always use the (no lock) so that the query would not hold up any other reads/updates and start building up any locks. As most of the stored procedures i write are for reporting purposes, its a habit i've never shook (like putting all updates etc in a transaction). I do need to learn more about different locking options though, as some of the stuff i am having to work on now needs 100% data accuracy, so thanks for the link.
Martin.
That is pretty common for reports and if inaccurate information is tolerated (to a point) it is an option. Definitely a good idea to read up and understand the implications.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 14, 2012 at 8:03 am
Hi Lynn,
Can i bother you with a quick question? Have had a look at the code and am intersted in a couple of bits that are new to me. The return TABLE in the functions, i thought that a user defined function could only return a single value, have i been spending too long on 2k? I have been slowly learning the new features and have come across the TABLE variable, but not that a UDF could use it. I am also new to the APPLY clause and have bookmarked some information on it to delve into, i can basically understand what it is doing though and can see it will be a valuable piece of information for me, thanks. I can't actually get the alter procedure part to run to see the code in action, getting a syntax error near '.'. in the Misqry0000026 part of the code, line 31 near the APPLY conditions of the code, and as i'm not familiar with it can't actually see why its throwing it out.
Thanks for the information,
Martin.
September 14, 2012 at 8:24 am
m.henly (9/14/2012)
Hi Lynn,Can i bother you with a quick question? Have had a look at the code and am intersted in a couple of bits that are new to me. The return TABLE in the functions, i thought that a user defined function could only return a single value, have i been spending too long on 2k? I have been slowly learning the new features and have come across the TABLE variable, but not that a UDF could use it. I am also new to the APPLY clause and have bookmarked some information on it to delve into, i can basically understand what it is doing though and can see it will be a valuable piece of information for me, thanks. I can't actually get the alter procedure part to run to see the code in action, getting a syntax error near '.'. in the Misqry0000026 part of the code, line 31 near the APPLY conditions of the code, and as i'm not familiar with it can't actually see why its throwing it out.
Thanks for the information,
Martin.
First, the error in the alter procedure. Can't really help you there as I don't have the tables used in the procedure or functions so I can't create either and there fore can't see the error you are getting. I really need the full error message you are getting. Also, if you double click on the error it will take you to the line for the error which would also help know ing which line.
Do the functions create okay?
SQL Server 2005 introduced table valued functions. There are two type, inline and multistatement. Both have their uses and limitations. Be careful when using the multistatement table valued functions, they can be performance killers.
The inline table valued functions can be looked at as parameterized views. In the code I provided by using the APPLY operator these functions are called for each value passed to the function and returns a single record table with a single column in this case. For more on APPLY please read the following articles by Paul White (SQL Kiwi):
http://www.sqlservercentral.com/articles/APPLY/69953/
http://www.sqlservercentral.com/articles/APPLY/69954/
You may also want to look at this, http://www.sqlservercentral.com/blogs/lynnpettis/2009/05/07/comparing-hardcoded-functions-in-line-tvf-s-and-scalar-functions/. Just remember to test anything you work on. What works in some situations may not work as well in others.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply