October 29, 2007 at 3:02 am
Oh really?
Then I assume that what was 'desired' was something else than what was written?
You're actually saying that the below example is 'incorrect', or 'undesired', or simply plain 'wrong'..?
[font="Courier New"]declare @n table (i int)
insert @n
select 1 union all
select 2 union all
select 11
select i from @n order by cast(i as char(2))
i
-----------
1
11
2
(3 row(s) affected)[/font]
I've written exactly what the server should to for me, and it does just that....
Unless, ofc, what I really wanted was a numeric sort.. Silly me for not sorting by numbers
instead of text, huh? 😉
You can't blame the machine for not doing what the human wants, rather than what he says
/Kenneth
October 29, 2007 at 3:05 am
Jeff Moden (10/26/2007)
Greg Snidow (10/26/2007)
Well, I can't replicate it, but I know I had an issue with it before. Maybe I'm just crazy.No, not crazy... both of the following will order data, incorrectly...
SELECT CONVERT(CHAR(10),YourDate,101)
FROM YourTable
ORDER BY CONVERT(CHAR(10),YourDate,101)
SELECT CONVERT(CHAR(10),YourDate,101) AS SomeDate
FROM YourTable
ORDER BY SomeDate...
Jeff,
I think the order in which the results are returned are, in fact, correct. Since you're forcing the optimizer to sort the results by char column (and in character chronology, this value 03/29/2004 comes before 03/30/2002).
And what I can say from this is that, it will not return in the order I intented for.
... correct way to do it, of course, is...
SELECT CONVERT(CHAR(10),YourDate,101) AS SomeDate
FROM YourTable
ORDER BY YourDate
Agreed on this:)...
--Ramesh
October 29, 2007 at 4:21 am
There is no need of Order by clause if ur default sort order is ascending.
October 29, 2007 at 4:28 am
the perfect query to rply your question is
select convert(varchar,getdate(),101)
you can get other formats by changing last parameter
Rahul arora mca 04/01
israna, panipat
hry,INDIA
October 29, 2007 at 4:43 am
Kenneth Wilhelmsson (10/29/2007)
Oh really?Then I assume that what was 'desired' was something else than what was written?
You're actually saying that the below example is 'incorrect', or 'undesired', or simply plain 'wrong'..?
[font="Courier New"]declare @n table (i int)
insert @n
select 1 union all
select 2 union all
select 11
select i from @n order by cast(i as char(2))
i
-----------
1
11
2
(3 row(s) affected)[/font]
I've written exactly what the server should to for me, and it does just that....
Unless, ofc, what I really wanted was a numeric sort.. Silly me for not sorting by numbers
instead of text, huh? 😉
You can't blame the machine for not doing what the human wants, rather than what he says
/Kenneth
Yes, really... If you want to sort dates or numbers in date or numeric order respectively, then your query is incorrect and that's what I was stating... no meaning of life rhetoric or SQL theology was intended... not sure why you feel the need to go there.
--Jeff Moden
October 29, 2007 at 5:16 am
Matt Miller (10/26/2007)
Results:raw sort 52956
convert sort 89013
and now with an index
raw sort 2550
convert sort 72000
As I mentioned - on something small - this gets "covered over". But it's rather drastic.
Matt,
agreed - there is a definitive overhead here, but I don't think that it should warrant a 'stay away from it at all cost, at all times'.. It's not *that* bad, is it?
Consider that we in the test has decided that it's worth the effort, and also to *return* (possibly not return to a console, but insert into a 'sequenced' table) a full 10 million rows. Then the differences for the sort-op's arent that huge. I mean, one minute or three, depending on what the requiremants are, aren't so bad for ten million rows after all...
I got similar figures as you, though a little different, but it's the same trend:
35020 raw sort
90423 convert sort
2483 raw sort
29126 convert sort
.. though you can see that the last 'convert sort' was a bit better than the first 'raw sort'.
The 2nd took the longest - 1,5 minutes vs ~30 seconds for the 1st and 4th...
The third is interesting in a couple of ways, the 'raw sort with a nonclustered index'.
The interesting part is that this result shows pretty well the power of a covered query when it comes to tuning.
'Raw sort' is a bit misleading label though, a better label would be - 'covered pre-sorted query',
which is what it is, and that's why we get such awesome performance from it.
This is the only query of the four that actually doesn't do a sort, even though there is an ORDER BY in the query. It's also covered by the index, so it's sufficient to just scan the index pages, and that happens to be already sorted in that order, so no sort is done at all.
By far this one gets the most efficient plan of them all.
It also serves well to show how inceredibly expensive any sort-op is.
Anyhow, sure there is a difference, but I don't think it's enough of a difference to plain disqualify the usage in each and every case. As you said, for smaller sets, the differences doesn't 'hurt' as much, even though it's there.
/Kenneth
October 29, 2007 at 5:25 am
Jeff Moden (10/29/2007)
Yes, really... If you want to sort dates or numbers in date or numeric order respectively, then your query is incorrect and that's what I was stating... no meaning of life rhetoric or SQL theology was intended... not sure why you feel the need to go there.
Don't worry Jeff, I don't intend to go there either...
I was merely pointing out that the blunt statement of 'this is wrong', was missing the context of what was wrong with it. For beginners, they may believe that there was something wrong in a query like that, or entirely missing the point of the (unspoken and not written) intention that was implied.
It's a pretty common 'mistake', when one wants to order by a certain way, but inadvertly orders by 'wrong datatype', so the end result isn't the expected one.
We should take care and explain *why* something is considered 'right' or 'wrong'... 😉
/Kenneth
October 29, 2007 at 5:28 am
vinuraj (10/29/2007)
There is no need of Order by clause if ur default sort order is ascending.
This is incorrect.
If you in anyway depend on a certain sortorder, you *must* specify an ORDER BY in the query.
There is absolutely nothing else that will guarantee that order to be maintained otherwise.
/Kenneth
October 29, 2007 at 5:37 am
Thing is the Sort Order is Default then no need for ascending sort again.
October 29, 2007 at 6:03 am
Matt Miller (10/26/2007)
raw sort 52956
convert sort 89013
and now with an index
raw sort 2550
convert sort 72000
quote]
Could someone, maybe in laymens terms, explain how you are testing these queries. This is something with which I have never been concercned because my tables are mostly under 10,000 records, but I do have two or three, not touched by users, that have almost a million. I have tried using the index wizard, but it never really seemed to do anything, probably because I do not know which columns to index.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 29, 2007 at 6:24 am
vinuraj (10/29/2007)
Thing is the Sort Order is Default then no need for ascending sort again.
If you're referring to Matt's test script, the third test, then that is correct in that you don't need to write ORDER BY for that query to skip the sort-step. The optimizer is smart enough to omit that step from the plan.
However, if you need to rely on that particular order to be returned with that order guaranteed to be retained, then you still need to specify ORDER BY. MS is very clear on that point.
/Kenneth
October 29, 2007 at 6:27 am
Greg Snidow (10/29/2007)
Could someone, maybe in laymens terms, explain how you are testing these queries. This is something with which I have never been concercned because my tables are mostly under 10,000 records, but I do have two or three, not touched by users, that have almost a million. I have tried using the index wizard, but it never really seemed to do anything, probably because I do not know which columns to index.
Greg
Greg,
I just copy and pasted Matt's scripts and ran it.
He has coded the numbers in the queries.
Another option to get additional info on what's going on, is to turn on 'Show actual executionplan'.
That way you can see what the optimizer decided to do with each query, and see what may be different etc...
/Kenneth
October 29, 2007 at 7:06 am
Greg,
Lot's of folks keep a bit of handy test table code around for these types of tests. It's built using the same method I showed you to build a Tally table way back when. In fact, it's nothing more than a Tally table with a bunch of random yet constrained data in it... here's the basic one I use...
[font="Courier New"]DROP TABLE JBMTest
GO
--===== Create and populate a 1,000,000 row test table.
     -- Column "RowNum" has a range of 1 to 1,000,000 unique numbers
     -- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers
     -- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings
     -- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers
     -- Column "SomeDate" has a range of  >=01/01/2000 and <01/01/2010 non-unique date/times
     -- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'
     --        for all rows.
     -- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)
     -- Takes about 19 seconds to execute.
 SELECT TOP 1000000
        RowNum       = IDENTITY(INT,1,1),
        SomeInt      = ABS(CHECKSUM(NEWID()))%50000+1,
        SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
                     + CHAR(ABS(CHECKSUM(NEWID()))%26+65),
        SomeCSV      = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),
        SomeMoney    = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
        SomeDate     = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
        SomeHex12    = RIGHT(NEWID(),12)
   INTO dbo.JBMTest
   FROM Master.dbo.SysColumns t1,
        Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN
--===== A table is not properly formed unless a Primary Key has been assigned
     -- Takes about 12 seconds to execute.
  ALTER TABLE dbo.JBMTest
        ADD PRIMARY KEY CLUSTERED (RowNum)
[/font]
--Jeff Moden
October 29, 2007 at 9:22 am
Greg Snidow (10/29/2007)
Could someone, maybe in laymens terms, explain how you are testing these queries. This is something with which I have never been concercned because my tables are mostly under 10,000 records, but I do have two or three, not touched by users, that have almost a million. I have tried using the index wizard, but it never really seemed to do anything, probably because I do not know which columns to index.Greg
Greg - I maintain a testing DB on all of my servers (including prod). They each have some "permanent" test data I can use. I also have a few of these "shorter term" data tables, that I customize to fit whatever I need to test.
I usually run tests on my own local SQL server instance (i.e. on my own PC), and then pass on the ones that I think will do OK to my test areas on my servers. They're definitely worth keeping on your server, because no matter how much you might test on your own dedicated machine - there's nothing like trying to see what something you THINK is well performing might do to a server with a lot of requests.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 29, 2007 at 9:42 am
Jeff Moden (10/29/2007)
Greg,Lot's of folks keep a bit of handy test table code around for these types of tests. It's built using the same method I showed you to build a Tally table way back when. In fact, it's nothing more than a Tally table with a bunch of random yet constrained data in it... here's the basic one I use...
[font="Courier New"]DROP TABLE JBMTest
GO
--===== Create and populate a 1,000,000 row test table.
     -- Column "RowNum" has a range of 1 to 1,000,000 unique numbers
     -- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers
     -- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings
     -- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers
     -- Column "SomeDate" has a range of  >=01/01/2000 and <01/01/2010 non-unique date/times
     -- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'
     --        for all rows.
     -- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)
     -- Takes about 19 seconds to execute.
 SELECT TOP 1000000
        RowNum       = IDENTITY(INT,1,1),
        SomeInt      = ABS(CHECKSUM(NEWID()))%50000+1,
        SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
                     + CHAR(ABS(CHECKSUM(NEWID()))%26+65),
        SomeCSV      = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),
        SomeMoney    = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
        SomeDate     = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
        SomeHex12    = RIGHT(NEWID(),12)
   INTO dbo.JBMTest
   FROM Master.dbo.SysColumns t1,
        Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN
--===== A table is not properly formed unless a Primary Key has been assigned
     -- Takes about 12 seconds to execute.
  ALTER TABLE dbo.JBMTest
        ADD PRIMARY KEY CLUSTERED (RowNum)
[/font]
Hey Jeff,
How could i get rid of these nbsp? I tried using MS Word as intermediator but of no use?
--Ramesh
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply