SQL,tables in a DB: columns

Showing posts with label columns. Show all posts

Thursday, March 22, 2012

'(-)' in list of index columns which I get after sp_helpindexes

Hi,
Does anybody know what '(-)' means in the list of index
columns when I execute sp_helpindexes for the table.
For example:
exec sp_helpindexes <table name> returns:
column1(-),column2,column3.
I saw this several times, and it gets disapeared when I
rebuild index.
Thanks,
OJ
Descending.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:866d01c4d0d3$b19ed5b0$a601280a@.phx.gbl...
> Hi,
> Does anybody know what '(-)' means in the list of index
> columns when I execute sp_helpindexes for the table.
> For example:
> exec sp_helpindexes <table name> returns:
> column1(-),column2,column3.
> I saw this several times, and it gets disapeared when I
> rebuild index.
> Thanks,
> OJ
|||Hi OJ
It means the index was build with the index keys sorted in descending order.
If you rebuild your indexes, and don't explicitly state you want to build
them in descending order, they will be built in ascending order and the (-)
will go away.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:866d01c4d0d3$b19ed5b0$a601280a@.phx.gbl...
> Hi,
> Does anybody know what '(-)' means in the list of index
> columns when I execute sp_helpindexes for the table.
> For example:
> exec sp_helpindexes <table name> returns:
> column1(-),column2,column3.
> I saw this several times, and it gets disapeared when I
> rebuild index.
> Thanks,
> OJ

'(-)' in list of index columns which I get after sp_helpindexes

Hi,
Does anybody know what '(-)' means in the list of index
columns when I execute sp_helpindexes for the table.
For example:
exec sp_helpindexes <table name> returns:
column1(-),column2,column3.
I saw this several times, and it gets disapeared when I
rebuild index.
Thanks,
OJDescending.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:866d01c4d0d3$b19ed5b0$a601280a@.phx.gbl...
> Hi,
> Does anybody know what '(-)' means in the list of index
> columns when I execute sp_helpindexes for the table.
> For example:
> exec sp_helpindexes <table name> returns:
> column1(-),column2,column3.
> I saw this several times, and it gets disapeared when I
> rebuild index.
> Thanks,
> OJ|||Hi OJ
It means the index was build with the index keys sorted in descending order.
If you rebuild your indexes, and don't explicitly state you want to build
them in descending order, they will be built in ascending order and the (-)
will go away.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:866d01c4d0d3$b19ed5b0$a601280a@.phx.gbl...
> Hi,
> Does anybody know what '(-)' means in the list of index
> columns when I execute sp_helpindexes for the table.
> For example:
> exec sp_helpindexes <table name> returns:
> column1(-),column2,column3.
> I saw this several times, and it gets disapeared when I
> rebuild index.
> Thanks,
> OJ

'(-)' in list of index columns which I get after sp_helpindexes

Hi,
Does anybody know what '(-)' means in the list of index
columns when I execute sp_helpindexes for the table.
For example:
exec sp_helpindexes <table name> returns:
column1(-),column2,column3.
I saw this several times, and it gets disapeared when I
rebuild index.
Thanks,
OJDescending.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:866d01c4d0d3$b19ed5b0$a601280a@.phx.gbl...
> Hi,
> Does anybody know what '(-)' means in the list of index
> columns when I execute sp_helpindexes for the table.
> For example:
> exec sp_helpindexes <table name> returns:
> column1(-),column2,column3.
> I saw this several times, and it gets disapeared when I
> rebuild index.
> Thanks,
> OJ|||Hi OJ
It means the index was build with the index keys sorted in descending order.
If you rebuild your indexes, and don't explicitly state you want to build
them in descending order, they will be built in ascending order and the (-)
will go away.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:866d01c4d0d3$b19ed5b0$a601280a@.phx.gbl...
> Hi,
> Does anybody know what '(-)' means in the list of index
> columns when I execute sp_helpindexes for the table.
> For example:
> exec sp_helpindexes <table name> returns:
> column1(-),column2,column3.
> I saw this several times, and it gets disapeared when I
> rebuild index.
> Thanks,
> OJ

Tuesday, March 20, 2012

"Views"(Edited)

Hai,
I have a table named "Student" which contains four columns and three rows.I want to see just the first row only from the "Student" table and the marks should be calculated.
Here is my table
sno smark1 smark2 smark3
1 60 60 60
2 70 70 70
3 90 90 90

The Ouput Should be:
sno smarks(smark1+ smark2+smark3)
1 180
2 210
3 270

I can't get ur question!!

What is the result u expecting from this table?

If you need only the first row,

Select Top 1 * from Student

|||

This would give you the first row based on just the sno. Depending on if you would want to see row 1 or 3 would be dependent on the order by. So to get sno[1] use "order by sno" alternately for the last sno[3] "order by sno desc".

Code Snippet

Select top 1 sno from student order by sno

Select top 1 sno from student order by sno desc

|||

You can do this by simply:

select sno, coalesce(smark1,0) + coalesce(smark2,0) + coalesce(smark3,0) as smarks

from yourTable

The coalesces are done to remove NULLS from the math.

In reality, it would be better if you build your table as:

studentMarks

============

studentNumber

sequenceNumber

dateOfScore

mark

Then you can sum any number of marks like this:

select studentNumber, sum(mark)

from studentMarks

group by studentNumber

sql

"Views"(Edited)

The Ouput Should be:
sno smarks(smark1+ smark2+smark3)
1 180
2 210
3 270

I can't get ur question!!

What is the result u expecting from this table?

If you need only the first row,

Select Top 1 * from Student

|||

Code Snippet

Select top 1 sno from student order by sno

Select top 1 sno from student order by sno desc

|||

You can do this by simply:

select sno, coalesce(smark1,0) + coalesce(smark2,0) + coalesce(smark3,0) as smarks

from yourTable

The coalesces are done to remove NULLS from the math.

In reality, it would be better if you build your table as:

studentMarks

============

studentNumber

sequenceNumber

dateOfScore

mark

Then you can sum any number of marks like this:

select studentNumber, sum(mark)

from studentMarks

group by studentNumber

"Views"

The Ouput Should be:
sno smarks(smark1+ smark2+smark3)
1 180
2 210
3 270

I can't get ur question!!

What is the result u expecting from this table?

If you need only the first row,

Select Top 1 * from Student

|||

Code Snippet

Select top 1 sno from student order by sno

Select top 1 sno from student order by sno desc

|||

You can do this by simply:

select sno, coalesce(smark1,0) + coalesce(smark2,0) + coalesce(smark3,0) as smarks

from yourTable

The coalesces are done to remove NULLS from the math.

In reality, it would be better if you build your table as:

studentMarks

============

studentNumber

sequenceNumber

dateOfScore

mark

Then you can sum any number of marks like this:

select studentNumber, sum(mark)

from studentMarks

group by studentNumber

Monday, March 19, 2012

"The package contains two objects with the duplicate name" - Package Created in UI - D

I've begun to get the above error from my package. The error message refers to two output columns.

Anyone know how this could happen from within the Visual Studio 2005 UI? I've seen the other posts on this subject, and they all seemed to be creating the packages in code.

Is there any way to see all of the columns in the data flow? Or is there any other way to find out which columns it's referring to?

Thanks!

I just can think about an ugly way; switch to the XML view (right click on the package name in the explorer) and do a 'find' within the code...

Perhaps you created some outpust by hand using the advanced editor of some components

|||

Well, next time it will be the XML editor. The problem went away "by itself".

"Slipt" rows based on datetime

Hello!

I have a table that, among other columns, has two datetime columns which indicate the initial and the final time. This would be an exemple of data in this table:

row1:

initial_time: 2006-05-24 8:00:00

final_time: 2006-05-24 8:30:00

row2:

initial_time: 2006-05-24 8:35:00

final_time: 2006-05-24 9:15:00

I would like to split a row in two new rows if final time's hour is different of initial time's hour, so I would like to split row2 into:

row2_a:

initial_time: 2006-05-24 8:35:00

initial_time: 2006-05-24 8:59:59

row2_b:

initial_time: 2006-05-24 9:00:00

initial_time: 2006-05-24 9:15:00

Is it possible to do it in a query, I mean, without using procedures?

Thank you!

? It gets a bit complex, but here's one way: create table #table ( initial_time datetime, final_time datetime) insert #table values( '2006-05-24 8:00:00', '2006-05-24 8:30:00') insert #table values( '2006-05-24 8:35:00', '2006-05-24 9:15:00') SELECT CASE x.N WHEN 0 THEN T.initial_time WHEN 1 THEN DATEADD(ms, ((DATEPART(minute, T.final_time) * 60000) + (DATEPART(second, T.final_time) * 1000) + DATEPART(millisecond, T.final_time)) * -1, T.final_time) END AS initial_time, CASE x.N WHEN 0 THEN CASE DATEDIFF(hour, T.initial_time, T.final_time) WHEN 0 THEN T.final_time WHEN 1 THEN DATEADD(millisecond, -3, DATEADD(millisecond, ((DATEPART(minute, T.final_time) * 60000) + (DATEPART(second, T.final_time) * 1000) + DATEPART(millisecond, T.final_time)) * -1, T.final_time)) END WHEN 1 THEN T.final_time END AS final_timeFROM #table TJOIN( SELECT 0 UNION ALL SELECT 1) x (N) ON x.N <= DATEDIFF(hour, T.initial_time, T.final_time) drop table #tablego -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <AnaC@.discussions.microsoft..com> wrote in message news:bdb330d8-5a34-409c-aaf3-57044628fc13@.discussions.microsoft.com... Hello! I have a table that, among other columns, has two datetime columns which indicate the initial and the final time. This would be an exemple of data in this table: row1: initial_time: 2006-05-24 8:00:00 final_time: 2006-05-24 8:30:00 row2: initial_time: 2006-05-24 8:35:00 final_time: 2006-05-24 9:15:00 I would like to split a row in two new rows if final time's hour is different of initial time's hour, so I would like to split row2 into: row2_a: initial_time: 2006-05-24 8:35:00 initial_time: 2006-05-24 8:59:59 row2_b: initial_time: 2006-05-24 9:00:00 initial_time: 2006-05-24 9:15:00 Is it possible to do it in a query, I mean, without using procedures? Thank you!|||Thank you!

Friday, March 16, 2012

"Rows into Columns and Columns into Rows"

I have data in a table. I want the values in the rows to place in columns and columns into rows.
Eg:-A table. It consists of three columns and three rows.

name id dept
a 1 x
b 2 y
c 3 z

I want the resultant table should look like this

a b c
1 2 3
x y z

Whether it's possible ?

Check this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326394&SiteID=1

You will need to add some value to the rows to uniquely identify the rows when you pivot (but you could do this in a CTE or temp table.

"Row yielded no match during lookup" when using 2 columns in Lookup

I am doing a lookup that requires mapping 2 columns in the column mapping section. When I do this, I get the error "Row yielded no match during lookup" . The SQL that I captured in SQL profiler does find the record when I run it in Management Studio. I have already tried trimming everything to no avail.

Why is this happening?

I tried enabling memory restrictions but then I my package hangs and I get a SQLDUMPER_ERRORLOG.log file with the following logged:

07/24/07 13:35:48, ERROR , SQLDUMPER_UNKNOWN_APP.EXE, AdjustTokenPrivileges () failed (00000514)
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters: 4 supplied
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID = 5952
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags = 0x0
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr = 0x0100C5D0
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir = <NULL>
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExceptionRecordPtr = 0x00000000
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr = 0x00000000
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile = <NULL>
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName = <NULL>
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName = <NULL>
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11 not used
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 15 not used
07/24/07 13:35:49, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7 not used
07/24/07 13:35:49, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump completed: C:\Program Files\Microsoft SQL Server\90\Shared\ErrorDumps\SQLDmpr0033.mdmp
07/24/07 13:35:49, ACTION, DtsDebugHost.exe, Watson Invoke: No

Why am I getting this error with "Enable Memory Restriction"?

Yeah, well, also note that SSIS lookups are CaSE sensitive. Running a query in Management Studio may not be.|||I don't know why your are getting that error. Using partial cache won't fix the no-match error. By default, the lookup component treats no matches as errors; to get around taht you need to open the Lookup component and change error output to redirect-error; that way the no matches will go to the error output (red arrow)|||Your problem is probably that SQL uses different comparison rules than the SSIS Lookup does. SQL Server's default collation is case-insensitive, so it will match FooBar with FOOBAR. Lookup will see these as different strings and won't match them. Unfortunately, you can't control the Lookup behavior like you can SQL's. Best you can do is probably convert everything to upper or lower case to achieve consistency.
|||The case is exactly the same but I will try converting them all to same case all the same|||When I redirect to a flat file, ALL the records (about 900) of them get redirected. The column being looked up is a required field so I need to be able to lookup the value|||

newbie1a wrote:
When I redirect to a flat file, ALL the records (about 900) of them get redirected. The column being looked up is a required field so I need to be able to lookup the value

Watch out for trailing spaces too. Recommend you trim both the source rows and the reference rows.
|||It appears I had not trimmed everything. It works now after applying trims to the source data and the lookup. I do not need to enable memory restriction but any idea why I get "AdjustTokenPrivileges () failed (00000514)" when I do?|||Help!!! Now I am intermittently getting this "AdjustTokenPrivileges () failed (00000514)" error. What is causing this?|||

newbie1a wrote:
Help!!! Now I am intermittently getting this "AdjustTokenPrivileges () failed (00000514)" error. What is causing this?

I've never seen that error. First thing I do when a component goes goofy is to delete it and drop in a new one.

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

Monday, February 13, 2012

'concatenating' several columns together for Insert into an XML column

Hi,

I am inserting a row into a Table. One of the columns in this table is of XML datatype.

I am using a Select statement to provide the values that will be inserted into the row. However, I would like to combine some of these values and parse them into XML for insertion into the XML column.

An example might help:

The Table to insert into has the following columns:

Table name: myTable

ID int

Data1 string

Data2 string

Data3 string

DataXml xml

I am inserting into this table using the following:

Code Snippet

insertinto myTable

(Data1, Data2, Data3, DataXml)

select Data1, Data2, Data3,

(select Data4, Data5, Data6)from myOtherTable as inside

where inside.ID = outside.ID forxmlraw)

from myOtherTable as outside

This works but it strikes me as inefficient. There is an additional lookup for each row which seems unnecessary as we are already at that row of the same table.

If it was a simple concatenation, I would do something like 'Data4 + Data5 + Data6', and it seems this is not much different except that instead of concatenation everything is being wrapped in Xml.

Does anyone have any ideas of a better way to do this?

Any help much appreciated.

Holf:

I am definitely not an expert at XML. I too am learning. This is also pretty much the way that I do it. I am also interested in seeing if Martin has a better way of doing this. To me, it looks like you are doing it right.

Kent

|||

Kent,

Thanks for the response. Well, I've checked the query plan generated and I can confirm that there is defintely some nested looping going on. I was wondering if the optimizer would realize what is happening and do some shortcuts.

Given this, I'm thinking it is going to be more efficient to concatenate the XML bits in, e.g. '<row Data4="' + Data4 + '" />' etc.

I don't want to do this because to manipulate XML I'd like to use XML tools, but I'll happily concatenate if it works out to be faster.

Thanks again for the reply. I'm learning XML too, and it does seem as though there's a lot to learn just now!

Holf

|||Be aware that FOR XML will do necessary escaping (e.g. a value like "foo & bar" will be escaped as "foo & bar") while SQL string concatenation will not do any escaping that XML requires.|||Thank you, Martin, I had forgotten about that particular side effect; I have experienced pain from this particular problem a couple of times.|||

Ah yes, that is a very good point.

Well, I may write a function to which I can pass my XML elements and which will respond with some nicely crafted XML. I am considering compiling and importing a .NET assembly specifically for this task, as in .NET there are some nice tools for building up valid XML from raw elements, and these tools take care of escaping invalid characters.

I know this sounds like overkill but I still think it will be more efficient than the subquery approach above. Of course, I will be testing to find out.

Better check that my hosting provider allows upload of .NET assemblies to SQL Server...

Thanks Martin and Kent for your thoughts on this.

|||

Well, I've checked with my hosting provider and they do not allow upload of .NET assemblies. This is not surprising really, given it is a shared database and with the rights to upload assemblies, users could compromise the entire database.

So, I've written a very simple function to escape the necessary characters (of which there are not many from what I read):

Code Snippet

SETANSI_NULLSON

SETQUOTED_IDENTIFIERON

ALTERFUNCTION [dbo].[fn_EscapeForXml]

(

@.stringToEscape asnvarchar(256)

)

RETURNSnvarchar(256)

BEGIN

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'&','&')

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'<','<')

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'>','>')

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'''',''')

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'"','"')

RETURN(@.stringToEscape)

END

Note that the '&' has to be escaped first, or otherwise the '&'s resulting from the other replace operations are themselves replaced.

When I am concatenating my strings to make an XML document, I pass anything which I know may have invalid characters through this function first.

I have had some XML that I have treated in this way returned to a .NET environment. When the XML is deserialized these characters are resolved as they should be into their original form, so it all seems to be working.

Thursday, March 22, 2012

'(-)' in list of index columns which I get after sp_helpindexes

'(-)' in list of index columns which I get after sp_helpindexes

'(-)' in list of index columns which I get after sp_helpindexes

Tuesday, March 20, 2012

"Views"(Edited)

"Views"(Edited)

"Views"

Monday, March 19, 2012

"The package contains two objects with the duplicate name" - Package Created in UI - D

"Slipt" rows based on datetime

Friday, March 16, 2012

"Rows into Columns and Columns into Rows"

"Row yielded no match during lookup" when using 2 columns in Lookup

Thursday, February 16, 2012

>= AND <= or just = ?

>= AND <= or just = ?

>= AND <= or just = ?

Monday, February 13, 2012

'concatenating' several columns together for Insert into an XML column

SQL,tables in a DB

Blog Archive

About Me