Order By computed columns

Hi all,
I have a long runing query took 70s and returns only 124 rows. I found the
problem is that it uses a compute column in the Order By clause. Something
like this
SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
If I took that Order By away, it only takes 2s. (That make me think my C#
client code can sort better than that :P )
Can anyone show me what are the ways I can do to optimize it?
Any thoughts are appreciated.
ConradConrad Chan wrote:
> Hi all,
> I have a long runing query took 70s and returns only 124 rows. I found th
e
> problem is that it uses a compute column in the Order By clause. Somethin
g
> like this
> SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
> ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
> If I took that Order By away, it only takes 2s. (That make me think my C#
> client code can sort better than that :P )
> Can anyone show me what are the ways I can do to optimize it?
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
If that expression is in the SELECT clause you can use
ORDER BY n
where n is the ordinal number of the expression in the SELECT clause.
E.g.:
SELECT col1, col2, (col4 * 0.25) / 100, ...
FROM ...
ORDER BY 3
Will sort the resultset by the value of the 3rd column in the SELECT
clause.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjdSGYechKqOuFEgEQKsQgCggvSDYuQCwIcw
DXSdEtuVA3YD+b4AnixS
BnAeboIAn+Ja2WD/GUp486uA
=1vFd
--END PGP SIGNATURE--|||If you can do it in the client side, then do it.
AMB
"Conrad Chan" wrote:

> Hi all,
> I have a long runing query took 70s and returns only 124 rows. I found th
e
> problem is that it uses a compute column in the Order By clause. Somethin
g
> like this
> SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
> ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
> If I took that Order By away, it only takes 2s. (That make me think my C#
> client code can sort better than that :P )
> Can anyone show me what are the ways I can do to optimize it?
> Any thoughts are appreciated.
> Conrad|||I will say only if db really cannot do a better job.
Thanks
Conrad
"Alejandro Mesa" wrote:
> If you can do it in the client side, then do it.
>
> AMB
> "Conrad Chan" wrote:
>|||Unfortunately it doesn't make a difference :<
Conrad
"MGFoster" wrote:

> Conrad Chan wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> If that expression is in the SELECT clause you can use
> ORDER BY n
> where n is the ordinal number of the expression in the SELECT clause.
> E.g.:
> SELECT col1, col2, (col4 * 0.25) / 100, ...
> FROM ...
> ORDER BY 3
> Will sort the resultset by the value of the 3rd column in the SELECT
> clause.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBQjdSGYechKqOuFEgEQKsQgCggvSDYuQCwIcw
DXSdEtuVA3YD+b4AnixS
> BnAeboIAn+Ja2WD/GUp486uA
> =1vFd
> --END PGP SIGNATURE--
>|||You can simplify the ORDER BY clause to
ORDER BY COALESCE(Table1.Field1, ''), COALESCE(CAST(Table2.Field2 AS
nvarchar), '''')
or even to
ORDER BY Table1.Field1, Table2.Field2
HTH,
Gert-Jan
Conrad Chan wrote:
> Hi all,
> I have a long runing query took 70s and returns only 124 rows. I found th
e
> problem is that it uses a compute column in the Order By clause. Somethin
g
> like this
> SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
> ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
> If I took that Order By away, it only takes 2s. (That make me think my C#
> client code can sort better than that :P )
> Can anyone show me what are the ways I can do to optimize it?
> Any thoughts are appreciated.
> Conrad|||Conrad,
In General it should never take SQL Server 68 seconds to sort 124
records... SOmething else is going on here ... Run the query in Query
Analyzer with ShowPlan ON, and see what step in the showplan is taking that
long...
"Conrad Chan" wrote:

> Hi all,
> I have a long runing query took 70s and returns only 124 rows. I found th
e
> problem is that it uses a compute column in the Order By clause. Somethin
g
> like this
> SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
> ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
> If I took that Order By away, it only takes 2s. (That make me think my C#
> client code can sort better than that :P )
> Can anyone show me what are the ways I can do to optimize it?
> Any thoughts are appreciated.
> Conrad|||Thanks CBretana,
I did look into query analyzer. 90% is done on the Sort. The only thing I
found may be interested is that the estimated row count is 15,000 compared
with 124 row count.
Conrad
"CBretana" wrote:
> Conrad,
> In General it should never take SQL Server 68 seconds to sort 124
> records... SOmething else is going on here ... Run the query in Query
> Analyzer with ShowPlan ON, and see what step in the showplan is taking tha
t
> long...
>
> "Conrad Chan" wrote:
>|||Then you have a filter in the query somewhere, which is reducing the output
from 15,000 to 124, and the sort is happening on the entire 15k recordset,
not the final 124... Suggestion
Rewrite the query as
Select <Stuff>
From (SubSquery: Select Stuff
From <Tables>
Where <Here goes filter predicate tha treduces 15k - 124)
Order By <Order by Clause>
Then inner query must process the filter and deliver the 124 records to the
outer part, where the Order By is...
See if that works...
"Conrad Chan" wrote:
> Thanks CBretana,
> I did look into query analyzer. 90% is done on the Sort. The only thing
I
> found may be interested is that the estimated row count is 15,000 compared
> with 124 row count.
> Conrad
> "CBretana" wrote:
>|||No luck. I simply try to do exactly like you suggest. SQL is smart enough
to realize they are the same. (It is too smart to be stupid)
However, for testing purpose, if I put a TOP inside my sub-select it does
return in 2s.
SELECT * FROM (
SELECT TOP 124 * FROM ...
) ORDER BY 1, 4
Conrad
"CBretana" wrote:
> Then you have a filter in the query somewhere, which is reducing the outpu
t
> from 15,000 to 124, and the sort is happening on the entire 15k recordset,
> not the final 124... Suggestion
> Rewrite the query as
> Select <Stuff>
> From (SubSquery: Select Stuff
> From <Tables>
> Where <Here goes filter predicate tha treduces 15k - 124)
> Order By <Order by Clause>
> Then inner query must process the filter and deliver the 124 records to th
e
> outer part, where the Order By is...
> See if that works...
> "Conrad Chan" wrote:
>sql