order by on views

[Ivan Smahin]

Hello Danny,

Tuesday, April 13, 2010, 5:11:25 PM, you wrote:

> Hey Ruslan, Ivan,

> regarding the slow select on views with order by, I have some remarks :

> 1. SELECT * FROM vw_organisation_users_lst takes : 15:58:59 Query time: 2.279 sec

> 2. SELECT pkid FROM vw_organisation_users_lst takes : 15:58:44 Query time: 0.048 sec

> 3. SELECT * FROM vw_organisation_users_lst ORDER BY pkid ASC takes : 15:58:29 Query time: 2.184 sec

> 4. SELECT pkid FROM vw_organisation_users_lst ORDER BY pkid ASC
> takes : 15:56:01 Query time: 2.182 sec

> The time to do a select * and to do a select pkid.. order by pkid is identical.

> So, it seems that with an order by, you always build the complete
> view(with all fields), and without order by only the requested fields.
> My question is now, why?

Why do you think so?
I mean why do you think it must be some difference between 3. and 4.?
Probably just a sorting takes 2 sec.?
And selecting all the fields or only one field makes no much diff.


> I think an order by on a view, would be much faster, if the view
> was first build with only the selected fields (+fields that I want to order by).

In  general - yes - it's true.
But  it  could  be,  say, 1 000 000 records. And sorting takes, say, 3
sec.

select f1... takes 0.05sec
select f1, f2 ...  takes 0.1sec

So you will get an almost the same picture

-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com