Indexes

[Hattusas]

In our db applications our clients would like to rearch our records rapidly and they generally request complex queries resulting more than 30000 or 40000 rows.
While making researches on that subject,I noticed that the basic problem was the sort phase.(When I remove the sort by blah. blah section the query worked like a charm).
Then it gave me an idea and I tried reorganizing the tables that took role in query.I also checked them one by one.When I created the index all of tables,the db brought the records in the order I specified in my index.But when I joined my tables and ran the query,I couldn't get the result I expected.I want to bring the records in order from the field of the most left table.Let me give an example
there are two tables.

Reservat.resehote a. (left table)
Reservat.resehoteroom b. (right table)
Fields of a
resehoteid (primary key)
resehotecompid (the field I put an index)
Fields of b
resehoteroomid (primary key)
resehoteid (foreign key referencing table a)

I reorganized my table a to take care of my index instead of primary key index.

But I write select a.resehotecompid,b.field1,b.field2
from reservat.resehote a,reservat.resehoteroom b
where a.rehoteid=b.resehoteid (or b.resehoteid=a.resehoteid)

It didn't sort my records by resehotecompid.
What must I do?If I use order by syntax the performance will drop.
It is extremely urgent and If someone has an idea please let me know.Thank you Salih Sipahi
Software Engineer.
City of Istanbul Turkey
openyourmind77@yahoo.com

 

[Shaixpeare]

The only way to *guarantee* a sorted result set is to do an order by or group by; it doesn't matter if you have an index that should seemingly bring back the rows in the order you want. There is no getting around this.
I have a suggestion though that might help your performance quite a bit. Why not change the primary key of Table B to look like the following:

Table B
========
resehoteid (1st col of PK & FK referencing table a)
resehoteroomid (2nd col of Primary key)

Then create a unique clustering Index to support the new Primary Key. This should speed things up quite a bit (at least for that particular query).

Hope this helps!