CODEWITHSUNDEEP
c#.netsql-serverslowdown
Sajjad Abdullah
Sajjad Abdullah

Reputation: 47

C# Desktop Application: SQL Server access suddenly gets slow

My C# desktop application has a form ItemsBrowser. My application is about an Inventory system. The ItemsBrowser form loads the Items Details while user add a new sale or new purchase. Here is LoadAllItems() code: -

    void LoadAllItems()
    {
        DBConnector dbc = new DBConnector();
        AccountsBasic.Classes.DBConnector dbca = new AccountsBasic.Classes.DBConnector();
        ArrayList lstItems = dbc.GetAllItems();
        var AddedItems = new List<DataGridViewRow>();

        Cursor.Current = Cursors.WaitCursor;
        dgvItems.Rows.Clear();
        for (int i=0; i<=lstItems.Count-1; i++)
        {
            Item itm = (Item)lstItems[i];
            ItemCategory ItemCat = dbc.GetThisItemCategory(itm.ItemCategoryCode);
            DataGridViewRow row = new DataGridViewRow();
            row.CreateCells(dgvItems);              
            row.Cells[0].Value = dbca.GetThisParty(dbc.GetThisItemCategory(itm.ItemCategoryCode).SupplierCode).PartyName;
            row.Cells[1].Value = ItemCat.ItemCategoryName;
            row.Cells[2].Value = itm.ItemID.ToString();
            row.Cells[3].Value = itm.ItemName;
            row.Cells[4].Value = itm.RetailPrice.ToString();
            row.Cells[5].Value = dbc.GetPresentStock_By_ItemID(itm.ItemID).ToString();

            AddedItems.Add(row);
            //dgvItems.Rows.Add(dbca.GetThisParty(dbc.GetThisItemCategory(itm.ItemCategoryCode).SupplierCode).PartyName, dbc.GetThisItemCategory(itm.ItemCategoryCode).ItemCategoryName, itm.ItemID.ToString(), itm.ItemName, itm.RetailPrice, dbc.GetPresentStock_By_ItemID(itm.ItemID).ToString());
        }

        dgvItems.Rows.AddRange(AddedItems.ToArray());
        dgvItems.AutoResizeColumns();
        Cursor.Current = Cursors.Default;
    }

This function was working fine and in speed. But suddenly It got slow very much. By checking each line one by one in the loop, I found that when a statement accessing the database like ItemCategory ItemCat = dbc.GetThisItemCategory(itm.ItemCategoryCode); the database access gets very slow. Although it was running pretty fine before. There are total 955 items in the table.

ALSO A VERY STRANGE THING I HAVE NOTICED...

I have installed this application on the client's machine and it is working fine there on client's machine with no delay...

GetAllItems() Function

public ArrayList GetAllItems(string SupplierCode = "", string ItemCategory = "")
{
    if (SupplierCode != "" && ItemCategory != "")
        comm.CommandText = "SELECT Items.ItemID, Items.ItemName, Items.Description, Items.ItemCategoryCode, Items.OpeningStock, Items.RetailPrice FROM Items, ItemCategories WHERE Items.ItemCategoryCode = ItemCategories.ItemCategoryCode AND ItemCategories.SupplierCode = '" + SupplierCode + "' AND ItemCategories.ItemCategory = '" + ItemCategory + "' ORDER BY Items.ItemID";
    else if (SupplierCode != "" && ItemCategory == "")
        comm.CommandText = "SELECT Items.ItemID, Items.ItemName, Items.Description, Items.ItemCategoryCode, Items.OpeningStock, Items.RetailPrice FROM Items, ItemCategories WHERE Items.ItemCategoryCode = ItemCategories.ItemCategoryCode AND ItemCategories.SupplierCode = '" + SupplierCode + "' ORDER BY ItemCategories.SupplierCode, ItemCategories.ItemCategory";
    else if (SupplierCode == "" && ItemCategory != "")
        comm.CommandText = "SELECT Items.ItemID, Items.ItemName, Items.Description, Items.ItemCategoryCode, Items.OpeningStock, Items.RetailPrice FROM Items, ItemCategories WHERE Items.ItemCategoryCode = ItemCategories.ItemCategoryCode AND ItemCategories.ItemCategory = '" + ItemCategory + "' ORDER BY Items.ItemID";
    else
        comm.CommandText = "SELECT * FROM Items Order By ItemID";

    ArrayList AllItems = new ArrayList();
    conn.Open();
    SqlDataReader dr;
    dr = comm.ExecuteReader();
    while (dr.Read())
    {
        Item it = new Item();
        it.ItemID = dr.GetInt32(0);
        it.ItemName = dr.GetString(1);
        it.Description = dr.IsDBNull(2) ? "" : dr.GetString(2);
        it.ItemCategoryCode = dr.IsDBNull(3) ? -1 : dr.GetInt32(3);
        it.OpeningStock = dr.IsDBNull(4) ? 0 : dr.GetInt32(4);
        it.RetailPrice = dr.IsDBNull(5) ? 0 : dr.GetDouble(5);

        AllItems.Add(it);
    }
    dr.Close();
    conn.Close();

    return AllItems;
}

GetThisItemCategory() Function

    public ItemCategory GetThisItemCategory(int ItemCategoryCode = -1, string SupplierCode = "", string ItemCategory = "")
{
    if (ItemCategoryCode == -1 && SupplierCode != "" && ItemCategory != "")
        comm.CommandText = "SELECT * FROM ItemCategories WHERE SupplierCode = '" + SupplierCode + "' AND ItemCategory = '" + ItemCategory + "' Order By SupplierCode, ItemCategory";
    else if (ItemCategoryCode == -1 && SupplierCode == "" && ItemCategory != "")
        comm.CommandText = "SELECT * FROM ItemCategories WHERE ItemCategory = '" + ItemCategory + "' Order By ItemCategory";
    else// if (ItemCategoryCode != -1 && SupplierCode == "" && ItemCategory == "")
        comm.CommandText = "SELECT * FROM ItemCategories WHERE ItemCategoryCode = '" + ItemCategoryCode + "' Order By SupplierCode, ItemCategory";

    SqlDataReader dr;
    ItemCategory ic = new ItemCategory();
    ic.ItemCategoryCode = -1;

    conn.Open();
    dr = comm.ExecuteReader();
    if (dr.Read())
    {
        ic.ItemCategoryCode = dr.GetInt32(0);
        ic.SupplierCode = dr.GetString(1);
        ic.ItemCategoryName = dr.GetString(2);
        ic.OrderableStockLimit = (dr.IsDBNull(3)) ? -1 : dr.GetInt32(3);
    }
    dr.Close();
    conn.Close();

    return ic;
}

Actually, problem is not about specific function. It is about any database access, whether it is GetThisItemCategory() or GetPresentStock_By_ItemID() function.

PLEASE NOTE EARLIER IT WAS WORKING PRETTY FINE. SUDDENLY IT STARTED BEHAVING IN THIS MANNER...

Upvotes: 0

Views: 641

Answers (1)

granadaCoder
granadaCoder

Reputation: 27908

You need to learn how to do "named parameters", to protect against injected sql attacks AND to get maximum plan-reuse from your RDBMS.

Here is an example:

using System;
using System.Data;
using System.Data.SqlClient;

class ParamDemo
{
    static void Main()
    {
        // conn and reader declared outside try
        // block for visibility in finally block
        SqlConnection conn   = null;
        SqlDataReader reader = null;

        string inputCity = "London";
        try
        {
            // instantiate and open connection
            conn =  new 
                SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
            conn.Open();

            // don't ever do this
            // SqlCommand cmd = new SqlCommand(
            // "select * from Customers where city = '" + inputCity + "'";

            // 1. declare command object with parameter
            SqlCommand cmd = new SqlCommand(
                "select * from Customers where city = @City", conn);

            // 2. define parameters used in command object
            SqlParameter param  = new SqlParameter();
            param.ParameterName = "@City";
            param.Value         = inputCity;

            // 3. add new parameter to command object
            cmd.Parameters.Add(param);

            // get data stream
            reader = cmd.ExecuteReader();

            // write each record
            while(reader.Read())
            {
                Console.WriteLine("{0}, {1}", 
                    reader["CompanyName"], 
                    reader["ContactName"]);
            }
        }
        finally
        {
            // close reader
            if (reader != null)
            {
                reader.Close();
            }

            // close connection
            if (conn != null)
            {
                conn.Close();
            }
        }
    }
}

http://csharp-station.com/Tutorial/AdoDotNet/Lesson06

You can read a few things about dynamic sql in this article.

http://sqlmag.com/database-performance-tuning/don-t-fear-dynamic-sql

(There is a mini overlap between your .cs C# "inline" sql vs this article...it'll give you a few things to research further if you're inclined)

.....

Finally, you need to learn the basics of "index tuning".

You can get an intro to that here:

https://sqlserverperformance.wordpress.com/2010/04/06/a-dmv-a-day-%E2%80%93-day-7/

As a guess, I would create an index on

ItemCategories.ItemCategoryCode

and a seperate index on

ItemCategories.SupplierCode

APPEND:

Finally, can you try this version of the code?

You want to get-rid of DataReaders as soon as possible, so your connection pool does not run out of connections.

public ItemCategory GetThisItemCategory(int ItemCategoryCode = -1, string SupplierCode = "", string ItemCategory = "")
{

    using (SqlCommand cmd = new SqlCommand("MyConnectionString")
    {

        /* TO DO !!! , build your sql-string and parameter list here */

        using (IDataReader dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
        {

            if /*while*/ (dataReader.Read())
            {
                ic.ItemCategoryCode = dr.GetInt32(0);
                ic.SupplierCode = dr.GetString(1);
                ic.ItemCategoryName = dr.GetString(2);
                ic.OrderableStockLimit = (dr.IsDBNull(3)) ? -1 : dr.GetInt32(3);
            }

            if (dataReader != null)
            {
                try
                {
                    dataReader.Close();
                }
                catch { }
            }           

        }

        cmd.Close();

    }

    return ic;

}

Upvotes: 1

Related Questions