poor index performance

Hey guys,

Having some trouble with indexes on sql server 2005. I'll explain it with a simplified example.
I have a customers table, and a sp to list customers :

create table Customers(
CusID int not null,
Name varchar(50) null,
Surname varchar(50) null,
CusNo int not null,
Deleted bit not null
)

create proc spCusLs (
@.CusID int = null,
@.Name varchar(50) = null,
@.Surname varchar(50) = null,
@.CusNo int = null
)
as

select
CusID,
Name,
Surname,
CusNo
from
Customers
where
Deleted = 0
and CusID <> 1000
and (@.CusID is null or CusID = @.CusID)
and (@.CusNo is null or CusNo = @.CusNo)
and (@.Name is null or Name like @.Name)
and (@.Surname is null or Surname like @.Surname)
order by
Name,
Surname

create nonclustered index ix_customers_name on customers ([name] asc)
with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on primary

create nonclustered index ix_customers_surname on customers (surname asc)
with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on primary

create nonclustered index ix_customers_cusno on customers (cusno asc)
with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on primary

I've recently noticed that some tables, including 'Customers' don't have indexes except primary keys. And I have added indexes to "name", "surname" and "cusno" columns. This has dropped the number of IO reads. But the strange thing is; one time it works with name / surname searches like ('joh%' '%') but when CusNo is included, it does a full scan. And vice versa when the SP is recompiled using 'alter', works ok with CusNo, but not with name/surname. Recompile it, and it's reversed again. When run as a single query, the execution plan looks different.

What's happening? Perhaps something to do with statistics? This doesn't have a big payload on the server, but there are some other procs suffering from this on heavy queries, making server performance worse than before...

You will get the best performance if you can create a "covering" index for this query, which is a non-clustered index that includes all of the columns needed to satisfy or "cover" the query.

In this case, I would try a unique, non-clustered index on Deleted, CusID, CusNo, Name, and SurName (all of these in a single NC index). You may have to play around with the order of the columns in this index (based on their selectivity) to get the best results.

Also, the fact that you are using OR and LIKE in your WHERE clause will cause performance issues. I would consider splitting this into two SP's instead of trying to use an "all-purpose" SP for this.

sql

Pointer to List of Namespaces for MSXML 4.0?

Hi,
Can anyone provide or point to a list of supported namespaces (and
standards) in MSXML 4.0. Having enormous trouble finding a simple list
of supported namespaces. For instance, it's taken me a hell of a long
time to work out that the reason position() isn't working is that the
page is currently referencing "http://www.w3.org/TR/WD-xsl" instead of
"http://www.w3.org/1999/XSL/Transform".
Incidiently, does the W3C have a page that lists current (and
superseded) namespaces. It would be very helpful!!
Cheers,
James
Versions 3 and below support the two you mentioned for transforms and only
those (other versions might I'm not sure). The WD-xsl is obsolete and
shouldn't be used for transforms.
Even XSLT 2.0 uses the http://www.w3.org/1999/XSL/Transform namespace,
although Microsoft doesn't support version 2.0.
Joe Fawcett - XML MVP
http://joe.fawcett.name
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:1161256895.279470.189640@.m7g2000cwm.googlegro ups.com...
> Hi,
> Can anyone provide or point to a list of supported namespaces (and
> standards) in MSXML 4.0. Having enormous trouble finding a simple list
> of supported namespaces. For instance, it's taken me a hell of a long
> time to work out that the reason position() isn't working is that the
> page is currently referencing "http://www.w3.org/TR/WD-xsl" instead of
> "
<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0"
>
<xsl:param name="person" />
<xsl:template match="/">
<html>
<head>
<title>Finding an age using an XSLT parameter</title>
</head>
<body>
<xsl:apply-templates select="/Ages/Person[@.name=$person]" />
</body>
</html>
</xsl:template>
<xsl:template match="Person">
<p>The age of <xsl:value-of select="$person" /> is <xsl:value-of
select="@.age"/> </p>
</xsl:template>
</xsl:stylesheet>".
> Incidiently, does the W3C have a page that lists current (and
> superseded) namespaces. It would be very helpful!!
> Cheers,
> James
>

Pointer to List of Namespaces for MSXML 4.0?

Hi,
Can anyone provide or point to a list of supported namespaces (and
standards) in MSXML 4.0. Having enormous trouble finding a simple list
of supported namespaces. For instance, it's taken me a hell of a long
time to work out that the reason position() isn't working is that the
page is currently referencing "http://www.w3.org/TR/WD-xsl" instead of
"http://www.w3.org/1999/XSL/Transform".
Incidiently, does the W3C have a page that lists current (and
superseded) namespaces. It would be very helpful!!
Cheers,
JamesVersions 3 and below support the two you mentioned for transforms and only
those (other versions might I'm not sure). The WD-xsl is obsolete and
shouldn't be used for transforms.
Even XSLT 2.0 uses the http://www.w3.org/1999/XSL/Transform namespace,
although Microsoft doesn't support version 2.0.
Joe Fawcett - XML MVP
http://joe.fawcett.name
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:1161256895.279470.189640@.m7g2000cwm.googlegroups.com...
> Hi,
> Can anyone provide or point to a list of supported namespaces (and
> standards) in MSXML 4.0. Having enormous trouble finding a simple list
> of supported namespaces. For instance, it's taken me a hell of a long
> time to work out that the reason position() isn't working is that the
> page is currently referencing "http://www.w3.org/TR/WD-xsl" instead of
> "
<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0"
>
<xsl:param name="person" />
<xsl:template match="/">
<html>
<head>
<title>Finding an age using an XSLT parameter</title>
</head>
<body>
<xsl:apply-templates select="/Ages/Person[@.name=$person]" />
</body>
</html>
</xsl:template>
<xsl:template match="Person">
<p>The age of <xsl:value-of select="$person" /> is <xsl:value-of
select="@.age"/> </p>
</xsl:template>
</xsl:stylesheet>".
> Incidiently, does the W3C have a page that lists current (and
> superseded) namespaces. It would be very helpful!!
> Cheers,
> James
>

Plug-in GetNodeProperty ATTRIBUTE_NAME

I'm in trouble with my Plug-in Algorithm while filling out my model rowsets.

How to return ATTRIBUTE_NAME through GetNodeProperty if does not exist an ID to do it? Also it appears that no call is made for retrieve such node property.

Thanks

Claudio

I'm replying by myself since I found out how to solve the problem.

The call to get this property is made through GetNodeArrayProperty and DMNP_ATTRIBUTES property ID.

Thanks anyway.

Claudio

|||

Good to hear you found the answer, Claudio.

It would be good to know more about your algorithm and what you may be using it for. Of course, I know this may not be something to share on a public forum. You could mail me directly at donald.farmer@.spamyenot.microsoft.com

It's good for us to understand the different uses that people find for the application, and especially useful to understand where and how new functions and algorithms are needed.

Of course, no problem if you would rather not discuss your scenarios. I understand that business and technical cases can be very confidential.

|||

Hi,

I sent you a personal email.