--=_NextPart_000_0052_01C4758A.B87F1790
Content-Type: text/plain;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
Hello,
I have a table with about 5 milion records, and the performance is = un-acceptable (more than 45 seconds for a query). The table was imported = from a text file. I have defined a primary key (two varchar(20) = columns). Then I checked and the database has created a clustered index = on these columns. But I suspect that something is wrong with the index: = When I run a select query, the rows are returned in an arbitrary order, = and not in the order defined by the index. Is it possible that something = is wrong with the index? If yes, how can I fix that?
thanks, David
--=_NextPart_000_0052_01C4758A.B87F1790
Content-Type: text/html;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hello,
I have a table with about 5 milion = records, and the performance is un-acceptable (more than 45 seconds for a query). The = table was imported from a text file. I have defined a primary key (two varchar(20) = columns). Then I checked and the database has created a clustered index = on these columns. But I suspect that something is wrong with the index: When I = run a select query, the rows are returned in an arbitrary order, and not = in the order defined by the index. Is it possible that something is wrong with = the index? If yes, how can I fix that?
thanks, = David
--=_NextPart_000_0052_01C4758A.B87F1790--This is a multi-part message in MIME format.
--=_NextPart_000_000B_01C4758B.8E928F10
Content-Type: text/plain;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
David
Did you spesify ORDER BY clause when you run SELECT statement.
What is your search criteria in the query?
"David" <dboaz@.bgumail.bgu.ac.il> wrote in message =news:um00pGXdEHA.3148@.TK2MSFTNGP10.phx.gbl...
Hello,
I have a table with about 5 milion records, and the performance is =un-acceptable (more than 45 seconds for a query). The table was imported =from a text file. I have defined a primary key (two varchar(20) =columns). Then I checked and the database has created a clustered index =on these columns. But I suspect that something is wrong with the index: =When I run a select query, the rows are returned in an arbitrary order, =and not in the order defined by the index. Is it possible that something =is wrong with the index? If yes, how can I fix that?
thanks, David
--=_NextPart_000_000B_01C4758B.8E928F10
Content-Type: text/html;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
David
Did you spesify ORDER BY clause when =you run SELECT statement.
What is your search criteria in the query?
"David"
Hello,
I have a table with about 5 milion =records, and the performance is un-acceptable (more than 45 seconds for a query). =The table was imported from a text file. I have defined a primary key (two =varchar(20) columns). Then I checked and the database has created a clustered =index on these columns. But I suspect that something is wrong with the index: =When I run a select query, the rows are returned in an arbitrary order, =and not in the order defined by the index. Is it possible that something is =wrong with the index? If yes, how can I fix that?
thanks, David
--=_NextPart_000_000B_01C4758B.8E928F10--|||A clustered index does not determine the sort order of a query result, so
there is no reason to suspect any integrity problem. Use an ORDER BY clause
in your SELECT statement to fix the order of the returned rows.
Did you check the query plan to see what indexes are being used? Could you
post the query and a CREATE TABLE statement for the table to give us an idea
of what optimizations might be possible.
--
David Portas
SQL Server MVP
--|||> When I run a select query, the rows are returned in an arbitrary order
A table is an unordered set of rows. What were you expecting? If you want
a defined order, use an ORDER BY clause.
I don't know where everyone gets the idea that the existence of a clustered
index means that is the order all SELECT queries will return the data. This
is NOT TRUE! It does happen more often that way, but it is not a law. The
plan will return the rows in the best way it sees fit, you could run the
query 10 times and it *could* return the rows 10 different ways. Usually
doesn't, but could.
I'll repeat: if you want a specific order, use an ORDER BY clause.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Try a DBCC DBREINDEX or DBCC INDEXDEFRAG depending upon
your preference, then update the statistics.
It sometimes happends when you do a bulk insert that the
statistics need re-doing (in my opinion).
Peter
>--Original Message--
>Hello,
>I have a table with about 5 milion records, and the
performance is un-acceptable (more than 45 seconds for a
query). The table was imported from a text file. I have
defined a primary key (two varchar(20) columns). Then I
checked and the database has created a clustered index on
these columns. But I suspect that something is wrong with
the index: When I run a select query, the rows are
returned in an arbitrary order, and not in the order
defined by the index. Is it possible that something is
wrong with the index? If yes, how can I fix that?
>thanks, David
No comments:
Post a Comment