order by desc take forever.... 
Author Message
 order by desc take forever....

Hi all,
I have the store procedure(SP) and inside SP I have select statement with
order by for example:
        ............
       select recordTime,value
       from datapoint dp (INDEX = sortedDatapointIndex)

       order by taskBinder_id DESC,
                    recordTime DESC
and this statement take about 3 hours for 600M of data in the table
datapoint
and this is the definition of the table:
   create table datapoint (
          taskBinder_id int NOT NULL REFERENCES taskBinder(taskBinder_id),
          recordTime int NOT NULL,
          value     real NOT NULL
  )

create CLUSTERED index datapointIndex
       on datapoint (taskBinder_id, recordTime)

create NONCLUSTERED index sortedDatapointIndex
      on datapoint (taskBinder_id, recordTime).

For Asc. order it's is very fast but not Desc.

If you know how to make this query to run fast for DESC. order please

Thanks a lot,
Joanne



Tue, 06 Jun 2000 03:00:00 GMT
 order by desc take forever....

Quote:

> I have the store procedure(SP) and inside SP I have select statement with
> order by for example:
>         ............
>        select recordTime,value
>        from datapoint dp (INDEX = sortedDatapointIndex)

                        ^^
                        You don't need the alias here, nor the
                        index specification.

Quote:

>        order by taskBinder_id DESC,
>                     recordTime DESC
> and this statement take about 3 hours for 600M of data in the table
> datapoint

[snip]

Quote:
> create CLUSTERED index datapointIndex
>        on datapoint (taskBinder_id, recordTime)

> create NONCLUSTERED index sortedDatapointIndex
>       on datapoint (taskBinder_id, recordTime).

        You have two identical indexes here. Loose the non-clustered
        one. You don't need it.

Quote:
> For Asc. order it's is very fast but not Desc.

        To be expected. The asc. ordering will use the index as is
        and won't create a worktable whereas the desc. one will.
        Its probably best if you did the ordering one the client
        instead.

-am



Tue, 06 Jun 2000 03:00:00 GMT
 order by desc take forever....

My SQL Server USE index for both sorting modes (ASC and DESC by index key).
You should check query PLAN.

select rpt_code from Report order by rpt_code DESC
--
STEP 1
The type of query is SELECT
FROM TABLE
Report
Nested iteration
Using Clustered Index
--
Sergei Didur
Systems Analyst



Quote:
>    To be expected. The asc. ordering will use the index as is
>    and won't create a worktable whereas the desc. one will.
>    Its probably best if you did the ordering one the client
>    instead.



Tue, 06 Jun 2000 03:00:00 GMT
 order by desc take forever....

Quote:

> Hi all,
> I have the store procedure(SP) and inside SP I have select statement with
> order by for example:
>         ............
>        select recordTime,value
>        from datapoint dp (INDEX = sortedDatapointIndex)

>        order by taskBinder_id DESC,
>                     recordTime DESC
> and this statement take about 3 hours for 600M of data in the table
> datapoint
> and this is the definition of the table:
>    create table datapoint (
>           taskBinder_id int NOT NULL REFERENCES taskBinder(taskBinder_id),
>           recordTime int NOT NULL,
>           value     real NOT NULL
>   )

> create CLUSTERED index datapointIndex
>        on datapoint (taskBinder_id, recordTime)

> create NONCLUSTERED index sortedDatapointIndex
>       on datapoint (taskBinder_id, recordTime).

> For Asc. order it's is very fast but not Desc.

> If you know how to make this query to run fast for DESC. order please

> Thanks a lot,
> Joanne

Prior to 11.5 the data server is not capable of traversing an index
page chain backwards, thus an extra sort step is involved in
your query in order to reverse the direction of the data.  With 11.5
you should notice a significant improvement in your query (and
you should find--I think--that ASE can now use an index for
positioning in '<' queries).

-scott

--

brken"

   http://www.voicenet.com/~gray/sqsh.html



Tue, 06 Jun 2000 03:00:00 GMT
 order by desc take forever....


says...

Quote:

> > I have the store procedure(SP) and inside SP I have select statement with
> > order by for example:
> >         ............
> >        select recordTime,value
> >        from datapoint dp (INDEX = sortedDatapointIndex)

>        order by taskBinder_id DESC,
>                     recordTime DESC
> > For Asc. order it's is very fast but not Desc.

>    To be expected. The asc. ordering will use the index as is
>    and won't create a worktable whereas the desc. one will.
>    Its probably best if you did the ordering one the client
>    instead.

This was true in older versions of the server, and for Sybase. But MS 6.5
will walk the index backwards for a descending sort. The problem is that
you're overriding the optimizer by specifying the index to use. Take away
the INDEX= directive and you should be OK.

Also be sure that your statistics are reasonably up to date. Verify the
decisions of the optimizer by setting showplan on before running the
query.
--
Ben McEwan, President, Geist, LLC



Wed, 07 Jun 2000 03:00:00 GMT
 order by desc take forever....


Quote:

>> create CLUSTERED index datapointIndex
>>        on datapoint (taskBinder_id, recordTime)

>> create NONCLUSTERED index sortedDatapointIndex
>>       on datapoint (taskBinder_id, recordTime).

>        You have two identical indexes here. Loose the non-clustered
>        one. You don't need it.

Depends on what he is up to. If he wants to run a lot of queries
concerning only the index keys *and* wants to run a lot of queries
that are based on the index keys, but also retrieves other data,
both indexes could be useful.

For the question at hand, though, he had given an optimizer hint to
use the nonclustered index, when the clustered one most likely is the
most appropriate.

--

F=F6r =F6vrigt anser jag att QP b=F6r f=F6rst=F6ras.
B=65sid=65s, I think QP should b=65 d=65stroy=65d.



Fri, 09 Jun 2000 03:00:00 GMT
 order by desc take forever....

Erland>
Erland> Depends on what he is up to. If he wants to run a lot of queries
Erland> concerning only the index keys *and* wants to run a lot of queries
Erland> that are based on the index keys, but also retrieves other data,
Erland> both indexes could be useful.

I believe that it would still be satisfied by the cluster index.
--
Pablo Sanchez              | Ph # (650) 933.3812          Fax # (650) 933.2821

-------------------------------------------------------------------------------
I am accountable for my actions.   http://reality.sgi.com/pablo [ /Sybase_FAQ ]



Fri, 09 Jun 2000 03:00:00 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. order by desc take forever....

2. ORDER BY DESC returning ASC not DESC !?

3. Order by desc slower than order by asc

4. SQL Server startup takes "forever"

5. Takes Forever to Design Storage

6. virtual cube processing takes forever

7. Processing Cubes Takes Forever

8. deleting rows from table using T-SQL taking forever

9. Delete on one table takes forever

10. Delete takes FOREVER

11. OPENQUERY takes forever

12. Query takes forever


 
Powered by phpBB® Forum Software