query tunning

I have a dropdown box to running a query filtering mt records.
The dropdown has 4 option: ALL, Close, Open, Pause.
which filter the status of records.
When I change dropdown from all to others works fine, but when I change from
the Close, Open or Pause to All then it takes more than 10 seconds. It seems
that the SQL server needs more time to return the records from others to ALL
,
but it taks less time from ALL to others.
Any suggestions is great appreciated,As far as we dont know what code happens when you chenge the Combobox its
hard to help you in here. Perhaps you might run the profiler to watch the
incoming queries...
HTH, Jens Suessmeyer.
http.//www.sqlserver2005.de
--
"Souris" <Souris@.discussions.microsoft.com> schrieb im Newsbeitrag
news:754D78B4-8A12-4664-9975-A876C9A37D1A@.microsoft.com...
>I have a dropdown box to running a query filtering mt records.
> The dropdown has 4 option: ALL, Close, Open, Pause.
> which filter the status of records.
> When I change dropdown from all to others works fine, but when I change
> from
> the Close, Open or Pause to All then it takes more than 10 seconds. It
> seems
> that the SQL server needs more time to return the records from others to
> ALL,
> but it taks less time from ALL to others.
> Any suggestions is great appreciated,
>
>|||Thanks for the message,
The dropdown box just run the query.
I will user profilter to watch it.
Thanks,
"Jens Sü?meyer" wrote:

> As far as we don′t know what code happens when you chenge the Combobox it
s
> hard to help you in here. Perhaps you might run the profiler to watch the
> incoming queries...
> HTH, Jens Suessmeyer.
> --
> http.//www.sqlserver2005.de
> --
>
> "Souris" <Souris@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:754D78B4-8A12-4664-9975-A876C9A37D1A@.microsoft.com...
>
>|||Chances are it is using an index and returning only a small portion of the
rows when it is other than ALL. When it is set to ALL it has to scan the
whole table and return a lot of rows.
Andrew J. Kelly SQL MVP
"Souris" <Souris@.discussions.microsoft.com> wrote in message
news:754D78B4-8A12-4664-9975-A876C9A37D1A@.microsoft.com...
>I have a dropdown box to running a query filtering mt records.
> The dropdown has 4 option: ALL, Close, Open, Pause.
> which filter the status of records.
> When I change dropdown from all to others works fine, but when I change
> from
> the Close, Open or Pause to All then it takes more than 10 seconds. It
> seems
> that the SQL server needs more time to return the records from others to
> ALL,
> but it taks less time from ALL to others.
> Any suggestions is great appreciated,
>
>|||Thanks!
You are right,
Are there any suggestions to resolve the issue?
Should I analyze the index fields?
Is it possible to change index fields will resolve the issue?
Thanks millions,
"Andrew J. Kelly" wrote:

> Chances are it is using an index and returning only a small portion of the
> rows when it is other than ALL. When it is set to ALL it has to scan the
> whole table and return a lot of rows.
> --
> Andrew J. Kelly SQL MVP
>
> "Souris" <Souris@.discussions.microsoft.com> wrote in message
> news:754D78B4-8A12-4664-9975-A876C9A37D1A@.microsoft.com...
>
>|||If there are no other columns which can be used in the WHERE clause then you
have no choice but to scan the table. Indexes won't help if there is no
WHERE clause. I don't know your business logic but you would probably be
best served by always requiring some field to be used as the search
criteria. Rarely is ALL a good choice. If there are thousands of rows or
more then the user usually can not sift through all of them effectively
anyway.
Andrew J. Kelly SQL MVP
"Souris" <Souris@.discussions.microsoft.com> wrote in message
news:64B6C003-1721-4DF8-9CC7-357E96311208@.microsoft.com...
> Thanks!
> You are right,
> Are there any suggestions to resolve the issue?
> Should I analyze the index fields?
> Is it possible to change index fields will resolve the issue?
> Thanks millions,
>
> "Andrew J. Kelly" wrote:
>|||Thanks millions,
Souris,
"Andrew J. Kelly" wrote:

> If there are no other columns which can be used in the WHERE clause then y
ou
> have no choice but to scan the table. Indexes won't help if there is no
> WHERE clause. I don't know your business logic but you would probably be
> best served by always requiring some field to be used as the search
> criteria. Rarely is ALL a good choice. If there are thousands of rows or
> more then the user usually can not sift through all of them effectively
> anyway.
> --
> Andrew J. Kelly SQL MVP
>
> "Souris" <Souris@.discussions.microsoft.com> wrote in message
> news:64B6C003-1721-4DF8-9CC7-357E96311208@.microsoft.com...
>
>sql