performance

hi all
how can I increase the speed of an SQL Server damaticly ?
configuration:
W2k, SQL Server 2000, 2 processors (1.4GHz)
table:
1000000 records
queryresult:
450000 records
time:
11s
i tried to set indexes on fields where i'm search for. but
there was not a lot of speed available. for my oppinion
it's a very poor value for a sql server to find 450000
records!!!
any hints to optimize the query or the server ?
thankx a lot
tomWhere do you issue that query from? You still have to move those 450,000
rows from the server to your client and that is going to take some time.
Even on my local machine, if I do SELECT * FROM table, where that table has
300,000 rows and a lot of columns it takes 40 seconds for the results to
show in Query Analyzer, but SELECT COUNT(*) is sub second, even when I make
sure that it uses a table scan by including a where clause on a non indexed
column that returns all rows.
--
Jacco Schalkwijk
SQL Server MVP
"thomas.marti@.netrics.ch" <anonymous@.discussions.microsoft.com> wrote in
message news:081f01c3c0a1$2df367a0$a401280a@.phx.gbl...
> hi all
> how can I increase the speed of an SQL Server damaticly ?
> configuration:
> W2k, SQL Server 2000, 2 processors (1.4GHz)
> table:
> 1000000 records
> queryresult:
> 450000 records
> time:
> 11s
> i tried to set indexes on fields where i'm search for. but
> there was not a lot of speed available. for my oppinion
> it's a very poor value for a sql server to find 450000
> records!!!
> any hints to optimize the query or the server ?
> thankx a lot
> tom|||it's on the same machine. so i think that it cannot be so
slow isnt't it?
but how do other people work with lots of records? one
million records is not really a big amount for a
database!!!
>--Original Message--
>Where do you issue that query from? You still have to
move those 450,000
>rows from the server to your client and that is going to
take some time.
>Even on my local machine, if I do SELECT * FROM table,
where that table has
>300,000 rows and a lot of columns it takes 40 seconds for
the results to
>show in Query Analyzer, but SELECT COUNT(*) is sub
second, even when I make
>sure that it uses a table scan by including a where
clause on a non indexed
>column that returns all rows.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"thomas.marti@.netrics.ch"
<anonymous@.discussions.microsoft.com> wrote in
>message news:081f01c3c0a1$2df367a0$a401280a@.phx.gbl...
>> hi all
>> how can I increase the speed of an SQL Server
damaticly ?
>> configuration:
>> W2k, SQL Server 2000, 2 processors (1.4GHz)
>> table:
>> 1000000 records
>> queryresult:
>> 450000 records
>> time:
>> 11s
>> i tried to set indexes on fields where i'm search for.
but
>> there was not a lot of speed available. for my oppinion
>> it's a very poor value for a sql server to find 450000
>> records!!!
>> any hints to optimize the query or the server ?
>> thankx a lot
>> tom
>
>.
>|||One million rows is not a lot for a database, but one million (or 450,000)
rows is a whole lot to return to your user. They are not going to look
through 450,000 rows just to find something.
As I said before the bottleneck is with getting the results to the client,
not with getting the rows from the server.
--
Jacco Schalkwijk
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:093b01c3c0b9$50a63b20$a401280a@.phx.gbl...
> it's on the same machine. so i think that it cannot be so
> slow isnt't it?
> but how do other people work with lots of records? one
> million records is not really a big amount for a
> database!!!
> >--Original Message--
> >Where do you issue that query from? You still have to
> move those 450,000
> >rows from the server to your client and that is going to
> take some time.
> >
> >Even on my local machine, if I do SELECT * FROM table,
> where that table has
> >300,000 rows and a lot of columns it takes 40 seconds for
> the results to
> >show in Query Analyzer, but SELECT COUNT(*) is sub
> second, even when I make
> >sure that it uses a table scan by including a where
> clause on a non indexed
> >column that returns all rows.
> >
> >--
> >Jacco Schalkwijk
> >SQL Server MVP
> >
> >
> >"thomas.marti@.netrics.ch"
> <anonymous@.discussions.microsoft.com> wrote in
> >message news:081f01c3c0a1$2df367a0$a401280a@.phx.gbl...
> >> hi all
> >> how can I increase the speed of an SQL Server
> damaticly ?
> >>
> >> configuration:
> >> W2k, SQL Server 2000, 2 processors (1.4GHz)
> >>
> >> table:
> >> 1000000 records
> >>
> >> queryresult:
> >> 450000 records
> >>
> >> time:
> >> 11s
> >>
> >> i tried to set indexes on fields where i'm search for.
> but
> >> there was not a lot of speed available. for my oppinion
> >> it's a very poor value for a sql server to find 450000
> >> records!!!
> >> any hints to optimize the query or the server ?
> >>
> >> thankx a lot
> >> tom
> >
> >
> >.
> >