December 31, 2012 at 7:06 am
i have created a stored procedure with the following SQL:
SELECT dbo.Generics.ProductName, dbo.Generics.Pack, dbo.Generics.OrderReference, dbo.Generics.CostPrice, dbo.Generics.Commission, dbo.Generics.Supplier,
dbo.Generics.Customer, dbo.Generics.ControlNumber, dbo.Generics.MonthYear, dbo.Generics.SalesType, dbo.Generics.FullPrice, dbo.Generics.PipCode,
dbo.Generics.MajorGroup, dbo.Customers.Customer AS Customer1, SUM(dbo.Generics.OrderQuantity) AS SumOfOrderQuantity, SUM(dbo.Generics.SellingValue)
AS SumOfSellingValue, dbo.Customers.CustomerName, dbo.Suppliers.SupplierName
FROM dbo.Generics RIGHT OUTER JOIN
dbo.Suppliers ON dbo.Generics.Supplier = dbo.Suppliers.Supplier LEFT OUTER JOIN
dbo.Customers ON dbo.Generics.Customer = dbo.Customers.Customer
WHERE (dbo.Generics.InvoiceDate BETWEEN @StartDate AND @EndDate)
GROUP BY dbo.Generics.ProductName, dbo.Generics.Pack, dbo.Generics.OrderReference, dbo.Generics.CostPrice, dbo.Generics.Commission, dbo.Generics.Supplier,
dbo.Generics.Customer, dbo.Generics.ControlNumber, dbo.Generics.MonthYear, dbo.Generics.SalesType, dbo.Generics.FullPrice, dbo.Generics.PipCode,
dbo.Generics.MajorGroup, dbo.Customers.Customer, dbo.Customers.CustomerName,dbo.Suppliers.SupplierName
HAVING (dbo.Generics.SalesType = 3)
I have then created a report in Business Intelligence Studio. The dataset contains the above the stored procedure. I have two paramters that are based on the report.
My problem is that when i view the report in a browser it is taking around 7 minutes to process. Is there something i am missing or can do to speed up the process.
Regards
December 31, 2012 at 7:26 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
P.S. The filter you have in the HAVING should be in the WHERE clause. Having is for filters on aggregates.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 31, 2012 at 6:31 pm
ziako (12/31/2012)
i have created a stored procedure with the following SQL:SELECT dbo.Generics.ProductName, dbo.Generics.Pack, dbo.Generics.OrderReference, dbo.Generics.CostPrice, dbo.Generics.Commission, dbo.Generics.Supplier,
dbo.Generics.Customer, dbo.Generics.ControlNumber, dbo.Generics.MonthYear, dbo.Generics.SalesType, dbo.Generics.FullPrice, dbo.Generics.PipCode,
dbo.Generics.MajorGroup, dbo.Customers.Customer AS Customer1, SUM(dbo.Generics.OrderQuantity) AS SumOfOrderQuantity, SUM(dbo.Generics.SellingValue)
AS SumOfSellingValue, dbo.Customers.CustomerName, dbo.Suppliers.SupplierName
FROM dbo.Generics RIGHT OUTER JOIN
dbo.Suppliers ON dbo.Generics.Supplier = dbo.Suppliers.Supplier LEFT OUTER JOIN
dbo.Customers ON dbo.Generics.Customer = dbo.Customers.Customer
WHERE (dbo.Generics.InvoiceDate BETWEEN @StartDate AND @EndDate)
GROUP BY dbo.Generics.ProductName, dbo.Generics.Pack, dbo.Generics.OrderReference, dbo.Generics.CostPrice, dbo.Generics.Commission, dbo.Generics.Supplier,
dbo.Generics.Customer, dbo.Generics.ControlNumber, dbo.Generics.MonthYear, dbo.Generics.SalesType, dbo.Generics.FullPrice, dbo.Generics.PipCode,
dbo.Generics.MajorGroup, dbo.Customers.Customer, dbo.Customers.CustomerName,dbo.Suppliers.SupplierName
HAVING (dbo.Generics.SalesType = 3)
I have then created a report in Business Intelligence Studio. The dataset contains the above the stored procedure. I have two paramters that are based on the report.
My problem is that when i view the report in a browser it is taking around 7 minutes to process. Is there something i am missing or can do to speed up the process.
Regards
The first thing I see (same as what Gail posted) is the HAVING clause which is executed only after all the other code has executed. I recommend move that to the WHERE clause so that the query doesn't have to process so much data only to be rejected at the very end.
To make the code easier to read, I also recommend that you use table aliases instead of the 3 part naming convention everywhere.
Other than that, it's not real easy to troubleshoot deeper performance problems without some extra information. Please see Gail's post above for a link that will show you what is needed.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 31, 2012 at 7:39 pm
The following is your code formatted, using 2-part naming in the SELECT list by using table aliases in the FROM clause, and moving the HAVING criteria to the WHERE clause.
SELECT
gen.ProductName,
gen.Pack,
gen.OrderReference,
gen.CostPrice,
gen.Commission,
gen.Supplier,
gen.Customer,
gen.ControlNumber,
gen.MonthYear,
gen.SalesType,
gen.FullPrice,
gen.PipCode,
gen.MajorGroup,
cus.Customer AS Customer1,
SUM(gen.OrderQuantity) AS SumOfOrderQuantity,
SUM(gen.SellingValue) AS SumOfSellingValue,
cus.CustomerName,
sup.SupplierName
FROM
dbo.Generics gen
RIGHT OUTER JOIN dbo.Suppliers sup
ON gen.Supplier = sup.Supplier
LEFT OUTER JOIN dbo.Customers cus
ON gen.Customer = cus.Customer
WHERE
(gen.InvoiceDate BETWEEN @StartDate AND @EndDate) and
(gen.SalesType = 3)
GROUP BY
gen.ProductName,
gen.Pack,
gen.OrderReference,
gen.CostPrice,
gen.Commission,
gen.Supplier,
gen.Customer,
gen.ControlNumber,
gen.MonthYear,
gen.SalesType,
gen.FullPrice,
gen.PipCode,
gen.MajorGroup,
cus.Customer,
cus.CustomerName,
sup.SupplierName;
Another reason for the table aliases and 2-part naming convention is the Microsoft has deprecated the use of 3- and 4-part naming conventions in SQL Server in the column list. This functionality will be removed from a future (yet unnamed) version of SQL Server. It is best to start getting used to writing your code like that above.
Edit: Corrected spelling error and added clarification to the statement regarding 3- and 4-part naming convention.
Also, you will find this information here: http://msdn.microsoft.com/en-us/library/ms143729(v=sql.100).aspx
January 1, 2013 at 12:09 am
Jeff Moden (12/31/2012)
The first thing I see (same as what Gail posted) is the HAVING clause which is executed only after all the other code has executed. I recommend move that to the WHERE clause so that the query doesn't have to process so much data only to be rejected at the very end.
The optimiser will turn it into a where clause. It's just bad style/understanding.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 2, 2013 at 12:28 am
Lynn Pettis (12/31/2012)
Another reason for the table aliases and 2-part naming convention is the Microsoft has deprecated the use of 3- and 4-part naming conventions in SQL Server. This functionality will be removed from a future (yet unnamed) version of SQL Server. It is best to start getting used to writing your code like that above.
Hah! Finally! Someone that saw the same thing I did in the original 2008 deprecation list. (actually, the deprecation was only for the columns, not the tables in the FROM clause, just to be sure).
The problem is, when I went to show my boss about a year ago, it wasn't there anymore. If you have an MS URL for a deprecation list that shows that 3 & 4 part naming for columns has been deprecated, you'd be doing me a huge favor by posting it.
{Edit} Never mind... I found it in the 2012 deprecation list. Thanks anyway.
(2nd edit} Wow! Not sure what I was smokin' before, but it's also in the 2008 list.
Thanks, Lynn.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2013 at 12:45 am
GilaMonster (1/1/2013)
Jeff Moden (12/31/2012)
The first thing I see (same as what Gail posted) is the HAVING clause which is executed only after all the other code has executed. I recommend move that to the WHERE clause so that the query doesn't have to process so much data only to be rejected at the very end.The optimiser will turn it into a where clause. It's just bad style/understanding.
Heh... tough position for me to be in. Your studies to achieve MCM make you smarter in SQL Server than I'll ever likely be so it's real hard to doubt you but I have to ask, are you sure?
I know that HAVING is used to exclude rows like a WHERE clause but, according to latest copy of BOL at http://msdn.microsoft.com/en-us/library/ms189499.aspx the HAVING clause isn't processed until 3 steps after the WHERE clause is. Here's the snippet from that MS article that I'm talking about. It seems to indicate that even the GROUP BY will have to work with all the rows because the HAVING isn't executed until after the GROUP BY does it's job.
[font="Arial Black"]Logical Processing Order of the SELECT statement[/font]
The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. Note that the actual physical execution of the statement is determined by the query processor and the order may vary from this list. [highlight]<<----<<<< Yep... I saw this but that only means that it "could" happen... not that it "will" happen. Any way that we can prove it in this case?[/highlight]
1.FROM
2.ON
3.JOIN
[highlight]4.WHERE[/highlight]
5.GROUP BY
6.WITH CUBE or WITH ROLLUP
[highlight]7.HAVING[/highlight]
8.SELECT
9.DISTINCT
10.ORDER BY
11.TOP
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2013 at 2:03 am
Jeff Moden (1/2/2013)
GilaMonster (1/1/2013)
Jeff Moden (12/31/2012)
The first thing I see (same as what Gail posted) is the HAVING clause which is executed only after all the other code has executed. I recommend move that to the WHERE clause so that the query doesn't have to process so much data only to be rejected at the very end.The optimiser will turn it into a where clause. It's just bad style/understanding.
Heh... tough position for me to be in. Your studies to achieve MCM make you smarter in SQL Server than I'll ever likely be so it's real hard to doubt you but I have to ask, are you sure?
Considering that all that's needed to see is to write a query, yes.
SELECT SalesOrderID, SpecialOfferID, SUM(linetotal) FROM Sales.SalesOrderDetail AS sod
WHERE UnitPriceDiscount = 0
GROUP BY SalesOrderID, SpecialOfferID
HAVING SpecialOfferID != 0
SELECT SalesOrderID, SpecialOfferID, SUM(linetotal) FROM Sales.SalesOrderDetail AS sod
WHERE UnitPriceDiscount = 0 AND SpecialOfferID != 0
GROUP BY SalesOrderID, SpecialOfferID
Execution plan is absolutely identical for the two. The filter is, in both cases, done after the clustered index scan and before the hash aggregate. (there's no index on either of those columns. Add a covering index on UnitPriceDiscount, SpecialOfferID and the plan becomes an index seek with two seek predicates)
HAVING with an aggregate has to be done after the aggregation. HAVING with a non-aggregate is just a where clause and, when the parser, algebriser and optimiser are done with the query, that's exactly what it's become.
Note that what you linked was the 'logical processing order', not the physical processing order. According to the logical order, where clauses aren't processed until all the joins are done, and I think we both know that's not how things are physically processed (if they were, indexes would be near-useless and most data access would be table scans with filters after the joins)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 2, 2013 at 2:32 am
Jeff Moden (1/2/2013)
Lynn Pettis (12/31/2012)
Another reason for the table aliases and 2-part naming convention is the Microsoft has deprecated the use of 3- and 4-part naming conventions in SQL Server. This functionality will be removed from a future (yet unnamed) version of SQL Server. It is best to start getting used to writing your code like that above.Hah! Finally! Someone that saw the same thing I did in the original 2008 deprecation list. (actually, the deprecation was only for the columns, not the tables in the FROM clause, just to be sure).
The problem is, when I went to show my boss about a year ago, it wasn't there anymore.
If you have an MS URL for a deprecation list that shows that 3 & 4 part naming for columns has been deprecated, you'd be doing me a huge favor by posting it.{Edit} Never mind... I found it in the 2012 deprecation list. Thanks anyway.
Thanks, Lynn.
Thanks, Jeff. I updated my earlier post and fixed the spelling error for deprecated. Think you could fix it your quote of my post? Appreciate it.
Lynn
January 2, 2013 at 2:44 am
For the OP:
Have the coding suggestions helped with the processing time of this report?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 2, 2013 at 2:45 am
edit: double-posted
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 2, 2013 at 3:01 am
Thanks for the tip and advice. Adding the sales type in the where clause has significantly helped.
January 2, 2013 at 11:29 am
GilaMonster (1/2/2013)
Jeff Moden (1/2/2013)
GilaMonster (1/1/2013)
Jeff Moden (12/31/2012)
The first thing I see (same as what Gail posted) is the HAVING clause which is executed only after all the other code has executed. I recommend move that to the WHERE clause so that the query doesn't have to process so much data only to be rejected at the very end.The optimiser will turn it into a where clause. It's just bad style/understanding.
Heh... tough position for me to be in. Your studies to achieve MCM make you smarter in SQL Server than I'll ever likely be so it's real hard to doubt you but I have to ask, are you sure?
Considering that all that's needed to see is to write a query, yes.
SELECT SalesOrderID, SpecialOfferID, SUM(linetotal) FROM Sales.SalesOrderDetail AS sodWHERE UnitPriceDiscount = 0
GROUP BY SalesOrderID, SpecialOfferID
HAVING SpecialOfferID != 0
SELECT SalesOrderID, SpecialOfferID, SUM(linetotal) FROM Sales.SalesOrderDetail AS sod
WHERE UnitPriceDiscount = 0 AND SpecialOfferID != 0
GROUP BY SalesOrderID, SpecialOfferID
Execution plan is absolutely identical for the two. The filter is, in both cases, done after the clustered index scan and before the hash aggregate. (there's no index on either of those columns. Add a covering index on UnitPriceDiscount, SpecialOfferID and the plan becomes an index seek with two seek predicates)
HAVING with an aggregate has to be done after the aggregation. HAVING with a non-aggregate is just a where clause and, when the parser, algebriser and optimiser are done with the query, that's exactly what it's become.
Note that what you linked was the 'logical processing order', not the physical processing order. According to the logical order, where clauses aren't processed until all the joins are done, and I think we both know that's not how things are physically processed (if they were, indexes would be near-useless and most data access would be table scans with filters after the joins)
ziako (1/2/2013)
Thanks for the tip and advice. Adding the sales type in the where clause has significantly helped.
Thanks, Gail. Based on what ziako posted above, I guess I'm going to have to fall back on the ol "It Depends" thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply