?
Solved

SQL Views

Posted on 2008-01-30
6
Medium Priority
?
298 Views
Last Modified: 2010-03-19
I have a big problem......I have 4 sql tables that are fairly large let say 10-12 GB each. Every month I dump previous month data and add new month data. Here is the situation user needs to access certain data from these big tables for example users looking for data in the field1 where the value is 123. I have been creating sub tables for the users. But now its becoming problem managing them. I tried creating views but it was taking for ever. Also the users use MS Access with sql link tables to access the data. is there a better way of doing this so I dont have to create sub tables. Also there is no index in tables cuz every month I drop old month data and add new month data. I do understand that by indexing it improves performance but we are talking millions of records here in a table. Please help me out here to resolve this problem. Also each table contains 110 coulumn.

Thank you.

0
Comment
Question by:proudpaki9
  • 2
  • 2
  • 2
6 Comments
 

Author Comment

by:proudpaki9
ID: 20780962
Also just to be exact on record count each table contains 200 million records.

Thanks
0
 
LVL 35

Accepted Solution

by:
David Todd earned 900 total points
ID: 20782129
Hi,

Indexes would help the queries a lot.

Suggest that you drop the indexes prior to dumping the table and reloading them, and as part of that process create these indexes.

Views is a good idea, but without indexes isn't going to be much better than querying the base table.

What version and edition of SQL are you running, for instance SQL 2000 standard edition, or SQL 2005 Enterprise edition ...

Cheers
  David
0
 

Author Comment

by:proudpaki9
ID: 20782413
sql 2000 standard edition....
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 35

Expert Comment

by:David Todd
ID: 20782504
Hi,

You can try to partition the data. That is, split into sub-tables by month or whatever, and have a view that is
select *
from table1

union all

select *
from table2

... etc ...

There is a feature to automate some of this in SQL 2005 Enterprise ...

But still could be done manually.

This does need indexes as well.

The idea is that users don't see the complexity - always querying from one table or view, but the query optimiser can quickly work out that the data for this query can all come from table1 and not have to read its way through all ... tables.

Another idea is to move many of the lesser read columns into another table. This has the effect of getting more rows on each data page, so each disk read returns more rows, meaning less reads, and hopefully better response.

But all these need indexes and statistics.

I suggest that you capture a few of yoru favourite querries and experiment with the index tuning wizard.

HTH
  David
0
 
LVL 12

Assisted Solution

by:Ken Selvia
Ken Selvia earned 600 total points
ID: 20803386
I browsed a few of your other questions.  Am I right that you are using DTS to load this data?  Are the input files already sorted?

If so, consider BULK INSERT and a clustered index.

truncate mytable
create clustered index mt_field1_idx on mytable (field1)

BULK INSERT dbo.mytable from 'c:\temp\myfile.txt'
ORDER = field1

Telling BULK INSERT the input file is already in the same order as the clustered index will allow SQL to load the file without sorting it, and your queries for field1=123 should be very quick

0
 
LVL 12

Expert Comment

by:Ken Selvia
ID: 20803401
One more thing.

If the data is not sorted, you don't want do use a clustered index.

create a regular index on field1 AFTER you load the table

truncate mytable
drop index mytable.mt_field1_idx

bulk insert or DTS here

create index my_field1_idx on mytable (field1)

Even for 100 million rows, creating a single NON CLUSTERED index on an integer field should be relatively fast. (10 - 15 minutes I'm guessing)



0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question