Can I make my clustered index scan cheaper?

Hi,
I have a query here that I would like to make cheaper in terms of query
cost. Here's the query:
________________________________________
__________________________
Select distinct
vw.CustomerID,
vw.BranchID,
vw.SourceOfBusiness ,
vw.SpkCustNo,
vw.Surname,
vw.Initials,
vw.Incode,
vw.Outcode,
vw.Updated
>From tbl_Insurer i
Inner join tbl_SpkInsurer si on i.ID = si.InsurerID
Inner join tbl_Scheme s on si.ID = s.SpkInsurerID
Inner join vw_SearchCustomerPolicy vw on vw.SchemeID = s.ID
Where i.id = @.InsurerID
and convert(datetime, convert(char(11), vw.RenewalDate, 103), 103) >=
@.StartDate
and (@.PolicyRef is null or vw.PolicyRef = @.PolicyRef)
and (@.ShowSSU = 1 or vw.BranchID <> @.SSUID)
and (@.BranchID is null or vw.BranchID = @.BranchID)
and (@.SpkCustNo is null or vw.SpkCustNo = @.SpkCustNo)
and (@.Surname is null or vw.Surname like @.Surname)
and (@.Initials is null or vw.Initials = @.Initials)
and (@.Incode is null or vw.Incode = @.Incode)
and (@.Outcode is null or vw.Outcode = @.Outcode)
and (@.SpkPolNo is null or vw.SpkPolNo = @.SpkPolNo)
Order by vw.Surname, vw.Initials, vw.BranchID
________________________________________
__________________________
This takes 14 seconds to return 25 or so rows, I would like to knock
the return time down to under 5 seconds. When I display the Execution
Plan, I can see that the most expensive item is a clustered index scan
on the vw_SearchCustomerPolicy (this is an indexed view) this is 51%.
Can I know alter my query to reduce that cost?
Thanks
qhWhat is the clustered index?
A clustered index s is faster than a scan but relies on only looking
at a portion of the index.
If the renewaldate were the clustered index that may help.
Martin
Scott wrote:
> Hi,
> I have a query here that I would like to make cheaper in terms of query
> cost. Here's the query:
> ________________________________________
__________________________
> Select distinct
> vw.CustomerID,
> vw.BranchID,
> vw.SourceOfBusiness ,
> vw.SpkCustNo,
> vw.Surname,
> vw.Initials,
> vw.Incode,
> vw.Outcode,
> vw.Updated
> Inner join tbl_SpkInsurer si on i.ID = si.InsurerID
> Inner join tbl_Scheme s on si.ID = s.SpkInsurerID
> Inner join vw_SearchCustomerPolicy vw on vw.SchemeID = s.ID
> Where i.id = @.InsurerID
> and convert(datetime, convert(char(11), vw.RenewalDate, 103), 103) >=
> @.StartDate
> and (@.PolicyRef is null or vw.PolicyRef = @.PolicyRef)
> and (@.ShowSSU = 1 or vw.BranchID <> @.SSUID)
> and (@.BranchID is null or vw.BranchID = @.BranchID)
> and (@.SpkCustNo is null or vw.SpkCustNo = @.SpkCustNo)
> and (@.Surname is null or vw.Surname like @.Surname)
> and (@.Initials is null or vw.Initials = @.Initials)
> and (@.Incode is null or vw.Incode = @.Incode)
> and (@.Outcode is null or vw.Outcode = @.Outcode)
> and (@.SpkPolNo is null or vw.SpkPolNo = @.SpkPolNo)
> Order by vw.Surname, vw.Initials, vw.BranchID
> ________________________________________
__________________________
> This takes 14 seconds to return 25 or so rows, I would like to knock
> the return time down to under 5 seconds. When I display the Execution
> Plan, I can see that the most expensive item is a clustered index scan
> on the vw_SearchCustomerPolicy (this is an indexed view) this is 51%.
>
> Can I know alter my query to reduce that cost?
>
> Thanks
> qh
>|||Martin Selway wrote:
> What is the clustered index?
The clustered index on the view is PolicyID. I have read that because
I am doing a search for null for every element in the where clause
these aren't picked up by the clustered index.
I could be wrong though...
Thanks
qh|||Don't do this...
convert(datetime, convert(char(11), vw.RenewalDate, 103), 103)
Do the convert on @.Start instead.
The convert will negate useful usage of an index (instead a full index scan
at best).
It looks like a query built from a search, i'd recommend building it up
using dynamic SQL so you don't have this...
and (@.Surname is null or vw.Surname like @.Surname)
but instead have something that can use an index properly...
and vw.Surname like @.Surname
You can then build up some nice indexes based on popular inputs from the
users.
That will significantly improve the performance of the query.
To prevent injection, use parameterisation and sp_executesql, for
instance...
declare @.nsql nvarchar(4000)
set @.nsql = 'select.... where vw.Surname like @.Surname'
exec sp_executesql @.nsql,
N'@.Surname varchar(...),
@.Surname
Hope that helps.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1129713424.015770.212820@.o13g2000cwo.googlegroups.com...
> Hi,
> I have a query here that I would like to make cheaper in terms of query
> cost. Here's the query:
> ________________________________________
__________________________
> Select distinct
> vw.CustomerID,
> vw.BranchID,
> vw.SourceOfBusiness ,
> vw.SpkCustNo,
> vw.Surname,
> vw.Initials,
> vw.Incode,
> vw.Outcode,
> vw.Updated
> Inner join tbl_SpkInsurer si on i.ID = si.InsurerID
> Inner join tbl_Scheme s on si.ID = s.SpkInsurerID
> Inner join vw_SearchCustomerPolicy vw on vw.SchemeID = s.ID
> Where i.id = @.InsurerID
> and convert(datetime, convert(char(11), vw.RenewalDate, 103), 103) >=
> @.StartDate
> and (@.PolicyRef is null or vw.PolicyRef = @.PolicyRef)
> and (@.ShowSSU = 1 or vw.BranchID <> @.SSUID)
> and (@.BranchID is null or vw.BranchID = @.BranchID)
> and (@.SpkCustNo is null or vw.SpkCustNo = @.SpkCustNo)
> and (@.Surname is null or vw.Surname like @.Surname)
> and (@.Initials is null or vw.Initials = @.Initials)
> and (@.Incode is null or vw.Incode = @.Incode)
> and (@.Outcode is null or vw.Outcode = @.Outcode)
> and (@.SpkPolNo is null or vw.SpkPolNo = @.SpkPolNo)
> Order by vw.Surname, vw.Initials, vw.BranchID
> ________________________________________
__________________________
> This takes 14 seconds to return 25 or so rows, I would like to knock
> the return time down to under 5 seconds. When I display the Execution
> Plan, I can see that the most expensive item is a clustered index scan
> on the vw_SearchCustomerPolicy (this is an indexed view) this is 51%.
>
> Can I know alter my query to reduce that cost?
>
> Thanks
> qh
>|||Scott wrote:
> Hi,
> I have a query here that I would like to make cheaper in terms of
> query cost. Here's the query:
> ________________________________________
__________________________
> Select distinct
> vw.CustomerID,
> vw.BranchID,
> vw.SourceOfBusiness ,
> vw.SpkCustNo,
> vw.Surname,
> vw.Initials,
> vw.Incode,
> vw.Outcode,
> vw.Updated
> Inner join tbl_SpkInsurer si on i.ID = si.InsurerID
> Inner join tbl_Scheme s on si.ID = s.SpkInsurerID
> Inner join vw_SearchCustomerPolicy vw on vw.SchemeID = s.ID
> Where i.id = @.InsurerID
> and convert(datetime, convert(char(11), vw.RenewalDate, 103), 103) >=
> @.StartDate
> and (@.PolicyRef is null or vw.PolicyRef = @.PolicyRef)
> and (@.ShowSSU = 1 or vw.BranchID <> @.SSUID)
> and (@.BranchID is null or vw.BranchID = @.BranchID)
> and (@.SpkCustNo is null or vw.SpkCustNo = @.SpkCustNo)
> and (@.Surname is null or vw.Surname like @.Surname)
> and (@.Initials is null or vw.Initials = @.Initials)
> and (@.Incode is null or vw.Incode = @.Incode)
> and (@.Outcode is null or vw.Outcode = @.Outcode)
> and (@.SpkPolNo is null or vw.SpkPolNo = @.SpkPolNo)
> Order by vw.Surname, vw.Initials, vw.BranchID
> ________________________________________
__________________________
> This takes 14 seconds to return 25 or so rows, I would like to knock
> the return time down to under 5 seconds. When I display the Execution
> Plan, I can see that the most expensive item is a clustered index scan
> on the vw_SearchCustomerPolicy (this is an indexed view) this is 51%.
You can create a text version of the plan with QA so we can have a look.

> Can I know alter my query to reduce that cost?
What indexes are in place? It seems all your criteria are on the view so
the view definition and the other DDL would be interesting to look at.
Some other remarks:
- you use like, that's usually slow especially if used with a leading
wildcard
- you convert vw.RenewalDate back and forth and then compare it. Try to
find a solution where @.StartDate
is converted instead or where @.StartDate is adjusted so it can be used
directly.
- do you have proper indexes on the join columns in place?
Kind regards
robert|||Never use functions on columns. That leads to table scan/index scan and inde
x
s is faster.
--
Regards
R.D
--Knowledge gets doubled when shared
"Tony Rogerson" wrote:

> Don't do this...
> convert(datetime, convert(char(11), vw.RenewalDate, 103), 103)
> Do the convert on @.Start instead.
> The convert will negate useful usage of an index (instead a full index sca
n
> at best).
> It looks like a query built from a search, i'd recommend building it up
> using dynamic SQL so you don't have this...
> and (@.Surname is null or vw.Surname like @.Surname)
> but instead have something that can use an index properly...
> and vw.Surname like @.Surname
> You can then build up some nice indexes based on popular inputs from the
> users.
> That will significantly improve the performance of the query.
> To prevent injection, use parameterisation and sp_executesql, for
> instance...
> declare @.nsql nvarchar(4000)
> set @.nsql = 'select.... where vw.Surname like @.Surname'
> exec sp_executesql @.nsql,
> N'@.Surname varchar(...),
> @.Surname
> Hope that helps.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
> news:1129713424.015770.212820@.o13g2000cwo.googlegroups.com...
>
>|||Cheers for all the replies. Tony R. managed to spot that fact that this
query used to be built using dynamic sql however the powers that be
came over all security conscious and said it had to be built this way.
Thanks Again
Scott|||That's not strictly true.
You should think carefully about using a function on a column that you've
indexed (the access path), you can use a function on the value column...
Index on RenewalDate...
where RenewalDate = datediff( day, -1, myothercolumn )
The above is fine, and the index will be used efficiently.
You can also use functions to good effect in the SELECT clause and ORDER
clause.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:6C2CF2D1-4BDB-4634-A8E5-8F477FC14378@.microsoft.com...
> Never use functions on columns. That leads to table scan/index scan and
> index
> s is faster.
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Tony Rogerson" wrote:
>|||Bizzarre,
They haven't really understood how injection happens then which is even more
scary!
I hope they are using the command object in the app code rather than
concatenating a string together and executing it.
I'd go back to the powers at be and tell them in order to get good
performance and scalability you need to either write out a ton of IF ELSE
statements or use safe, parameterised dynamic SQL.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Quackhandle" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1129718265.957237.248090@.g47g2000cwa.googlegroups.com...
> Cheers for all the replies. Tony R. managed to spot that fact that this
> query used to be built using dynamic sql however the powers that be
> came over all security conscious and said it had to be built this way.
> Thanks Again
> Scott
>|||This is the view:
CREATE VIEW dbo.vw_SearchCustomerPolicy WITH SCHEMABINDING
AS
SELECT
c.ID AS CustomerID,
c.BranchID,
c.SourceOfBusiness,
c.SpkCustNo,
c.Surname,
c.Initials,
c.Incode,
c.Outcode,
c.Updated,
p.ID AS PolicyID,
p.PolicyRef,
p.SpkPolNo,
p.RenewalDate,
p.SchemeID
FROM dbo.tbl_CA_Customer c
INNER JOIN dbo.tbl_CA_Policy p ON p.CA_CustomerID = c.ID
And this is the text version of the execution plan.
Regards,
qh