Performance and Large Data

Hi *.*
I've a table with 14 columns and 640.000 rows, a simple select (* from
table ) is very slow (about 16 seconds on my P4 2800, 750 MB of Ram),
Is it normal? How can I reduce this time? With some reports that use
this table, I've use Index Tuning Wizard and performance is now a
little better but I don't know if this is a limitation of SQL Server or
limitation of my knowledge? (SQL Server 2000-SP4 on WIndows XP Pro)
Would you plz kindly help me with your tips and advices?
Thanks in Advance,
HamedHamed,
pls can you post up your query, the table schema, tell us what indexes you
have at present and the showplan output from running the query.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Are you saying that you return 640,000 rows? If so:
Why? Who can make heads or tails out of 640,000 rows? Without doing something reasonable, like
filtering, aggregating to cut down number of rows to be returned, there's not much to be done. One
thing that can be interesting is the Query Analyzer is much quicker to print the result in grid
compared to text mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hamed" <hoveisi@.gmail.com> wrote in message
news:1155289810.440647.110830@.i3g2000cwc.googlegroups.com...
> Hi *.*
> I've a table with 14 columns and 640.000 rows, a simple select (* from
> table ) is very slow (about 16 seconds on my P4 2800, 750 MB of Ram),
> Is it normal? How can I reduce this time? With some reports that use
> this table, I've use Index Tuning Wizard and performance is now a
> little better but I don't know if this is a limitation of SQL Server or
> limitation of my knowledge? (SQL Server 2000-SP4 on WIndows XP Pro)
> Would you plz kindly help me with your tips and advices?
> Thanks in Advance,
> Hamed
>|||On 11 Aug 2006 02:50:10 -0700, "Hamed" <hoveisi@.gmail.com> wrote:
> I've a table with 14 columns and 640.000 rows, a simple select (* from
>table ) is very slow (about 16 seconds on my P4 2800, 750 MB of Ram),
>Is it normal? How can I reduce this time? With some reports that use
>this table, I've use Index Tuning Wizard and performance is now a
>little better but I don't know if this is a limitation of SQL Server or
>limitation of my knowledge? (SQL Server 2000-SP4 on WIndows XP Pro)
If it has to read and compare the rows to some kind of where clause,
or if you have no where clause and it has to return all 640k rows,
which is probably at least 60mb, it just takes that long to move the
data around.
If your logic is such that the SQLServer optimizer thinks it needs to
scan the table and not use any available indexes, then it is simply
going to take a while. OTOH, if you have 750mb of RAM, it may be that
the first time you run the select, it runs slowly because it is doing
physical disk I/O, but if you rerun the same query immediately it will
run much, much faster because ALL of the data is cached in RAM.
Hope that helps.
J.