C# code and SQL Server performance

Question

I have a SQL Server database designed like this :

TableParameter
  Id    (int, PRIMARY KEY, IDENTITY)
  Name1 (string)
  Name2 (string, can be null)
  Name3 (string, can be null)
  Name4 (string, can be null)

TableValue
  Iteration         (int)
  IdTableParameter  (int, FOREIGN KEY)
  Type              (string)
  Value             (decimal)

So, as you've just understood, TableValue is linked to TableParameter. TableParameter is like a multidimensionnal dictionary.

TableParameter is supposed to have a lot of rows (more than 300,000 rows)

From my c# client program, I have to fill this database after each Compute() function :

for (int iteration = 0; iteration < 5000; iteration++)
{
    Compute();
    FillResultsInDatabase();
}

In FillResultsInDatabase() method, I have to :

1. Check if the label of my parameter already exists in TableParameter. If it doesn't exist, i have to insert a new one.
2. I have to insert the value in the TableValue

Step 1 takes a long time ! I load all the table TableParameter in a IEnumerable property and then, for each parameter I make a

.FirstOfDefault( x => x.Name1 == item.Name1 &&
                      x.Name2 == item.Name2 &&
                      x.Name3 == item.Name3 &&
                      x.Name4 == item.Name4 );

in order to detect if it already exists (and after to get the id).

Performance are very bad like this !

I've tried to make selection with WHERE word in order to avoid loading every row of TableParameter but performance are worse !

How can I improve the performance of step 1 ?

For Step 2, performance are still bad with classic INSERT. I am going to try SqlBulkCopy.

How can I improve the performance of step 2 ?

EDITED

I've tried with Store Procedure :

CREATE PROCEDURE GetIdParameter
    @Id     int OUTPUT,
    @Name1  nvarchar(50) = null,
    @Name2  nvarchar(50) = null,
    @Name3  nvarchar(50) = null
AS
SELECT TOP 1 @Id = Id FROM TableParameter
WHERE
TableParameter.Name1 = @Name1   
AND
(@Name2 IS NULL OR TableParameter.Name2= @Name2)
AND
(@Name3 IS NULL OR TableParameter.Name3 = @Name3)
GO

CREATE PROCEDURE CreateValue
    @Iteration int,
    @Type   nvarchar(50),
    @Value  decimal(32, 18),
    @Name1  nvarchar(50) = null,
    @Name2  nvarchar(50) = null,
    @Name3  nvarchar(50) = null
AS
DECLARE @IdParameter int
EXEC GetIdParameter @IdParameter OUTPUT, 
                    @Name1, @Name2, @Name3
IF @IdParameter IS NULL
BEGIN
    INSERT TablePArameter (Name1, Name2, Name3) 
                               VALUES
                              (@Name1, @Name2, @Name3)

    SELECT @IdParameter= SCOPE_IDENTITY()
END
  INSERT TableValue (Iteration, IdParamter, Type, Value) 
                              VALUES
                              (@Iteration, @IdParameter, @Type, @Value)
GO

I still have the same performance... :-( (not acceptable)

Answer 1

If I understand what's happening you're querying the database to see if the data is there in step 1. I'd use a db call to a stored procedure that that inserts the data if it not there. So just compute the results and pass to the sp.

Can you compute the results first, and then insert in batches?

Does the compute function take data from the database? If so can you turn the operation in to a set based operation and perform it on the server itself? Or may part of it?

Remember that sql server is designed for a large dataset operations.

Edit: reflecting comments Since the code is slow on the data inserts, and you suspect that it's because the insert has to search back before it can be done, I'd suggest that you may need to place SQL Indexes on the columns that you search on in order to improve searching speed.

However I have another idea.

Why don't you just insert the data without the check and then later when you read the data remove the duplicates in that query?

Answer 2

Given the fact that name2 - name3 can be null, would it be possible to restructure the parameter table:

TableParameter
  Id    (int, PRIMARY KEY, IDENTITY)
  Name  (string)
  Dimension int

Now you can index it and simplify the query. (WHERE name = "TheNameIWant" AND Dimension="2")

(And speaking of indices, you do have index the name columns in the parameter table?)

Where do you do your commits on the insert? if you do one statement commits, group multiple inserts into one.

If you are the only one inserting values, if speed is really of essence, load all values from the database into the memory and check there.

just some ideas

hth

Mario

Answer 3

I must admit that I'm struggling to grasp the business process that you are trying to achieve here.

On initial review, it appears as if you are are performing a data comparison within your application tier. I would advise against this and suggest that you let the Database Engine do what it is designed to do, to manage and implement your data access.

As another poster has mentioned, I concur that you should look to create a Stored Procedure to handle your record insertion logic. The procedure can perform a simple check to see if your records already exist.

You should also consider:

• Enforcing the insertion logic/rule by creating a Unique Constraint across the four name columns.
• Creating a covering non-clustered index incorporating the four name columns.

With regard to performance of your inserts, perhaps you can provide some metrics to qualify what it is that you are seeing and how you are measuring it?

To give you a yardstick the current ETL insertion record for SQL Server is approx 16 million rows per second. What sort of numbers are you expecting and wanting to see?

Answer 4

the fastest way ( i know so far) is bulk insert. but not just lines of INSERT. try insert + select + union. it works pretty fast.

insert into myTable
select a1, b1, c1, ...
union select a2, b2, c2, ...
union select a3, b3, c3, ...