SQL Server 7 performance questions 1

[rtgordon]

I have a part database that in essence has two tables with a one to many relationship joined on a key. The PK table contains parametric data about the unique part and the FK table has information like part number, mfg, etc.
For example

parameters          details
==========          ==========
id                  id
size                part_num
casing              mfg
tolerance           
and so on

My questions are... In the parameters table, there will probably be 2-3 hundred thousand. In the detail table, we expect that there will be not less than five million, but probably not more than 12 million. Given this, is this a database that I should be creating in SQL Server (ie will sql server handle it efficiently to be queryable from a web interface)? If so, what kind of hardware will I need to make it perform well? I currently have a quad processor (200 Mhz each) with 2Gb of RAM and about 20Gb of HD space available in the partition where I am building it. How big should I expect this thing to be?

Also, one more thing, the scripts that I am running to insert the data only seem to be taking up about 1/3 of the processor power. It runs pretty slow, too (along the lines of inserting five records per second) Any ideas to help it along?

Any input is appreciated. If I left an important piece out, let me know.

Thanks,
gordon

 

[tlbroadbent]

I believe the Server is adequate for the task. The only question mark would be the amount of disk space available. Without knowing row sizes, it is impossible to get a handle on spcae needed.

SQL 7 should handle queries against the tables very efficiently. You must make sure the tables have adequate indexing. I recommend clustered indexes on the ID columns on each table. Add other indexes as needed for WHERE criteria. Make sure indexes are rebuilt regularly if there are a lot of update, insert or delete transactions against the tables. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[rtgordon]

Thanks for the quick reply...


After I built clustered indexes on the id's, I built non-unique (obviously non-clustered) indexes for each column on each of the two table and gave them a fill factor of 75. Was this an incorrect approach? Can you be a little more specific about what you said about adding other indexes as needed for where criteria. (maybe an example?)

Also, by rebuilding, I have been going into the index manager and opening each index, clicking edit sql, then clicking execute. Is there a faster way than this to rebuild indexes?

Is there anything I can do to help determine the future size?

thanks for the help!
gordon

 

[tlbroadbent]

You may or may not need indexes on all columns. Usually, indexing all columns is counter-productive. When a query will search the table based on a specific criteria such as Part_Num = '12345678' or Tolerance = .002, then that column will be a candidate for indexing.

For more info in indexing check my SQL Articles page for links to indexing articles. I will be adding some more links today.

http://tlbroadbent.home.attbi.com/sqlarticles.htm

75% fill factor will be okay as you build the tables. However, as they begin to stabilize, you may want to increase the fill factor or set it to 0 and let SQL Server handle it.

You can use DBCC DBREINDEX in Query Analyzer to rebuild indexes on a table. This is much faster than editing in the Enterprise Manager.

The following example will rebuild all indexes on the table Employees while maintaining the current fill factor. The second param is the index name. Insert a name if you want to rebuild a single index. Be aware that if you choose to rebuild a clustered index, all indexes will be rebuilt. Change the third param to modify the fill factor.

DBCC DBREINDEX ('Employees', '', 0) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[rtgordon]

Great info... Thanks!

I was under that impression, but I wasn't quite sure after I read the faq on indexing. I understand now that I don't need an index on columns like the one I set up for insert date and so on.

I will try out the index rebuild. I could not access your page though. It returns forbidden.

Thanks again!
gordon

 

[tlbroadbent]

Sometimes I get that message when trying to access one of my pages. I try again and can get there. Just one of those things. Try the Articles link or if you want to look at the new SQL Performance page click here...

http://tlbroadbent.home.attbi.com/sql_performance.htm

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[rtgordon]

I couldn't simply click on the link. I had to copy/paste into the asddress.

 

[tlbroadbent]

Which browser are you using? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[rtgordon]

IE 6.0.26