April 2, 2008 at 4:05 pm
Let me preface this by stating I am VERY new to the wonderful world of database administration. Let me also state that I have learned a great deal from this site alone -- SCC ROCKS!
The query below is intended to retrieve the price change history records for multiple condo units. I've implemented it using a CTE to retrieve the history then querying the CTE to get the data the way I want it. When I run the CTE code by itself, I get the correct results in no time at all. However, when running the entire statement, it takes close to 3 minutes to return just 797 records! All the tables in question have lees than 200 records each with the exception of the pricehistory table which has just under 800. The inventory_grouping table has only a Clustered Index on inventory_grouping_id. None of the other tables have any indexes at all!
WITH price_history AS (
SELECT i.inventory_id,
ph.price, ph.recorddate, ph.automan, ph.username, ph.ipaddress, ph.notes, ph.filter_type, ph.change_type,
ROW_NUMBER() OVER (PARTITION BY (CAST(ph.building AS VARCHAR(25)) + ph.unit) ORDER BY ph.recorddate) AS rownum
FROM otherDB.dbo.contract c
INNER JOIN otherDB.dbo.buildinginfo bi
ON bi.building = c.building
INNER JOIN otherDB.dbo.rowinfo ri
ON ri.building = c.building
INNER JOIN otherDB.dbo.pricehistory ph
ON ph.building = c.building
AND ph.unit = c.unit
INNER JOIN otherDB.dbo.unitinfo ui
ON ui.unitname = c.unitname
INNER JOIN inventory i
ON i.inventory_name = c.unit
INNER JOIN inventory_grouping igUnitType
ON igUnitType.inventory_grouping_id = i.inventory_grouping_id
AND igUnitType.grouping_name = c.unitname
INNER JOIN inventory_grouping igModel
ON igModel.inventory_grouping_id = igUnitType.parent_grouping_id
AND igModel.grouping_name = ISNULL(ui.modelname,ui.unitname)
INNER JOIN inventory_grouping igSection
ON igSection.inventory_grouping_id = igModel.parent_grouping_id
AND igSection.grouping_name = CASE
WHEN ri.rowname IS NULL OR ri.rowname = '' THEN CAST(ri.row AS VARCHAR(10))
ELSE ri.rowname
END
INNER JOIN inventory_grouping igBuilding
ON igBuilding.inventory_grouping_id = igSection.parent_grouping_id
WHERE igBuilding.grouping_name = CASE
WHEN bi.buildingname IS NULL OR bi.buildingname = '' THEN CAST(bi.building AS VARCHAR(10))
ELSE bi.buildingname
END
)
SELECT currow.rownum, currow.inventory_id, currow.price, currow.automan AS change_action,
currow.filter_type AS change_type, currow.change_type AS change_method, currow.ipaddress AS ip_address,
currow.recorddate AS from_date, currow.notes, nextrow.recorddate AS thru_date,
(
SELECT party_id
FROM party_login
WHERE username = currow.username
) AS party_id_initiated
FROM price_history currow
LEFT JOIN price_history prevrow
ON currow.rownum = prevrow.rownum + 1
AND currow.inventory_id = prevrow.inventory_id
LEFT JOIN price_history nextrow
ON currow.rownum = nextrow.rownum - 1
AND currow.inventory_id = nextrow.inventory_id
ORDER BY currow.inventory_id, currow.recorddate ASC
I know to best provide solutions you guys usually need CREATE TABLE and INSERT scripts, but there are a lot of tables here. Nevertheless, if need be I can provide.
I have attached a copy of the Actual Execution Plan for the above query. You'll have to remove the .txt extension to use it.
Any suggestions you can provide will be greatly appreciated.
April 3, 2008 at 12:35 am
Ok, I haven't looked at the entire plan in detail (it's huge)
Here are some very quick index suggestions. Try them out, but no guarentees.
I don't know what indexes you have or what other queries run on those tables. I don't know the data distribution.
Inventory_grouping - NC on inventory_grouping_id, include parent_grouping_id, SITEDB_import_inv, grouping_name
inventory - NC on inventory_grouping_id include SITEDB_import_inv, inventory_name, inventory_id
Inventory - NC on inventory_name
Contract - widen the NC IDX_contract_unit to include building and unitname
PriceHistory - index on building
UnitInfo - index on unitname
Rowinfo - index on building
Buildinginfo - index on building
I haven't gone too much into the sql statement itself.
Could you explain a bit more what you have and what you want. It could be that someone here has a better solution...
One other thing. the functions you have on joins of filters (CASE, isnull, CAST) will hinder index use.
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
April 3, 2008 at 6:38 am
Without looking at this in a lot of detail, I would break it into two pieces if your application architecture allows it.
The CTE runs fast, so why not execute that portion of the query into a temp table with a clustered index on the fields you are doing your subsequent joins on?
Since the joins in the main query are on the rownum fields, you are probably ending up with table scans everywhere because the CTE does not materialize. So, you will probably end up with the best performance if you dump it into an indexed temp table. You could also try using SELECT TOP 100 PERCENT and an ORDER BY in the CTE to get the database engine to pre-sort, but I think using a temp table will end up giving you the best performance.
April 3, 2008 at 7:28 am
Why not try replacing your subquery
(
SELECT
PARTY_ID
FROM
PARTY_LOGIN
WHERE
USERNAME = CURROW.USERNAME
) AS PARTY_ID_INITIATED
With a join
Select
....,
PL.Party_Id as Party_ID_Initiated
FROM
PRICE_HISTORY CURROW JOIN
PARTY_LOGIN PL ON
PL.USERNAME = CURROW.USERNAME LEFT JOIN
PRICE_HISTORY PREVROW ON
CURROW.ROWNUM = PREVROW.ROWNUM + 1 AND
CURROW.INVENTORY_ID = PREVROW.INVENTORY_ID LEFT JOIN
PRICE_HISTORY NEXTROW ON
CURROW.ROWNUM = NEXTROW.ROWNUM - 1 AND
CURROW.INVENTORY_ID = NEXTROW.INVENTORY_ID
The subquery does RBAR (Row by Row) processing while the join will process it as a set. I think this should give you the same results and only hit the Party_Login table once instead of multiple times
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 3, 2008 at 8:48 am
WOW! You guys (and girl) are good!
I implemented the temp table suggestion as well as substituting the sub-query for a JOIN and viola... 800 records in 1 sec. I intended on taking Gail's suggestions on indexing and applying them to the temp table but with the performance the way it is, I don't think it's necessary.
Thank you all for the quick and useful responses. This is exactly why I love this site!
April 4, 2008 at 12:08 am
los (4/3/2008)
I intended on taking Gail's suggestions on indexing and applying them to the temp table but with the performance the way it is, I don't think it's necessary.
Maybe not now, but what about when the row count increases?
Good performance = Good code + good indexes
If I may suggests, look at indexes for the base tables. They'll probably help for other queries than this one and may get this one down to sub second.
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
April 4, 2008 at 5:54 am
I have to agree with Gail. If there are indexes that will help regularly run queries it is a "best" practice to add them.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply