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 Scanning

Please read following statements. statement 1 runs very faster and uses index scan. The statement 2 runs very slow uses table scan.

The Following things are already done.

DBCC DBREINDEX
Sp_updatestats
update statistics

It is only affecting one column (date_added) and also other statistic name start with _WA_SYS_* but date column starts as statistic_date_added. This has happened only for the last two days. All the production store procedure accessing date_columns now running longer.

Is there anybod can explain this and please post a solution. ?

Raj Sankar

Statement 1.
select * from rx_control where store_id = @.store_id and date_added between '08/01/02' and '08/20/02'
------------------------
statement 2.
------------
declare @.bdate as datetime
declare @.edate as datetime
declare @.rxid as int

set @.store_id = '52'
set @.rxid = '158315'
set @.bdate = '08/01/02'
set @.edate = '08/10/02'

select * from rx_control where store_id = @.store_id and date_added between @.bdate and @.edate
------------------------check with dbcc showcontig report and depending on this u should go for dbcc indexfrag on respective fields.

if still the problem persists send me the showcontig report for the respectve table if u can.|||I had problem like this or very similar.
Query optimizer creates an execution plan based on conditions. For the first statement optimizer knows date range exactly and creates the best execution plan.
The second statement is using local variables and optimizer creates common execution plan (without using particular conditions).
How to resolve this problem? In my situation I created additional table where parameters were saved and I was using join. It looks like a stupid decision but it works.
You can try to use this idea.

Good luck

Index related problems? Whats happening here?

All queries for a particular table seems to be slow. It has one
clustered index on the primary key column which of data type INT and
has identity insert ON. This table has < 10000 rows and is fast with
response in all other circumstances. The clustered index is at a fill
factor of 90% and I have toyed upto 70% fillfactor.
When it is slow I ran DBCC SHOWCONTIG and there were signs of
fragmentation which didn't look very serious. The BOL says it is not
reliable for smaller tables.
I run DBCC INDEXDEFRAG on a particular database. The results suggest
that there were 72 pages and 72 pages were moved and 0 deleted. Still
no improvement in performance.
I run DBCC DBREINDEX and viola query runs fast... I am happy but what
is happening here?
All help is welcome and appreciated...
ThanksDid you do a lot of updates/inserts/deletes and you didn't update statistics?
http://sqlservercode.blogspot.com/
"MasterNone" wrote:
> All queries for a particular table seems to be slow. It has one
> clustered index on the primary key column which of data type INT and
> has identity insert ON. This table has < 10000 rows and is fast with
> response in all other circumstances. The clustered index is at a fill
> factor of 90% and I have toyed upto 70% fillfactor.
> When it is slow I ran DBCC SHOWCONTIG and there were signs of
> fragmentation which didn't look very serious. The BOL says it is not
> reliable for smaller tables.
> I run DBCC INDEXDEFRAG on a particular database. The results suggest
> that there were 72 pages and 72 pages were moved and 0 deleted. Still
> no improvement in performance.
> I run DBCC DBREINDEX and viola query runs fast... I am happy but what
> is happening here?
>
> All help is welcome and appreciated...
> Thanks
>|||I had been monitoring the inserts they are of the order of 10-11 for a
table of 7500 rows. There were the same number of updates but not to
the primary key/indexed column. Currently the Autoupdate Statistics
option is turned on.|||MasterNone wrote:
> I had been monitoring the inserts they are of the order of 10-11 for a
> table of 7500 rows. There were the same number of updates but not to
> the primary key/indexed column. Currently the Autoupdate Statistics
> option is turned on.
Please post table DDL and your slow queries. You should also look at the
query plan with QA. A common cause for the phenomenon you seem to observe
is that the index is not used at all.
Regards
robert

index question

Hi,
The below query is running slow in production.
SELECT MIN(STMT_DATE) as STATEMENT_DATE FROM CALL_TB WHERE PHONE_ID = ?
CALL_TB table has three indexes. (1) primary key on CALL_ID (2)
non-clustered index on STMT_DATE (IX_CALL_TB2) (3) non-clustered index on
PHONE_ID (IX_CALL_TB)
The execution plan shows:
SELECT MIN(STMT_DATE) STMT_DATE FROM CALL_TB WHERE PHONE_ID =171
|--Stream Aggregate(DEFINE[Expr1002]=MIN([CALL_TB].[STMT_DATE])))
|--Bookmark Lookup(BOOKMARK[Bmk1000]),
OBJECT[CALLDB].[dbo].[CALL_TB]) WITH PREFETCH)
|--Index Seek(OBJECT[CALLDB].[dbo].[CALL_TB].[IX_CALL_TB]),
SEEK[CALL_TB].[PHONE_ID]=171) ORDERED FORWARD)
Index seek on PHONE_ID takes estimated cost about 0.00881, where as Bookmark
Lookup takes about 9.82. Overall select statement takes estimated cost about
9.83
To reduce the estimated cost, I have modifed the non-clustered index on
PHONE_ID to include the column STMT_DATE. In order words, I made it as
composite index. First column in the index is PHONE_ID and second column is
STMT_DATE.
Since I made it as covering index, the query estimated cost took about
0.008, which is a significant improvement. I have a question here...Does
this change affects any other query to run slow? I mean, for example if there
is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
Thanks,
Ramu
Adding the second column to the index will not visibly change the
performance when only the first column is used. The index tree will
be a tiny bit deeper but that will not amount to anything worth
worrying about.
Roy Harvey
Beacon Falls, CT
On Sun, 14 Jan 2007 19:16:01 -0800, Ramu
<Ramu@.discussions.microsoft.com> wrote:

>Hi,
>The below query is running slow in production.
>SELECT MIN(STMT_DATE) as STATEMENT_DATE FROM CALL_TB WHERE PHONE_ID = ?
>CALL_TB table has three indexes. (1) primary key on CALL_ID (2)
>non-clustered index on STMT_DATE (IX_CALL_TB2) (3) non-clustered index on
>PHONE_ID (IX_CALL_TB)
>The execution plan shows:
>SELECT MIN(STMT_DATE) STMT_DATE FROM CALL_TB WHERE PHONE_ID =171
> |--Stream Aggregate(DEFINE[Expr1002]=MIN([CALL_TB].[STMT_DATE])))
> |--Bookmark Lookup(BOOKMARK[Bmk1000]),
>OBJECT[CALLDB].[dbo].[CALL_TB]) WITH PREFETCH)
> |--Index Seek(OBJECT[CALLDB].[dbo].[CALL_TB].[IX_CALL_TB]),
>SEEK[CALL_TB].[PHONE_ID]=171) ORDERED FORWARD)
>
>Index seek on PHONE_ID takes estimated cost about 0.00881, where as Bookmark
>Lookup takes about 9.82. Overall select statement takes estimated cost about
>9.83
>To reduce the estimated cost, I have modifed the non-clustered index on
>PHONE_ID to include the column STMT_DATE. In order words, I made it as
>composite index. First column in the index is PHONE_ID and second column is
>STMT_DATE.
>Since I made it as covering index, the query estimated cost took about
>0.008, which is a significant improvement. I have a question here...Does
>this change affects any other query to run slow? I mean, for example if there
>is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
>CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
>Thanks,
>Ramu
|||On Sun, 14 Jan 2007 19:16:01 -0800, Ramu
<Ramu@.discussions.microsoft.com> wrote:

>The below query is running slow in production.
What do you mean, "slow"?

>Since I made it as covering index, the query estimated cost took about
>0.008, which is a significant improvement. I have a question here...Does
>this change affects any other query to run slow? I mean, for example if there
>is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
>CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
So it runs about 10% faster? OK.
But it was already pretty efficient, as I presume phone_id is already
highly selective. The covering index means it can all be done in the
index instead of scanning the data. This might be more significant as
your database size grows, if it's not already fully populated.
Yes, it will cause some other queries to run a percent or three slower
because a few more pages of index will be needed, with the fatter
two-field key.
J.

index question

Hi,
The below query is running slow in production.
SELECT MIN(STMT_DATE) as STATEMENT_DATE FROM CALL_TB WHERE PHONE_ID = ?
CALL_TB table has three indexes. (1) primary key on CALL_ID (2)
non-clustered index on STMT_DATE (IX_CALL_TB2) (3) non-clustered index on
PHONE_ID (IX_CALL_TB)
The execution plan shows:
SELECT MIN(STMT_DATE) STMT_DATE FROM CALL_TB WHERE PHONE_ID =171
|--Stream Aggregate(DEFINE[Expr1002]=MIN([CALL_TB].[STMT_DATE]
)))
|--Bookmark Lookup(BOOKMARK[Bmk1000]),
OBJECT[CALLDB].[dbo].[CALL_TB]) WITH PREFETCH)
|--Index Seek(OBJECT[CALLDB].[dbo].[CALL_TB].[IX_CALL_TB])
,
SEEK[CALL_TB].[PHONE_ID]=171) ORDERED FORWARD)
Index seek on PHONE_ID takes estimated cost about 0.00881, where as Bookmark
Lookup takes about 9.82. Overall select statement takes estimated cost about
9.83
To reduce the estimated cost, I have modifed the non-clustered index on
PHONE_ID to include the column STMT_DATE. In order words, I made it as
composite index. First column in the index is PHONE_ID and second column is
STMT_DATE.
Since I made it as covering index, the query estimated cost took about
0.008, which is a significant improvement. I have a question here...Does
this change affects any other query to run slow? I mean, for example if ther
e
is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
Thanks,
RamuAdding the second column to the index will not visibly change the
performance when only the first column is used. The index tree will
be a tiny bit deeper but that will not amount to anything worth
worrying about.
Roy Harvey
Beacon Falls, CT
On Sun, 14 Jan 2007 19:16:01 -0800, Ramu
<Ramu@.discussions.microsoft.com> wrote:

>Hi,
>The below query is running slow in production.
>SELECT MIN(STMT_DATE) as STATEMENT_DATE FROM CALL_TB WHERE PHONE_ID = ?
>CALL_TB table has three indexes. (1) primary key on CALL_ID (2)
>non-clustered index on STMT_DATE (IX_CALL_TB2) (3) non-clustered index on
>PHONE_ID (IX_CALL_TB)
>The execution plan shows:
>SELECT MIN(STMT_DATE) STMT_DATE FROM CALL_TB WHERE PHONE_ID =171
> |--Stream Aggregate(DEFINE[Expr1002]=MIN([CALL_TB].[STMT_DA
TE])))
> |--Bookmark Lookup(BOOKMARK[Bmk1000]),
>OBJECT[CALLDB].[dbo].[CALL_TB]) WITH PREFETCH)
> |--Index Seek(OBJECT[CALLDB].[dbo].[CALL_TB].[
;IX_CALL_TB]),
>SEEK[CALL_TB].[PHONE_ID]=171) ORDERED FORWARD)
>
>Index seek on PHONE_ID takes estimated cost about 0.00881, where as Bookmar
k
>Lookup takes about 9.82. Overall select statement takes estimated cost abou
t
>9.83
>To reduce the estimated cost, I have modifed the non-clustered index on
>PHONE_ID to include the column STMT_DATE. In order words, I made it as
>composite index. First column in the index is PHONE_ID and second column is
>STMT_DATE.
>Since I made it as covering index, the query estimated cost took about
>0.008, which is a significant improvement. I have a question here...Does
>this change affects any other query to run slow? I mean, for example if the
re
>is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
>CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
>Thanks,
>Ramu|||On Sun, 14 Jan 2007 19:16:01 -0800, Ramu
<Ramu@.discussions.microsoft.com> wrote:

>The below query is running slow in production.
What do you mean, "slow"?

>Since I made it as covering index, the query estimated cost took about
>0.008, which is a significant improvement. I have a question here...Does
>this change affects any other query to run slow? I mean, for example if the
re
>is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
>CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
So it runs about 10% faster? OK.
But it was already pretty efficient, as I presume phone_id is already
highly selective. The covering index means it can all be done in the
index instead of scanning the data. This might be more significant as
your database size grows, if it's not already fully populated.
Yes, it will cause some other queries to run a percent or three slower
because a few more pages of index will be needed, with the fatter
two-field key.
J.

index question

Hi,
The below query is running slow in production.
SELECT MIN(STMT_DATE) as STATEMENT_DATE FROM CALL_TB WHERE PHONE_ID = ?
CALL_TB table has three indexes. (1) primary key on CALL_ID (2)
non-clustered index on STMT_DATE (IX_CALL_TB2) (3) non-clustered index on
PHONE_ID (IX_CALL_TB)
The execution plan shows:
SELECT MIN(STMT_DATE) STMT_DATE FROM CALL_TB WHERE PHONE_ID =171
|--Stream Aggregate(DEFINE:([Expr1002]=MIN([CALL_TB].[STMT_DATE])))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([CALLDB].[dbo].[CALL_TB]) WITH PREFETCH)
|--Index Seek(OBJECT:([CALLDB].[dbo].[CALL_TB].[IX_CALL_TB]),
SEEK:([CALL_TB].[PHONE_ID]=171) ORDERED FORWARD)
Index seek on PHONE_ID takes estimated cost about 0.00881, where as Bookmark
Lookup takes about 9.82. Overall select statement takes estimated cost about
9.83
To reduce the estimated cost, I have modifed the non-clustered index on
PHONE_ID to include the column STMT_DATE. In order words, I made it as
composite index. First column in the index is PHONE_ID and second column is
STMT_DATE.
Since I made it as covering index, the query estimated cost took about
0.008, which is a significant improvement. I have a question here...Does
this change affects any other query to run slow? I mean, for example if there
is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
Thanks,
RamuAdding the second column to the index will not visibly change the
performance when only the first column is used. The index tree will
be a tiny bit deeper but that will not amount to anything worth
worrying about.
Roy Harvey
Beacon Falls, CT
On Sun, 14 Jan 2007 19:16:01 -0800, Ramu
<Ramu@.discussions.microsoft.com> wrote:
>Hi,
>The below query is running slow in production.
>SELECT MIN(STMT_DATE) as STATEMENT_DATE FROM CALL_TB WHERE PHONE_ID = ?
>CALL_TB table has three indexes. (1) primary key on CALL_ID (2)
>non-clustered index on STMT_DATE (IX_CALL_TB2) (3) non-clustered index on
>PHONE_ID (IX_CALL_TB)
>The execution plan shows:
>SELECT MIN(STMT_DATE) STMT_DATE FROM CALL_TB WHERE PHONE_ID =171
> |--Stream Aggregate(DEFINE:([Expr1002]=MIN([CALL_TB].[STMT_DATE])))
> |--Bookmark Lookup(BOOKMARK:([Bmk1000]),
>OBJECT:([CALLDB].[dbo].[CALL_TB]) WITH PREFETCH)
> |--Index Seek(OBJECT:([CALLDB].[dbo].[CALL_TB].[IX_CALL_TB]),
>SEEK:([CALL_TB].[PHONE_ID]=171) ORDERED FORWARD)
>
>Index seek on PHONE_ID takes estimated cost about 0.00881, where as Bookmark
>Lookup takes about 9.82. Overall select statement takes estimated cost about
>9.83
>To reduce the estimated cost, I have modifed the non-clustered index on
>PHONE_ID to include the column STMT_DATE. In order words, I made it as
>composite index. First column in the index is PHONE_ID and second column is
>STMT_DATE.
>Since I made it as covering index, the query estimated cost took about
>0.008, which is a significant improvement. I have a question here...Does
>this change affects any other query to run slow? I mean, for example if there
>is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
>CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
>Thanks,
>Ramu|||On Sun, 14 Jan 2007 19:16:01 -0800, Ramu
<Ramu@.discussions.microsoft.com> wrote:
>The below query is running slow in production.
What do you mean, "slow"?
>Since I made it as covering index, the query estimated cost took about
>0.008, which is a significant improvement. I have a question here...Does
>this change affects any other query to run slow? I mean, for example if there
>is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
>CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
So it runs about 10% faster? OK.
But it was already pretty efficient, as I presume phone_id is already
highly selective. The covering index means it can all be done in the
index instead of scanning the data. This might be more significant as
your database size grows, if it's not already fully populated.
Yes, it will cause some other queries to run a percent or three slower
because a few more pages of index will be needed, with the fatter
two-field key.
J.

index on view

If an application uses a lot of views and the performance
is slow, should we create indexes on views? What kind of
indexes to create?
Thanks.
The following link has some useful information on improving performance using indexed views:
http://msdn.microsoft.com/library/de...exedviews1.asp
However, to be specific to your scenario, you might consider running a workload through Index Tuning Wizard to see whether it suggests that you create any.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"Julia" <kqd02@.yahoo.com> wrote in message news:143df01c444cd$f6355010$a301280a@.phx.gbl...
If an application uses a lot of views and the performance
is slow, should we create indexes on views? What kind of
indexes to create?
Thanks.
|||You may consider indexed view, some people call it materialized view
too. Beware that there're alot limitations on indexed view, for detail,
read BOL.
You may also want to investigate how those views are constructed, are
they nested views? views joining another view? Based on my experience,
joining differnet views are bad idea, it may be easy to program, but
performance really sucks.
If that's not the case, run those views inside query analyzer to see if
there are any table scans, then create index accordingly.
Eric
Julia wrote:

> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
Eric Li
SQL DBA
MCDBA
|||If performance is low, then you should start by adding the appropriate
indexes to the base tables that are used in the view. SQL-Server will
automatically take these into consideration.
If that doesn't help (enough), and you are running Enterprise Edition of
SQL-Server 2000, you could consider indexed views.
Hope this helps,
Gert-Jan
Julia wrote:
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
(Please reply only to the newsgroup)
|||Hi
Just to add to the other posts...
You may also want to consider if the view is being used appropriately!!!
e.g. It is not a great idea of using a view that joins half a dozen tables
when you only want data from a single base table.
John
"Julia" <kqd02@.yahoo.com> wrote in message
news:143df01c444cd$f6355010$a301280a@.phx.gbl...
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
|||Be very careful about creating indexed views. The cost of maintenance can be
very high... So exhaust all other possibilities prior to choosing indexed
views as a solution.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Julia" <kqd02@.yahoo.com> wrote in message
news:143df01c444cd$f6355010$a301280a@.phx.gbl...
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.

index on view

If an application uses a lot of views and the performance
is slow, should we create indexes on views? What kind of
indexes to create?
Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C44498.B5850D10
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
The following link has some useful information on improving performance =using indexed views:
http://msdn.microsoft.com/library/default.asp?url=3D/library/en-us/dnsql2=
k/html/indexedviews1.asp
However, to be specific to your scenario, you might consider running a =workload through Index Tuning Wizard to see whether it suggests that you =create any.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"Julia" <kqd02@.yahoo.com> wrote in message =news:143df01c444cd$f6355010$a301280a@.phx.gbl...
If an application uses a lot of views and the performance is slow, should we create indexes on views? What kind of indexes to create?
Thanks.
--=_NextPart_000_0008_01C44498.B5850D10
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

The following link has some useful information on =improving performance using indexed views:
http://msdn.microsoft.com/library/defau=lt.asp?url=3D/library/en-us/dnsql2k/html/indexedviews1.asp
However, to be specific to your scenario, you might =consider running a workload through Index Tuning Wizard to see whether it =suggests that you create any.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"Julia" wrote in message news:143df01c444cd$=f6355010$a301280a@.phx.gbl...If an application uses a lot of views and the performance is slow, =should we create indexes on views? What kind of indexes to create?Thanks.

---

--=_NextPart_000_0008_01C44498.B5850D10--|||You may consider indexed view, some people call it materialized view
too. Beware that there're alot limitations on indexed view, for detail,
read BOL.
You may also want to investigate how those views are constructed, are
they nested views? views joining another view? Based on my experience,
joining differnet views are bad idea, it may be easy to program, but
performance really sucks.
If that's not the case, run those views inside query analyzer to see if
there are any table scans, then create index accordingly.
Eric
Julia wrote:
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
Eric Li
SQL DBA
MCDBA|||If performance is low, then you should start by adding the appropriate
indexes to the base tables that are used in the view. SQL-Server will
automatically take these into consideration.
If that doesn't help (enough), and you are running Enterprise Edition of
SQL-Server 2000, you could consider indexed views.
Hope this helps,
Gert-Jan
Julia wrote:
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
--
(Please reply only to the newsgroup)|||Hi
Just to add to the other posts...
You may also want to consider if the view is being used appropriately!!!
e.g. It is not a great idea of using a view that joins half a dozen tables
when you only want data from a single base table.
John
"Julia" <kqd02@.yahoo.com> wrote in message
news:143df01c444cd$f6355010$a301280a@.phx.gbl...
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.|||Be very careful about creating indexed views. The cost of maintenance can be
very high... So exhaust all other possibilities prior to choosing indexed
views as a solution.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Julia" <kqd02@.yahoo.com> wrote in message
news:143df01c444cd$f6355010$a301280a@.phx.gbl...
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.

Index is not faster any more

After I have modified couple columns my database access is very slow in that table. (Update Statistics Rx_Control is in Progress). It happened before I got back to same status by restoring the data. I really don't want to restore this time. Please some one post a sloution.

Thank you
Raj SankarOriginally posted by raj_sankar
After I have modified couple columns my database access is very slow in that table. (Update Statistics Rx_Control is in Progress). It happened before I got back to same status by restoring the data. I really don't want to restore this time. Please some one post a sloution.

Thank you
Raj Sankar
Are these updates on any index columns. Try rebuilding the index using
DBCC dbreindex

Joe|||Originally posted by mkg_1232000
Are these updates on any index columns. Try rebuilding the index using
DBCC dbreindex

Joe

The index is ok, Some how execution plan changed. Now every this ok after uodating the statistics. how ever the foloowing problem remains.

from query analyser.
select * from table_name where store = '3' --is faster and using index scan.

Declare @.store as int
set @.store = '3'
select * from table_name where store = @.store --is very slow and using table scan.

I don't how to fix this, it was ok before.

Index Fragmenation

I'm having trouble with slow performance due to index fragmentation. I'll insert 20000 records into a table and then have index fragmentation of like 67%! The database is so slow query this data back (over 3 minutes!). After rebuilding the index on the primary key (the only index) the same query takes less than 2 seconds. I'm using GUID as my primary key and I have recently switched from using NEWID() to using the new NEWSEQUENTAILID() to generate them. Can anyone suggest why I'm still having such a hard time with fragmentation?Unless you really NEED a GUID because you need an id that HAS to be unique across an entire network, I'd use an integer or bigint. There are a lot of articles out there regarding the performance hit associated with GUID's primarily due to page splits if memory serves. Is your primary key also a clustered index?
|||Yes it is a clustered index. I thought the problem with GUIDs in general is that they are pseudo-random. That is why I went to the newsequentialid() function that generates sequential guids across a machine.|||Index fragementation caused by using GUIDs in indexed columns shouldn't be causing the issues you're seeing.

While it is certainly true that you'll see very high index fragmentation for these columns (our production DB often has 95%+ fragmentation), this isn't necessarily going to kill your performance. We did extensive performance comparisons before decided to go with nearly 100% GUIDs are primary keys. There was certainly a perf difference, but it was negligible overall.

In fact, INSERT performance may actually increase thanks to the fact that disk hot spots are far less common.

I would examine the query plans before/after your defragmentation/rebuild your index. I think something else must be going on here.|||Its very likely that you have out of date statistics on the table that is giving you a bad query plan after the inserts. Rebuilding the index automatically updates statistics. Try just running UPDATE STATISTICS TableName after the INSERT and see if that gives you a better query plan.

Index / Join / Where clause very slow

Hello,

first of all, some facts of the case:


Table Master Table Dimension
ID Code Price ID Name
1 A44333 5000 1 "Scanner"
2 D442 3000 2 "Notebook"
3 D6644 4000 3 "Banana"

I join both tables on ID and search one time for ID and another time for Name. Looks like

(a)
SELECT AVG(Price) From Master JOIN Dimension ON Master.id = Dimension.id
WHERE master.id=1
AND Code like 'A44'
(b)
SELECT AVG(Price) From Master JOIN Dimension ON Master.id = Dimension.id
WHERE Name = 'Scanner'
AND Code like 'A44'

Why does query (b) take longer than query (a)? Dimension has 12 Rows and
Master has about 24M Rows.

For index I did
Create Index IX_Master_ID on Master(ID)
Create Index IX_Master_Code on Master(Code)
Create Index IX_Dimension_ID on Dimension(ID)
Create Index IX_Dimension_Name on Dimension(Name)

I noticed, that when i leave the Code like 'A44' clause, query (a) and (b) do take same time. I'm really confused. Can someone please help me out?

Thank you

Silaslike 'A44' should really be = 'A44'.

Have you looked at the query plans or run it with set statistics io on?

The efficiency will improve also if the optimiser knows which indexes are unique. I suspect (but don't know) that the below two are unique:
Create Index IX_Dimension_ID on Dimension(ID)
Create Index IX_Dimension_Name on Dimension(Name)
I also suspect a composite index on master would be unique:

Create Index IX_Master_ID_Code on Master(ID, Code)|||like 'A44' should really be = 'A44'.
like 'A44%' :-)

Both ID's are not unique. Of course there is a unique field but the ID in this example is just kind of a foreign key. Moreover, there are a lot of Dimensions and much much more data fields. I think it was 14 foreign keys and about 40 data fields. But I wanted to keep things simple, so I did not mention.

EDIT: Oh, my fault. In Dimension table, ID and NAME are unique!

I did take a look at the query plan, but I can't really make sense of this.
I see, that both plans are not equal, when I keep the like clause.

But i found out something very interesting. If I create a composite index , as you suggested, and delete IX_Master_ID and IX_Master_Code, so that the server only uses the composite index, then both queries have the same execution time.

Finally, this is a big problem. To grant best execution times, I will have to create a composite index, that includes nearly all columns that can be affected by a user query. Is this usual?|||Here's my 2p;
Should this not really be a LEFT JOIN?

In query B you need it to read Dimension.Name (could be an ambiguous column name - certainly sounds it!)
EDIT: 'name' is also a reserved word - you should avoid using this!

As mentioned before - your LIKE clause is wrong.
Using a LIKE comparison means that your index on the column are ignored.|||What is the Count(*) value associated with the averages in your query? Your sample data doesn't help me get a handle on the query scale.

It would help me a bunch if you could execute:SET SHOWPLAN_TEXT ON
GO
meta_your_SQL_goes_here
GO
SET SHOWPLAN_TEXT OFF
GO...and post the results so we could see just what your server is doing.

My first guess is bad statistics, but that's only a guess at this point.

Oh, and by the way George:Using a LIKE comparison means that your index on the column are ignored. is not always true. If the LIKE is unambiguous on the left (meaning there are no leading wildcards), then a LIKE can ride an index.

-PatP|||Now I'm totally confused. I did another index on Price and what happens, query (b) now is 3 times faster that (a) :confused:

Indexes are
PK_objects_year_quarter_obid
IX_objects_price
IX_objects_eid
IX_objects_zip

IX_dim_estate_estate
PK_dim_estate_eid

PLAN A

|--Compute Scalar(DEFINE:([Expr1006]=CASE WHEN [globalagg1008]=(0) THEN NULL ELSE [globalagg1010]/CONVERT_IMPLICIT(decimal(19,0),[globalagg1008],0) END))
|--Stream Aggregate(DEFINE:([globalagg1008]=SUM([partialagg1007]), [globalagg1010]=SUM([partialagg1009])))
|--Nested Loops(Inner Join)
|--Clustered Index Seek(OBJECT:([testdb].[dbo].[dim_estate].[PK_dim_Estate_eid]), SEEK:([testdb].[dbo].[dim_estate].[EID]=(3.)) ORDERED FORWARD)
|--Stream Aggregate(DEFINE:([partialagg1007]=COUNT_BIG([testdb].[dbo].[Objects].[PRICE]), [partialagg1009]=SUM([testdb].[dbo].[Objects].[PRICE])))
|--Nested Loops(Inner Join, OUTER REFERENCES:([testdb].[dbo].[Objects].[YEAR], [testdb].[dbo].[Objects].[QUARTER], [testdb].[dbo].[Objects].[OBID], [Expr1012]) OPTIMIZED WITH UNORDERED PREFETCH)
|--Merge Join(Inner Join, MERGE:([testdb].[dbo].[Objects].[YEAR], [testdb].[dbo].[Objects].[QUARTER], [testdb].[dbo].[Objects].[OBID])=([testdb].[dbo].[Objects].[YEAR], [testdb].[dbo].[Objects].[QUARTER], [testdb].[dbo].[Objects].[OBID]), RESIDUAL:([testdb].[dbo].[Objects].[YEAR] = [testdb].[dbo].[Objects].[YEAR] AND [testdb].[dbo].[Objects].[QUARTER] = [testdb].[dbo].[Objects].[QUARTER] AND [testdb].[dbo].[Objects].[OBID] = [testdb].[dbo].[Objects].[OBID]))
| |--Sort(ORDER BY:([testdb].[dbo].[Objects].[YEAR] ASC, [testdb].[dbo].[Objects].[QUARTER] ASC, [testdb].[dbo].[Objects].[OBID] ASC))
| | |--Index Seek(OBJECT:([testdb].[dbo].[Objects].[IX_objects_zip]), SEEK:([testdb].[dbo].[Objects].[ZIP] >= N'44' AND [testdb].[dbo].[Objects].[ZIP] < N'45'), WHERE:([testdb].[dbo].[Objects].[ZIP] like N'44%') ORDERED FORWARD)
| |--Index Seek(OBJECT:([testdb].[dbo].[Objects].[IX_objects_eid]), SEEK:([testdb].[dbo].[Objects].[EID]=(3.)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([testdb].[dbo].[Objects].[PK_Objects_year_quarter_obid]), SEEK:([testdb].[dbo].[Objects].[YEAR]=[testdb].[dbo].[Objects].[YEAR] AND [testdb].[dbo].[Objects].[QUARTER]=[testdb].[dbo].[Objects].[QUARTER] AND [testdb].[dbo].[Objects].[OBID]=[testdb].[dbo].[Objects].[OBID]) LOOKUP ORDERED FORWARD)

PLAN B

|--Compute Scalar(DEFINE:([Expr1006]=CASE WHEN [globalagg1008]=(0) THEN NULL ELSE [globalagg1010]/CONVERT_IMPLICIT(decimal(19,0),[globalagg1008],0) END))
|--Stream Aggregate(DEFINE:([globalagg1008]=SUM([partialagg1007]), [globalagg1010]=SUM([partialagg1009])))
|--Nested Loops(Inner Join, OUTER REFERENCES:([testdb].[dbo].[Objects].[EID]))
|--Hash Match(Aggregate, HASH:([testdb].[dbo].[Objects].[EID]), RESIDUAL:([testdb].[dbo].[Objects].[EID] = [testdb].[dbo].[Objects].[EID]) DEFINE:([partialagg1007]=COUNT_BIG([testdb].[dbo].[Objects].[PRICE]), [partialagg1009]=SUM([testdb].[dbo].[Objects].[PRICE])))
| |--Hash Match(Inner Join, HASH:([testdb].[dbo].[Objects].[YEAR], [testdb].[dbo].[Objects].[QUARTER], [testdb].[dbo].[Objects].[OBID])=([testdb].[dbo].[Objects].[YEAR], [testdb].[dbo].[Objects].[QUARTER], [testdb].[dbo].[Objects].[OBID]), RESIDUAL:([testdb].[dbo].[Objects].[YEAR] = [testdb].[dbo].[Objects].[YEAR] AND [testdb].[dbo].[Objects].[QUARTER] = [testdb].[dbo].[Objects].[QUARTER] AND [testdb].[dbo].[Objects].[OBID] = [testdb].[dbo].[Objects].[OBID]))
| |--Hash Match(Inner Join, HASH:([testdb].[dbo].[Objects].[YEAR], [testdb].[dbo].[Objects].[QUARTER], [testdb].[dbo].[Objects].[OBID])=([testdb].[dbo].[Objects].[YEAR], [testdb].[dbo].[Objects].[QUARTER], [testdb].[dbo].[Objects].[OBID]), RESIDUAL:([testdb].[dbo].[Objects].[YEAR] = [testdb].[dbo].[Objects].[YEAR] AND [testdb].[dbo].[Objects].[QUARTER] = [testdb].[dbo].[Objects].[QUARTER] AND [testdb].[dbo].[Objects].[OBID] = [testdb].[dbo].[Objects].[OBID]))
| | |--Index Seek(OBJECT:([testdb].[dbo].[Objects].[IX_objects_zip]), SEEK:([testdb].[dbo].[Objects].[ZIP] >= N'44' AND [testdb].[dbo].[Objects].[ZIP] < N'45'), WHERE:([testdb].[dbo].[Objects].[ZIP] like N'44%') ORDERED FORWARD)
| | |--Index Scan(OBJECT:([testdb].[dbo].[Objects].[IX_objects_eid]))
| |--Index Scan(OBJECT:([testdb].[dbo].[Objects].[IX_objects_price]))
|--Clustered Index Seek(OBJECT:([testdb].[dbo].[dim_estate].[PK_dim_Estate_eid]), SEEK:([testdb].[dbo].[dim_estate].[EID]=[testdb].[dbo].[Objects].[EID]), WHERE:([testdb].[dbo].[dim_estate].[Estate]='Villa') ORDERED FORWARD)

Hope you can read this language ;-)|||Your second query is much more explicit, it queries a much smaller number of rows on the estate dimension.

Once both queries are executed a few times so that the relevant data is loaded into cache, I would expect the queries to both run quickly, but the second one ought to run faster than the first one no matter how much RAM buffer you've got.

-PatP|||Thanks Pat, but isn't this strange? Look at this

(A)
Select AVG(Price) from dbo.objects
where eid=3 and zip like '44%'
(34 seconds)

is slower than

(B)
Select AVG(Price)
from dbo.objects join dbo.dim_estate on dbo.objects.eid=dbo.dim_estate.eid
where estate = 'Villa' and zip like '44%'
(13 seconds)

This doesn't make sense to me. Is it because of Microsoft SQL Server?
Ok, finally, the fastest query is the more userfriendly query (user does not have to know the right eid) but it still leaves kind of a bad taste. Just as you said (B) is better than (A).
Another question, is there something like Oracles index only table in sql server?|||There are apparently many things about your schema that are not intuitively obvious, based on the showplan output versus the code snippets that you've posted. My guess is that you've "simplified" the code snippets in some way, but posted the showplan output as it is actually generated by the SQL engine. Without a lot more "inside knowledge" to help me understand the differences, I can't offer a useful opinion.

The MS-SQL and Oracle database engines are radically different in the way that they do things. Each has its strong and weak points, neither is intrinsically "better" or "worse" than the other, they are just different. Because of the differences in implementation, I can't think of anything quite like the Oracle index only table in the Microsoft SQL environment for this example.

-PatP|||I only snipped Plan A. Showplan (b) belongs to Query (b). Nothing changed there. The Code for A originaly was

Select AVG(Price) from
dbo.objects join dbo.dim_estate on dbo.objects.eid=dbo.dim_estate.eid
where dbo.objects.eid=3 and zip like '44%'

Obviously, I do not really need the join here, that's why i left it. The execution time in both cases is the same for (a).

Anyway, here is the showplan for snipped (a) (without the fat text)

|--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011]/CONVERT_IMPLICIT(decimal(19,0),[Expr1010],0) END))
|--Stream Aggregate(DEFINE:([Expr1010]=COUNT_BIG([testdb].[dbo].[Objects].[PRICE]), [Expr1011]=SUM([testdb].[dbo].[Objects].[PRICE])))
|--Nested Loops(Inner Join, OUTER REFERENCES:([testdb].[dbo].[Objects].[YEAR], [testdb].[dbo].[Objects].[QUARTER], [testdb].[dbo].[Objects].[OBID], [Expr1009]) WITH UNORDERED PREFETCH)
|--Merge Join(Inner Join, MERGE:([testdb].[dbo].[Objects].[YEAR], [testdb].[dbo].[Objects].[QUARTER], [testdb].[dbo].[Objects].[OBID])=([testdb].[dbo].[Objects].[YEAR], [testdb].[dbo].[Objects].[QUARTER], [testdb].[dbo].[Objects].[OBID]), RESIDUAL:([testdb].[dbo].[Objects].[YEAR] = [testdb].[dbo].[Objects].[YEAR] AND [testdb].[dbo].[Objects].[QUARTER] = [testdb].[dbo].[Objects].[QUARTER] AND [testdb].[dbo].[Objects].[OBID] = [testdb].[dbo].[Objects].[OBID]))
| |--Sort(ORDER BY:([testdb].[dbo].[Objects].[YEAR] ASC, [testdb].[dbo].[Objects].[QUARTER] ASC, [testdb].[dbo].[Objects].[OBID] ASC))
| | |--Index Seek(OBJECT:([testdb].[dbo].[Objects].[IX_objects_zip]), SEEK:([testdb].[dbo].[Objects].[ZIP] >= N'44' AND [testdb].[dbo].[Objects].[ZIP] < N'45'), WHERE:([testdb].[dbo].[Objects].[ZIP] like N'44%') ORDERED FORWARD)
| |--Index Seek(OBJECT:([testdb].[dbo].[Objects].[IX_objects_eid]), SEEK:([testdb].[dbo].[Objects].[EID]=(3.)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([testdb].[dbo].[Objects].[PK_Objects_year_quarter_obid]), SEEK:([testdb].[dbo].[Objects].[YEAR]=[testdb].[dbo].[Objects].[YEAR] AND [testdb].[dbo].[Objects].[QUARTER]=[testdb].[dbo].[Objects].[QUARTER] AND [testdb].[dbo].[Objects].[OBID]=[testdb].[dbo].[Objects].[OBID]) LOOKUP ORDERED FORWARD)