Reputation: 47
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
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