ORDER BY efficiency

Hi,

I'm using a table with about 5 million records, i'm preforming a select query onthe table. I retrieve from that query about 2-4 million records when i'm not sorting the data the query return very fast. If I'm sorting the data (ORDER BY timestamp - when timestamp is a BIG_INT with an index) the query takes a long time to return about 30 seconds.

I tryed to do the same thing with access DB and i get better results: the query with ORDER BY takes about 1 second.

I dont think it's possible that access DB have better performance then SQL server, does any one have any idea what can be me problem?

thanks ishay.

can you set this filed as clustered index?|||

It will normally take much longer to sort 2-4 million rows than it will to simply select the data. Also, is the access database on your local machine? If this is the case, then YES, Access might well APPEAR WAY more efficient than SQL Server on an external machine because with the Access database you do not have to spend telecommunication time transceiving the data between your machine and the server.

Dave

|||What's the definition of the index on the timestamp column? Is it the clustered index? Have you looked at the query plan? In SQL Server Management Studio select "Include Actual Execution Plan" under the query menu to see the query plan. You want to see a clustered index scan over the index for the timestamp column for best performance.|||

Thank you all. I set the index to be clustered index and it's working prepectly!

Ishay

Oracle XE

I haven't installed this yet but does anyone who has have any comments on
the pros and cons vs. SQL2005 Express?
Also, does it have any graphical management tools (analogous to Mgt. Studio
Express)?
Many thanks.
No one knows, huh?
"Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
news:erEAtjYAGHA.3840@.TK2MSFTNGP15.phx.gbl...
>I haven't installed this yet but does anyone who has have any comments on
>the pros and cons vs. SQL2005 Express?
> Also, does it have any graphical management tools (analogous to Mgt.
> Studio Express)?
> Many thanks.
>

oracle and sql

is it possible to have an oracle server (runtime) and an sql server on
the same machine, or better said, is it wise?
the instance of sql server is not that heavily used, neither will the
oracle server service more than 10 users max
what issues could arise with such a setup
thnxThey both sure can be installed on the same machine. But I would only do
that on a development machine, not in production.
In a typical production scenario, both the RDBMSes will be competing for the
resources on the machine. Even from a maintenance point of view, if you have
to reboot the machine for the sake of applying an Oracle patch, you will
unnecessarily impact SQL Server users.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"chriske911" <chriske911nospam@.yahoo.com> wrote in message
news:mn.fa317d5a865c2693.36062@.yahoo.com...
is it possible to have an oracle server (runtime) and an sql server on
the same machine, or better said, is it wise?
the instance of sql server is not that heavily used, neither will the
oracle server service more than 10 users max
what issues could arise with such a setup
thnx|||Hi,
I also agree with Vyas on this. Earlier I had a non critical production
server which has both SQL Server and Oracle.
For applying any pathes on SQL Server or Oracle needs a reboot and will
cause a downtime.
As well as the resource usage also will be high. So I recommend you to have
either one of this RDBMS installed in a single server.
Thanks
Hari
SQL Server MVP
"chriske911" <chriske911nospam@.yahoo.com> wrote in message
news:mn.fa317d5a865c2693.36062@.yahoo.com...
> is it possible to have an oracle server (runtime) and an sql server on the
> same machine, or better said, is it wise?
> the instance of sql server is not that heavily used, neither will the
> oracle server service more than 10 users max
> what issues could arise with such a setup
> thnx
>|||> is it possible to have an oracle server (runtime) and an sql server on the
> same machine, or better said, is it wise?
> the instance of sql server is not that heavily used, neither will the orac
le
> server service more than 10 users max
> what issues could arise with such a setup
> thnx
OK
thnx for your input
it'll be an older server that will be used then
grtz

oracle and sql

is it possible to have an oracle server (runtime) and an sql server on
the same machine, or better said, is it wise?
the instance of sql server is not that heavily used, neither will the
oracle server service more than 10 users max
what issues could arise with such a setup
thnx
They both sure can be installed on the same machine. But I would only do
that on a development machine, not in production.
In a typical production scenario, both the RDBMSes will be competing for the
resources on the machine. Even from a maintenance point of view, if you have
to reboot the machine for the sake of applying an Oracle patch, you will
unnecessarily impact SQL Server users.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"chriske911" <chriske911nospam@.yahoo.com> wrote in message
news:mn.fa317d5a865c2693.36062@.yahoo.com...
is it possible to have an oracle server (runtime) and an sql server on
the same machine, or better said, is it wise?
the instance of sql server is not that heavily used, neither will the
oracle server service more than 10 users max
what issues could arise with such a setup
thnx
|||Hi,
I also agree with Vyas on this. Earlier I had a non critical production
server which has both SQL Server and Oracle.
For applying any pathes on SQL Server or Oracle needs a reboot and will
cause a downtime.
As well as the resource usage also will be high. So I recommend you to have
either one of this RDBMS installed in a single server.
Thanks
Hari
SQL Server MVP
"chriske911" <chriske911nospam@.yahoo.com> wrote in message
news:mn.fa317d5a865c2693.36062@.yahoo.com...
> is it possible to have an oracle server (runtime) and an sql server on the
> same machine, or better said, is it wise?
> the instance of sql server is not that heavily used, neither will the
> oracle server service more than 10 users max
> what issues could arise with such a setup
> thnx
>
|||> is it possible to have an oracle server (runtime) and an sql server on the
> same machine, or better said, is it wise?
> the instance of sql server is not that heavily used, neither will the oracle
> server service more than 10 users max
> what issues could arise with such a setup
> thnx
OK
thnx for your input
it'll be an older server that will be used then
grtz