|
Home > Archive > MS SQL Server Clients > May 2005 > Problem !
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| Here is the quick question, i have created MedicalHistory Partitionned view
and it's working pretty good.. when I write this select top 1 * from
MedicalHistory ... result comes in a second but when I write this query..
select top 1 * from MedicalHistory order by 1 desc... result will not come
till half an hour but when I hit it's partitioned table like
MedicalHistory_2005 then the result comes on the spot... I think there is
some minor problem but can't findout ....
Any idea?
| |
| David Gugick 2005-05-26, 1:23 pm |
| Joh wrote:
> Here is the quick question, i have created MedicalHistory
> Partitionned view and it's working pretty good.. when I write this
> select top 1 * from MedicalHistory ... result comes in a second but
> when I write this query.. select top 1 * from MedicalHistory order by
> 1 desc... result will not come till half an hour but when I hit it's
> partitioned table like MedicalHistory_2005 then the result comes on
> the spot... I think there is some minor problem but can't findout ....
>
> Any idea?
By issuing the Order By (which is a requirement for TOP) you are forcing
SQL Server to sort the table by the first column. If there is no index
on that column, then SQL Server must sort the entire partitioned view
and then pull the first row. Probably performing table scan / clustered
index scan operations on all underlying tables. In any case, you should
avoid SELECT *.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com
| |
|
| Note:
1) Non Clustered Index on MedID
2) Clustered Index on LInfoID
Working:- select top 1 MedID, LInfoID from Medicalhistory order by 1
Working:- select top 1 LInfoID from Medicalhistory order by 1 LInfoID desc
Not working:- select top 1 MedID, LInfoID from Medicalhistory order by 1
desc
Thanks
"David Gugick" <david.gugick-nospam@quest.com> wrote in message
news:O0LZD7hYFHA.796@TK2MSFTNGP10.phx.gbl...
> Joh wrote:
>
> By issuing the Order By (which is a requirement for TOP) you are forcing
> SQL Server to sort the table by the first column. If there is no index
> on that column, then SQL Server must sort the entire partitioned view
> and then pull the first row. Probably performing table scan / clustered
> index scan operations on all underlying tables. In any case, you should
> avoid SELECT *.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
| |
| David Gugick 2005-05-26, 8:24 pm |
| Joh wrote:
> Note:
> 1) Non Clustered Index on MedID
> 2) Clustered Index on LInfoID
>
> Working:- select top 1 MedID, LInfoID from Medicalhistory order by 1
>
> Working:- select top 1 LInfoID from Medicalhistory order by 1 LInfoID
> desc
>
> Not working:- select top 1 MedID, LInfoID from Medicalhistory order
> by 1 desc
>
What's the execution plan for each.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com
|
|
|
|
|