June 12, 2013 at 4:07 am
Hi,
I have a query that now actually does exactly what I want it to and performs well. But the site has still low traffic and small amounts of data.
I have never seen examples of queries with more than 2 "select levels" before, here I have "3 select levels", combined with 6 joins.
Do you think this is going to be a problem as the site grows to tables with about 10 to 100 thousands rows ?
SELECT DistinctCompanyCount, DenseGroup, CatName, CatId, CompanyId, CompanyName, PropertyId, Status, Day, LinkFormat, Updated, VardeFloat1
FROM
(
SELECT MAX(DenseGroup) OVER(PARTITION BY PropertyId) AS DistinctCompanyCount, DenseGroup, CatName, CatId, CompanyId, CompanyName, PropertyId, Status, Day, LinkFormat, Updated, VardeFloat1
FROM
(SELECT
DENSE_RANK() OVER (ORDER BY Categories.CatName,CompProp.VardeFloat1,CP2.Updated DESC,Company.CompanyId) AS DenseGroup,
Categories.CatNamn,Categories.CatId,Company.CompanyId,Company.CompanyName,CompProp.PropertyId,Days.Status, Days.Day,OrgInfo.LinkFormat, CP2.Updated,CompProp.VardeFloat1
FROM Company
INNER JOIN CompProp ON Company.CompanyId = CompProp.CompanyId AND CompProp.PropertyId = 2 AND CompProp.State = 1
INNER JOIN CompCat ON CompCat.CompanyId = CompProp.CompanyId
INNER JOIN Categories ON CompCat.CatId = Categories.CatId
INNER JOIN OrgInfo ON OrgInfo.UserId = Company.OrgId
LEFT OUTER JOIN Days ON Day.CompanyId = Company.CompanyId AND Days.Day >= '2013-06-10 00:00:00' AND Days.Day < '2013-06-15 00:00:00'
LEFT OUTER JOIN CompProp AS CP2 ON Company.CompanyId = CP2.CompanyId AND CP2.PropertyId = 1
WHERE Company.State = 1)
AS PagingQ1
)
AS PagingQ2
WHERE RankGrupp BETWEEN 1 AND 20
ORDER BY CatName,VardeFloat1,Updated DESC,CompanyId,Day
June 12, 2013 at 4:26 am
Hard to say as it also depends on your indexes. My experience however is that derived tables can cause performance penalties. As soon as you filter on a derived table SQL cannot use any indexes. If the derived table contains lots of rows this can be a problem.
When i have complex queries I tend to use SP's and try to split the query in multiple result sets stored in temp tables. You can put extra indexes on the temp tables to get the best performance.
__________________
MS-SQL / SSIS / SSRS junkie
Visit my blog at dba60k.net
June 12, 2013 at 5:02 am
Your query looks fine to me apart from the syntax errors. SQL Server is quite adept at optimising out query nesting.
SELECT
DistinctCompanyCount,
DenseGroup, CatName, CatId, CompanyId, CompanyName, PropertyId,
Status, Day, LinkFormat, Updated, VardeFloat1
FROM
(
SELECT
DistinctCompanyCount = MAX(DenseGroup) OVER(PARTITION BY PropertyId),
DenseGroup, CatName, CatId, CompanyId, CompanyName, PropertyId,
Status, Day, LinkFormat, Updated, VardeFloat1
FROM
(
SELECT
DenseGroup = DENSE_RANK() OVER (ORDER BY cg.CatName, cp1.VardeFloat1, CP2.Updated DESC, co.CompanyId),
cg.CatNamn, -- <<<--- error here
cg.CatId, co.CompanyId, co.CompanyName, cp1.PropertyId,
Days.Status, Days.Day, OrgInfo.LinkFormat, CP2.Updated, cp1.VardeFloat1
FROM Company co
INNER JOIN CompProp cp1
ON co.CompanyId = cp1.CompanyId
AND cp1.PropertyId = 2
AND cp1.State = 1
INNER JOIN CompCat ON CompCat.CompanyId = cp1.CompanyId
INNER JOIN Categories cg ON CompCat.CatId = cg.CatId
INNER JOIN OrgInfo ON OrgInfo.UserId = co.OrgId
LEFT OUTER JOIN [Days]
ON [Day].CompanyId = co.CompanyId -- <<<--- error here
AND [Days].Day >= '2013-06-10 00:00:00'
AND [Days].Day < '2013-06-15 00:00:00'
LEFT OUTER JOIN CompProp CP2
ON co.CompanyId = CP2.CompanyId
AND CP2.PropertyId = 1
WHERE co.[State] = 1
)
AS PagingQ1
)
AS PagingQ2
WHERE RankGrupp BETWEEN 1 AND 20 -- <<<--- error here: where is 'RankGrupp' from?
ORDER BY CatName, VardeFloat1, Updated DESC, CompanyId, Day
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
June 12, 2013 at 6:15 am
Thank you very much for your answers!
...The syntax errors are because I quickly renamed all column and tables from swedish before posting, so it would make a little more sense.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply