query takes long time!

Jassim Rahma

I am using this code on SQL Server Expess as stored procedure. The icd_disease has 12,000 records but with LEFT(icd_code, 1) = 'R' it’s just 400 records. Then why it takes more than 40seconds to bind into grid control although I ave already indexed the icd_code and icd_description field

SELECT icd_diseases.icd_id, icd_diseases.icd_code, icd_diseases.icd_description
FROM icd_diseases
WHERE LEFT(icd_code, 1) = 'R'
ORDER BY icd_description, icd_code

This is my binding code

data_table = new DataTable();

sql_connection = new SqlConnection((string)public_var._system_parameters_hash["SQL_SERVER_CONNECTION"]);

sql_connection.Open();
sql_command = new SqlCommand("sp_get_all_symptoms", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_adapter = new SqlDataAdapter(sql_command);
sql_adapter.Fill(data_table);
dataSymptoms.DataSource = null;
dataSymptoms.ResetBindings();
dataSymptoms.DataSource = data_table;

gridSymptoms.Columns["icd_id"].Caption = "icd_id#";
gridSymptoms.Columns["icd_id"].AppearanceHeader.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
gridSymptoms.Columns["icd_id"].AppearanceCell.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
gridSymptoms.Columns["icd_id"].Visible = false;

gridSymptoms.Columns["icd_code"].Caption = "icd_code#";
gridSymptoms.Columns["icd_code"].AppearanceHeader.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
gridSymptoms.Columns["icd_code"].AppearanceCell.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
gridSymptoms.Columns["icd_code"].Visible = false;

gridSymptoms.Columns["icd_description"].Caption = "Symptom";
gridSymptoms.Columns["icd_description"].AppearanceHeader.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Default;
gridSymptoms.Columns["icd_description"].AppearanceCell.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Default;
gridSymptoms.Columns["icd_description"].Width = 420;

dan sh

Figure out what takes more time, the query or the code. If it is the query, take a look at execution plan. Might help.

Lost User

WHERE LEFT(icd_code, 1) = 'R'

Doesn't look very efficient. I'd add an extra (redundant) column that holds the first character that you're filtering on. And put an index on that field :)

I are Troll :suss:

Jassim Rahma

I am sure there is something wrong because running a query to get all the 12000 on the same PC but using SSMS takes 1sec only! The issue is when I run it on winform either to get all or to get starting with R so no creating another col holding the first character won't help when I want to get all

Lost User

jrahma wrote:

when I want to get all

sp_get_all_symptoms

jrahma wrote:

I am sure there is something wrong because running a query to get all the 12000 on the same PC but using SSMS takes 1sec only!

I don't think that SSMS is using a datatable to cache the entire table locally. Might be using a reader, filling your grids with results as they arrive over TCP. If all the ICD-codes need to be on the client, why not put them on the clientmachine as a SqlCe database?

I are Troll :suss:

David Skelly

Why not just do this?

WHERE icd_code LIKE 'R%'

Isn't that the same thing as LEFT(icd_code, 1)? I don't know whether the query optimiser is smart enough to reduce the two queries down to the same thing or not. Using LIKE would definitely result in the optimiser considering using an index on icd_code if there is one. I don't know whether the query optimiser considers using an index for LEFT or not.

Lost User

David Skelly wrote:

Why not just do this?

Would be an option too, even cleaner to read. Another (a bit more obfuscating perhaps) alternative would be a computed column, including an index;

BEGIN TRANSACTION
CREATE TABLE Test
(
ICDKEY VARCHAR(8)
,ICDVAL VARCHAR(5000)

,KEYPFX AS LEFT(ICDKEY, 1)
)

CREATE INDEX Test_KeyPfx_Idx ON Test (KEYPFX)

ROLLBACK

Then again, if I have the option of doing the processing ahead, I'd surely choose to add a readonly-column and do a single update-statement. Mean part of that query is that it's dragging along a large text-field.

I are Troll :suss:

PIEBALDconsult

Start with using LIKE (as mentioned) 1) Don't prefix your stored procedure names with SP_. 2) Don't use a DataAdapter, use a DataReader 3) Be sure to close your Connection