November 8, 2005 at 1:09 pm
I am pretty good at select statements, however, I have been sent a request to index a view I created. I am unsure of how to create it or if it at all possible to optimize my query. A little background....our SQL Server data is imported every evening by our software vendor. The tables in our SQL Server are owned by our software vendor and I cannot access them or manipulate them in any way. I can create views on the main database, and I do have ability to create tables, stored procedures, etc on another database on the same server. I have a select statement (and I know it is pretty ugly) that reports our daily sales. Due to multiple issues (and requests to how the data will be seen currently) there are multiple union clauses. We have currently a Cognos Initiative and the query for all of the sales data is pulled from the following view. I need to know if there is way to either a) optimize the runtime or b) index it to optimize the runtime. I have been playing with a stored procedure to create a new table and insert the data based off of the view. I dont know if that approach is the best. The query is below:
select a.Source, a.TransDate, a.StoreID, a.ProductID,
a.VendorID, a.PostalCodeID,
a.ProductDescription, a.SalesPersonID, a.OrderID,
a.CategoryID,
sum(a.Retail) as 'NetRetail',
sum(a.Units) as 'NetUnits',
sum(a.LandedCost) as 'LandedCost',
sum(a.GrossRetail) as 'GrossRetail',
sum(a.GrossUnits)as 'GrossUnits',
sum(a.GrossCost) as 'GrossCost',
sum(a.returns) as 'ReturnSales',
sum(a.returnunits) as 'ReturnUnits',
sum(a.returncost) as 'ReturnCost',
sum(a.discount) as 'Discounts'
from
---first query pulls sales by store not keyed at a DC
(select bta.Source, bta.orderid, bta.vendorid, bta.postalcodeid,
bta.Transdate, bta.salespersonid, pro.description as 'ProductDescription',
case when bta.StoreID = '449' then '414' else bta.storeid end as 'StoreID'
, bta.ProductID,
case when gro.categoryid = 'CONSIG' then gro.groupid else gro.categoryid end as 'CategoryID',
sum(bta.slsprice - bta.slsdiscnt - bta.rtnprice + bta.rtndiscnt) as 'Retail',
sum(bta.slsunits - bta.rtnunits) as 'Units',
sum(bta.slscost-bta.rtncost) as 'LandedCost',
sum(bta.slscost) as 'GrossCost',
sum(-bta.rtncost) as 'ReturnCost',
sum(bta.slsprice-bta.slsdiscnt) as 'GrossRetail',
sum(bta.slsunits) as 'GrossUnits',
sum(-bta.rtnprice+bta.rtndiscnt) as 'Returns',
sum(-bta.rtnunits) as 'ReturnUnits',
sum(-bta.slsdiscnt+bta.rtndiscnt) as 'Discount'
from btadata bta, product pro, groups gro
where bta.productid = pro.productid
and pro.groupid = gro.groupid
and bta.kitstatus NOT IN ('C', 'M')
and bta.storeid not in ('203','211','440','403','110','550','402','350','204','201','401','501','207','220')
group by bta.source, bta.orderid,bta.postalcodeid,bta.transdate,
bta.storeid, bta.salespersonid, pro.description ,bta.vendorid,
bta.productid, gro.categoryid, gro.groupid
UNION
select bta.Source, bta.orderid, bta.vendorid, bta.postalcodeid,
bta.Transdate, bta.salespersonid, pro.description as 'ProductDescription',
case when bta.StoreID = '449' then '414' else bta.storeid end as 'StoreID'
, bta.ProductID,
case when gro.categoryid = 'CONSIG' then gro.groupid else gro.categoryid end as 'CategoryID',
sum(bta.slsprice - bta.slsdiscnt - bta.rtnprice + bta.rtndiscnt) as 'Retail',
sum(bta.slsunits - bta.rtnunits) as 'Units',
sum(bta.slscost-bta.rtncost) as 'LandedCost',
sum(bta.slscost) as 'GrossCost',
sum(-bta.rtncost) as 'ReturnCost',
sum(bta.slsprice-bta.slsdiscnt) as 'GrossRetail',
sum(bta.slsunits) as 'GrossUnits',
sum(-bta.rtnprice+bta.rtndiscnt) as 'Returns',
sum(-bta.rtnunits) as 'ReturnUnits',
sum(-bta.slsdiscnt+bta.rtndiscnt) as 'Discount'
from btadata bta, product pro, groups gro
where bta.productid = pro.productid
and pro.groupid = gro.groupid
and bta.kitstatus = 'M'
and bta.storeid not in ('203','211','440','403','110','550','402','350','204','201','401','501','207','220')
group by bta.source, bta.orderid,bta.postalcodeid,bta.transdate,
bta.storeid, bta.salespersonid, pro.description ,bta.vendorid,
bta.productid, gro.categoryid, gro.groupid
UNION
---second query pulls delivered dollars only adj keyed against a kit sku (not keyed at a DC)
select bta.Source, bta.orderid, bta.vendorid, bta.postalcodeid,
bta.Transdate, bta.salespersonid, pro.description as 'ProductDescription',
case when bta.StoreID = '449' then '414' else bta.storeid end as 'StoreID'
, bta.ProductID,
case when gro.categoryid = 'CONSIG' then gro.groupid else gro.categoryid end as 'CategoryID',
sum(bta.slsprice - bta.slsdiscnt - bta.rtnprice + bta.rtndiscnt) as 'Retail',
sum(bta.slsunits - bta.rtnunits) as 'Units',
sum(bta.slscost-bta.rtncost) as 'LandedCost',
sum(bta.slscost) as 'GrossCost',
sum(-bta.rtncost) as 'ReturnCost',
sum(bta.slsprice-bta.slsdiscnt) as 'GrossRetail',
sum(bta.slsunits) as 'GrossUnits',
sum(-bta.rtnprice+bta.rtndiscnt) as 'Returns',
sum(-bta.rtnunits) as 'ReturnUnits',
sum(-bta.slsdiscnt+bta.rtndiscnt) as 'Discount'
from btadata bta, product pro, groups gro
where bta.productid = pro.productid
and pro.groupid = gro.groupid
and bta.kitstatus = 'M'
and bta.transcodeid in ('20','50')
and bta.storeid not in ('203','211','440','403','110','550','402','350','204','201','401','501','207','220')
group by bta.source, bta.orderid,bta.postalcodeid,bta.transdate,
bta.storeid, bta.salespersonid, pro.description ,bta.vendorid,
bta.productid, gro.categoryid, gro.groupid
---third query pulls sales by store keyed at a DC for reclass
UNION
select bta.Source, bta.orderid,bta.vendorid, bta.postalcodeid,
bta.Transdate, bta.salespersonid, pro.description as 'ProductDescription',
case when cust.StoreID = '449' then '414' else cust.storeid end as 'StoreID'
, bta.ProductID,
case when gro.categoryid = 'CONSIG' then gro.groupid else gro.categoryid end as 'CategoryID',
sum(bta.slsprice - bta.slsdiscnt - bta.rtnprice + bta.rtndiscnt) as 'Retail',
sum(bta.slsunits - bta.rtnunits) as 'Units',
sum(bta.slscost-bta.rtncost) as 'LandedCost',
sum(bta.slscost) as 'GrossCost',
sum(-bta.rtncost) as 'ReturnCost',
sum(bta.slsprice-bta.slsdiscnt) as 'GrossRetail',
sum(bta.slsunits) as 'GrossUnits',
sum(-bta.rtnprice+bta.rtndiscnt) as 'Returns',
sum(-bta.rtnunits) as 'ReturnUnits',
sum(-bta.slsdiscnt+bta.rtndiscnt) as 'Discount'
from btadata bta, product pro, groups gro, customer cust
where bta.productid = pro.productid
and pro.groupid = gro.groupid
and bta.customerid = cust.customerid
and bta.kitstatus NOT IN ('C', 'M')
and bta.customerid <> '9500'
and bta.storeid in ('203','211','440','403','110','550','402','350','204','201','401','501','207','220')
group by bta.source, bta.orderid, bta.transdate, cust.storeid, bta.salespersonid, pro.description,
bta.productid, gro.categoryid, gro.groupid, bta.vendorid, bta.postalcodeid
UNION
select bta.Source, bta.orderid,bta.vendorid, bta.postalcodeid,
bta.Transdate, bta.salespersonid, pro.description as 'ProductDescription',
case when cust.StoreID = '449' then '414' else cust.storeid end as 'StoreID'
, bta.ProductID,
case when gro.categoryid = 'CONSIG' then gro.groupid else gro.categoryid end as 'CategoryID',
sum(bta.slsprice - bta.slsdiscnt - bta.rtnprice + bta.rtndiscnt) as 'Retail',
sum(bta.slsunits - bta.rtnunits) as 'Units',
sum(bta.slscost-bta.rtncost) as 'LandedCost',
sum(bta.slscost) as 'GrossCost',
sum(-bta.rtncost) as 'ReturnCost',
sum(bta.slsprice-bta.slsdiscnt) as 'GrossRetail',
sum(bta.slsunits) as 'GrossUnits',
sum(-bta.rtnprice+bta.rtndiscnt) as 'Returns',
sum(-bta.rtnunits) as 'ReturnUnits',
sum(-bta.slsdiscnt+bta.rtndiscnt) as 'Discount'
from btadata bta, product pro, groups gro, customer cust
where bta.productid = pro.productid
and pro.groupid = gro.groupid
and bta.customerid = cust.customerid
and bta.kitstatus = 'M'
and bta.customerid <> '9500'
and bta.storeid in ('203','211','440','403','110','550','402','350','204','201','401','501','207','220')
group by bta.source, bta.orderid, bta.transdate, cust.storeid, bta.salespersonid, pro.description,
bta.productid, gro.categoryid, gro.groupid, bta.vendorid, bta.postalcodeid
---fourth query pulls delivered dollars only adj keyed at a DC for reclass
UNION
select bta.Source, bta.orderid, bta.vendorid, bta.postalcodeid,
bta.Transdate, bta.salespersonid, pro.description as 'ProductDescription',
case when cust.StoreID = '449' then '414' else cust.storeid end as 'StoreID'
, bta.ProductID,
case when gro.categoryid = 'CONSIG' then gro.groupid else gro.categoryid end as 'CategoryID',
sum(bta.slsprice - bta.slsdiscnt - bta.rtnprice + bta.rtndiscnt) as 'Retail',
sum(bta.slsunits - bta.rtnunits) as 'Units',
sum(bta.slscost-bta.rtncost) as 'LandedCost',
sum(bta.slscost) as 'GrossCost',
sum(-bta.rtncost) as 'ReturnCost',
sum(bta.slsprice-bta.slsdiscnt) as 'GrossRetail',
sum(bta.slsunits) as 'GrossUnits',
sum(-bta.rtnprice+bta.rtndiscnt) as 'Returns',
sum(-bta.rtnunits) as 'ReturnUnits',
sum(-bta.slsdiscnt+bta.rtndiscnt) as 'Discount'
from btadata bta, product pro, groups gro, customer cust
where bta.productid = pro.productid
and pro.groupid = gro.groupid
and bta.customerid = cust.customerid
and bta.kitstatus = 'M'
and bta.customerid <> '9500'
and bta.transcodeid in ('20','50')
and bta.storeid in ('203','211','440','403','110','550','402','350','204','201','401','501','207','220')
group by bta.source, bta.orderid, bta.vendorid, bta.postalcodeid,
bta.transdate, cust.storeid, bta.salespersonid, pro.description ,
bta.productid, gro.categoryid, gro.groupid
UNION
---fifth query pulls sales keyed for Greensboro
select bta.Source,bta.orderid, bta.vendorid, bta.postalcodeid,
bta.Transdate, bta.salespersonid, pro.description as 'ProductDescription',
case when bta.StoreID = '220' then '295' else bta.storeid end as 'StoreID',
bta.ProductID,
case when gro.categoryid = 'CONSIG' then gro.groupid else gro.categoryid end as 'CategoryID',
sum(bta.slsprice - bta.slsdiscnt - bta.rtnprice + bta.rtndiscnt) as 'Retail',
sum(bta.slsunits - bta.rtnunits) as 'Units',
sum(bta.slscost-bta.rtncost) as 'LandedCost',
sum(bta.slscost) as 'GrossCost',
sum(-bta.rtncost) as 'ReturnCost',
sum(bta.slsprice-bta.slsdiscnt) as 'GrossRetail',
sum(bta.slsunits) as 'GrossUnits',
sum(-bta.rtnprice+bta.rtndiscnt) as 'Returns',
sum(-bta.rtnunits) as 'ReturnUnits',
sum(-bta.slsdiscnt+bta.rtndiscnt) as 'Discount'
from btadata bta, product pro, groups gro
where bta.productid = pro.productid
and pro.groupid = gro.groupid
and bta.kitstatus NOT IN ('C', 'M')
and bta.storeid = '220'
and bta.customerid = '9500'
group by bta.source,bta.orderid, bta.vendorid, bta.postalcodeid,
bta.transdate, bta.storeid, bta.salespersonid, pro.description ,
bta.productid, gro.categoryid, gro.groupid
UNION
select bta.Source,bta.orderid, bta.vendorid, bta.postalcodeid,
bta.Transdate, bta.salespersonid, pro.description as 'ProductDescription',
case when bta.StoreID = '220' then '295' else bta.storeid end as 'StoreID',
bta.ProductID,
case when gro.categoryid = 'CONSIG' then gro.groupid else gro.categoryid end as 'CategoryID',
sum(bta.slsprice - bta.slsdiscnt - bta.rtnprice + bta.rtndiscnt) as 'Retail',
sum(bta.slsunits - bta.rtnunits) as 'Units',
sum(bta.slscost-bta.rtncost) as 'LandedCost',
sum(bta.slscost) as 'GrossCost',
sum(-bta.rtncost) as 'ReturnCost',
sum(bta.slsprice-bta.slsdiscnt) as 'GrossRetail',
sum(bta.slsunits) as 'GrossUnits',
sum(-bta.rtnprice+bta.rtndiscnt) as 'Returns',
sum(-bta.rtnunits) as 'ReturnUnits',
sum(-bta.slsdiscnt+bta.rtndiscnt) as 'Discount'
from btadata bta, product pro, groups gro
where bta.productid = pro.productid
and pro.groupid = gro.groupid
and bta.kitstatus = 'M'
and bta.storeid = '220'
and bta.customerid = '9500'
group by bta.source,bta.orderid, bta.vendorid, bta.postalcodeid,
bta.transdate, bta.storeid, bta.salespersonid, pro.description ,
bta.productid, gro.categoryid, gro.groupid
UNION
---sixth query pulls delivered dollars only adj keyed against kit sku for Greensboro
select bta.Source, bta.orderid, bta.vendorid, bta.postalcodeid,
bta.Transdate, bta.salespersonid,
pro.description as 'ProductDescription',
case when bta.StoreID = '220' then '295' else bta.storeid end as 'StoreID',
bta.ProductID,
case when gro.categoryid = 'CONSIG' then gro.groupid else gro.categoryid end as 'CategoryID',
sum(bta.slsprice - bta.slsdiscnt - bta.rtnprice + bta.rtndiscnt) as 'Retail',
sum(bta.slsunits - bta.rtnunits) as 'Units',
sum(bta.slscost-bta.rtncost) as 'LandedCost',
sum(bta.slscost) as 'GrossCost',
sum(-bta.rtncost) as 'ReturnCost',
sum(bta.slsprice-bta.slsdiscnt) as 'GrossRetail',
sum(bta.slsunits) as 'GrossUnits',
sum(-bta.rtnprice+bta.rtndiscnt) as 'Returns',
sum(-bta.rtnunits) as 'ReturnUnits',
sum(-bta.slsdiscnt+bta.rtndiscnt) as 'Discount'
from btadata bta, product pro, groups gro
where bta.productid = pro.productid
and pro.groupid = gro.groupid
and bta.kitstatus = 'M'
and bta.transcodeid in ('20','50')
and bta.storeid ='220'
and bta.customerid = '9500'
group by bta.source,bta.orderid,bta.vendorid, bta.postalcodeid,
bta.transdate, bta.storeid, bta.salespersonid, pro.description,
bta.productid, gro.categoryid, gro.groupid
UNION
SELECT SOURCE, orderid, vendorid, postalcodeid, transdate, salespersonid, productdescription, storeid,
productid, categoryid,
SUM(NETRETAIL) AS 'Retail',
sum(NETUNITS) as 'Units',
sum(LANDEDCOST) as 'LandedCost',
sum(GROSSCost) as 'GrossCost',
sum(returncost) as 'ReturnCost',
sum(GrossRetail) as 'GrossRetail',
sum(GROSSUNITS) as 'GrossUnits',
sum(returnSales)as 'Returns',
sum(returnUnits) as 'ReturnUnits',
sum(discounts) as Discount
from salesadj
group by SOURCE, orderid, vendorid, postalcodeid, transdate, salespersonid, productdescription, storeid,
productid, categoryid)a
where a.storeid between '1' and '999'
and a.Transdate > '30-Nov-03'
and a.categoryid <> '<No Value>'
group by a.Source, a.TransDate, a.StoreID, a.ProductID,
a.VendorID, a.PostalCodeID,
a.ProductDescription, a.SalesPersonID, a.OrderID,
a.CategoryID
Any of your expert advice will be greatly appreciated. Thanks!
November 8, 2005 at 2:10 pm
G'day,
In order to index a view, take a look at 'WITH SCHEMABINDING' in books online. Your hosting company may or may not allow you to do this on their database. If permitted, work closely with their DBA as this can blow performance faster than you can blink, particularly on a transactional DB.
I would recommend a periodic extract to "your" database and create the schemabound view in that space. That way, only you are affected by the performance hit and not the other DB. Once you have the schemabound view then you can index it multiple ways.
Another word of caution - indexed views can do strange things to ETL packages. If performance during the ETL process becomes an issue, you may want to consider tactics such as dropping the indexes during the ETL cycle and recreating them afterwards.
A final thought for consideration - you may want to look at precomputing and storing some intermediate results so that the final view/select is less complex.
Unfortunately, much of your effort is going to be on a trial and error basis, hopefully in a test environment. Balancing performance against simplicity is not technically difficult in what you are doing, but can be time consuming to fine tune.
Hopefully some of this makes sense and helps
Wayne
November 9, 2005 at 6:02 am
Hi Wayne,
Thanks for your response....I ended up going with the simplest route, I created a table on my database which is a compilation of all of the aggregates in the "Main View". My stored procedure then only inserts the previous days information to the new table. I then created a new view on the main database that pulls the compiled information. It speeded query time for our end-users when pulling a years worth of data from 5 minutes to 18 seconds.
Thanks again for your reply.
Kaye
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply