Table too big for queries... 1

[bluenoser337]

I have a single table in a SQLServer7.0 application that gets about 10,000 rows entered automatically each day (as various parts are manufactured). As time passes, the queries become slow to impossible for daily, weekly, etc reporting. I'd like to have the table contents summarized (combine all red parts of the same size and have a "count" in the new table, for example) and cleared each week but stored first in another table that can be queried for a monthly (or larger) report. The original table needs to empty each time but what if the power to the PC is off...and it misses a weekly save? Any samples out there of how to do this "save to another place, then clear" functioality. Hope this makes sense. Many thanks!!!!

 

[royjimenez]

u could try to improve the performance using views...

u can create a new tables with checks

table01
field1 where ( field1 >= 0 and field1 < 1000 )
field2
field3
...

table02
field1 where ( field1 >= 1000 and field1 < 2000 )
field2
field3
...

...
..
.
go on





later, u can create a view with all the tables

create view OriginaltableName
select * from table01
union all
select * from table02
...


If u separate your tables with sense, u could get a better performance.

Read the help for more details.

Regards



The life is too short to cry and long enough to try it... God bless us.

 

[bluenoser337]

Thanks! This would probably be great except I am too green. I would still like to know how to do the &quot;end of week&quot; thing.

 

[royjimenez]

look up this in the help

&quot;partitioned views&quot;

regards

The life is too short to cry and long enough to try it... God bless us.

 

[SQLBill]

Do you have indexes on your table? Do you defrag/reindex them?

The main table on my database gets over 100,000 rows every day and my queries aren't slowed down (the table is now over 22 million rows). So, it seems to me you need to have indexes or improve your existing indexes.

-SQLBill

 

[fluteplr]

I agree with SQLBill that table is not nearly big enough to cause the kond of issues you are seeing unless you have not indexed the table properly, there is more to your queries than indicated in your email or you are running on a really underpowered or over used server.

Send us more details and we can be more help.

 

[royjimenez]

HI!

bluenoseer wrote that he has 10000 rows per day, i guess that he needs to improve the performance in his table.

bluenoseer:

I agree with them, I think u should review your indexes, later, u should be your queries for understanding if these queries are using indexes. If u have a huge table, u should use a partitioned views...


Regards

The life is too short to cry and long enough to try it... God bless us.