No Performance gain with Indexed view ?
-
Hi Guys,
Note: Ignore the smileys in between the query. They are getting added for every closing paranthesis.
Recently I have read articles (http://www.sql-server-performance.com) on how an indexed view can really boost up the application performance. So I started to search for one of my procedure that nearly takes some 1615.2 milliseconds (trial and average of 5 executions) to execute. I opted this stored procedure as it involves more joins. My actual query in the stored procedure is as follows;
SELECT CM.CustomerCode, CustomerName, Street1, Street2, Country, State, City,
ZipCode, Phone, Fax, Email, AddressType
FROM tblCustomerMaster CM
LEFT OUTER JOIN
(
SELECT CA.CustomerCode, StreetAddress1 AS Street1, StreetAddress2 As Street2,
C.LongName AS Country, S.LongName AS State, CY.CityName As City,
Zipcode, Phone1 As Phone, Fax, Email, CA.AddressType
FROM tblCustomerAddress CA
INNER JOIN
(
SELECT MAX(AddressType) As AddressType, Max(AddressID) As AddressID FROM tblCustomerAddress
WHERE IsDefault = 1
GROUP BY CustomerCode
 
) As CAF ON CA.AddressID = CAF.AddressIDINNER JOIN Country C ON CA.CountryID = C.CountryID
INNER JOIN States S ON CA.StateID = S.StateID
INNER JOIN Cities CY ON CA.CityID = CY.CityID
WHERE IsDefault = 1
 
) CAP ON CM.CustomerCode = CAP.CustomerCodeI took the bolded portion of the above query and created a view as follows;
CREATE VIEW dbo.vwCustomerAddress WITH SCHEMABINDING
AS
SELECT CA.AddressID, CA.CustomerCode, CA.StreetAddress1 AS Street1, CA.StreetAddress2 AS Street2, C.longname AS Country, S.longname AS State,
CY.cityname AS City, CA.Zipcode, CA.Phone1 AS Phone, CA.Fax, CA.Email, CA.AddressType
FROM dbo.tblCustomerAddress CA INNER JOIN
dbo.country C ON CA.CountryID = C.countryid INNER JOIN
dbo.states S ON CA.StateID = S.stateid INNER JOIN
dbo.cities CY ON CA.CityID = CY.cityid
WHERE CA.IsDefault = 1
Then, i created a unique clustered index over the AddressID column of this view as follows;
CREATE UNIQUE CLUSTERED INDEX idxCustomerAddress ON vwCustomerAddress(AddressID)
Having created this indexed view, i modified my original query as follows;
SELECT CM.CustomerCode, CustomerName, Street1, Street2, Country, State, City,
ZipCode, Phone, Fax, Email, AddressType
FROM tblCustomerMaster CM
LEFT OUTER JOIN
(
SELECT CustomerCode, Street1, Street2,
Country, State, City,
Zipcode, Phone, Fax, Email, CA.AddressType
FROM vwCustomerAddress CA
INNER JOIN
(
SELECT MAX(AddressType) As AddressType, Max(AddressID) As AddressID FROM tblCustomerAddress
WHERE IsDefault = 1
GROUP BY CustomerCode
 
) As CAF ON CA.AddressID = CAF.AddressID 
) CAP ON CM.CustomerCode = CAP.CustomerCodeThen i have given a 5 trial execution and the average of it was around 1630.6 milliseconds (15 milliseconds more than my original one).
Please let me know where i'm wrong
Hemant
-
To see a performance difference, time the query that you replaced and the view separately and compare before you plug it into your stored proc. Do all the join fields have indexes on them? Also is IsDefault a boolean column? if yout does it have any index? You could also provide index hints and force the query to use certain indexes.
******************
Dinakar Nethi
Life is short. Enjoy it.
****************** -
Hi Dinakar,
Thanx for looking into my issue. Yeah, I have tested my original query and the indexed one before plugging into the procedure. And the average time consumption of my Original query is 1615.2 milliseconds of five trial execution. And that of the indexed query is 1630.6 milliseconds for the same number of trial runs.
Do all the join fields have indexes on them?
I have joined three tables to tblCustomerAddress table. They are country, states and cities.
- tblCustomerAddress has a primary key on AddressID column
- country has a primary key on countryid column
- states has a primary key on stateid column
- cities has a primary key on cityid column
Also is IsDefault a boolean column?
Yes, IsDefault is a boolean column
if yout does it have any index?
Didn't understood this
You could also provide index hints and force the query to use certain indexes.
How do i do this and what is the advantage in doing this way. Sorry i'm 100% new to the indexed view topic.
Regards,
Hemant
-
Hemant,
I was looking at your code and to me it is somewhat confusing.
Your original query contains 2 derived tables, both based on the same table dbo.tblCustomerAddress.
Both queries also restrict the selection from this table to IsDefault = 1.
First question, can a given Customer (Unique CustomerCode have more than one address recorded coded as
IsDefault = 1? In your second derived table, one of the columns is max(AddressType) but this value is ignored
and the value of the record that matches the max(AddressID) for a given CustomerCode is used.
Without seeing some test data and the expected output, it is difficult to help you tune the query or procedure.
I would definately look at a rewrite of the procedure, but can't give you much in the way of how not knowing
the data in your system. It looks to me that there is extra processing that may be unnecessary.
Thanks,
Lynn
-
Thanks Lynn Pettis for looking into the issue.
First question, can a given Customer (Unique CustomerCode have more than one address recorded coded as
IsDefault = 1?
Let me make the picture more clearer.
- I have two tables and they are tblCustomerMaster and tblCustomerAddress.
- In the tblCustomerMaster table, CustomerCode is the Primary Key and in the tblCustomerAddress table, AddressID is the Primary Key and the CustomerCode is the Foreign Key.
- The tblCustomerAddress table is capable of maintaining more than one address information for a given customer.
- I maintain two types of Addresses identified using the AddressType Column. AddressType = 1 means BILLING ADDRESS and AddressType = 2 means SHIPPING ADDRESS.
Now, the required output from the query is as follows;
- Needs to pull Customer Name (from tblCustomerMaster table using CustomerCode column as the join column)
- Needs to pull address information using the columns StreetAddress1, StreetAddress2, CountryID, StateID, CityID, Zipcode, Phone1, Fax, Email, AddressType (from tblCustomerAddress table. Here Address ID is the Primary Key and the CustomerCode is the Foreign Key)
- Instead of pulling the CountryID, StateID or CityID, i need to pull the CountryName, StateName and CityName from countries, states and cities table respectively.
- countries table has CountryID as the primary key. States table has stateid as the primary key and cities table has cityid as the primary key
- Note: Only one address information has to be pulled for a Customer.
- A default address is identified using the IsDefault column in the tblCustomerAddress table.
- If the Customer has DEFAULT SHIPPING ADDRESS, then the query should include this address for a customer.
- If the Customer has no DEFAULT SHIPPING ADDRESS but has DEFAULT BILLING ADDRESS, then the query should include DEFAULT BILLING address for a customer.
- If no DEFAULT BILLING / SHIPPING ADDRESS is found, then the relevant address columns should hold null values.
- Note: All the Customers has to be pulled irrespective of either he has records in the tblCustomerAddress table or not.
Hope my query requirement is more clearer now.
In your second derived table, one of the columns is max(AddressType) but this value is ignored
Yes, from the above requirement specification you can see that first the SHIPPING ADDRESS is sought and thatz why i have added this clause.
If anyone have a better idea of pulling the information more efficiently, then please help me out. Thanx again for trying to help me.
Regards,
Hemant
-
Were the test performed straight on the server (to eliminate network congestions)? Can you conduct some more tests ( about 1000...).Were there any caches freed (DBCC FREEPROCCACHE)? Statistics are up to date?
Are the indexes used in the queryplan?
Not sure if this query is any better.
SELECT CM.CustomerCode, CM.CustomerName
, DefaultAddressCompleted.Street1, DefaultAddressCompleted.Street2
, DefaultAddressCompleted.Country
, DefaultAddressCompleted.State
, DefaultAddressCompleted.City
, DefaultAddressCompleted.ZipCode
, DefaultAddressCompleted.Phone
, DefaultAddressCompleted.Fax
, DefaultAddressCompleted.Email
, DefaultAddressCompleted.AddressType
FROM
tblCustomerMaster CM
LEFT OUTER JOIN /*fill with NULLS if no default found*/
(
/*completing default addresses*/
SELECT CA.CustomerCode, CA.StreetAddress1 AS Street1, CA.StreetAddress2 As Street2,
C.LongName AS Country, S.LongName AS State, CY.CityName As City,
CA.Zipcode, CA.Phone1 As Phone, CA.Fax, CA.Email, CA.AddressType
FROM tblCustomerAddress CA
INNER JOIN
(
/*choose the one with the highest AddressType in case of a tie*/
SELECT CustomerCode,MAX(AddressType) As AddressType
FROM tblCustomerAddress
WHERE IsDefault = convert(bit,1) /*helping the optimizer a bit since this is a bit field*/
GROUP BY CustomerCode
) DefaultAddress on CA.CustomerCode=DefaultAddress.CustomerCode
and DefaultAddress.AddressType=CA.AddressType
/*max addressid not needed since there is only
one default per addresstype per customer*/
and CA.IsDefault=convert(bit,1)
/*might change join CA.CountryID to DefaultAddress.CountryID*/
INNER JOIN Country C ON CA.CountryID = C.CountryID
INNER JOIN States S ON CA.StateID = S.StateID
INNER JOIN Cities CY ON CA.CityID = CY.CityID
) DefaultAddressCompleted
on CM.CustomerCode=DefaultAddressCompleted.CustomerCode
-
As far as no performance gain.. I'd say consider when/why you would get a gain from an indexed view, and if that applies here.
first, by using a view, you get the benefit of it already being compiled, but you're already getting that benefit by virtue of the stored proc.
second, an indexed view will allow a sql to take advantage of an index (or a better index) where it wasn't before. Chances are pretty good that it was already using the index on tblCustomerAddress.AddressID, so any benefit from the index on AddressID in the view would be minimal if at all.
and i'm not sure what the other person was getting at, but you wouldn't want to waste an index on a boolean column.
Kindest Regards,Chris Lockhart
infinitejellyroll.com -
Hi Chris Lockhart,
Thanx for looking into the issue
As far as no performance gain.. I'd say consider when/why you would get a gain from an indexed view, and if that applies here.
I have read an article on Indexed view at (http://www.sql-server-performance.com) on how an indexed view can really boost up the query performance. And i also came across that if a query happens to have more than a couple of joins and being used a number of times, then it is a good candidate of becoming an Indexed view. Also, how the gain is obtained is that the indexed view does store the data physically and when queries does uses the join again and again, thereby seeing a gain. And this is the reason why i have opted an indexed view for one of my query. (the one discussed previously)
i'm not sure what the other person was getting at, but you wouldn't want to waste an index on a boolean column.
No, i haven't indexed a boolean column.
Now the real question is Chris, Why i'm seeing a 15 millisecond gain in case of a query that performs normally than an indexed one.
One more information, My tblCustomerMaster table has 10595 records and tblCustomerAddress table has 27948 records.
Regards,
Hemant.
-
with due respect I think you're missing the point. To use an indexed view to tune a query you don't then add the view in the query - the idea is that you can use indexed views to create more than one clustered index on a table, to be honest indexing a single column within a view isn't going to do much for you - I normally use an indexed view to create a covered clustered index for a table which I already have a clustered index on.
Anyway the idea of the view is that your original query makes use of the additional clustered index created by the view. The downside is persisted data in the view.
To be blunt picking up an idea without understanding what it does is only going to lead to grief!! I've only very rarely used this technique, normally better indexing on the base tables will give you performance improvements.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Hemant,
Not having your table layouts or test data makes it a little hard to help you with your problem, however, based on your posts I have tried to come up with an alternative for you to test and play with in your test environment. I'd be interested in hearing how it works; good, bad, or indifferent to how you currently are doing it.
Here is my code, and it does not use an indexed view:
SELECT
cm.CustomerCode,
cm.CustomerName,
ca.StreetAddress1,
ca.StreetAddress2,
ct.CityName as City,
st.LongName as State,
cn.LongName as Country,
ca.Zipcode,
ca.Phone1 as Phone,
ca.Fax,
ca.Email,
ca.AddressType
FROM
dbo.tblCustomerMaster cm
left outer join dbo.tblCustomerAddress ca
on (cm.CustomerCode = ca.CustomerCode
and ca.AddressID = (select top 1
ca1.AddressID
from
dbo.tblCustomerAddress ca1
where
ca1.CustomerCode = cm.CustomerCode
and ca1.IsDefault = 1
order by
ca1.AddressType desc,
ca1.AddressID desc))
left outer join dbo.Country cn
on (cn.CountryID = ca.CountryID)
left outer join dbo.States st
on (st.StateID = ca.StateID)
left outer join dbo.Cities ct
on (ct.CityID = ca.CityID)
You may also want to look at your current index structure on these tables, as changes there may also help.
hth,
Lynn
-
If the query above doesn't work, here is another one to try:
SELECT
cm.CustomerCode,
cm.CustomerName,
ca.StreetAddress1,
ca.StreetAddress2,
ct.CityName as City,
st.LongName as State,
cn.LongName as Country,
ca.Zipcode,
ca.Phone1 as Phone,
ca.Fax,
ca.Email,
ca.AddressType
FROM
dbo.tblCustomerMaster cm
left outer join dbo.tblCustomerAddress ca
on (cm.CustomerCode = ca.CustomerCode
and ca.AddressID = ( select top 1
ca1.AddressID
from
dbo.tblCustomerAddress ca1
where
ca1.CustomerCode = cm.CustomerCode
and ca1.IsDefault = 1
order by
ca1.AddressType desc,
ca1.AddressID desc))
left outer join dbo.Country cn
on (cn.CountryID = ca.CountryID)
left outer join dbo.States st
on (st.StateID = ca.StateID)
left outer join dbo.Cities ct
on (ct.CityID = ca.CityID)
SELECT
cm.CustomerCode,
cm.CustomerName,
dca.StreetAddress1,
dca.StreetAddress2,
dca.CityName as City,
dca.LongName as State,
dca.LongName as Country,
dca.Zipcode,
dca.Phone1 as Phone,
dca.Fax,
dca.Email,
dca.AddressType
FROM
dbo.tblCustomerMaster cm
left outer join (
select
ca.CustomerCode as CustomerCode,
ca.StreetAddress1 as Street1,
ca.StreetAddress2 as Street2,
ct.CityName as City,
st.LongName as State,
cn.LongName as Country,
ca.Zipcode as Zipcode,
ca.Phone1 as Phone,
ca.Fax as Fax,
ca.Email as Email,
ca.AddressType as AddressType
from
dbo.tblCustomerAddress ca
inner join dbo.Country cn
on (cn.CountryID = ca.CountryID)
inner join dbo.States st
on (st.StateID = ca.StateID)
inner join dbo.Cities ct
on (ct.CityID = ca.CityID)
where
ca.AddressID = ( select top 1
ca1.AddressID
from
dbo.tblCustomerAddress ca1
where
ca1.CustomerCode = ca.CustomerCode
and ca1.IsDefault = 1
order by
ca1.AddressType desc,
ca1.AddressID desc)
) dca
on (cm.CustomerCode = dca.CustomerCode)
-
Opps. Forgot a few changes to the second query. Here is a corrected version of the second one:
SELECT
cm.CustomerCode,
cm.CustomerName,
dca.Street1,
dca.Street2,
dca.City,
dca.State,
dca.Country,
dca.Zipcode,
dca.Phone,
dca.Fax,
dca.Email,
dca.AddressType
FROM
dbo.tblCustomerMaster cm
left outer join (
select
ca.CustomerCode as CustomerCode,
ca.StreetAddress1 as Street1,
ca.StreetAddress2 as Street2,
ct.CityName as City,
st.LongName as State,
cn.LongName as Country,
ca.Zipcode as Zipcode,
ca.Phone1 as Phone,
ca.Fax as Fax,
ca.Email as Email,
ca.AddressType as AddressType
from
dbo.tblCustomerAddress ca
inner join dbo.Country cn
on (cn.CountryID = ca.CountryID)
inner join dbo.States st
on (st.StateID = ca.StateID)
inner join dbo.Cities ct
on (ct.CityID = ca.CityID)
where
ca.AddressID = ( select top 1
ca1.AddressID
from
dbo.tblCustomerAddress ca1
where
ca1.CustomerCode = ca.CustomerCode
and ca1.IsDefault = 1
order by
ca1.AddressType desc,
ca1.AddressID desc)
) dca
on (cm.CustomerCode = dca.CustomerCode)
-
Is the indexed view even being considered? Check the query plan (Ctrl+L in Query Analyzer) and look to see if the index in view is even being used.
Are you running Enterprise Edition? If not, the view's indexes will only be considered for use if you apply the NOEXPAND hint to the query.
SELECT field, field, field FROM dbo.MyIndexedView WITH (NOEXPAND) WHERE ...
-Eddie
Eddie Wuerch
MCM: SQL -
Thanx colin Leversuch-Roberts for your directions
I don't know anything about this covering index but will surely look into this.Thanx Lynn Pettis for your queries
. I will give a try on these queries and let you know how it performs. Thanx again man Hi Eddie Wuerch and thanx for looking into the issue
. Is the indexed view even being considered? Check the query plan (Ctrl+L in Query Analyzer) and look to see if the index in view is even being used.
Yes Eddie, the index in the view is considered and i have verified this from the query analyzer show plan.
Are you running Enterprise Edition? If not, the view's indexes will only be considered for use if you apply the NOEXPAND hint to the query.
No, I'm using Developer Edition.... OK I will make use of the NOEXPAND option in the query with the view and let you know more. Thanx again for your views
Regards,
Hemant.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply