Sql-server – Slow fill database

csql server

The source sends in my program a large number of rows. When the number of rows such as two thousand per minute for one table, all is saved in the database properly. But if you send two thousand per minute to 200 tables, then there is a delay. Sometimes a big delay, in five minutes. How can I make it so that all the information was entered on time. How to parallelize? Or any other ways out of the situation?

Example:

send request
get response

Code:

void feed_response(int requestId, string copmanyname, string someinformation)
 {
   SQL.sendToSQL(int id, string copmanyname, string someinformation);
 }

sql.sendtosql

Code:

public static void sendToSQL(int id, string copmanyname, string someinformation)
 {
  SqlCommand commandTradeToDb = new SqlCommand("INSERT INTO dbo."+ copmanyname.ToUpper() +" (id, someinformation) VALUES (@id, @someinformation)", connection);

  commandTradeToDb.Parameters.Add("@id", id);
  commandTradeToDb.Parameters.Add("@someinformation", someinformation);
 }

Code is very simply.

ps Variables in a method sql.sendToSql changed.

Best Answer

When you are sending 2000 requests to the same table, SQL Server already has the pertinent pages in memory i.e. SQL Server caches data pages in memory so they don't have to be read in from disk next time. When you are writing to multiple tables, SQL has to read data pages in each of those tables to find the correct spot to insert new rows. Unless you have enough memory allocated to SQL SErver, this will push out data pages that have been previously cached.

To get around this, you could group all the inserts for each particular table and execute them in sequence. You could also add memory to the server, giving it a larger cache to work with.

Related Solutions

Sql-server – Database Tasks in C#

For those kind of tasks, you should use the tools you are most comfortable with.

A DB would of course run a scheduled database script or use SSIS. A C# dev creates a small C# tool and a sysadmin creates a PowerShell script.

Sql-server – deteriorating stored procedure running times

What is up with FROM part JOIN model ON 1=1? This the same as FROM part, model, which is a cartesian join and will result in a very large number of rows. Is that join supposed to be like that?

You will likely help us help you if you provide details about the tables involved. Please "script" the definition of the tables, along with any indexes defined on those tables.

This sounds like a classic case of parameter sniffing resulting in good plan/bad plan choices for various scenarios in your data.

You may be able to get more reliable performance by making SQL Server cache different plans for different scenarios by using sp_executesql, as in the following example:

CREATE PROCEDURE [dbo].[create_grid_materials2] 
(
    @partlistid bigint
    , @pid bigint
    , @masterid bigint
)
AS
BEGIN
    begin
        DECLARE @cmd NVARCHAR(MAX);

        SET @cmd = '   
        INSERT INTO material (partid, personid, modelID)
        SELECT 
            partid = part.id
            , personid = @pid
            , modelid = model.id  
        FROM part
            INNER JOIN model ON 1=1
        WHERE (
            model.masterid = ' + CONVERT(NVARCHAR(50), @masterid) + ' 
                AND model.modelSetID IS NULL
                AND part.partlistid = ' + CONVERT(NVARCHAR(50), @partlistid) + '
                AND (
                    part.partType = 100 
                    or part.partType=120 
                    or part.partType = 130
                )
            )
            AND NOT EXISTS (
                SELECT 1 
                FROM material AS a1 
                WHERE a1.partid = part.id 
                    AND a1.personid=@pid 
                    AND a1.modelid=model.id
                )';
        DECLARE @Params VARCHAR(200);
        SET @Params = '@pid INT';
        EXEC sys.sp_executesql @cmd
            , @Params
            , @pid = @pid;
    end
End

The above code will cause a new plan to be generated for each combination of @partlistid, and @masterid.

The presumption here is some combinations of those two variables lead to a very small number of rows, whereas some combinations lead to a very large number of rows.

Forcing a plan for each combination allows SQL Server to generate more efficient plans for each. I've explicitly not included @pid since you probably want to try it with a fairly small number of combinations first; adding a third variable to the mix will make for an exponentially larger number of possible plans.

Related Question