August 25, 2011 at 6:54 am
LutzM (8/25/2011)
chandan_jha18 (8/25/2011)
...you are right about a table variable holding many rows. I will try to switch that into a temp table with some index on it.
I agree that like '%' like causes scan no matter what but the text they search is dynamic so I am bound to use it. However as you said that it offers no selectivity, can we move it to the end of the where clause because I think doing this we are just trying to change the selectivity and not the output. Output will be the same irrespective of order of filters.
Seems like a misunderstanding:
When I wrote
If the 'LIKE %' clause can be applied at the very end (e.g. since it doesn't provide a high selectivity), I would not include in the first query.
I was referring to the divide'n'conquer approach, meaning to perform this select statement at the very end of a list of statements, not at the end of a WHERE clause in a complex statement.
Sorry for the confusion.
sorry for not understanding your idea.I apologize. So do you suggest that i should take the select statement with like operator out of the where clause, write it at the end and then join both of them?
August 25, 2011 at 7:10 am
and you have that OR problem, with that you will also do scan. I suggest, if it's possible, to change app and set a checkbox to filter "exactly, start like, anywhere". And default use one of the first or second. in that case you can use LIKE 'bla%' and that use index. In other case you need to use fulltext serch and change like operator to fulltext.
-
Lic. Andrés M. Aiello
DBA MSSQL - Oracle
August 25, 2011 at 7:28 am
chandan_jha18 (8/25/2011)
...sorry for not understanding your idea.I apologize. So do you suggest that i should take the select statement with like operator out of the where clause, write it at the end and then join both of them?
That's just part of it. The major issue is the catch-all query all by itself together with the table variable. And I just noticed the view definition makes a heavy usage of the NOLOCK hint. I hope you're happy with "almost correct results"... (yesterday there was a link to a related article: http://www.mssqltips.com/tip.asp?tip=2470)
What I probably would end up doing is one query per parameter set related to one of the large source tables, store those in temp tables and join those temp tables at the very end.
Just a base concept though. To really speed up this monster seems to be a little more than what should be done with a forum post. There's just too much to cover. And the options may vary based on the data structure and the whole business process.
August 25, 2011 at 7:59 am
LutzM (8/25/2011)
chandan_jha18 (8/25/2011)
...sorry for not understanding your idea.I apologize. So do you suggest that i should take the select statement with like operator out of the where clause, write it at the end and then join both of them?
That's just part of it. The major issue is the catch-all query all by itself together with the table variable. And I just noticed the view definition makes a heavy usage of the NOLOCK hint. I hope you're happy with "almost correct results"... (yesterday there was a link to a related article: http://www.mssqltips.com/tip.asp?tip=2470)
What I probably would end up doing is one query per parameter set related to one of the large source tables, store those in temp tables and join those temp tables at the very end.
Just a base concept though. To really speed up this monster seems to be a little more than what should be done with a forum post. There's just too much to cover. And the options may vary based on the data structure and the whole business process.
i know that view has no lock hints. Getting rid of it did not help much in my performance so i have left it the way it was designed.
also, when i join my base tables and a temp variable(having million rows), why this temp variable reads does not appear when i use 'set statistics io on'?
I will convert them to temp tables instead and use some index but with a lot of issues in schema itself, it is just not sufficient to use just indexes here.
August 25, 2011 at 8:18 am
chandan_jha18 (8/25/2011)
i know that view has no lock hints. Getting rid of it did not help much in my performance so i have left it the way it was designed.
Getting rid of nolock won't improve performance, but it might fix some occasional incorrect results that your users have been getting.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply