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-07 : 09:03:01
|
| Not sure if anyone will be able to help on this, as problem described here is rather vague, but here goes:I have to write a number of varchars (read from another table) into a text field, up to about 8k on average through a stored procedure. I have to do this around 30000 times per hour. The best I'm able to achieve currebtly is around 10000 per hour. Does this seem like reasonable performance or am I missing something?The server spec is 1 GB Ram, 1 PIII 1 GHz processor.Would partitioning the table where the text is written over multiple file groups make any difference? |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-11-07 : 12:43:54
|
| If you could post the relevent tables DDL and also the relevent code....(hiding any state secrets)....you may get more help.the problem may not be your machine....it may be your code/database setup. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-11-07 : 12:54:39
|
| Without the DDL, the only thing that I could suggest right now is to remove all indexes on the table that is being written to. This would speed up the writes, but it would negatively affect the reads. |
|
|
|
mohamedyousuff@yahoo.com
Starting Member
24 Posts |
Posted - 2002-11-08 : 00:38:53
|
You may also consider increasing the I/O performance particularly the write performance by using RAID systems for the SQL data files and log files. You can expect a significant boost in the performance by this.quote:
Not sure if anyone will be able to help on this, as problem described here is rather vague, but here goes:I have to write a number of varchars (read from another table) into a text field, up to about 8k on average through a stored procedure. I have to do this around 30000 times per hour. The best I'm able to achieve currebtly is around 10000 per hour. Does this seem like reasonable performance or am I missing something?The server spec is 1 GB Ram, 1 PIII 1 GHz processor.Would partitioning the table where the text is written over multiple file groups make any difference?
|
|
|
|
jsmith
Starting Member
24 Posts |
Posted - 2002-11-08 : 11:43:50
|
| Thanks for the suggestions.Not sure adding code here is practical - company would take a dim view and it is very complicated.Thanks again for your help. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-11-08 : 12:44:49
|
| A second processor and the right RAID configuration might help.Can you describe your current disk sub-system?You are only able to do 10k inserts of a VARCHAR(8000) into a TEXT field per hour? That seems kinda slow.Another solution might be to have another DB server that does JUST this. Depending on how real-time the data needs to be you'll need to look at replication or logshipping to move the data around.Just a few thoughts, HTHMichael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
jsmith
Starting Member
24 Posts |
Posted - 2002-11-11 : 10:11:35
|
| Thanks Michael - Its RAID 5 with a single PIII 1 GHz Processor. I'm reasonably confident with the code/schema. Several of the DBAs here have reviewed it and are happy with it also. Have thought about using a dedicated server for this, but my superiors want this to be a last resort due to further complication of an already complicated system. I'm going to push for a second processor anyway - would this and changing the RAID system have any benefit? |
|
|
|
|
|
|
|
|
Write Text Performance