SQL,tables in a DB: seconds

Showing posts with label seconds. Show all posts

Thursday, March 22, 2012

(almost) duplicates

I have a script to remove duplicate records from a table. I have since
found out that I have dups with a date/time stamp that are a few seconds off
(thus, I guess technically making them not duplicates). How can I properly
get rid of the later records. Again the only difference is the seconds in a
date/time field.
JeffPlease post your table structures and sample data along with the script
which you use to distingush the duplicates. Without clear specifications and
an usable repro, it is hard for others to understand what would
"technically" make some rows non-duplicates when in reality, they are.
--
Anith|||Any other column to identitfy the row?
delete t
where exists (select * from t as t1 where t1.col1 = t.col1 and
t1.col_datetime < t.col_datetime)
AMB
"J. Clarke" wrote:
> I have a script to remove duplicate records from a table. I have since
> found out that I have dups with a date/time stamp that are a few seconds off
> (thus, I guess technically making them not duplicates). How can I properly
> get rid of the later records. Again the only difference is the seconds in a
> date/time field.
> Jeff
>
>|||Hmmm...wouldn't this delete everything except the most recent record (or am
I missreading this)?
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E562DCB5-4F62-4DD2-B859-2F357856DBF4@.microsoft.com...
> Any other column to identitfy the row?
> delete t
> where exists (select * from t as t1 where t1.col1 = t.col1 and
> t1.col_datetime < t.col_datetime)
>
> AMB
> "J. Clarke" wrote:
>> I have a script to remove duplicate records from a table. I have since
>> found out that I have dups with a date/time stamp that are a few seconds
>> off
>> (thus, I guess technically making them not duplicates). How can I
>> properly
>> get rid of the later records. Again the only difference is the seconds
>> in a
>> date/time field.
>> Jeff
>>|||I was afraid of this. Basically all the fields in a multiple rows contain
the same values EXCEPT the datetime field. So technically their not
duplicates (the datetimes are a couple of seconds off from each other).
However, I know the front end application had an error that was sticking
them in. I need to keep the 1st record it stuck in and get rid of the rest
that are a few seconds off from the 1st record.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%239EpuQ7MFHA.2656@.TK2MSFTNGP10.phx.gbl...
> Please post your table structures and sample data along with the script
> which you use to distingush the duplicates. Without clear specifications
> and an usable repro, it is hard for others to understand what would
> "technically" make some rows non-duplicates when in reality, they are.
> --
> Anith
>|||No, it wouldn't . this is a correlated subquery... Take a look at the table
alias t and t1... the same table but treated as 2 different tables...
This takes a row from table t and is trying to decide whether or not to
delete it... It looks to find a row in the same table ( but aliased to t1)
that has the same col1 ( supposedly the id column) but which has a date
which is < than the date of the row in t you are considering for
deletion... If that expression is true, that means there is another row
with the same key but which has an earlier date, so this row must be the
additionaly row that was added later , and there fore should be deleted.
Hope this helps..
--
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
"J. Clarke" <jaclarke01@.hotmail.comNOSPAM> wrote in message
news:u8lQjW$MFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Hmmm...wouldn't this delete everything except the most recent record (or
> am I missreading this)?
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:E562DCB5-4F62-4DD2-B859-2F357856DBF4@.microsoft.com...
>> Any other column to identitfy the row?
>> delete t
>> where exists (select * from t as t1 where t1.col1 = t.col1 and
>> t1.col_datetime < t.col_datetime)
>>
>> AMB
>> "J. Clarke" wrote:
>> I have a script to remove duplicate records from a table. I have since
>> found out that I have dups with a date/time stamp that are a few seconds
>> off
>> (thus, I guess technically making them not duplicates). How can I
>> properly
>> get rid of the later records. Again the only difference is the seconds
>> in a
>> date/time field.
>> Jeff
>>
>|||On Mon, 28 Mar 2005 10:35:43 -0500, J. Clarke wrote:
>I have a script to remove duplicate records from a table. I have since
>found out that I have dups with a date/time stamp that are a few seconds off
>(thus, I guess technically making them not duplicates). How can I properly
>get rid of the later records. Again the only difference is the seconds in a
>date/time field.
>Jeff
>
Hi Jeff,
Assuming Col1, Col2 and Col3 are exactly the same and Col4 is the
datetime column with a few seconds difference, and that you want to
delete the duplicates if the time difference is no more than 20 seconds,
use:
DELETE FROM MyTable
WHERE EXISTS
(SELECT *
FROM MyTable AS b
WHERE b.Col1 = MyTable.Col1
AND b.Col2 = MyTable.Col2
AND b.Col3 = MyTable.Col3
AND b.Col4 < MyTable.Col4
AND b.Col4 >= DATEDIFF(second, 20, MyTable.Col4))
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||similar to Hugo's idea you could also try the following:
DELETE MyTable
from MyTable my
LEFT JOIN (
select Col1, Col2, Col3, MIN(Col4)as MinCol4 from MyTable
group by Col1, Col2, Col3) as gb
ON my.Col1 = gb.Col1,
and my.Col2 = gb.Col2,
and my.Col3 = gb.Col3,
and my.Col4 = gb.MinCol4
where gb.MinCol4 IS NULL
This does not take into account any specific time lag. This would find
duplicates that are days apart not just 20 seconds.
This uses a subquery "group by" to identify distinct records and the
"earliest" (min) date stamp and then matches it back and drops all the
matching records that are not the earliest datetime.
The magic comes from the LEFT JOIN and the fact that MinCol4 IS NULL
Good luck.
--
Message posted via http://www.sqlmonster.com|||Just to make you feel better, you can try this test to prove the theory:
(also note: I inadvertantly had commas in the "and" section of the left
join)
if exists(select name from sysobjects where name = 'MyTable' and type = 'U')
drop table MyTable
go
create table MyTable(Col1 int, Col2 varchar(10), Col3 int, Col4 datetime)
insert MyTable select 1, 'Lucy', 101, '1/1/2005'
insert MyTable select 1, 'Lucy', 101, '1/2/2005'
insert MyTable select 2, 'Ricky', 102, '1/1/2005'
insert MyTable select 2, 'Ricky', 102, '1/2/2005'
insert MyTable select 2, 'Ricky', 102, '1/3/2005'
insert MyTable select 3, 'Fred', 103, '1/1/2005'
insert MyTable select 4, 'Ethel', 104, '1/1/2005'
insert MyTable select 4, 'Ethel', 104, '1/2/2005'
insert MyTable select 4, 'Ethel', 104, '1/3/2005'
insert MyTable select 4, 'Ethel', 104, '1/4/2005'
DELETE MyTable
from MyTable my
LEFT JOIN (
select Col1, Col2, Col3, MIN(Col4)as MinCol4 from MyTable
group by Col1, Col2, Col3) as gb
ON my.Col1 = gb.Col1
and my.Col2 = gb.Col2
and my.Col3 = gb.Col3
and my.Col4 = gb.MinCol4
where gb.MinCol4 IS NULL
select * from MyTable
--
Message posted via http://www.sqlmonster.com|||Ah...Thanks for the explaination Wayne. That helps alot.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%232IH7KGNFHA.1476@.TK2MSFTNGP09.phx.gbl...
> No, it wouldn't . this is a correlated subquery... Take a look at the
> table alias t and t1... the same table but treated as 2 different
> tables...
> This takes a row from table t and is trying to decide whether or not to
> delete it... It looks to find a row in the same table ( but aliased to t1)
> that has the same col1 ( supposedly the id column) but which has a date
> which is < than the date of the row in t you are considering for
> deletion... If that expression is true, that means there is another row
> with the same key but which has an earlier date, so this row must be the
> additionaly row that was added later , and there fore should be deleted.
> Hope this helps..
> --
> 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
> "J. Clarke" <jaclarke01@.hotmail.comNOSPAM> wrote in message
> news:u8lQjW$MFHA.2604@.TK2MSFTNGP10.phx.gbl...
>> Hmmm...wouldn't this delete everything except the most recent record (or
>> am I missreading this)?
>> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
>> message news:E562DCB5-4F62-4DD2-B859-2F357856DBF4@.microsoft.com...
>> Any other column to identitfy the row?
>> delete t
>> where exists (select * from t as t1 where t1.col1 = t.col1 and
>> t1.col_datetime < t.col_datetime)
>>
>> AMB
>> "J. Clarke" wrote:
>> I have a script to remove duplicate records from a table. I have since
>> found out that I have dups with a date/time stamp that are a few
>> seconds off
>> (thus, I guess technically making them not duplicates). How can I
>> properly
>> get rid of the later records. Again the only difference is the seconds
>> in a
>> date/time field.
>> Jeff
>>
>>
>|||Thanks Hugo - I'm not sure if this will work for me as the time may expand
beyond a set periord of seconds (maybe to a few minutes?). I get the gist
though - I appreciate your help and explaination!
Jeff
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:92nj411t3o257ghsm8jfm3oicddhrc6p3k@.4ax.com...
> On Mon, 28 Mar 2005 10:35:43 -0500, J. Clarke wrote:
>>I have a script to remove duplicate records from a table. I have since
>>found out that I have dups with a date/time stamp that are a few seconds
>>off
>>(thus, I guess technically making them not duplicates). How can I
>>properly
>>get rid of the later records. Again the only difference is the seconds in
>>a
>>date/time field.
>>Jeff
> Hi Jeff,
> Assuming Col1, Col2 and Col3 are exactly the same and Col4 is the
> datetime column with a few seconds difference, and that you want to
> delete the duplicates if the time difference is no more than 20 seconds,
> use:
> DELETE FROM MyTable
> WHERE EXISTS
> (SELECT *
> FROM MyTable AS b
> WHERE b.Col1 = MyTable.Col1
> AND b.Col2 = MyTable.Col2
> AND b.Col3 = MyTable.Col3
> AND b.Col4 < MyTable.Col4
> AND b.Col4 >= DATEDIFF(second, 20, MyTable.Col4))
> (untested)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Wow! Thanks for the explaination and the example. Your example I think may
work for the best of me. From one example I was peeking at I may up to 4000
'dups' off the 1st record (yikes!). I'm concerned tho, that I may have an
actual record on another day and I need to ensure I'm not including those
puppies
Jeff
"Geoffrey Kahan via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:b532bdf904ce437fb849575d7ccfd8fb@.SQLMonster.com...
> Just to make you feel better, you can try this test to prove the theory:
> (also note: I inadvertantly had commas in the "and" section of the left
> join)
> if exists(select name from sysobjects where name = 'MyTable' and type => 'U')
> drop table MyTable
> go
> create table MyTable(Col1 int, Col2 varchar(10), Col3 int, Col4 datetime)
> insert MyTable select 1, 'Lucy', 101, '1/1/2005'
> insert MyTable select 1, 'Lucy', 101, '1/2/2005'
> insert MyTable select 2, 'Ricky', 102, '1/1/2005'
> insert MyTable select 2, 'Ricky', 102, '1/2/2005'
> insert MyTable select 2, 'Ricky', 102, '1/3/2005'
> insert MyTable select 3, 'Fred', 103, '1/1/2005'
> insert MyTable select 4, 'Ethel', 104, '1/1/2005'
> insert MyTable select 4, 'Ethel', 104, '1/2/2005'
> insert MyTable select 4, 'Ethel', 104, '1/3/2005'
> insert MyTable select 4, 'Ethel', 104, '1/4/2005'
> DELETE MyTable
> from MyTable my
> LEFT JOIN (
> select Col1, Col2, Col3, MIN(Col4)as MinCol4 from MyTable
> group by Col1, Col2, Col3) as gb
> ON my.Col1 = gb.Col1
> and my.Col2 = gb.Col2
> and my.Col3 = gb.Col3
> and my.Col4 = gb.MinCol4
> where gb.MinCol4 IS NULL
> select * from MyTable
> --
> Message posted via http://www.sqlmonster.com|||I don't think this is gonna work Geoffrey - I have 928,457 records in the DB
and the scripts been running now for 16+ hours - I don't think my sites are
gonna want to be shutdown this long. It's on my local box, nothing other
than SQL QA is connected to it.
Here is my complete script (maybe I did it wrong):
DELETE dbo.TRM_VISN_REPORT
from dbo.TRM_VISN_REPORT my
LEFT JOIN (
select VistaUserName, MIN(DateTimeofCall)as MinDateTimeofCall,
UserLocStation, CallDuration, CPTCode, CPTDescription, ClinicalCall,
RegisteredPatient, ChiefComplaint, FollowupIntRec,
FollowupIntAct, FollowupLoc, CallerResponse, CallerArea, VEJDIFN,
TypeOfCall, CallFiledAtStation, PatientName, SSN
from dbo.TRM_VISN_REPORT
group by VistaUserName, UserLocStation, CallDuration, CPTCode,
CPTDescription, ClinicalCall, RegisteredPatient, ChiefComplaint,
FollowupIntRec,
FollowupIntAct, FollowupLoc, CallerResponse, CallerArea, VEJDIFN,
TypeOfCall, CallFiledAtStation, PatientName, SSN) as gb
ON my.VistaUserName = gb.VistaUserName
and my.DateTimeofCall = gb.MinDateTimeofCall
and my.UserLocStation = gb.UserLocStation
and my.CallDuration = gb.CallDuration
and my.CPTCode = gb.CPTCode
and my.CPTDescription = gb.CPTDescription
and my.ClinicalCall = gb.ClinicalCall
and my.RegisteredPatient = gb.RegisteredPatient
and my.ChiefComplaint = gb.ChiefComplaint
and my.FollowupIntRec = gb.FollowupIntRec
and my.FollowupIntAct = gb.FollowupIntAct
and my.FollowupLoc = gb.FollowupLoc
and my.CallerResponse = gb.CallerResponse
and my.CallerArea = gb.CallerArea
and my.VEJDIFN = gb.VEJDIFN
and my.TypeOfCall = gb.TypeOfCall
and my.CallFiledAtStation = gb.CallFiledAtStation
and my.PatientName = gb.PatientName
and my.SSN = gb.SSN
where gb.MinDateTimeofCall IS NULL
Jeff
"Geoffrey Kahan via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:b532bdf904ce437fb849575d7ccfd8fb@.SQLMonster.com...
> Just to make you feel better, you can try this test to prove the theory:
> (also note: I inadvertantly had commas in the "and" section of the left
> join)
> if exists(select name from sysobjects where name = 'MyTable' and type ='U')
> drop table MyTable
> go
> create table MyTable(Col1 int, Col2 varchar(10), Col3 int, Col4 datetime)
> insert MyTable select 1, 'Lucy', 101, '1/1/2005'
> insert MyTable select 1, 'Lucy', 101, '1/2/2005'
> insert MyTable select 2, 'Ricky', 102, '1/1/2005'
> insert MyTable select 2, 'Ricky', 102, '1/2/2005'
> insert MyTable select 2, 'Ricky', 102, '1/3/2005'
> insert MyTable select 3, 'Fred', 103, '1/1/2005'
> insert MyTable select 4, 'Ethel', 104, '1/1/2005'
> insert MyTable select 4, 'Ethel', 104, '1/2/2005'
> insert MyTable select 4, 'Ethel', 104, '1/3/2005'
> insert MyTable select 4, 'Ethel', 104, '1/4/2005'
> DELETE MyTable
> from MyTable my
> LEFT JOIN (
> select Col1, Col2, Col3, MIN(Col4)as MinCol4 from MyTable
> group by Col1, Col2, Col3) as gb
> ON my.Col1 = gb.Col1
> and my.Col2 = gb.Col2
> and my.Col3 = gb.Col3
> and my.Col4 = gb.MinCol4
> where gb.MinCol4 IS NULL
> select * from MyTable
> --
> Message posted via http://www.sqlmonster.comsql

Thursday, February 16, 2012

>= AND <= or just = ?

[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
AS [od]), SEEK

[od].[LName]=[@.1] AND [od].[FName]=[@.2] AND [od].[DOB_Year]
>= [@.3] AND [od].[DOB_Year] <= [@.4]) ORDERED FORWARD)
Test 2:
-- This one (in the Graphical Execution Plan) displayed an Estimated Row
Count of 7,678. Ran in less than 1 sec.
SET SHOWPLAN_TEXT ON
GO
SELECT od.OffenderID
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName >= 'James'
AND od.FName <= 'James'
AND od.DOB_Year = 1960
GO
SET SHOWPLAN_TEXT OFF
GO
--ShowPlan Results:
--SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith' AND
od.FName >= 'James' AND od.FName <= 'James' AND od.DOB_Year = 1960
-- |--Clustered Index
Seek(OBJECT

[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
AS [od]), SEEK

[od].[LName]=[@.1] AND ([od].[FName], [od].[DOB_Year]) >=
([@.2], [@.4]) AND ([od].[FName], [od].[DOB_Year]) <= ([@.3], [@.4])),
WHERE

[od].[DOB_Ye
Test 3:
-- This one (in the Graphical Execution Plan) displayed an Estimated Row
Count of 357,915. Took about 10 secs to run.
SET SHOWPLAN_TEXT ON
GO
SELECT od.OffenderID
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName = 'James'
AND od.DOB_Year = 1960
GO
SET SHOWPLAN_TEXT OFF
GO
--SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith' AND
od.FName = 'James' AND od.DOB_Year = 1960
-- |--Clustered Index
Seek(OBJECT

[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
AS [od]), SEEK

[od].[LName]=[@.1] AND [od].[FName]=[@.2] AND
[od].[DOB_Year]=[@.3]) ORDERED FORWARD)
The FName and LName columns are VARCHAR(32) NOT NULL. The DOB_Year column
is INT NOT NULL. OffenderID column is a BIGINT. It is the Primary Key, but
is non-clustered. It is not part of the Clustered Index. The table has a
clustered index of (FName, LName, DOB_Year). Table has about 15 million
rows in it currently. I tried the same type of thing with a non-clustered
index columns on a different table and ended up with less inspiring results.
I also tried it on a couple non-indexed columns, and confirmed (for myself
at least) that there was no point to that test

It seems like >= AND <= in place of = on the clustered index columns made a
significant difference for me. It would be cool if the Query Optimizer
would automatically take care of this conversion for me internally in this
situation. I would think SQL Server would recognize the >= AND <= and the =
as being equivalent and handle that when it generated the plan.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42602F3B.9612B18B@.toomuchspamalready.nl...
> If the only index on the table is a clustered index, then only the
> columns in this index are relevant. If any of the three column
> (FirstName, LastName, DOB_Year) is not part of this index, then
> rewriting them probably makes no difference.
> When you look at the query plan, make sure you differentiate between
> SEEK parameter and the predicates mentioned in the WHERE clause of the
> SEEK operator. It is the SEEK parameter that primarily determines the
> performance, because that determines which rows are read.
> I must say, I am starting to get very curious to see the actual
> (estimated) query plans of both queries. If you run "SET SHOWPLAN_TEXT
> ON" before you run the query, then SQL-Server will only generate a query
> plan. Please post both query plans. Maybe there is a data type mismatch
> somewhere, maybe there is a bug (or flaw) that you have uncovered, maybe
> we have overlooked something.
> Gert-Jan
>
> Michael C# wrote:
> <snip>
|||BTW, in the end, this particular query returns just one record... although
there are situations where I'll be pulling back as many as 500+ records with
a search like this. Thanks.
"Michael C#" <howsa@.boutdat.com> wrote in message
news:OYBUAVgQFHA.1392@.TK2MSFTNGP10.phx.gbl...
> OK, they just got the network up down there and I was able to run a few
> tests. Here are the results for you:
> Test 1:
> -- This one (in the Graphical Execution Plan) displayed an Estimated Row
> Count of 7,328. Ran in less than 1 sec.
> SET SHOWPLAN_TEXT ON
> GO
> SELECT od.OffenderID
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName = 'James'
> AND od.DOB_Year >= 1960
> AND od.DOB_Year <= 1960
> GO
> SET SHOWPLAN_TEXT OFF
> GO
> -- ShowPlan Results:
> --SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith'
> AND od.FName = 'James' AND od.DOB_Year >= 1960 AND od.DOB_Year <= 1960
> -- |--Clustered Index
> Seek(OBJECT

[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
> AS [od]), SEEK

[od].[LName]=[@.1] AND [od].[FName]=[@.2] AND
> [od].[DOB_Year]
>
> Test 2:
> -- This one (in the Graphical Execution Plan) displayed an Estimated Row
> Count of 7,678. Ran in less than 1 sec.
> SET SHOWPLAN_TEXT ON
> GO
> SELECT od.OffenderID
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName >= 'James'
> AND od.FName <= 'James'
> AND od.DOB_Year = 1960
> GO
> SET SHOWPLAN_TEXT OFF
> GO
> --ShowPlan Results:
> --SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith'
> AND od.FName >= 'James' AND od.FName <= 'James' AND od.DOB_Year = 1960
> -- |--Clustered Index
> Seek(OBJECT

[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
> AS [od]), SEEK

[od].[LName]=[@.1] AND ([od].[FName], [od].[DOB_Year]) >=
> ([@.2], [@.4]) AND ([od].[FName], [od].[DOB_Year]) <= ([@.3], [@.4])),
> WHERE

[od].[DOB_Ye
> Test 3:
> -- This one (in the Graphical Execution Plan) displayed an Estimated Row
> Count of 357,915. Took about 10 secs to run.
> SET SHOWPLAN_TEXT ON
> GO
> SELECT od.OffenderID
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName = 'James'
> AND od.DOB_Year = 1960
> GO
> SET SHOWPLAN_TEXT OFF
> GO
> --SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith'
> AND od.FName = 'James' AND od.DOB_Year = 1960
> -- |--Clustered Index
> Seek(OBJECT

[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
> AS [od]), SEEK

[od].[LName]=[@.1] AND [od].[FName]=[@.2] AND
> [od].[DOB_Year]=[@.3]) ORDERED FORWARD)
> The FName and LName columns are VARCHAR(32) NOT NULL. The DOB_Year column
> is INT NOT NULL. OffenderID column is a BIGINT. It is the Primary Key,
> but is non-clustered. It is not part of the Clustered Index. The table
> has a clustered index of (FName, LName, DOB_Year). Table has about 15
> million rows in it currently. I tried the same type of thing with a
> non-clustered index columns on a different table and ended up with less
> inspiring results. I also tried it on a couple non-indexed columns, and
> confirmed (for myself at least) that there was no point to that test

> It seems like >= AND <= in place of = on the clustered index columns made
> a significant difference for me. It would be cool if the Query Optimizer
> would automatically take care of this conversion for me internally in this
> situation. I would think SQL Server would recognize the >= AND <= and the
> = as being equivalent and handle that when it generated the plan.
>
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:42602F3B.9612B18B@.toomuchspamalready.nl...
>

>= AND <= or just = ?

[OffenderData].[dbo].[Offender_Details].[IX_Off
ender_Details]
AS [od]), SEEK

[od].[LName]=[@.1] AND [od].[FName]=[@.2] AND [
;od].[DOB_Year]
>= [@.3] AND [od].[DOB_Year] <= [@.4]) ORDERED FORWARD)
Test 2:
-- This one (in the Graphical Execution Plan) displayed an Estimated Row
Count of 7,678. Ran in less than 1 sec.
SET SHOWPLAN_TEXT ON
GO
SELECT od.OffenderID
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName >= 'James'
AND od.FName <= 'James'
AND od.DOB_Year = 1960
GO
SET SHOWPLAN_TEXT OFF
GO
--ShowPlan Results:
--SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith' AND
od.FName >= 'James' AND od.FName <= 'James' AND od.DOB_Year = 1960
-- |--Clustered Index
Seek(OBJECT

[OffenderData].[dbo].[Offender_Details].[IX_Off
ender_Details]
AS [od]), SEEK

[od].[LName]=[@.1] AND ([od].[FName],
[od].[DOB_Year]) >=
([@.2], [@.4]) AND ([od].[FName], [od].[DOB_Year]) <=
([@.3], [@.4])),
WHERE

[OffenderData].[dbo].[Offender_Details].[IX_Off
ender_Details]
AS [od]), SEEK

[od].[LName]=[@.1] AND [od].[FName]=&
#91;@.2] AND
[od].[DOB_Year]=[@.3]) ORDERED FORWARD)
The FName and LName columns are VARCHAR(32) NOT NULL. The DOB_Year column
is INT NOT NULL. OffenderID column is a BIGINT. It is the Primary Key, but
is non-clustered. It is not part of the Clustered Index. The table has a
clustered index of (FName, LName, DOB_Year). Table has about 15 million
rows in it currently. I tried the same type of thing with a non-clustered
index columns on a different table and ended up with less inspiring results.
I also tried it on a couple non-indexed columns, and confirmed (for myself
at least) that there was no point to that test

It seems like >= AND <= in place of = on the clustered index columns made a
significant difference for me. It would be cool if the Query Optimizer
would automatically take care of this conversion for me internally in this
situation. I would think SQL Server would recognize the >= AND <= and the =
as being equivalent and handle that when it generated the plan.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42602F3B.9612B18B@.toomuchspamalready.nl...
> If the only index on the table is a clustered index, then only the
> columns in this index are relevant. If any of the three column
> (FirstName, LastName, DOB_Year) is not part of this index, then
> rewriting them probably makes no difference.
> When you look at the query plan, make sure you differentiate between
> SEEK parameter and the predicates mentioned in the WHERE clause of the
> SEEK operator. It is the SEEK parameter that primarily determines the
> performance, because that determines which rows are read.
> I must say, I am starting to get very curious to see the actual
> (estimated) query plans of both queries. If you run "SET SHOWPLAN_TEXT
> ON" before you run the query, then SQL-Server will only generate a query
> plan. Please post both query plans. Maybe there is a data type mismatch
> somewhere, maybe there is a bug (or flaw) that you have uncovered, maybe
> we have overlooked something.
> Gert-Jan
>
> Michael C# wrote:
> <snip>|||BTW, in the end, this particular query returns just one record... although
there are situations where I'll be pulling back as many as 500+ records with
a search like this. Thanks.
"Michael C#" <howsa@.boutdat.com> wrote in message
news:OYBUAVgQFHA.1392@.TK2MSFTNGP10.phx.gbl...
> OK, they just got the network up down there and I was able to run a few
> tests. Here are the results for you:
> Test 1:
> -- This one (in the Graphical Execution Plan) displayed an Estimated Row
> Count of 7,328. Ran in less than 1 sec.
> SET SHOWPLAN_TEXT ON
> GO
> SELECT od.OffenderID
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName = 'James'
> AND od.DOB_Year >= 1960
> AND od.DOB_Year <= 1960
> GO
> SET SHOWPLAN_TEXT OFF
> GO
> -- ShowPlan Results:
> --SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith'
> AND od.FName = 'James' AND od.DOB_Year >= 1960 AND od.DOB_Year <= 1960
> -- |--Clustered Index
> Seek(OBJECT

[OffenderData].[dbo].[Offender_Details].[IX_O
ffender_Details]
> AS [od]), SEEK

[od].[LName]=[@.1] AND [od].[FName]
=[@.2] AND
> [od].[DOB_Year]
>
> Test 2:
> -- This one (in the Graphical Execution Plan) displayed an Estimated Row
> Count of 7,678. Ran in less than 1 sec.
> SET SHOWPLAN_TEXT ON
> GO
> SELECT od.OffenderID
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName >= 'James'
> AND od.FName <= 'James'
> AND od.DOB_Year = 1960
> GO
> SET SHOWPLAN_TEXT OFF
> GO
> --ShowPlan Results:
> --SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith'
> AND od.FName >= 'James' AND od.FName <= 'James' AND od.DOB_Year = 1960
> -- |--Clustered Index
> Seek(OBJECT

[OffenderData].[dbo].[Offender_Details].[IX_O
ffender_Details]
> AS [od]), SEEK

[od].[LName]=[@.1] AND ([od].[FName
], [od].[DOB_Year]) >=
> ([@.2], [@.4]) AND ([od].[FName], [od].[DOB_Year]) <
= ([@.3], [@.4])),
> WHERE

[OffenderData].[dbo].[Offender_Details].[IX_O
ffender_Details]
> AS [od]), SEEK

[od].[LName]=[@.1] AND [od].[FName]
=[@.2] AND
> [od].[DOB_Year]=[@.3]) ORDERED FORWARD)
> The FName and LName columns are VARCHAR(32) NOT NULL. The DOB_Year column
> is INT NOT NULL. OffenderID column is a BIGINT. It is the Primary Key,
> but is non-clustered. It is not part of the Clustered Index. The table
> has a clustered index of (FName, LName, DOB_Year). Table has about 15
> million rows in it currently. I tried the same type of thing with a
> non-clustered index columns on a different table and ended up with less
> inspiring results. I also tried it on a couple non-indexed columns, and
> confirmed (for myself at least) that there was no point to that test

>= AND <= or just = ?

This is just a general question. I just ran a query with several columns in
the WHERE Clause. It took about 10 seconds to complete. The WHERE clause
looked something like this:
DBCC DROPCLEANBUFFERS
GO
... WHERE LastName = 'Smith' AND FirstName = 'James' AND DOB_Year = 1960 ...
GO
I played around with it, and found that re-forming the WHERE Clause like
this:
DBCC DROPCLEANBUFFERS
GO
... WHERE LastName >= 'Smith' AND LastName <= 'Smith' AND FirstName >= 'James' AND FirstName <= 'James' AND DOB_Year >= 1960 AND DOB_Year <= 1960
...
GO
Resulted in the same query being executed in less than 1 second. Anyone
know why these two seemingly equivalent WHERE clauses would be so
drastically different in practice?Maybe the data is part of the dirty buffers, and is retrieved from cache
in the second query?
But seriously: check out the query plans and look at the differences.
There lies the answer.
If there really is a significant difference, then it would be
interesting to know what difference the query plan shows...
Gert-Jan
Michael C# wrote:
> This is just a general question. I just ran a query with several columns in
> the WHERE Clause. It took about 10 seconds to complete. The WHERE clause
> looked something like this:
> DBCC DROPCLEANBUFFERS
> GO
> ... WHERE LastName = 'Smith' AND FirstName = 'James' AND DOB_Year = 1960 ...
> GO
> I played around with it, and found that re-forming the WHERE Clause like
> this:
> DBCC DROPCLEANBUFFERS
> GO
> ... WHERE LastName >= 'Smith' AND LastName <= 'Smith' AND FirstName >=> 'James' AND FirstName <= 'James' AND DOB_Year >= 1960 AND DOB_Year <= 1960
> ...
> GO
> Resulted in the same query being executed in less than 1 second. Anyone
> know why these two seemingly equivalent WHERE clauses would be so
> drastically different in practice?|||According to the Query Plans it looks like switching from "=" syntax to ">=AND <=" syntax cut down the Estimated Rows on my Clustered Index Seek from
about 350,000 to about 7,000. That's the only difference - but wow, what a
difference! Anyone have any ideas on why this happens, and better yet, why
the Query Optimizer doesn't convert "=" to ">= AND <="? Now I'm wondering
what kind of effect it will have on non-clustered indexes and non-indexed
fields...
Thanks
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42601468.9462CCDF@.toomuchspamalready.nl...
> Maybe the data is part of the dirty buffers, and is retrieved from cache
> in the second query?
> But seriously: check out the query plans and look at the differences.
> There lies the answer.
> If there really is a significant difference, then it would be
> interesting to know what difference the query plan shows...
> Gert-Jan
>
> Michael C# wrote:
>> This is just a general question. I just ran a query with several columns
>> in
>> the WHERE Clause. It took about 10 seconds to complete. The WHERE
>> clause
>> looked something like this:
>> DBCC DROPCLEANBUFFERS
>> GO
>> ... WHERE LastName = 'Smith' AND FirstName = 'James' AND DOB_Year = 1960
>> ...
>> GO
>> I played around with it, and found that re-forming the WHERE Clause like
>> this:
>> DBCC DROPCLEANBUFFERS
>> GO
>> ... WHERE LastName >= 'Smith' AND LastName <= 'Smith' AND FirstName >=>> 'James' AND FirstName <= 'James' AND DOB_Year >= 1960 AND DOB_Year <=>> 1960
>> ...
>> GO
>> Resulted in the same query being executed in less than 1 second. Anyone
>> know why these two seemingly equivalent WHERE clauses would be so
>> drastically different in practice?|||Tried it with a query on a couple of columns in a non-clustered Index, and
ended up with not-so-promising results. So far it appears to work best on
Clustered Indexes...
Thanks
"Michael C#" <howsa@.boutdat.com> wrote in message
news:Og1YtKfQFHA.2584@.TK2MSFTNGP15.phx.gbl...
> According to the Query Plans it looks like switching from "=" syntax to
> ">= AND <=" syntax cut down the Estimated Rows on my Clustered Index Seek
> from about 350,000 to about 7,000. That's the only difference - but wow,
> what a difference! Anyone have any ideas on why this happens, and better
> yet, why the Query Optimizer doesn't convert "=" to ">= AND <="? Now I'm
> wondering what kind of effect it will have on non-clustered indexes and
> non-indexed fields...
> Thanks
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:42601468.9462CCDF@.toomuchspamalready.nl...
>> Maybe the data is part of the dirty buffers, and is retrieved from cache
>> in the second query?
>> But seriously: check out the query plans and look at the differences.
>> There lies the answer.
>> If there really is a significant difference, then it would be
>> interesting to know what difference the query plan shows...
>> Gert-Jan
>>
>> Michael C# wrote:
>> This is just a general question. I just ran a query with several
>> columns in
>> the WHERE Clause. It took about 10 seconds to complete. The WHERE
>> clause
>> looked something like this:
>> DBCC DROPCLEANBUFFERS
>> GO
>> ... WHERE LastName = 'Smith' AND FirstName = 'James' AND DOB_Year = 1960
>> ...
>> GO
>> I played around with it, and found that re-forming the WHERE Clause like
>> this:
>> DBCC DROPCLEANBUFFERS
>> GO
>> ... WHERE LastName >= 'Smith' AND LastName <= 'Smith' AND FirstName >=>> 'James' AND FirstName <= 'James' AND DOB_Year >= 1960 AND DOB_Year <=>> 1960
>> ...
>> GO
>> Resulted in the same query being executed in less than 1 second. Anyone
>> know why these two seemingly equivalent WHERE clauses would be so
>> drastically different in practice?
>|||I hope column DOB_Year doesn't happen to be a varchar? That would be an
explanation.
Does the performance also increase if you change just one of the
predicates? Or does a rewrite of each predicate improve the performance?
If the guess (above) about the data type turns out to be correct, then
only the predicate with DOB_Year would make the difference.
In addition, if the clustered index seek cuts down the estimated number
of rows, then the seek parameters must be different (or a different
index is used).
Gert-Jan
Michael C# wrote:
> According to the Query Plans it looks like switching from "=" syntax to ">=> AND <=" syntax cut down the Estimated Rows on my Clustered Index Seek from
> about 350,000 to about 7,000. That's the only difference - but wow, what a
> difference! Anyone have any ideas on why this happens, and better yet, why
> the Query Optimizer doesn't convert "=" to ">= AND <="? Now I'm wondering
> what kind of effect it will have on non-clustered indexes and non-indexed
> fields...
> Thanks
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:42601468.9462CCDF@.toomuchspamalready.nl...
> > Maybe the data is part of the dirty buffers, and is retrieved from cache
> > in the second query?
> >
> > But seriously: check out the query plans and look at the differences.
> > There lies the answer.
> >
> > If there really is a significant difference, then it would be
> > interesting to know what difference the query plan shows...
> >
> > Gert-Jan
> >
> >
> > Michael C# wrote:
> >>
> >> This is just a general question. I just ran a query with several columns
> >> in
> >> the WHERE Clause. It took about 10 seconds to complete. The WHERE
> >> clause
> >> looked something like this:
> >>
> >> DBCC DROPCLEANBUFFERS
> >> GO
> >> ... WHERE LastName = 'Smith' AND FirstName = 'James' AND DOB_Year = 1960
> >> ...
> >> GO
> >>
> >> I played around with it, and found that re-forming the WHERE Clause like
> >> this:
> >>
> >> DBCC DROPCLEANBUFFERS
> >> GO
> >> ... WHERE LastName >= 'Smith' AND LastName <= 'Smith' AND FirstName >=> >> 'James' AND FirstName <= 'James' AND DOB_Year >= 1960 AND DOB_Year <=> >> 1960
> >> ...
> >> GO
> >>
> >> Resulted in the same query being executed in less than 1 second. Anyone
> >> know why these two seemingly equivalent WHERE clauses would be so
> >> drastically different in practice?|||DOB_Year is an INT. In addition, there are DOB_Month (INT) and DOB_Day
(INT) columns. I re-wrote all the predicates - did not try them
individually since this got me such a good result (no point in breaking it).
There is one Clustered Index on this table. No non-clustered indexes.
The Seek Parameters must be different then, and it appears to be caused by
the different operators used. Nothing else on the query, or on the table or
index have been changed. I just find this whole thing fascinating. I was
thinking this might be common knowledge I had missed out on somewhere along
the way. Anyways, thanks.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:426022AB.7C56005D@.toomuchspamalready.nl...
>I hope column DOB_Year doesn't happen to be a varchar? That would be an
> explanation.
> Does the performance also increase if you change just one of the
> predicates? Or does a rewrite of each predicate improve the performance?
> If the guess (above) about the data type turns out to be correct, then
> only the predicate with DOB_Year would make the difference.
> In addition, if the clustered index seek cuts down the estimated number
> of rows, then the seek parameters must be different (or a different
> index is used).
> Gert-Jan
>
> Michael C# wrote:
>> According to the Query Plans it looks like switching from "=" syntax to
>> ">=>> AND <=" syntax cut down the Estimated Rows on my Clustered Index Seek
>> from
>> about 350,000 to about 7,000. That's the only difference - but wow, what
>> a
>> difference! Anyone have any ideas on why this happens, and better yet,
>> why
>> the Query Optimizer doesn't convert "=" to ">= AND <="? Now I'm
>> wondering
>> what kind of effect it will have on non-clustered indexes and non-indexed
>> fields...
>> Thanks
>> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
>> news:42601468.9462CCDF@.toomuchspamalready.nl...
>> > Maybe the data is part of the dirty buffers, and is retrieved from
>> > cache
>> > in the second query?
>> >
>> > But seriously: check out the query plans and look at the differences.
>> > There lies the answer.
>> >
>> > If there really is a significant difference, then it would be
>> > interesting to know what difference the query plan shows...
>> >
>> > Gert-Jan
>> >
>> >
>> > Michael C# wrote:
>> >>
>> >> This is just a general question. I just ran a query with several
>> >> columns
>> >> in
>> >> the WHERE Clause. It took about 10 seconds to complete. The WHERE
>> >> clause
>> >> looked something like this:
>> >>
>> >> DBCC DROPCLEANBUFFERS
>> >> GO
>> >> ... WHERE LastName = 'Smith' AND FirstName = 'James' AND DOB_Year =>> >> 1960
>> >> ...
>> >> GO
>> >>
>> >> I played around with it, and found that re-forming the WHERE Clause
>> >> like
>> >> this:
>> >>
>> >> DBCC DROPCLEANBUFFERS
>> >> GO
>> >> ... WHERE LastName >= 'Smith' AND LastName <= 'Smith' AND FirstName >=>> >> 'James' AND FirstName <= 'James' AND DOB_Year >= 1960 AND DOB_Year <=>> >> 1960
>> >> ...
>> >> GO
>> >>
>> >> Resulted in the same query being executed in less than 1 second.
>> >> Anyone
>> >> know why these two seemingly equivalent WHERE clauses would be so
>> >> drastically different in practice?|||> (INT) columns. I re-wrote all the predicates - did not try them
> individually since this got me such a good result (no point in breaking
> it).
Might be worth trying it so you'll know exactly what is going on next time
this comes up. Wouldn't hurt to validate Gert-Jan's theories, either.|||I can't get on the server right now - they're doing some network maintenance
at the facility. I'll duplicate the code and play around with it when I can
get access to them. BTW, the theory is that "changing DOB_Year from '='
format to '>= AND <=' format is the only change that makes a difference"
right? I'll test that later, but it would be interesting to know the train
of thought behind that particular theory. Thanks.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OemA8zfQFHA.2948@.TK2MSFTNGP14.phx.gbl...
>> (INT) columns. I re-wrote all the predicates - did not try them
>> individually since this got me such a good result (no point in breaking
>> it).
> Might be worth trying it so you'll know exactly what is going on next time
> this comes up. Wouldn't hurt to validate Gert-Jan's theories, either.
>|||If the only index on the table is a clustered index, then only the
columns in this index are relevant. If any of the three column
(FirstName, LastName, DOB_Year) is not part of this index, then
rewriting them probably makes no difference.
When you look at the query plan, make sure you differentiate between
SEEK parameter and the predicates mentioned in the WHERE clause of the
SEEK operator. It is the SEEK parameter that primarily determines the
performance, because that determines which rows are read.
I must say, I am starting to get very curious to see the actual
(estimated) query plans of both queries. If you run "SET SHOWPLAN_TEXT
ON" before you run the query, then SQL-Server will only generate a query
plan. Please post both query plans. Maybe there is a data type mismatch
somewhere, maybe there is a bug (or flaw) that you have uncovered, maybe
we have overlooked something.
Gert-Jan
Michael C# wrote:
> DOB_Year is an INT. In addition, there are DOB_Month (INT) and DOB_Day
> (INT) columns. I re-wrote all the predicates - did not try them
> individually since this got me such a good result (no point in breaking it).
> There is one Clustered Index on this table. No non-clustered indexes.
> The Seek Parameters must be different then, and it appears to be caused by
> the different operators used. Nothing else on the query, or on the table or
> index have been changed. I just find this whole thing fascinating. I was
> thinking this might be common knowledge I had missed out on somewhere along
> the way. Anyways, thanks.
<snip>|||OK, they just got the network up down there and I was able to run a few
tests. Here are the results for you:
Test 1:
-- This one (in the Graphical Execution Plan) displayed an Estimated Row
Count of 7,328. Ran in less than 1 sec.
SET SHOWPLAN_TEXT ON
GO
SELECT od.OffenderID
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName = 'James'
AND od.DOB_Year >= 1960
AND od.DOB_Year <= 1960
GO
SET SHOWPLAN_TEXT OFF
GO
-- ShowPlan Results:
--SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith' AND
od.FName = 'James' AND od.DOB_Year >= 1960 AND od.DOB_Year <= 1960
-- |--Clustered Index
Seek(OBJECT:([OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
AS [od]), SEEK:([od].[LName]=[@.1] AND [od].[FName]=[@.2] AND [od].[DOB_Year]
>= [@.3] AND [od].[DOB_Year] <= [@.4]) ORDERED FORWARD)
Test 2:
-- This one (in the Graphical Execution Plan) displayed an Estimated Row
Count of 7,678. Ran in less than 1 sec.
SET SHOWPLAN_TEXT ON
GO
SELECT od.OffenderID
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName >= 'James'
AND od.FName <= 'James'
AND od.DOB_Year = 1960
GO
SET SHOWPLAN_TEXT OFF
GO
--ShowPlan Results:
--SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith' AND
od.FName >= 'James' AND od.FName <= 'James' AND od.DOB_Year = 1960
-- |--Clustered Index
Seek(OBJECT:([OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
AS [od]), SEEK:([od].[LName]=[@.1] AND ([od].[FName], [od].[DOB_Year]) >=([@.2], [@.4]) AND ([od].[FName], [od].[DOB_Year]) <= ([@.3], [@.4])),
WHERE:([od].[DOB_Ye
Test 3:
-- This one (in the Graphical Execution Plan) displayed an Estimated Row
Count of 357,915. Took about 10 secs to run.
SET SHOWPLAN_TEXT ON
GO
SELECT od.OffenderID
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName = 'James'
AND od.DOB_Year = 1960
GO
SET SHOWPLAN_TEXT OFF
GO
--SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith' AND
od.FName = 'James' AND od.DOB_Year = 1960
-- |--Clustered Index
Seek(OBJECT:([OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
AS [od]), SEEK:([od].[LName]=[@.1] AND [od].[FName]=[@.2] AND
[od].[DOB_Year]=[@.3]) ORDERED FORWARD)
The FName and LName columns are VARCHAR(32) NOT NULL. The DOB_Year column
is INT NOT NULL. OffenderID column is a BIGINT. It is the Primary Key, but
is non-clustered. It is not part of the Clustered Index. The table has a
clustered index of (FName, LName, DOB_Year). Table has about 15 million
rows in it currently. I tried the same type of thing with a non-clustered
index columns on a different table and ended up with less inspiring results.
I also tried it on a couple non-indexed columns, and confirmed (for myself
at least) that there was no point to that test :)
It seems like >= AND <= in place of = on the clustered index columns made a
significant difference for me. It would be cool if the Query Optimizer
would automatically take care of this conversion for me internally in this
situation. I would think SQL Server would recognize the >= AND <= and the =as being equivalent and handle that when it generated the plan.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42602F3B.9612B18B@.toomuchspamalready.nl...
> If the only index on the table is a clustered index, then only the
> columns in this index are relevant. If any of the three column
> (FirstName, LastName, DOB_Year) is not part of this index, then
> rewriting them probably makes no difference.
> When you look at the query plan, make sure you differentiate between
> SEEK parameter and the predicates mentioned in the WHERE clause of the
> SEEK operator. It is the SEEK parameter that primarily determines the
> performance, because that determines which rows are read.
> I must say, I am starting to get very curious to see the actual
> (estimated) query plans of both queries. If you run "SET SHOWPLAN_TEXT
> ON" before you run the query, then SQL-Server will only generate a query
> plan. Please post both query plans. Maybe there is a data type mismatch
> somewhere, maybe there is a bug (or flaw) that you have uncovered, maybe
> we have overlooked something.
> Gert-Jan
>
> Michael C# wrote:
>> DOB_Year is an INT. In addition, there are DOB_Month (INT) and DOB_Day
>> (INT) columns. I re-wrote all the predicates - did not try them
>> individually since this got me such a good result (no point in breaking
>> it).
>> There is one Clustered Index on this table. No non-clustered indexes.
>> The Seek Parameters must be different then, and it appears to be caused
>> by
>> the different operators used. Nothing else on the query, or on the table
>> or
>> index have been changed. I just find this whole thing fascinating. I
>> was
>> thinking this might be common knowledge I had missed out on somewhere
>> along
>> the way. Anyways, thanks.
> <snip>|||BTW, in the end, this particular query returns just one record... although
there are situations where I'll be pulling back as many as 500+ records with
a search like this. Thanks.
"Michael C#" <howsa@.boutdat.com> wrote in message
news:OYBUAVgQFHA.1392@.TK2MSFTNGP10.phx.gbl...
> OK, they just got the network up down there and I was able to run a few
> tests. Here are the results for you:
> Test 1:
> -- This one (in the Graphical Execution Plan) displayed an Estimated Row
> Count of 7,328. Ran in less than 1 sec.
> SET SHOWPLAN_TEXT ON
> GO
> SELECT od.OffenderID
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName = 'James'
> AND od.DOB_Year >= 1960
> AND od.DOB_Year <= 1960
> GO
> SET SHOWPLAN_TEXT OFF
> GO
> -- ShowPlan Results:
> --SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith'
> AND od.FName = 'James' AND od.DOB_Year >= 1960 AND od.DOB_Year <= 1960
> -- |--Clustered Index
> Seek(OBJECT:([OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
> AS [od]), SEEK:([od].[LName]=[@.1] AND [od].[FName]=[@.2] AND
> [od].[DOB_Year]
> >= [@.3] AND [od].[DOB_Year] <= [@.4]) ORDERED FORWARD)
>
> Test 2:
> -- This one (in the Graphical Execution Plan) displayed an Estimated Row
> Count of 7,678. Ran in less than 1 sec.
> SET SHOWPLAN_TEXT ON
> GO
> SELECT od.OffenderID
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName >= 'James'
> AND od.FName <= 'James'
> AND od.DOB_Year = 1960
> GO
> SET SHOWPLAN_TEXT OFF
> GO
> --ShowPlan Results:
> --SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith'
> AND od.FName >= 'James' AND od.FName <= 'James' AND od.DOB_Year = 1960
> -- |--Clustered Index
> Seek(OBJECT:([OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
> AS [od]), SEEK:([od].[LName]=[@.1] AND ([od].[FName], [od].[DOB_Year]) >=> ([@.2], [@.4]) AND ([od].[FName], [od].[DOB_Year]) <= ([@.3], [@.4])),
> WHERE:([od].[DOB_Ye
> Test 3:
> -- This one (in the Graphical Execution Plan) displayed an Estimated Row
> Count of 357,915. Took about 10 secs to run.
> SET SHOWPLAN_TEXT ON
> GO
> SELECT od.OffenderID
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName = 'James'
> AND od.DOB_Year = 1960
> GO
> SET SHOWPLAN_TEXT OFF
> GO
> --SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith'
> AND od.FName = 'James' AND od.DOB_Year = 1960
> -- |--Clustered Index
> Seek(OBJECT:([OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
> AS [od]), SEEK:([od].[LName]=[@.1] AND [od].[FName]=[@.2] AND
> [od].[DOB_Year]=[@.3]) ORDERED FORWARD)
> The FName and LName columns are VARCHAR(32) NOT NULL. The DOB_Year column
> is INT NOT NULL. OffenderID column is a BIGINT. It is the Primary Key,
> but is non-clustered. It is not part of the Clustered Index. The table
> has a clustered index of (FName, LName, DOB_Year). Table has about 15
> million rows in it currently. I tried the same type of thing with a
> non-clustered index columns on a different table and ended up with less
> inspiring results. I also tried it on a couple non-indexed columns, and
> confirmed (for myself at least) that there was no point to that test :)
> It seems like >= AND <= in place of = on the clustered index columns made
> a significant difference for me. It would be cool if the Query Optimizer
> would automatically take care of this conversion for me internally in this
> situation. I would think SQL Server would recognize the >= AND <= and the
> = as being equivalent and handle that when it generated the plan.
>
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:42602F3B.9612B18B@.toomuchspamalready.nl...
>> If the only index on the table is a clustered index, then only the
>> columns in this index are relevant. If any of the three column
>> (FirstName, LastName, DOB_Year) is not part of this index, then
>> rewriting them probably makes no difference.
>> When you look at the query plan, make sure you differentiate between
>> SEEK parameter and the predicates mentioned in the WHERE clause of the
>> SEEK operator. It is the SEEK parameter that primarily determines the
>> performance, because that determines which rows are read.
>> I must say, I am starting to get very curious to see the actual
>> (estimated) query plans of both queries. If you run "SET SHOWPLAN_TEXT
>> ON" before you run the query, then SQL-Server will only generate a query
>> plan. Please post both query plans. Maybe there is a data type mismatch
>> somewhere, maybe there is a bug (or flaw) that you have uncovered, maybe
>> we have overlooked something.
>> Gert-Jan
>>
>> Michael C# wrote:
>> DOB_Year is an INT. In addition, there are DOB_Month (INT) and DOB_Day
>> (INT) columns. I re-wrote all the predicates - did not try them
>> individually since this got me such a good result (no point in breaking
>> it).
>> There is one Clustered Index on this table. No non-clustered indexes.
>> The Seek Parameters must be different then, and it appears to be caused
>> by
>> the different operators used. Nothing else on the query, or on the
>> table or
>> index have been changed. I just find this whole thing fascinating. I
>> was
>> thinking this might be common knowledge I had missed out on somewhere
>> along
>> the way. Anyways, thanks.
>> <snip>
>|||Michael,
Thanks for posting the query plans. It is an interesting case.
The third query plan should be lightning fast! It does an exact match on
the clustered index key. It should be faster than queries 1 and 2, which
only partially seek the (same) index. Query 2 should be the slowest.
Do you have the latest service pack installed?
The only explanation I can think of, is that the third query is not
actually using the clustered index, but is scanning the primary key
index. All nonclustered indexes also contain the clustered index key.
For your query, the primary key index is a covering index. In that
scenario, the (much higher) row count estimation would make sense.
However, that is not what the query plan is reporting, so there is a bug
somewhere...
SQL-Server would consider two access paths for your query, and selected
the fastest.
Scenario 1: seek the nonunique clustered index and return the all
corresponding rows.
Scenario 2: scan the (entire) unique primary key index and return the
corresponding OffenderID.
SQL-Server will optimize for a cold cache, so it will minimize the
reads. If the table is very wide, and the primary key index narrow, then
reading the entire primary key index might involve less reads than
seeking the clustered index. In that case (and only in that case) it
would select scenario 1.
You probably have a hot cache. Most of the table data will be in memory.
For your situation SQL-Server's choice is obviously a very poor choice.
Maybe because of its focus on a cold cache, but most likely it is simply
a bug / flaw.
You can use SQL Profiler to view the actual query plan (event
Performance/Execution Plan), or in SQL Query Analyzer, press CTRL + K
before running the query. Maybe that will report a nonclustered index
scan for the 'bad' query plan...
Hope this helps,
Gert-Jan
<snip>
> Test 3:
> -- This one (in the Graphical Execution Plan) displayed an Estimated Row
> Count of 357,915. Took about 10 secs to run.
> SET SHOWPLAN_TEXT ON
> GO
> SELECT od.OffenderID
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName = 'James'
> AND od.DOB_Year = 1960
> GO
> SET SHOWPLAN_TEXT OFF
> GO
> --SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith' AND
> od.FName = 'James' AND od.DOB_Year = 1960
> -- |--Clustered Index
> Seek(OBJECT:([OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
> AS [od]), SEEK:([od].[LName]=[@.1] AND [od].[FName]=[@.2] AND
> [od].[DOB_Year]=[@.3]) ORDERED FORWARD)
<snip>
> OffenderID column is a BIGINT. It is the Primary Key, but is non-clustered.
<snip>|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42604250.4392E900@.toomuchspamalready.nl...
> Michael,
> Thanks for posting the query plans. It is an interesting case.
> The third query plan should be lightning fast! It does an exact match on
> the clustered index key. It should be faster than queries 1 and 2, which
> only partially seek the (same) index. Query 2 should be the slowest.
> Do you have the latest service pack installed?
Service Pack 3a. I'm testing Service Pack 4 Beta on another machine but it
doesn't have this particular database installed on it. And no way am I
putting a Beta on my production server :)
> The only explanation I can think of, is that the third query is not
> actually using the clustered index, but is scanning the primary key
> index. All nonclustered indexes also contain the clustered index key.
> For your query, the primary key index is a covering index. In that
> scenario, the (much higher) row count estimation would make sense.
> However, that is not what the query plan is reporting, so there is a bug
> somewhere...
You're right, the query plan doesn't seem to be concerned with the Primary
Key, which is good :) But the extremely high row count estimation is
throwing me a little off here. I suppose if they have a fix for this in SP
4, then I'll fix my query accordingly when it's released. In the meantime,
I'll just leave it in the >= AND <= format.
> SQL-Server would consider two access paths for your query, and selected
> the fastest.
> Scenario 1: seek the nonunique clustered index and return the all
> corresponding rows.
> Scenario 2: scan the (entire) unique primary key index and return the
> corresponding OffenderID.
> SQL-Server will optimize for a cold cache, so it will minimize the
> reads. If the table is very wide, and the primary key index narrow, then
> reading the entire primary key index might involve less reads than
> seeking the clustered index. In that case (and only in that case) it
> would select scenario 1.
> You probably have a hot cache. Most of the table data will be in memory.
> For your situation SQL-Server's choice is obviously a very poor choice.
> Maybe because of its focus on a cold cache, but most likely it is simply
> a bug / flaw.
> You can use SQL Profiler to view the actual query plan (event
> Performance/Execution Plan), or in SQL Query Analyzer, press CTRL + K
> before running the query. Maybe that will report a nonclustered index
> scan for the 'bad' query plan...
Already done that :)
I've been performing DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE before
each test. Is there another statement I can use to ensure all cached data
is cleared out before I test it again?
When I look at the actual query plan, I get the following results:
For Test #1:
--SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith' AND
od.FName = 'James' AND od.DOB_Year >= 1960 AND od.DOB_Year <= 1960
Phys. Op: Clustered Index Seek
Log. Op: Clustered Index Seek
Row Count: 226
Est. Row Size: 15
I/O Cost: 0.0750
CPU Cost: 0.00813
No. of Executes: 1
Cost: 0.083195
Subtree Cost: 0.0831
Est. Row Count: 7,328
For Test #3:
--SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith' AND
od.FName = 'James' AND od.DOB_Year = 1960
Phys. Op: Clustered Index Seek
Log. Op: Clustered Index Seek
Row Count: 120
Est. Row Size: 15
I/O Cost: 3.53
CPU Cost: 0.393
No. of Executes: 1
Cost: 3.926618
Subtree Cost: 3.93
Est. Row Count: 357,915
> Hope this helps,
> Gert-Jan
Actually I still don't understand why "=" on a clustered index seek is
slower than ">= AND <=", but for now I'm just happy I stumbled onto the
info. Thanks.|||<snip>
> I've been performing DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE before
> each test. Is there another statement I can use to ensure all cached data
> is cleared out before I test it again?
No, that's about it. The only thing you could add is CHECKPOINT so the
dirty pages are written to disk.
And since you were testing with a cold cache, this affirms that it is a
bug.
<snip>
> Actually I still don't understand why "=" on a clustered index seek is
> slower than ">= AND <=", but for now I'm just happy I stumbled onto the
> info. Thanks.
It isn't faster. Or at least, it shouldn't be. This is definitely a bug.
I have never seen that the query plan reports a clustered index seek and
that the index is not actually seeked.
I tried to reproduce the problem by creating a table with similar
properties as yours. I tried it with a 5 GB 15 mln row table and with a
9 GB 10 mln row table, but didn't succeed in replicating the problem.
But I did notice a very small difference. Your query plan reports
"[od].[DOB_Year]=[@.3]" whereas mine reported
"[od].[DOB_Year]=Convert([@.3])", but I don't see how that would explain
the problem.
Since it is a bug, maybe one of these syntaxis work around it:
1) force use of clustered index
SELECT OffenderID FROM Offender_Details (index=1)
WHERE LName = 'Smith' AND FName = 'James' AND DOB_Year = 1960
2) make sure it is not the parallellism bug
SELECT OffenderID FROM Offender_Details
WHERE LName = 'Smith' AND FName = 'James' AND DOB_Year = 1960
OPTION (maxdop 1)
3) make sure it is not a casting problem
SELECT OffenderID FROM Offender_Details
WHERE LName = 'Smith' AND FName = 'James' AND DOB_Year = CAST(1960 as
int)
Maybe somebody else can reproduce the problem, or maybe it is possible
to create a repro script. I am out of ideas...
Gert-Jan

Thursday, March 22, 2012

(almost) duplicates

(almost) duplicates

(almost) duplicates

Thursday, February 16, 2012

>= AND <= or just = ?

>= AND <= or just = ?

>= AND <= or just = ?

SQL,tables in a DB

Blog Archive

About Me