Index Speed use of REPLACE in clause

I have a frequently run Query, due to the design of the Sproc the indexed
columns I have added to support these are being used but are slow due to the
fact the use of the REPLACE in the Clause (Replace is being used to remove
spaces)
I.E
Select <columns>
FROM <Table>
WHERE
(sEMail1 = 'hambly_neil@.hotmail.com' OR sEMail2 = ''hambly_neil@.hotmail.com')
OR
(REPLACE(sTel1,' ','') = '0123456789' AND REPLACE(sPostCode,' ','') = 'N118HQ')
Does anyone have any ideas on recoding or indexes changes to help here
--
Neil HamblyHow about cleansing the data on the way in (i.e. when you INSERT it, run the
REPLACE), so that you won't have to do it every time you query?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
news:3D4000CA-7EFE-4A9F-AF74-3F0F6CB465EF@.microsoft.com...
> I have a frequently run Query, due to the design of the Sproc the indexed
> columns I have added to support these are being used but are slow due to
the
> fact the use of the REPLACE in the Clause (Replace is being used to remove
> spaces)
> I.E
> Select <columns>
> FROM <Table>
> WHERE
> (sEMail1 = 'hambly_neil@.hotmail.com' OR sEMail2 => ''hambly_neil@.hotmail.com')
> OR
> (REPLACE(sTel1,' ','') = '0123456789' AND REPLACE(sPostCode,' ','') => 'N118HQ')
> Does anyone have any ideas on recoding or indexes changes to help here
> --
> Neil Hambly|||This would be the ideal scenario of course, unofrtunately due to Data-Privacy
and other reasons, the data cannot be Cleansed
One thought I had was the use of Computed colums - do not know if this would
be suitable for this type of issue
"Adam Machanic" wrote:
> How about cleansing the data on the way in (i.e. when you INSERT it, run the
> REPLACE), so that you won't have to do it every time you query?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
> news:3D4000CA-7EFE-4A9F-AF74-3F0F6CB465EF@.microsoft.com...
> > I have a frequently run Query, due to the design of the Sproc the indexed
> > columns I have added to support these are being used but are slow due to
> the
> > fact the use of the REPLACE in the Clause (Replace is being used to remove
> > spaces)
> > I.E
> > Select <columns>
> > FROM <Table>
> > WHERE
> > (sEMail1 = 'hambly_neil@.hotmail.com' OR sEMail2 => > ''hambly_neil@.hotmail.com')
> > OR
> > (REPLACE(sTel1,' ','') = '0123456789' AND REPLACE(sPostCode,' ','') => > 'N118HQ')
> >
> > Does anyone have any ideas on recoding or indexes changes to help here
> >
> > --
> > Neil Hambly
>
>|||Possibly, yes. You can index computed columns to semi-persist them, as well
(they'll be persisted in the index); in this case, you could do:
ALTER TABLE YourTable
ADD Tel1NoSpace AS (REPLACE(sTel1, ' ', ''))
ALTER TABLE YourTable
ADD PostCodeNoSpace AS (REPLACE(sPostCode, ' ', ''))
CREATE NONCLUSTERED INDEX IX_TelPostSearch ON YourTable (Tel1NoSpace,
PostCodeNoSpace)
... That would solve your issue, I think. But _better_ would still be to
cleanse the data -- I'm not sure what you mean by data privacy; the data is
in your database already, isn't it?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
news:40183D6E-BAD0-4A2E-A474-26B6DD25E763@.microsoft.com...
> This would be the ideal scenario of course, unofrtunately due to
Data-Privacy
> and other reasons, the data cannot be Cleansed
> One thought I had was the use of Computed colums - do not know if this
would
> be suitable for this type of issue
> "Adam Machanic" wrote:
> > How about cleansing the data on the way in (i.e. when you INSERT it, run
the
> > REPLACE), so that you won't have to do it every time you query?
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
> > news:3D4000CA-7EFE-4A9F-AF74-3F0F6CB465EF@.microsoft.com...
> > > I have a frequently run Query, due to the design of the Sproc the
indexed
> > > columns I have added to support these are being used but are slow due
to
> > the
> > > fact the use of the REPLACE in the Clause (Replace is being used to
remove
> > > spaces)
> > > I.E
> > > Select <columns>
> > > FROM <Table>
> > > WHERE
> > > (sEMail1 = 'hambly_neil@.hotmail.com' OR sEMail2 => > > ''hambly_neil@.hotmail.com')
> > > OR
> > > (REPLACE(sTel1,' ','') = '0123456789' AND REPLACE(sPostCode,' ','') => > > 'N118HQ')
> > >
> > > Does anyone have any ideas on recoding or indexes changes to help here
> > >
> > > --
> > > Neil Hambly
> >
> >
> >|||Yes, a little test on the Northwind database suggests that this should
work (provided you are using Enterprise Edition).
Gert-Jan
Neil Hambly wrote:
> This would be the ideal scenario of course, unofrtunately due to Data-Privacy
> and other reasons, the data cannot be Cleansed
> One thought I had was the use of Computed colums - do not know if this would
> be suitable for this type of issue
> "Adam Machanic" wrote:
> > How about cleansing the data on the way in (i.e. when you INSERT it, run the
> > REPLACE), so that you won't have to do it every time you query?
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
> > news:3D4000CA-7EFE-4A9F-AF74-3F0F6CB465EF@.microsoft.com...
> > > I have a frequently run Query, due to the design of the Sproc the indexed
> > > columns I have added to support these are being used but are slow due to
> > the
> > > fact the use of the REPLACE in the Clause (Replace is being used to remove
> > > spaces)
> > > I.E
> > > Select <columns>
> > > FROM <Table>
> > > WHERE
> > > (sEMail1 = 'hambly_neil@.hotmail.com' OR sEMail2 => > > ''hambly_neil@.hotmail.com')
> > > OR
> > > (REPLACE(sTel1,' ','') = '0123456789' AND REPLACE(sPostCode,' ','') => > > 'N118HQ')
> > >
> > > Does anyone have any ideas on recoding or indexes changes to help here
> > >
> > > --
> > > Neil Hambly
> >
> >
> >|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41C22D18.3AE1AF4E@.toomuchspamalready.nl...
> Yes, a little test on the Northwind database suggests that this should
> work (provided you are using Enterprise Edition).
What's Enterprise Edition have to do with it?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Re: Data privacy act - We do not own all the data some of it is supplied by
our clients.
Anyway I built some computed columns with the formula using Replace(sTel1, '
', '') etc..Created indexes on these columns and amended my Sprocs to use the
computed columns etc.. Went from 10 secs (22, 000 logical reads) to a few ms
with 12 logical reads..
As this is run over 300,000 times per month that is a huge perf impact
Thanks for your posts
"Adam Machanic" wrote:
> Possibly, yes. You can index computed columns to semi-persist them, as well
> (they'll be persisted in the index); in this case, you could do:
> ALTER TABLE YourTable
> ADD Tel1NoSpace AS (REPLACE(sTel1, ' ', ''))
> ALTER TABLE YourTable
> ADD PostCodeNoSpace AS (REPLACE(sPostCode, ' ', ''))
> CREATE NONCLUSTERED INDEX IX_TelPostSearch ON YourTable (Tel1NoSpace,
> PostCodeNoSpace)
>
> ... That would solve your issue, I think. But _better_ would still be to
> cleanse the data -- I'm not sure what you mean by data privacy; the data is
> in your database already, isn't it?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
> news:40183D6E-BAD0-4A2E-A474-26B6DD25E763@.microsoft.com...
> > This would be the ideal scenario of course, unofrtunately due to
> Data-Privacy
> > and other reasons, the data cannot be Cleansed
> > One thought I had was the use of Computed colums - do not know if this
> would
> > be suitable for this type of issue
> >
> > "Adam Machanic" wrote:
> >
> > > How about cleansing the data on the way in (i.e. when you INSERT it, run
> the
> > > REPLACE), so that you won't have to do it every time you query?
> > >
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --
> > >
> > >
> > > "Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
> > > news:3D4000CA-7EFE-4A9F-AF74-3F0F6CB465EF@.microsoft.com...
> > > > I have a frequently run Query, due to the design of the Sproc the
> indexed
> > > > columns I have added to support these are being used but are slow due
> to
> > > the
> > > > fact the use of the REPLACE in the Clause (Replace is being used to
> remove
> > > > spaces)
> > > > I.E
> > > > Select <columns>
> > > > FROM <Table>
> > > > WHERE
> > > > (sEMail1 = 'hambly_neil@.hotmail.com' OR sEMail2 => > > > ''hambly_neil@.hotmail.com')
> > > > OR
> > > > (REPLACE(sTel1,' ','') = '0123456789' AND REPLACE(sPostCode,' ','') => > > > 'N118HQ')
> > > >
> > > > Does anyone have any ideas on recoding or indexes changes to help here
> > > >
> > > > --
> > > > Neil Hambly
> > >
> > >
> > >
>
>|||I could be wrong. I thought that maybe indexes on computed columns would
not be automatically used in Standard Edition, just as indexes on views
are not automatically used (unless you use Enterprise Edition or provide
special hints).
But as I said: I could be wrong. Maybe someone with Standard Edition can
test this example to see if an index seek is used.
USE Northwind
GO
ALTER TABLE Customers ADD CompanyNameNoSpace AS
Replace(CompanyName,space(1),'')
GO
CREATE INDEX IX_Customers_CompanyNameNoSpace ON
Customers(CompanyNameNoSpace)
GO
SET SHOWPLAN_TEXT ON
GO
SELECT * FROM Customers WHERE CompanyNameNoSpace='EasternConnection'
GO
SET SHOWPLAN_TEXT OFF
Gert-Jan
Adam Machanic wrote:
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:41C22D18.3AE1AF4E@.toomuchspamalready.nl...
> > Yes, a little test on the Northwind database suggests that this should
> > work (provided you are using Enterprise Edition).
> What's Enterprise Edition have to do with it?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --|||Confirmed:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
|--Compute
Scalar(DEFINE:([Customers].[CompanyNameNoSpace]=replace([Customers].[Company
Name], Convert(space(1)), Convert(''))))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([Northwind].[dbo].[Customers]))
|--Index
Seek(OBJECT:([Northwind].[dbo].[Customers].[IX_Customers_CompanyNameNoSpace]
), SEEK:([Customers].[CompanyNameNoSpace]=Convert([@.1])) ORDERED FORWARD)
:-)
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41C2E4BC.868B93CB@.toomuchspamalready.nl...
> I could be wrong. I thought that maybe indexes on computed columns would
> not be automatically used in Standard Edition, just as indexes on views
> are not automatically used (unless you use Enterprise Edition or provide
> special hints).
> But as I said: I could be wrong. Maybe someone with Standard Edition can
> test this example to see if an index seek is used.
> USE Northwind
> GO
> ALTER TABLE Customers ADD CompanyNameNoSpace AS
> Replace(CompanyName,space(1),'')
> GO
> CREATE INDEX IX_Customers_CompanyNameNoSpace ON
> Customers(CompanyNameNoSpace)
> GO
> SET SHOWPLAN_TEXT ON
> GO
> SELECT * FROM Customers WHERE CompanyNameNoSpace='EasternConnection'
> GO
> SET SHOWPLAN_TEXT OFF
> Gert-Jan
> Adam Machanic wrote:
> >
> > "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> > news:41C22D18.3AE1AF4E@.toomuchspamalready.nl...
> > > Yes, a little test on the Northwind database suggests that this should
> > > work (provided you are using Enterprise Edition).
> >
> > What's Enterprise Edition have to do with it?
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --|||Thanks,
Gert-Jan
Adam Machanic wrote:
> Confirmed:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)
> |--Compute
> Scalar(DEFINE:([Customers].[CompanyNameNoSpace]=replace([Customers].[Company
> Name], Convert(space(1)), Convert(''))))
> |--Bookmark Lookup(BOOKMARK:([Bmk1000]),
> OBJECT:([Northwind].[dbo].[Customers]))
> |--Index
> Seek(OBJECT:([Northwind].[dbo].[Customers].[IX_Customers_CompanyNameNoSpace]
> ), SEEK:([Customers].[CompanyNameNoSpace]=Convert([@.1])) ORDERED FORWARD)
> :-)
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:41C2E4BC.868B93CB@.toomuchspamalready.nl...
> > I could be wrong. I thought that maybe indexes on computed columns would
> > not be automatically used in Standard Edition, just as indexes on views
> > are not automatically used (unless you use Enterprise Edition or provide
> > special hints).
> >
> > But as I said: I could be wrong. Maybe someone with Standard Edition can
> > test this example to see if an index seek is used.
> >
> > USE Northwind
> > GO
> > ALTER TABLE Customers ADD CompanyNameNoSpace AS
> > Replace(CompanyName,space(1),'')
> > GO
> > CREATE INDEX IX_Customers_CompanyNameNoSpace ON
> > Customers(CompanyNameNoSpace)
> > GO
> > SET SHOWPLAN_TEXT ON
> > GO
> > SELECT * FROM Customers WHERE CompanyNameNoSpace='EasternConnection'
> > GO
> > SET SHOWPLAN_TEXT OFF
> >
> > Gert-Jan
> >
> > Adam Machanic wrote:
> > >
> > > "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> > > news:41C22D18.3AE1AF4E@.toomuchspamalready.nl...
> > > > Yes, a little test on the Northwind database suggests that this should
> > > > work (provided you are using Enterprise Edition).
> > >
> > > What's Enterprise Edition have to do with it?
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --|||Actually, I just took a closer look at the execution plan. Interesting that
CompanyNameNoSpace is being re-computed after the bookmark lookup, even
though it was used for the initial index seek! I'm surprised that SQL
Server can't use the value from the index...
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ekLNvJE5EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Confirmed:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)
>
> |--Compute
>
Scalar(DEFINE:([Customers].[CompanyNameNoSpace]=replace([Customers].[Company
> Name], Convert(space(1)), Convert(''))))
> |--Bookmark Lookup(BOOKMARK:([Bmk1000]),
> OBJECT:([Northwind].[dbo].[Customers]))
> |--Index
>
Seek(OBJECT:([Northwind].[dbo].[Customers].[IX_Customers_CompanyNameNoSpace]
> ), SEEK:([Customers].[CompanyNameNoSpace]=Convert([@.1])) ORDERED FORWARD)
> :-)
>|||Indeed. I hadn't noticed.
I think Microsoft never took the time to build that optimization. To me,
that shows that they really only added a rudimentary version of indexed
computed columns.
Because the index value could have been used. If you change the query to
SELECT CustomerID,CompanyNameNoSpace FROM Customers WHERE
CompanyNameNoSpace='EasternConnection'
you will see that only the covering index is accessed, and that the
computed value is not recomputed.
Gert-Jan
Adam Machanic wrote:
> Actually, I just took a closer look at the execution plan. Interesting that
> CompanyNameNoSpace is being re-computed after the bookmark lookup, even
> though it was used for the initial index seek! I'm surprised that SQL
> Server can't use the value from the index...
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:ekLNvJE5EHA.2804@.TK2MSFTNGP15.phx.gbl...
> > Confirmed:
> >
> > Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> > Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> > NT 5.0 (Build 2195: Service Pack 4)
> >
> >
> > |--Compute
> >
> Scalar(DEFINE:([Customers].[CompanyNameNoSpace]=replace([Customers].[Company
> > Name], Convert(space(1)), Convert(''))))
> > |--Bookmark Lookup(BOOKMARK:([Bmk1000]),
> > OBJECT:([Northwind].[dbo].[Customers]))
> > |--Index
> >
> Seek(OBJECT:([Northwind].[dbo].[Customers].[IX_Customers_CompanyNameNoSpace]
> > ), SEEK:([Customers].[CompanyNameNoSpace]=Convert([@.1])) ORDERED FORWARD)
> >
> > :-)
> >|||On Wed, 15 Dec 2004 07:35:02 -0800, "Neil Hambly"
<hambly_neil@.hotmail.com> wrote:
>Does anyone have any ideas on recoding or indexes changes to help here
If you have a LOT of this kind of thing, how about using the full-text
indexing?
J.

Index Speed use of REPLACE in clause

I have a frequently run Query, due to the design of the Sproc the indexed
columns I have added to support these are being used but are slow due to the
fact the use of the REPLACE in the Clause (Replace is being used to remove
spaces)
I.E
Select <columns>
FROM <Table>
WHERE
(sEMail1 = 'hambly_neil@.hotmail.com' OR sEMail2 =
''hambly_neil@.hotmail.com')
OR
(REPLACE(sTel1,' ','') = '0123456789' AND REPLACE(sPostCode,' ','') =
'N118HQ')
Does anyone have any ideas on recoding or indexes changes to help here
Neil Hambly
How about cleansing the data on the way in (i.e. when you INSERT it, run the
REPLACE), so that you won't have to do it every time you query?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
news:3D4000CA-7EFE-4A9F-AF74-3F0F6CB465EF@.microsoft.com...
> I have a frequently run Query, due to the design of the Sproc the indexed
> columns I have added to support these are being used but are slow due to
the
> fact the use of the REPLACE in the Clause (Replace is being used to remove
> spaces)
> I.E
> Select <columns>
> FROM <Table>
> WHERE
> (sEMail1 = 'hambly_neil@.hotmail.com' OR sEMail2 =
> ''hambly_neil@.hotmail.com')
> OR
> (REPLACE(sTel1,' ','') = '0123456789' AND REPLACE(sPostCode,' ','') =
> 'N118HQ')
> Does anyone have any ideas on recoding or indexes changes to help here
> --
> Neil Hambly
|||This would be the ideal scenario of course, unofrtunately due to Data-Privacy
and other reasons, the data cannot be Cleansed
One thought I had was the use of Computed colums - do not know if this would
be suitable for this type of issue
"Adam Machanic" wrote:

> How about cleansing the data on the way in (i.e. when you INSERT it, run the
> REPLACE), so that you won't have to do it every time you query?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
> news:3D4000CA-7EFE-4A9F-AF74-3F0F6CB465EF@.microsoft.com...
> the
>
>
|||Possibly, yes. You can index computed columns to semi-persist them, as well
(they'll be persisted in the index); in this case, you could do:
ALTER TABLE YourTable
ADD Tel1NoSpace AS (REPLACE(sTel1, ' ', ''))
ALTER TABLE YourTable
ADD PostCodeNoSpace AS (REPLACE(sPostCode, ' ', ''))
CREATE NONCLUSTERED INDEX IX_TelPostSearch ON YourTable (Tel1NoSpace,
PostCodeNoSpace)
... That would solve your issue, I think. But _better_ would still be to
cleanse the data -- I'm not sure what you mean by data privacy; the data is
in your database already, isn't it?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
news:40183D6E-BAD0-4A2E-A474-26B6DD25E763@.microsoft.com...
> This would be the ideal scenario of course, unofrtunately due to
Data-Privacy
> and other reasons, the data cannot be Cleansed
> One thought I had was the use of Computed colums - do not know if this
would[vbcol=seagreen]
> be suitable for this type of issue
> "Adam Machanic" wrote:
the[vbcol=seagreen]
indexed[vbcol=seagreen]
to[vbcol=seagreen]
remove[vbcol=seagreen]
|||Yes, a little test on the Northwind database suggests that this should
work (provided you are using Enterprise Edition).
Gert-Jan
Neil Hambly wrote:[vbcol=seagreen]
> This would be the ideal scenario of course, unofrtunately due to Data-Privacy
> and other reasons, the data cannot be Cleansed
> One thought I had was the use of Computed colums - do not know if this would
> be suitable for this type of issue
> "Adam Machanic" wrote:
|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41C22D18.3AE1AF4E@.toomuchspamalready.nl...
> Yes, a little test on the Northwind database suggests that this should
> work (provided you are using Enterprise Edition).
What's Enterprise Edition have to do with it?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||Re: Data privacy act - We do not own all the data some of it is supplied by
our clients.
Anyway I built some computed columns with the formula using Replace(sTel1, '
', '') etc..Created indexes on these columns and amended my Sprocs to use the
computed columns etc.. Went from 10 secs (22, 000 logical reads) to a few ms
with 12 logical reads..
As this is run over 300,000 times per month that is a huge perf impact
Thanks for your posts
"Adam Machanic" wrote:

> Possibly, yes. You can index computed columns to semi-persist them, as well
> (they'll be persisted in the index); in this case, you could do:
> ALTER TABLE YourTable
> ADD Tel1NoSpace AS (REPLACE(sTel1, ' ', ''))
> ALTER TABLE YourTable
> ADD PostCodeNoSpace AS (REPLACE(sPostCode, ' ', ''))
> CREATE NONCLUSTERED INDEX IX_TelPostSearch ON YourTable (Tel1NoSpace,
> PostCodeNoSpace)
>
> ... That would solve your issue, I think. But _better_ would still be to
> cleanse the data -- I'm not sure what you mean by data privacy; the data is
> in your database already, isn't it?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
> news:40183D6E-BAD0-4A2E-A474-26B6DD25E763@.microsoft.com...
> Data-Privacy
> would
> the
> indexed
> to
> remove
>
>
|||I could be wrong. I thought that maybe indexes on computed columns would
not be automatically used in Standard Edition, just as indexes on views
are not automatically used (unless you use Enterprise Edition or provide
special hints).
But as I said: I could be wrong. Maybe someone with Standard Edition can
test this example to see if an index seek is used.
USE Northwind
GO
ALTER TABLE Customers ADD CompanyNameNoSpace AS
Replace(CompanyName,space(1),'')
GO
CREATE INDEX IX_Customers_CompanyNameNoSpace ON
Customers(CompanyNameNoSpace)
GO
SET SHOWPLAN_TEXT ON
GO
SELECT * FROM Customers WHERE CompanyNameNoSpace='EasternConnection'
GO
SET SHOWPLAN_TEXT OFF
Gert-Jan
Adam Machanic wrote:
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:41C22D18.3AE1AF4E@.toomuchspamalready.nl...
> What's Enterprise Edition have to do with it?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
|||Confirmed:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
|--Compute
Scalar(DEFINE[Customers].[CompanyNameNoSpace]=replace([Customers].[Company
Name], Convert(space(1)), Convert(''))))
|--Bookmark Lookup(BOOKMARK[Bmk1000]),
OBJECT[Northwind].[dbo].[Customers]))
|--Index
Seek(OBJECT[Northwind].[dbo].[Customers].[IX_Customers_CompanyNameNoSpace]
), SEEK[Customers].[CompanyNameNoSpace]=Convert([@.1])) ORDERED FORWARD)
:-)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41C2E4BC.868B93CB@.toomuchspamalready.nl...[vbcol=seagreen]
> I could be wrong. I thought that maybe indexes on computed columns would
> not be automatically used in Standard Edition, just as indexes on views
> are not automatically used (unless you use Enterprise Edition or provide
> special hints).
> But as I said: I could be wrong. Maybe someone with Standard Edition can
> test this example to see if an index seek is used.
> USE Northwind
> GO
> ALTER TABLE Customers ADD CompanyNameNoSpace AS
> Replace(CompanyName,space(1),'')
> GO
> CREATE INDEX IX_Customers_CompanyNameNoSpace ON
> Customers(CompanyNameNoSpace)
> GO
> SET SHOWPLAN_TEXT ON
> GO
> SELECT * FROM Customers WHERE CompanyNameNoSpace='EasternConnection'
> GO
> SET SHOWPLAN_TEXT OFF
> Gert-Jan
> Adam Machanic wrote:
|||Thanks,
Gert-Jan
Adam Machanic wrote:[vbcol=seagreen]
> Confirmed:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)
> |--Compute
> Scalar(DEFINE[Customers].[CompanyNameNoSpace]=replace([Customers].[Company
> Name], Convert(space(1)), Convert(''))))
> |--Bookmark Lookup(BOOKMARK[Bmk1000]),
> OBJECT[Northwind].[dbo].[Customers]))
> |--Index
> Seek(OBJECT[Northwind].[dbo].[Customers].[IX_Customers_CompanyNameNoSpace]
> ), SEEK[Customers].[CompanyNameNoSpace]=Convert([@.1])) ORDERED FORWARD)
> :-)
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:41C2E4BC.868B93CB@.toomuchspamalready.nl...

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 on a varchar column?

In an effort to improve the speed of queries against my main table,
I'll be indexing a column whose data type is varchar(50).

Would I be better off (better performance) if I changed the column's
data type to some numeric type? I would have to update the column's
data to accomodate this, but I would do it if this offers a
performance gain.

-- Bill
Bill wrote:

> In an effort to improve the speed of queries against my main table,
> I'll be indexing a column whose data type is varchar(50).
> Would I be better off (better performance) if I changed the column's
> data type to some numeric type? I would have to update the column's
> data to accomodate this, but I would do it if this offers a
> performance gain.
> -- Bill

If the varchar field is usually using all or most of the 50
characters, *and* it can be changed to an integer, then your
index pages and data pages will certain become smaller, meaning
that more can be in memory at a time, and fewer levels to the
index, etc. Also, the comparison of one integer to another is
faster than varchar-to-varchar.
If the varchar field is most often just a 4-character code,
then the savings will be much less. FInally, consider whether there
is any human-readability value to the varchar content. If not, then
I'd do it...

Joe Weinstein at BEA|||Joe Weinstein <joeNOSPAM@.bea.com> wrote in message news:<3FDDF197.1040901@.bea.com>...
> Bill wrote:
> > In an effort to improve the speed of queries against my main table,
> > I'll be indexing a column whose data type is varchar(50).
> > Would I be better off (better performance) if I changed the column's
> > data type to some numeric type? I would have to update the column's
> > data to accomodate this, but I would do it if this offers a
> > performance gain.
> > -- Bill
> If the varchar field is usually using all or most of the 50
> characters, *and* it can be changed to an integer, then your
> index pages and data pages will certain become smaller, meaning
> that more can be in memory at a time, and fewer levels to the
> index, etc. Also, the comparison of one integer to another is
> faster than varchar-to-varchar.
> If the varchar field is most often just a 4-character code,
> then the savings will be much less. FInally, consider whether there
> is any human-readability value to the varchar content. If not, then
> I'd do it...
> Joe Weinstein at BEA

Thanks for the help, Joe.

The values in the varchar(50) field are invariably of this format:
abc_1234. Always eight characters in length, alpha alpha alpha
underscore digit digit digit digit.

Maybe if I change the columns data type to char(8), then index?

-- Bill|||If the data is by deinition always eight characters, why use define it
as having a size of 50 OR having a variable size?

With a field width of eight, I don't think that you'll realize any speed
improvements by converting to an integer.

HTH

=======================================
Everyone here speaks SQL; some are more fluent, others less. When
describing your SQL object (table, etc.), do so in the language that we
all understand - SQL, not English. It makes it easier to understand
your issue and makes it more likely that you will get the assistance
that you are asking for.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||
Bill wrote:

> Joe Weinstein <joeNOSPAM@.bea.com> wrote in message news:<3FDDF197.1040901@.bea.com>...
>>Bill wrote:
>>
>>
>>>In an effort to improve the speed of queries against my main table,
>>>I'll be indexing a column whose data type is varchar(50).
>>>
>>>Would I be better off (better performance) if I changed the column's
>>>data type to some numeric type? I would have to update the column's
>>>data to accomodate this, but I would do it if this offers a
>>>performance gain.
>>>
>>>-- Bill
>>
>>If the varchar field is usually using all or most of the 50
>>characters, *and* it can be changed to an integer, then your
>>index pages and data pages will certain become smaller, meaning
>>that more can be in memory at a time, and fewer levels to the
>>index, etc. Also, the comparison of one integer to another is
>>faster than varchar-to-varchar.
>> If the varchar field is most often just a 4-character code,
>>then the savings will be much less. FInally, consider whether there
>>is any human-readability value to the varchar content. If not, then
>>I'd do it...
>>
>>Joe Weinstein at BEA
>
> Thanks for the help, Joe.
> The values in the varchar(50) field are invariably of this format:
> abc_1234. Always eight characters in length, alpha alpha alpha
> underscore digit digit digit digit.
> Maybe if I change the columns data type to char(8), then index?

Well, a varchar field doesn't waste 50 chars for an 8-char value,
so the index size will really only drop from 8 bytes to 4 (per entry).
There will be a *little* improvement with an int column, in data volume
and in comparison speed.|||Joe Weinstein <joeNOSPAM@.bea.com> wrote in message news:<3FDDF197.1040901@.bea.com>...
> Bill wrote:
> > In an effort to improve the speed of queries against my main table,
> > I'll be indexing a column whose data type is varchar(50).
> > Would I be better off (better performance) if I changed the column's
> > data type to some numeric type? I would have to update the column's
> > data to accomodate this, but I would do it if this offers a
> > performance gain.
> > -- Bill
> If the varchar field is usually using all or most of the 50
> characters, *and* it can be changed to an integer, then your
> index pages and data pages will certain become smaller, meaning
> that more can be in memory at a time, and fewer levels to the
> index, etc. Also, the comparison of one integer to another is
> faster than varchar-to-varchar.
> If the varchar field is most often just a 4-character code,
> then the savings will be much less. FInally, consider whether there
> is any human-readability value to the varchar content. If not, then
> I'd do it...
> Joe Weinstein at BEA

What Joe said is right.
There are also a couple more things to consider. Sql can always search
numbers faster than text. If all you have in the field are numbers,
then absolutely change it. If you have text in the field. . . If the
data length is closer to 10 or 20 changing it to char might speed
things up. Also if this is the field that is searched most often make
it the clustered index (the order the data is stored on disk). Put
your sql strings in query analyzer and look at the execution plan.
You may find you have table scans on other things that are slowing it
down.
HTH
Pachydermitis|||Neither the use of varying size nor the use of 50 for a data item of
eight characters are erroneous in the sense that they yield incorrect
data; their just, well, inexact. They don't waste space, they just
offend the sensibilities.

The reduction in index size from eight to four should, technically,
allow for faster selects. I just don't think that the speed up will be
significant.

The proof is in the pudding, so try it to see.

HTH

=======================================
Everyone here speaks SQL; some are more fluent, others less. When
describing your SQL object (table, etc.), do so in the language that we
all understand - SQL, not English. It makes it easier to understand
your issue and makes it more likely that you will get the assistance
that you are asking for.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

index for tables that located in different databases?

When design index for table to speed the joining, where conditions, etc,
any different comparing with tables in the same database in the same server?
and how about the tables in different server?No difference. In the end, SQL Server need to access the data. If that access ban be supported by an
index (to limit number of rows to go though, support sort or grouping etc), then it doesn't make a
difference from there the whole query originated.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"nick" <nick@.discussions.microsoft.com> wrote in message
news:BBA362DF-462E-494C-95A3-2B8A61D60A94@.microsoft.com...
> When design index for table to speed the joining, where conditions, etc,
> any different comparing with tables in the same database in the same server?
> and how about the tables in different server?

index for tables that located in different databases?

When design index for table to speed the joining, where conditions, etc,
any different comparing with tables in the same database in the same server?
and how about the tables in different server?
No difference. In the end, SQL Server need to access the data. If that access ban be supported by an
index (to limit number of rows to go though, support sort or grouping etc), then it doesn't make a
difference from there the whole query originated.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"nick" <nick@.discussions.microsoft.com> wrote in message
news:BBA362DF-462E-494C-95A3-2B8A61D60A94@.microsoft.com...
> When design index for table to speed the joining, where conditions, etc,
> any different comparing with tables in the same database in the same server?
> and how about the tables in different server?

index for tables that located in different databases?

When design index for table to speed the joining, where conditions, etc,
any different comparing with tables in the same database in the same server?
and how about the tables in different server?No difference. In the end, SQL Server need to access the data. If that acces
s ban be supported by an
index (to limit number of rows to go though, support sort or grouping etc),
then it doesn't make a
difference from there the whole query originated.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"nick" <nick@.discussions.microsoft.com> wrote in message
news:BBA362DF-462E-494C-95A3-2B8A61D60A94@.microsoft.com...
> When design index for table to speed the joining, where conditions, etc,
> any different comparing with tables in the same database in the same serve
r?
> and how about the tables in different server?

Index for Join

Hi,
If there are two tables with 1 to many relationship.
In order to speed up the join, what would be the major different if I put
the Clustered or Non-Clustered index on ForeignKey (Many Side) and
MajorKey(One Side)? If I put Clustered on both, would it be faster or it
doesn't matter if I put Non clustered' The reason I ask is because I would
like to put the clustered index on a date field instead of the key field.
since the key field join to another table, I am not sure if it will slow dow
n
the query if i remove the clustered and replace it with non clustered...
Thanks
EdIt is likely that a clustered index on the many-side table will increase per
formance of the join,
but other factors are also relevant. Test and check the execution plan and y
ou will see.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:1A44B506-86E8-4E57-A2FD-D3AFB4099B2C@.microsoft.com...
> Hi,
> If there are two tables with 1 to many relationship.
> In order to speed up the join, what would be the major different if I put
> the Clustered or Non-Clustered index on ForeignKey (Many Side) and
> MajorKey(One Side)? If I put Clustered on both, would it be faster or i
t
> doesn't matter if I put Non clustered' The reason I ask is because I wou
ld
> like to put the clustered index on a date field instead of the key field.
> since the key field join to another table, I am not sure if it will slow d
own
> the query if i remove the clustered and replace it with non clustered...
> Thanks
> Ed|||Ed (Ed@.discussions.microsoft.com) writes:
> If there are two tables with 1 to many relationship.
> In order to speed up the join, what would be the major different if I
> put the Clustered or Non-Clustered index on ForeignKey (Many Side) and
> MajorKey(One Side)? If I put Clustered on both, would it be faster or
> it doesn't matter if I put Non clustered' The reason I ask is because
> I would like to put the clustered index on a date field instead of the
> key field. since the key field join to another table, I am not sure if
> it will slow down the query if i remove the clustered and replace it
> with non clustered...
It's difficult to answer for sure given this little information. Where
to place the clustered index can often be a delicate question. But, to
take a little more concreate example, consider an Orders and an OrderDetails
table. For the OrderDetails it is typically a good idea to cluster on
the primary key (OrderID, RowNo), as you often retrieve all rows for an
order. On the other hand, Orders is likely to have a better column to
cluster on than the OrderId, for instance OrderDate or CustomerId.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx