SQL server is very slow when retrieving data with C#

Question

Retrieving data from my SQL Server is very slow. Does any one see a problem?

Create the SQL Server table:

using (SqlCommand cmd = connection.CreateCommand())
{
    cmd.Parameters.AddWithValue("jaar", jaartal);
    cmd.CommandText =
        @" 

       BEGIN
       CREATE TABLE " + jaartal + @"(
        [code] [Varchar](max) NULL,
        [type] [Varchar](max) NULL,
        [datum] [datetime] NULL,
        [uur] [Varchar](max) NULL,
        [controller] [Varchar](max) NULL,
        [fout] [Varchar](max) NULL,
        [teller] [Varchar](max) NULL,
        [omschrijving] [Varchar](max) NULL,
        [graad] [Varchar](max) NULL,
        [var1] [Varchar](max) NULL,
        [var2] [Varchar](max) NULL,
        [var3] [Varchar](max) NULL,
        [var4] [Varchar](max) NULL,
        [var5] [Varchar](max) NULL,
        [var6] [Varchar](max) NULL,
        [var7] [Varchar](max) NULL,
        [var8] [Varchar](max) NULL,
        [var9] [Varchar](max) NULL,
        [var10] [Varchar](max) NULL,
        [var11] [Varchar](max) NULL,
        [var12] [Varchar](max) NULL,
        [var13] [Varchar](max) NULL,
        [var14] [Varchar](max) NULL,
        [var15] [Varchar](max) NULL,
        [var16] [Varchar](max) NULL,
        [var17] [Varchar](max) NULL,
        [var18] [Varchar](max) NULL,
        [var19] [Varchar](max) NULL,
        [var20] [Varchar](max) NULL,
        [var21] [Varchar](max) NULL,
        [var22] [Varchar](max) NULL,
        [var23] [Varchar](max) NULL,
        [var24] [Varchar](max) NULL,
        [var25] [Varchar](max) NULL,
        [var26] [Varchar](max) NULL,
        [var27] [Varchar](max) NULL,
        [var28] [Varchar](max) NULL,
        [var29] [Varchar](max) NULL,
        [var30] [Varchar](max) NULL,
        [var31] [Varchar](max) NULL,
        [var32] [Varchar](max) NULL,
        [var33] [Varchar](max) NULL,
        [var34] [Varchar](max) NULL,
        [var35] [Varchar](max) NULL,
        [var36] [Varchar](max) NULL,
        [var37] [Varchar](max) NULL,
        [var38] [Varchar](max) NULL,
        [var39] [Varchar](max) NULL,
        [var40] [Varchar](max) NULL,
        [Cat_MMAP] [Varchar](max) NULL
        ); END";

    try
    {           
        cmd.ExecuteNonQuery();
    }
    catch
    { 
    }
}

connection.Close();

Retrieving data from SQL Server:

I have a table for every year so I must look in all these tables.

But I have 1000 000 rows in all the database.

Tbl_events.Columns.Clear();

aantalfouten = 0; //numbers of error 
jaartaltabel = DataContainer.jaartalstart;
statusbalk.Value=statusbalk.Minimum;

while (aantalfouten < 40)
{
    try
    {
        statusbalk.Visible = true;

        tabelnaam = "Evenementen" + jaartaltabel.ToString(); //create name of the table that i want to read so as Events2013                       
        connection.Open(); 
        datatabel.Load(new SqlCommand("SELECT type,datum,uur,controller,fout,teller,omschrijving,graad,var1,var2,var3,var4,var5,var6,var7,var8,var9,var10,var11,var12,var13,var14,var15,var16,var17,var18,var19,var20,var21,var22,var23,var24,var25,var26,var27,var28,var29,var30,var31,var32,var33,var34,var35,Cat_MMAP  FROM " + tabelnaam + " where " + filter + "", connection).ExecuteReader());//Select statement
        connection.Close();

        Tbl_events.DataSource = datatabel.DefaultView; //insert the data in my datagridview this takes also a  lot of time
        jaartaltabel++;
    }
    catch
    {
        jaartaltabel++;
        aantalfouten++;
        connection.Close();
    }
}
ColumnWidth();

I have also use a filter with the where statement for don't receive all the data.

Sometimes I have also a system out of memory exception.

Does anyone have a solution for these problems?

Answer 1

You are running the query till you have 40 faults, this means you continue running your query till you have 40 exception. The exceptions will come when you run out of memory... This means you will retrieve a lot of data before crashing, and that will take a while.

Remove the while and you will be fine.

Or do you want to get 40 jaartabellen? Change aantalfouten to jaartaltabel:

 while (aantalfouten < 40) ====> while (jaartaltabel < 40)

I think your filter is on the [datum] [datetime] NULL column of the tables, so add a index on the datum column will improve you query if you have a lot of data in your table (at the end you your create table queries).

CREATE NONCLUSTERED INDEX IX_"+jaartal+" ON "+jaartal+" 
(
     datum
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW