StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>SQL Server, like other relational databases, is desgined to, and is pretty good at, working on sets of data.</p> <p>Databases are not good at procedural code where all the opportunities for optimization are obscured from the query processing engine.</p> <p>Using <code>RawStatusFeed</code> to store some proprietry request string and then processing a list of those one by one, is going to be ineffiencnt for database code. This might make the inserts very fast for the client, and this might be very important, but it comes at a cost.</p> <p>If you break the request string down on insert, or better still, before insert via a specialised SP call, then you can store the required changes in some intermediate relational model, rather than a list of strings. Then, every so often, you can process all the changes at once with one call to a stored procedure. Admittedly, it would probably make sense for that stored procedure to contain several query statements. However, with the right indexes and statistics the query processing engine will able to make an efficient execution plan for this new stored procedure.</p> <p>The exact details of how this should be achieved depend on the exact details of the <code>RawStatusFeed</code> table and the implementation of <code>usp_pushRawDataAndProcess</code>. Although this seems like a rewrite, I don't imagine the <code>DeviceCode</code> column is that complicated.</p> <hr> <p>So, the short answer is certainly yes but, I'd need to know what <code>usp_pushRawDataAndProcess</code> does in detail.</p> <p>The signature of the <code>usp_pushRawDataAndProcess</code> SP is acting as a bottle neck.</p> <hr> <p>If you can't change <code>usp_pushRawDataAndProcess</code> and and won't create a set based alternative then you are stuck with the bottle neck.</p> <p>So, rather than removing the bottle neck you could take another tack. Why not make more concurrent instances of the bottle neck to feed the data through.</p> <p>If you are using SQL Server 2005 or above you could use some CLR to perform numerous instances of <code>usp_pushRawDataAndProcess</code> in parallel.</p> <p><a href="http://www.codeproject.com/Articles/29356/Asynchronous-T-SQL-Execution-Without-Service-Broke" rel="nofollow">Here is a link</a> to a project I used before to do something similar.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload