September 20, 2010 at 10:48 pm
Hello,
I have a query that I run against a View to get product Sellers and count of products for each Seller in a specific category. It works very well. The problem is that I need to pass more parameters in the WHERE filter from other tables. But if I add these table and fields to the View, it requires to add the same fields in the Group By (because I have Count_big(*) in the select list). If I add fields to the select list and Group By - I get wrong counts and I am not able to create Unique Clustered Index on this View. What is the best way to handle this? any help is appreciated. I am posting my query along with View and Index.
Eugene.
Query:
with keys as (
select getproductssellers.productslocation_cid,
count(distinct getproductssellers.products_pid) as CNT
from GetProductsSellers WITH (NOEXPAND)
WHERE getproductssellers.products_subcategory=130
group by getproductssellers.productslocation_cid
)
select customer.businessname as Seller, productslocation_cid, CNT
from keys
inner join customer on productslocation_cid=customer.cid
order by CNT desc
View:
USE [WebApp]
GO
/****** Object: View [dbo].[GetProductsSellers] Script Date: 09/20/2010 21:48:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[GetProductsSellers]
WITH SCHEMABINDING
AS
SELECT dbo.Products.subcategory AS Products_subcategory, dbo.ProductsLocation.cID AS Productslocation_cid, dbo.Products.pID AS Products_pid, COUNT_BIG(*)
AS CNT
FROM dbo.Products INNER JOIN
dbo.ProductsLocation ON dbo.Products.pID = dbo.ProductsLocation.pID
GROUP BY dbo.Products.subcategory, dbo.ProductsLocation.cID, dbo.Products.pID
Index:
USE [WebApp]
GO
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
/****** Object: Index [_dta_index__dta_mv_0_8066_Products_c_8_1285683728__K1_K2_K3] Script Date: 09/20/2010 21:48:37 ******/
CREATE UNIQUE CLUSTERED INDEX [_dta_index__dta_mv_0_8066_Products_c_8_1285683728__K1_K2_K3] ON [dbo].[GetProductsSellers]
(
[Products_subcategory] ASC,
[Productslocation_cid] ASC,
[Products_pid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
September 20, 2010 at 10:54 pm
I'm curious, but what's wrong with adding additional filters after the view's built? This is a short form of your code, but it's to help me understand what you're trying to do.
CREATE View GroupView
AS
SELECT
GroupID,
SUM( Value1) AS SumVal
FROM
TableA
GO
CREATE Proc GetData
AS
SELECT
ut.UserName,
v.GroupID,
v.SumVal
FROM
UserTable AS ut
JOIN
GroupView AS v
ON ut.groupID = v.groupId
WHERE
ut.active = 1
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 20, 2010 at 10:58 pm
If I join other tables and add filters after the View it significantly slows down performance of this query.
Thank you.
September 20, 2010 at 11:03 pm
You'll want to try it without the NOEXPAND then, or we'll need to look at optimizing it. Either way, to get the correct values, you're subquerying and then reconnecting to it. Whether that's by View, CTE, dump to temptable, actual subquery, or Cross Apply... you've got to subquery the data.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 20, 2010 at 11:16 pm
My test environment has SQL 2005 developer's edition, but my production one has Workgroup edition. So I have to use (NOEXPAND) for production environment. Where do I add JOIN another table (productslocationattr) and additional WHERE filters: inside the CTE or outside? If I try to add it outside, it gives me an error.
set statistics time on
Go
with keys as (
select getproductssellers.productslocation_cid,
count(distinct getproductssellers.products_pid) as CNT
from GetProductsSellers WITH (NOEXPAND)
--inner join productslocationattr on getproductssellers.products_pid = productslocationattr.pid
WHERE getproductssellers.products_subcategory=130
group by getproductssellers.productslocation_cid
)
select customer.businessname as Seller, productslocation_cid, CNT
from keys
inner join customer on productslocation_cid=customer.cid
--inner join productslocationattr on getproductssellers.products_pid = productslocationattr.pid
order by CNT desc
September 20, 2010 at 11:31 pm
I am sorry, in the previous reply I posted the wrong JOIN. Here is correct oneI am trying to do. Only by adding 2 JOINs, execution time increases from 45ms tp 831 ms.
set statistics time on
Go
with keys as (
select getproductssellers.productslocation_cid,
count(distinct getproductssellers.products_pid) as CNT
from GetProductsSellers WITH (NOEXPAND)
inner join productslocation on getproductssellers.products_pid = productslocation.pid
inner join productslocationattr on productslocation.laid = productslocationattr.laid
WHERE getproductssellers.products_subcategory=130
group by getproductssellers.productslocation_cid
)
select customer.businessname as Seller, productslocation_cid, CNT
from keys
inner join customer on productslocation_cid=customer.cid
--inner join productslocation on products_pid = productslocation.pid
--inner join productslocationattr on productslocation.laid = productslocationattr.laid
order by CNT desc
September 20, 2010 at 11:31 pm
elayevskiy (9/20/2010)
My test environment has SQL 2005 developer's edition, but my production one has Workgroup edition. So I have to use (NOEXPAND) for production environment.
Ahhh. That's needed to force the index, as far as I understand, not to force the view's usage at all. See this: http://msdn.microsoft.com/en-us/library/ms187373.aspx
Where do I add JOIN another table (productslocationattr) and additional WHERE filters: inside the CTE or outside? If I try to add it outside, it gives me an error.
set statistics time on
Go
with keys as (
select getproductssellers.productslocation_cid,
count(distinct getproductssellers.products_pid) as CNT
from GetProductsSellers WITH (NOEXPAND)
--inner join productslocationattr on getproductssellers.products_pid = productslocationattr.pid
WHERE getproductssellers.products_subcategory=130
group by getproductssellers.productslocation_cid
)
select customer.businessname as Seller, productslocation_cid, CNT
from keys
inner join customer on productslocation_cid=customer.cid
--inner join productslocationattr on getproductssellers.products_pid = productslocationattr.pid
order by CNT desc
Yeah, this is going to be problematic. You're grouping your information up by productslocation, and not bringing the pid out in the with above it. Now you're trying to connect to more granular data, which is going to end up with some other strange and interesting results.
You'll either need to bring the getproductssellers.products_pid out in the with clause, or take a serious look at what result set you're trying to get out of the information, and the granularity that you're storing said information at.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 20, 2010 at 11:40 pm
If I bring products_pid into Select part, it will require to add it to the Group By. Then I am getting very strange results.
September 20, 2010 at 11:49 pm
I'm afraid to help you puzzle out your solution, I'm going to need more from you. DDL's of all the base tables, sample data, and expected results from the sample data. Unfortunately there's too many assumptions to make about what repeats where in your data to discern a solution from what's there so far.
If that's not available to you, maybe someone else will see something I can't in this.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 20, 2010 at 11:58 pm
I will gather all the information. Should I post it here or provide it in some other way?
September 21, 2010 at 12:01 am
You can post it here, or if it's very large, there's an 'edit attachments' button at the bottom of all posts. You can copy/paste your script to MSWord, notepad, wherever and attach it there.
If you need some help in organizing/extracting it, check out the link at the top of my signature.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 22, 2010 at 10:10 pm
Craig,
I tried to play with this query a bit more and you are absolutely right
that first, I have to subquery outside of With, and second, that I have to bring pid out in the With. Then I can reconnect using pid with other tables or View and apply required WHERE filters. I run into a problem that I don't see a solution for. If I Select pid - I have to add pid in the Group By and then I get very strange results. What options do I have? How can I get pid out of from the With ?
Thank you,
Eugene.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply