index and key: clause

Showing posts with label clause. Show all posts

Friday, March 30, 2012

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

[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...

Wednesday, March 21, 2012

Index on query with both where clause and order by

What is best index for this query

Select * from Table1 whete Table1.C1 >100 and Table1.C2=23 order by Table1.C3

How many index do I need and what column order?

I would appreciate if some body can put link to complex query index guideline

Thanks for help.

It's difficult to give an optimal answer without first gaining an understanding of the distribution and volume of your data. For instance if your table contains only one row then a full table scan will be the most efficient way of returning the results. If your table contains millions of rows, yet 99% of the rows satisfy the criteria (Table1.C1 > 100 AND Table1.C2 = 23) then a table scan is still likely to be the most efficient way of obtaining the data.

As a first step, have you tried running the query past either the 'Index Tuning Wizard' or the 'Database Tuning Advisor'?

Chris

|||

Do you actually need all of the columns to be returned in the SELECT statement? Depending on the answer to that, you may be able to use a "covering index", that covers the query without having to access the base table.

In your case that means a single index would have C3, C2, C1, and all of the columns in the SELECT list included. Depending on how many columns are in the SELECT list, this may or may not be feasible. The new INCLUDED columns feature in SQL Server 2005 gives you a lot more flexibility there. You also have to consider how volatile the data is.

As far as column order for the index, it depends on the selectivity of the data in the columns. Most often, the columns in the WHERE clause are the most important (i.e. they are the first columns in the index).

I would try creating two indexes, one with C1, C2, C3, and the second with C2, C1,C3 to start.

Really, you just need to fire up a Query window in SSMS, run SET STATISTICS IO ON, turn on the graphical execution plan, and then run the query a few times with different input values, and see which gives the best results.

You would also want to parametize the query or make it a stored procedure with input parameters, so that you get one copy of it in the procedure cache. If it is hard-coded with literal values or submitted ad-hoc that way, you will have multiple copies of it in the procedure cache.

http://glennberrysqlperformance.spaces.live.com

Monday, March 19, 2012

index on datetime

If I have an index on a datetime, will a where clause with year(<datatime
column>) use the index? Or should I create a seperate index for it?
Thank you
No it won't use the index. But you can change your query so that it will
use the index. Instead of
Select ...
Where Year(MyDateTime) = 2008
do
Select ...
Where MyDateTime >= '20080101' And MyDateTime < '20090101'
and that will use the index.
Tom
"SandpointGuy" <SandpointGuy@.discussions.microsoft.com> wrote in message
news:186D952F-DB39-45F1-8ED3-70A7BA2162C7@.microsoft.com...
> If I have an index on a datetime, will a where clause with year(<datatime
> column>) use the index? Or should I create a seperate index for it?
> Thank you
|||Actually, I should clarify. If you do the query as
Select ...
Where MyDateTime >= '20080101' And MyDateTime < '20090101'
then SQL will use the index whenever SQL believes using the index will be
faster. Depending on the amount and distribution of your data and exactly
what your query is, SQL may decide that it is faster to scan the table or
use another index. In those cases, of course, SQL will not use the index.
And, if your query is "covered" by this index, either query will use the
index. But the form using the Year() function will scan the entire index,
and the other will only scan the part of the index that contain the 2008
entries.
That's the long and messy answer to your question. The short answer is that
the form of the query using the Year() function is never better at using the
index and is often worse.
Tom
"Tom Cooper" <tomcooper@.comcast.no.spam.please.net> wrote in message
news:%23DIHnGShIHA.5368@.TK2MSFTNGP04.phx.gbl...
> No it won't use the index. But you can change your query so that it will
> use the index. Instead of
> Select ...
> Where Year(MyDateTime) = 2008
> do
> Select ...
> Where MyDateTime >= '20080101' And MyDateTime < '20090101'
> and that will use the index.
> Tom
> "SandpointGuy" <SandpointGuy@.discussions.microsoft.com> wrote in message
> news:186D952F-DB39-45F1-8ED3-70A7BA2162C7@.microsoft.com...
>

index on datetime

If I have an index on a datetime, will a where clause with year(<datatime
column>) use the index? Or should I create a seperate index for it?
Thank youNo it won't use the index. But you can change your query so that it will
use the index. Instead of
Select ...
Where Year(MyDateTime) = 2008
do
Select ...
Where MyDateTime >= '20080101' And MyDateTime < '20090101'
and that will use the index.
Tom
"SandpointGuy" <SandpointGuy@.discussions.microsoft.com> wrote in message
news:186D952F-DB39-45F1-8ED3-70A7BA2162C7@.microsoft.com...
> If I have an index on a datetime, will a where clause with year(<datatime
> column>) use the index? Or should I create a seperate index for it?
> Thank you|||Actually, I should clarify. If you do the query as
Select ...
Where MyDateTime >= '20080101' And MyDateTime < '20090101'
then SQL will use the index whenever SQL believes using the index will be
faster. Depending on the amount and distribution of your data and exactly
what your query is, SQL may decide that it is faster to scan the table or
use another index. In those cases, of course, SQL will not use the index.
And, if your query is "covered" by this index, either query will use the
index. But the form using the Year() function will scan the entire index,
and the other will only scan the part of the index that contain the 2008
entries.
That's the long and messy answer to your question. The short answer is that
the form of the query using the Year() function is never better at using the
index and is often worse.
Tom
"Tom Cooper" <tomcooper@.comcast.no.spam.please.net> wrote in message
news:%23DIHnGShIHA.5368@.TK2MSFTNGP04.phx.gbl...
> No it won't use the index. But you can change your query so that it will
> use the index. Instead of
> Select ...
> Where Year(MyDateTime) = 2008
> do
> Select ...
> Where MyDateTime >= '20080101' And MyDateTime < '20090101'
> and that will use the index.
> Tom
> "SandpointGuy" <SandpointGuy@.discussions.microsoft.com> wrote in message
> news:186D952F-DB39-45F1-8ED3-70A7BA2162C7@.microsoft.com...
>> If I have an index on a datetime, will a where clause with
>> year(<datatime
>> column>) use the index? Or should I create a seperate index for it?
>> Thank you
>

Sunday, February 19, 2012

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)

Friday, March 30, 2012

Index Speed use of REPLACE in clause

Index Speed use of REPLACE in clause

Wednesday, March 21, 2012

Index on query with both where clause and order by

Monday, March 19, 2012

index on datetime

index on datetime

Sunday, February 19, 2012

Index / Join / Where clause very slow

index and key

Blog Archive

About Me