Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jsmith
Starting Member
24 Posts |
 Posted - 2002-11-27 : 11:06:29
|
| Hi All,I have a stored procedure that is called frequently. It builds a recordset, then writes the data in the recordset into a text field.The recordset is constructed by joining several tables, sizes as follows:table 1 - 600 rowstable 2 - 30,000 rowstable 3 - 120 rowstable 4 - 50 rowstable 5 - 20 rowsThe data in tables 2-5 is static, the data in table 1 changes hourly.The recordset is constructed in a table variable, which is then looped through and written into a text field.The stored procedure runs around 250 times per minute (15000/hour).How would multiple processors/more memory help (if at all)?Also, what would be the optimum raid configuration for this?Edited by - jsmith on 11/27/2002 11:16:39Edited by - jsmith on 11/27/2002 11:17:21 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-11-27 : 11:48:06
|
| I think RAID 0/1 (RAID 10 is what most folks call it) is the optimial for performance for pretty much any system. The problem is it's pretty damn expensive to setup. You need at least 4 drives, and you only have half of the usable space for the drives.The stored proc runs 250 time per MINUTE? Is that really needed? That seems insanely fast. Maybe you could post some DDL and DML and we can help you optimise what you are doing, so that you don't need the worlds fastest PC to run this thing.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
jsmith
Starting Member
24 Posts |
Posted - 2002-11-27 : 12:06:07
|
| Thanks Michael.Basically, the table with 30000 rows is a user table, and the table with 600 rows is a news story table. An application sends the stories matching the users preferences (which are stored in the other tables) to the users twice daily, hence the 250 executions per minute (a scheduled job loops through the user table, calling this stored procedure for each user). The app requires all the stories to be built into one text field. My personal opinion is that the overall architecture could be improved, however I'm stuck with it for now, so I'm focussing on getting this stored procedure performing as fast as possible.The current hardware is 2 PIII 5000 Mhz processors, 1.5 Gb RAM, and RAID 5.Does 250 executions per minute seem like reasonable performance?Edited by - jsmith on 11/27/2002 12:07:23 |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-11-27 : 13:21:24
|
| Well if you can't change the system, then hardware is the answer.~4 records a second doesn't seem very fast, but I guess that is really dependant on those "preferences" and how many each user has etc.I think you need to some looking at some performance monitor counters and see where your bottleneck is. It's either the CPU's or Storage Subsystem. Take a look at the following article, and it should give you an idea of how to figure out where the bottleneck is.http://www.sql-server-performance.com/performance_monitor_counters.aspMichael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
|
|
|
|
query/raid/processor performance correlation