SQLServer Read & Write

[SQLMeToo]

Our company processes numerous(100,000) records every<br>15 minututes (bcp), stored procedures are used to <br>disperse the data -we have staff and clients accing<br>the same database - during the insert and stored <br>procedure processing we are experiencing major<br>performance issues on select (staff and clients) -<br>any advise on solving this - should we be replicating<br>the DB.<br><br>Thanks

 

[foxdev]

Replication would be one approach, if you have an additional server available.&nbsp;&nbsp;You'd probably want to put your more powerful machine as the query box, but there are a lot of variables involved, so your mileage may vary.<br><br>If you have an SMP box, and the process is throttled by CPU usage, you could add additional processors.<br><br>If, as I suspect, everything is disk I/O bound, adding CPUs won't help much.&nbsp;&nbsp;Can you postpone the imports until off-peak times, rather than every 15 minutes? <p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br>

 

[Packermann]

Try running UPDATE STATISTICS on your tables and then re-compile your procedures. <br><br>Also, check the number of indexes on your tables. Try reducing the number by combining some indexes into one. This will reduce the amount of IO your INSERT have to do. This may mean you would have to change your queries a bit.<br><br>Also, try breaking your INSERT statement into smaller INSERT statements. Instead of having your INSERT statement process the whole 100,000 records at once, try processing only a 1,000 at a time, and loop through until all are processed. This will take longer, but it gives a chance for others to query the table before the next 1,000 is processed and the table page is locked. <br><br>

 

[calahans]

Review you procedures and your application. You BCP every fifteen minutes!!!<br><br>How many users? Do you need to constantly bulk load? Can you rearchitect you procedures... <p>Cal<br><a href=mailto: > </a><br><a href= > </a><br>