none
Meultithreading using SqlClient with connected layer

    Question

  • Im use stored procedures in db like this:

    GO
    CREATE PROCEDURE EncountAll
    AS 
    BEGIN TRANSACTION
    	SELECT COUNT(RowId) FROM Rows;
    	if (@@error<>0) ROLLBACK;
    COMMIT
    GO

    How can I exclude all possibility of error? Is there a better approach to writing sql-code for multi-threaded applications? How can I disable all types of logging for my database, and the collection of statistics and logging to improve performance? What pitfalls are at the level of the connected using multi-threaded programs stored procedure calls? Thank you.

    Saturday, August 24, 2013 6:44 AM

Answers

  • As Olaf suggested, You do not need a transaction in your table. 

    1. Transaction are great mechnism to enforce the ACID properties. As long as you are not touching your data (insert/delete/update) you do not need transaction in your procedure given.

    2. You are using Select count(ROWID) From Rows. The issue with this code is that if there is any chance your ROWID can have NULL Value, then it will not count the records. Hopefully, ROWID is a primary hece you will be safe here.

    3. SQL Server logging is by default. This is very important feature in SQL Server at the time of any prod issues or tracking down your issues, hence do not think of disabling the logging feature. BTB, you can not do it also...

    4. Thread configuration is more of a business layer concept. Once the procedure is being called, it just as another work to SQL Server. So, if you want to configure anything in the thread, you may better tune at your business layer.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Papy NormandModerator Monday, August 26, 2013 6:56 PM
    • Marked as answer by _Alex9_ Wednesday, August 28, 2013 2:27 PM
    Monday, August 26, 2013 5:32 AM

All replies

  • Can the code that I gave, caused by site users lead to deadlock or where it will be executed only for one user, and for a second there?
    Saturday, August 24, 2013 10:23 AM
  • Hi Alex,

    Welcome to MSDN forum.

    I think this thread is specific to SqlServer. This form is to discuss problems about ADO.NET development. So I am helping you to move this thread directly into SqlServer forum.

    If you have any concerns, please don’t hesitate to let me know. I will be glad to help you again.

    Best Regards,


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Monday, August 26, 2013 2:51 AM
  • Hello Alex,

    Your SP don't make sense, because you run a simple SELECT statement; there is nothing to rollback or to commit.

    Logging is an internal mechanism of SQL Server (of any RDBMS), you can't disable it.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, August 26, 2013 4:53 AM
  • As Olaf suggested, You do not need a transaction in your table. 

    1. Transaction are great mechnism to enforce the ACID properties. As long as you are not touching your data (insert/delete/update) you do not need transaction in your procedure given.

    2. You are using Select count(ROWID) From Rows. The issue with this code is that if there is any chance your ROWID can have NULL Value, then it will not count the records. Hopefully, ROWID is a primary hece you will be safe here.

    3. SQL Server logging is by default. This is very important feature in SQL Server at the time of any prod issues or tracking down your issues, hence do not think of disabling the logging feature. BTB, you can not do it also...

    4. Thread configuration is more of a business layer concept. Once the procedure is being called, it just as another work to SQL Server. So, if you want to configure anything in the thread, you may better tune at your business layer.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Papy NormandModerator Monday, August 26, 2013 6:56 PM
    • Marked as answer by _Alex9_ Wednesday, August 28, 2013 2:27 PM
    Monday, August 26, 2013 5:32 AM