0

I have database with around 50.000 records. Let say that I have this query (it is just a part) in stored procedure (using c# + sqlserver):

SELECT .... 
FROM
Table1 t1
LEFT JOIN Table2 t2 ON t1.ID=t2.ID
LEFT JOIN Table3 t3 ON t2.ColumnName=t3.ColumnName
WHERE 
ISNULL(t1.ColumnName,'') LIKE @arg1 //FAST
ISNULL(t2.ColumnName2,'') LIKE @arg2//SLOW
ISNULL(t3.ColumnName,'') LIKE @arg3 //SLOW

If I search after t3.Column2 a Query is slow. If I look after some t1 column (where is not left join), then it works like a charm, very fast. What is the problem? How can I speed up this? I tried to not use joins but it's not possible. Can I put some index on tables? On which in this particiular case?

michael24B
  • 293
  • 1
  • 6
  • 20
  • 1
    It would help to know what you are searching for - what is @arg1 ? Are there any indexes on t1.ColumnName or t3.ColumnName2 ? – JamieA Jul 21 '14 at 09:06
  • It's value of textbox - search parameter, let say name of person. – michael24B Jul 21 '14 at 09:07
  • Yes, index is on t1.Id (Clustered). I think it's automatically generated. – michael24B Jul 21 '14 at 09:19
  • First thing, you don't need `ISNULL`. 2nd, searching over a text column while using `LIKE` may take a lot of time you need to tell us more than that. – Tzah Mama Jul 21 '14 at 09:22
  • Yes I need ISNULL, because that column can also be null, but I need to show other columns which are not null anyway. I need Like because he can type just a part of whole word and results should be also displayed. – michael24B Jul 21 '14 at 09:34

1 Answers1

1

I would try the below options and see if the performance improves. You may also try 'Execution Plan' for any index suggestions.

Create an index on,

  • ColumnName in t2 & ColumnName in t3
  • If above didn't help add one more index on ColumnName2 in t2

P.S. Indexes will have an impact on update/insert/delete, more information here.

Edit - Updated as per the edited question

Community
  • 1
  • 1
Baga
  • 1,354
  • 13
  • 24
  • So there won't be any performance speed up if I use only Select statements? Because I never update or insert anything (read-only). – michael24B Jul 21 '14 at 09:33
  • If it is mostly read only, then adding indexes will definitely improve performance of select statements. – Baga Jul 21 '14 at 09:37
  • I missed something, sorry. Can you rewrite your instrouction beacuse I rewrite code (I missed something)? See current code on my post. Thanks for your time, I really appreciate it. – michael24B Jul 21 '14 at 09:51
  • Thanks, but no sugnificant change. You talk about creating non-clustered-Index, right? – michael24B Jul 21 '14 at 10:22
  • yes, non-clustered index. As @Tzah has suggested ISNULL is not required in your case. You can try removing it, like `t1.ColumnName LIKE @arg1 OR t2.ColumnName2 LIKE @arg2`... – Baga Jul 21 '14 at 10:34
  • "Yes I need ISNULL, because that column can also be null, but I need to show other columns which are not null anyway. I need Like because he can type just a part of whole word and results should be also displayed." I will accept your answer. – michael24B Jul 21 '14 at 10:44
  • is your question answered? if not pls un-accept the answer and wait for better answers.. – Baga Jul 21 '14 at 10:50
  • You made a great explanation, but I don't see any other appropriate solution. – michael24B Jul 21 '14 at 10:52