June 19, 2011 at 6:07 pm
iam using c sharp and mssql for my coding....
i have used an simple select statement in c sharp, which will be executed very fast. which is working fast for 50 times, but after that it is getting slow down. if i again issue the same command after 2 min, it is again running fast for 20 - 30 time, then again it is getting slow down.. the command is very simple,
select productname from productdetail where productname<="abc";
i was not able to find the reason for its slowness, please help me on this....
June 19, 2011 at 6:16 pm
How many rows in that table?
What's the filter value when it's slow? I mean if you enter where productname <= 'z' it has to return all rows so that will definitely take more time than <= 'a'.
How many rows returned when fast vs slow?
Please save and upload the actual execution plan for the slow and then the fast results.
June 19, 2011 at 6:35 pm
Do you mean you are executing this query many times per second/minute?
Are you using a connection pool? Is it possible that you are using up the connections in the pool, then having to create new ones (this is where it slows down)? Then after 2 minutes idle, the connections are released and available again?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 19, 2011 at 11:50 pm
i did not use any connection pool.
iam doing this software for an super market, so if they keep on pressing up or down arrow.. value has to be get from the table. so, the command will be executed morethan once in a second. since there will be morethan 5000 item in a table, iam getting 20 rows at a time...
currently i have only 3 sample rows a, ab, aaa ....
if iam using mysql, samething is working fine 20 rows. but in mssql, even with 3 rows it is not working.
June 20, 2011 at 12:07 am
Sounds as if you need some form of server side paging.
June 20, 2011 at 12:29 am
here is my coding
SqlConnection conn = new SqlConnection();
conn = new SqlConnection("Data Source=10.0.0.2;Database=SuperShop;User ID=sa;Password=sa;");
SqlCommand command = conn.CreateCommand();
command.Connection = conn;
try
{
conn.Open();
command.CommandType = CommandType.Text;
command.CommandText = "Select TOP 20 ProductName,ProductId from ProductDetail where ProductName >='" + searchvalue + "' order by ProductName asc";
reader = command.ExecuteReader();
while (reader.Read())
{
rowcount++;
this.ProductSearchDataGrid.Rows.Add("", "");
ProductSearchDataGrid.Rows[rowcount - 1].Cells["ProductNameDataGrid"].Value = reader["ProductName"].ToString();
ProductSearchDataGrid.Rows[rowcount - 1].Cells["ProductNameIdDataGrid"].Value = reader["ProductId"].ToString();
}
reader.Close();
reader = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
command.Dispose();
command = null;
conn.Close();
conn = null;
}
June 20, 2011 at 1:34 am
a) dont use 'sa' , 'sa' as user / password
b) read up on sql injection attacks
c) have you an index on ProductDetail.ProductName ?
June 20, 2011 at 4:53 am
no i have not used indexing...
June 20, 2011 at 5:17 am
Dave Ballantyne (6/20/2011)
a) dont use 'sa' , 'sa' as user / passwordb) read up on sql injection attacks
c) have you an index on ProductDetail.ProductName ?
And don't post that login info on the WEB.
You need to go in the server and change it NOW.
Still waiting on the actual execution plan to help out.
June 20, 2011 at 5:31 am
thanks for your replies,
since it in development phase iam using the default username and password. later i will change it to new password.
i have not used indexing on my table....
actual execution plan means, what information you are expecting...
June 20, 2011 at 5:35 am
with out trying to sound too harsh, your really need to re-develop that code before putting it into production otherwise you open yourself up to a major security issues through sql injections.
June 20, 2011 at 5:41 am
vivekvinodhraj (6/20/2011)
thanks for your replies,since it in development phase iam using the default username and password. later i will change it to new password.
i have not used indexing on my table....
actual execution plan means, what information you are expecting...
By which time you're likely to break every query in the system. It's better to implement that from day 1.
Copy your query in SSMS.
In the Query menu, hit show ACTUAL execution plan. Save that output to .sqlplan file and upload here.
Make sure to save both the plan for the quick query and the slow one.
June 20, 2011 at 7:09 am
vivekvinodhraj (6/20/2011)
no i have not used indexing...
Then try adding one,
Create index idxProductDetail on ProductDetail (ProductName )
Should do you
June 21, 2011 at 8:22 pm
Hi guys, sorry for the delay....
here i have attached the trace file with this reply, it have the execution plan... i think, it will help you to provide me the solution....
starting transaction took very less duration 30 to finish, but later it is increased to nearly 470
June 22, 2011 at 10:00 am
hi, please let me know if you need any other details regards this issue
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply