after some advice about how to do this quicker. currently 15mins

i am doing a sum of sales orders where a specific product has been bought then dividing by a sum of all salesorders,

can anyone offer a way of speeding this up?

code attached in case anyone can think of some ideas. (the no locks are only to stop me crucifing the database whilst getting this to work. )

select ccx_sopsystemaccountid, horsepercent,name,address1_postalcode,owneridname, horse, allsales, ccx_coresegmentname, ccx_industryname
from
(select a.ccx_sopsystemaccountid, horse/allsales horsepercent ,name,address1_postalcode,owneridname, horse, allsales, ccx_coresegmentname, ccx_industryname
from
(select ccx_sopsystemaccountid,horse ,name,address1_postalcode,owneridname, ccx_coresegmentname, ccx_industryname
from
(SELECT FilteredAccount.ccx_sopsystemaccountid, filteredaccount.name,FilteredAccount.owneridname, filteredaccount.address1_postalcode,sum(FilteredSalesOrderDetail.baseamount)horse, ccx_coresegmentname, ccx_industryname
FROM FilteredAccount WITH (nolock) INNER JOIN
FilteredSalesOrder WITH (nolock) ON FilteredAccount.accountid = FilteredSalesOrder.accountid INNER JOIN
FilteredSalesOrderDetail WITH (nolock) ON FilteredSalesOrder.salesorderid = FilteredSalesOrderDetail.salesorderid INNER JOIN
FilteredProduct WITH (nolock) ON FilteredProduct.productid = FilteredSalesOrderDetail.productid
WHERE (FilteredAccount.statecodename = ‘active’) and submitstatusdescription=’invoiced’
AND (FilteredProduct.ccx_productcategoryidname in (‘PRI-Equest Feed & Bed’,’PRI-Equest Accessories’) or FilteredProduct.ccx_productgroupidname = ‘BMS-Horse’)
AND (FilteredSalesOrder.ccx_cog_invoicedate BETWEEN CONVERT(DATETIME, ‘2012-01-01 00:00:00’, 102) AND CONVERT(DATETIME, ‘2012-12-31 00:00:00’, 102))
group by FilteredAccount.ccx_sopsystemaccountid, filteredaccount.name, filteredaccount.address1_postalcode,filteredaccount.owneridname, ccx_coresegmentname, ccx_industryname
)aa
where horse>500
)a
inner join
(SELECT FilteredAccount.ccx_sopsystemaccountid, sum(FilteredSalesOrderDetail.baseamount)allsales
FROM FilteredAccount WITH (nolock) INNER JOIN
FilteredSalesOrder WITH (nolock) ON FilteredAccount.accountid = FilteredSalesOrder.accountid INNER JOIN
FilteredSalesOrderDetail WITH (nolock) ON FilteredSalesOrder.salesorderid = FilteredSalesOrderDetail.salesorderid INNER JOIN
FilteredProduct WITH (nolock) ON FilteredProduct.productid = FilteredSalesOrderDetail.productid
WHERE (FilteredAccount.statecodename = ‘active’) and submitstatusdescription=’invoiced’
AND (FilteredSalesOrder.ccx_cog_invoicedate BETWEEN CONVERT(DATETIME, ‘2012-01-01 00:00:00’, 102) AND CONVERT(DATETIME, ‘2012-12-31 00:00:00’, 102))
group by FilteredAccount.ccx_sopsystemaccountid)b
on a.ccx_sopsystemaccountid=b.ccx_sopsystemaccountid
where horse/allsales>0.25)aaa

Might be better to summarise your table structures/relationships and what you want to achieve so people can suggest a solution from scratch rather than trying to decypher a mass of spaghetti SQL code..?

could do,

briefly account table links inner join to order table, to salesorderdetail to product. where product is specific

divide by above but no filter on product.

What version of SQL Server are you using?

I’d suggest you post so DDL so we can use http://sqlfiddle.com/ to create a mock up and play with your T-SQL in there.

P.S. I am not a SQL Developer so the chances that I can help you are very slim…

Stupid question but is FilteredSalesOrder.ccx_cog_invoicedate indexed?

leffeboy – that is never a stupid question 😯

all tables are indexed, or so the DBA tells me.

The real question is whether or not that particular field is indexed as it is unlikely that every field in every table it indexed. At the moment that is where I would start looking

The other place is the function ‘horse/allsales>0.25’ that you have. That has to be calculated for each record. You might find that 4 * horse>allsales runs faster or if it can be removed that might be even better.

As per leffeboy’s comments, also get rid of any multiple function calls doing the same thing and replace with constants or variables i.e:
CONVERT(DATETIME, ‘2012-01-01 00:00:00’, 102) AND CONVERT(DATETIME, ‘2012-12-31 00:00:00’, 102))
to
DECLARE @Start DATETIME = CONVERT(DATETIME, ‘2012-01-01 00:00:00’, 102)
DECLARE @End DATETIME = CONVERT(DATETIME, ‘2012-12-31 00:00:00’, 102)
….. cog_invoicedate BETWEEN @Start and @End

You could break the query into smaller chunks and insert into table variables or CTE’s, also analyse the query execution plan for poor performing steps (doing it against your large query wold be a pita) and this will give you some hints on missing indexes.

Importantly, (again stealing leffeboys thunder a little) run the SQL profiler and/or index tuning wizard to identify columns should be indexed (you should probably do at least those you join on and in a WHERE clause); this will give you the biggest benefit as it will do the work for you and in 99% of cases will give you a marked improvement.

Last but not least you could hire someone to re-write it for you and improve performance (email in profile, payment in cash, bike bits, or biscuits)!

again stealing leffeboys thunder a little

you can have it, my wife doesn’t appreciate it 🙂

horse ? 🙄

Lovely for when someone else has to maintain the code.

I think we need a “code” button on here….

Change your unique identifiers for statuses, categories and so on to be numeric rather than varchars. Depending on how you’ve got it indexed, it can be 50% slower.

To be fair I don’t think that is ever going to be lovely to maintain!

Does tsql implement the with clause? that might at least make it easier to read and remove the subqueries.

Can you clarify what you’re after?

Is it (for a given date period), for each accountid, the sum of horse sales, the sum of all sales and the percentage of horse sales, where the percentage is > 0.25?

I’m sure you have solved this by this now but, if not, give this a whirl (obviously untested)

/* Basic data */
;WITH cte as(
(SELECT fa.ccx_sopsystemaccountid AS AccountID, fsod.baseamount, fp.ccx_productgroupidname as GroupName, fp.ccx_productcategoryidname as CatName
FROM FilteredAccount AS fa WITH (nolock)
INNER JOIN FilteredSalesOrder AS fso WITH (nolock) ON fso.accountid = fa.accountid
INNER JOIN FilteredSalesOrderDetail AS fsod WITH (nolock) ON fsod.salesorderid = fso.salesorderid
INNER JOIN FilteredProduct AS fp WITH (nolock) ON fp.productid = fsod.productid
WHERE (ac.statecodename = ‘active’)
AND submitstatusdescription=’invoiced’
AND (fso.ccx_cog_invoicedate BETWEEN CONVERT(DATETIME, ‘2012-01-01 00:00:00’, 102) AND CONVERT(DATETIME, ‘2012-12-31 00:00:00’, 102))
)
/* Sales value */
,TotalsByAccount AS
(
SELECT g1.AccountID, g1.TotalSales, g2.TotalHorse FROM
(SELECT AccountID, SUM(baseamount) AS TotalSales
FROM cte
GROUP BY AccountID) AS g1
INNER JOIN
(SELECT AccountID, SUM(baseamount) as TotalHorse
FROM cte
WHERE GroupName = ‘BMS-Horse’ OR CatName = ‘PRI-Equest Feed & Bed’ OR CatName = ‘PRI-Equest Accessories’
GROUP BY AccountID
HAVING Horse > 500) AS g2 ON g2.AccountId = g1.AccountID
)
/* Final output */
SELECT AccountID, TotalSales, TotalHorse, (TotalHorse/TotalSales * 100) AS PercentageHorse
, name, address1_postalcode, owneridname, ccx_coresegmentname, ccx_industryname
FROM TotalsByAccount AS t
INNER JOIN FilteredAccount AS fa
ON fa.ccx_sopsystemaccountid = t.AccountID
WHERE (TotalHorse/TotalSales > 0.25);