Search code examples
sql-serverbatch-insert

Reduce sql server overhead with group insertion

I have a crawler that fetch documents across web pages. when i receive a page, i immediately insert the content in sql server, (20 insertion per second). this slow down my application and server responsiveness. I thought I could collect 20 item in a group and then insert it using a store procedure with (20*fieldCount).

This reduce the sql server overhead a bit, but i don't know passing 200 parameter to sql server stored procedure doesn't make another overhead. please help me. is there any better way to minimize the overhead of this frequent insertion?

Solution

  • Less connection opening

    It would be faster even to call the same SP as you have now and call it 20 times in a row. Because you'd only establish a SQL connection once and then push 20 records in. The major overhead being opening and closing a connection.