Index on XML to improve speed doesn't work, any suggestion?

I have a xml field in my member table, it stores data like

<field name="n1">v1</field><field name="n2">v2</field> ....

When I retrieve 10 fields from the xml field like

select exfield.value('(field[@.name="n1"]/.)[1]','nvarchar(max)'),exfield.value('(field[@.name="n2"]/.)[1]','nvarchar(max)')..... from members

when returns 2200 records, it takes 37 seconds,

I tried to add different xml index on the xml field, but none of them help.

What do you think if there is an index can help to arthieve that?

thanks

I highly suggest that you read the excellent whitepaper at http://msdn2.microsoft.com/en-us/library/ms345118.aspx. You need to taylor your indexes and queries to your needs and this paper can help you make those decisions.

Regards,

Galex

Index is large on a small Table?

Table data size is 3 meg
Index_size is 1 gig,
I rebuilt the index and it did drop my a meg or so but index is still
1gig.
XML indexes are in play,
Why would the index be that big?
Thanks in advance,rDray
Can you post the DDL?
"rDray" <randy.dray@.gmail.com> wrote in message
news:1162846100.891547.231880@.h54g2000cwb.googlegroups.com...
> Table data size is 3 meg
> Index_size is 1 gig,
> I rebuilt the index and it did drop my a meg or so but index is still
> 1gig.
> XML indexes are in play,
> Why would the index be that big?
> Thanks in advance,
>|||Thanks for responding, I am Green, What and how do you post the DDL?
Thanks
Uri Dimant wrote:
> rDray
> Can you post the DDL?
>
> "rDray" <randy.dray@.gmail.com> wrote in message
> news:1162846100.891547.231880@.h54g2000cwb.googlegroups.com...
> > Table data size is 3 meg
> >
> > Index_size is 1 gig,
> >
> > I rebuilt the index and it did drop my a meg or so but index is still
> > 1gig.
> > XML indexes are in play,
> >
> > Why would the index be that big?
> >
> > Thanks in advance,
> >|||"rDray" <randy.dray@.gmail.com> wrote in message
news:1163162103.739927.217160@.k70g2000cwa.googlegroups.com...
> Thanks for responding, I am Green, What and how do you post the DDL?
A script to recreate the database and some example data.
> Thanks
>
> Uri Dimant wrote:
>> rDray
>> Can you post the DDL?
>>
>> "rDray" <randy.dray@.gmail.com> wrote in message
>> news:1162846100.891547.231880@.h54g2000cwb.googlegroups.com...
>> > Table data size is 3 meg
>> >
>> > Index_size is 1 gig,
>> >
>> > I rebuilt the index and it did drop my a meg or so but index is still
>> > 1gig.
>> > XML indexes are in play,
>> >
>> > Why would the index be that big?
>> >
>> > Thanks in advance,
>> >
>

Index is large on a small Table?

Table data size is 3 meg
Index_size is 1 gig,
I rebuilt the index and it did drop my a meg or so but index is still
1gig.
XML indexes are in play,
Why would the index be that big?
Thanks in advance,rDray
Can you post the DDL?
"rDray" <randy.dray@.gmail.com> wrote in message
news:1162846100.891547.231880@.h54g2000cwb.googlegroups.com...
> Table data size is 3 meg
> Index_size is 1 gig,
> I rebuilt the index and it did drop my a meg or so but index is still
> 1gig.
> XML indexes are in play,
> Why would the index be that big?
> Thanks in advance,
>|||Thanks for responding, I am Green, What and how do you post the DDL?
Thanks
Uri Dimant wrote:[vbcol=seagreen]
> rDray
> Can you post the DDL?
>
> "rDray" <randy.dray@.gmail.com> wrote in message
> news:1162846100.891547.231880@.h54g2000cwb.googlegroups.com...|||"rDray" <randy.dray@.gmail.com> wrote in message
news:1163162103.739927.217160@.k70g2000cwa.googlegroups.com...
> Thanks for responding, I am Green, What and how do you post the DDL?
A script to recreate the database and some example data.

> Thanks
>
> Uri Dimant wrote:
>