November 2, 2021 at 9:31 pm
Yes. WHERE character_column LIKE ‘string%’
To elaborate, currently when a user calls the help desk, we need to identify the client they work for, as well as a few other things. The functionality of the apps do not have a very good search, and far too often the users don't know or provide enough info to find them in the system.
So, we allow our help desk people to run ad-hoc queries against production databases. Needless to say that can be a nightmare.
We are in the process of creating a utility app that does a search for the clients, and provides the help desk with all the info they need. The current search on email only is woefully inadequate, and they end up doing all kinds of LIKE searches that sometimes kill the system.
The utility does an exact search on email and names. A LIKE search, WHERE name LIKE '%name%', is enormously slow. Doing a table scan of an 80 GB table is not very efficient.
So, we do the exact, and if the user wants, they do a "Starts with" search. That is infinitely faster than the like. They can also do the like, but we throw up a warning that it will take forever.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 3, 2021 at 3:26 pm
ScottPletcher wrote:Jeff Moden wrote:ScottPletcher wrote:Actually you could be causing a performance problem by getting SQL to use the same query plan for different values, i.e., by avoiding the "overhead" of a recompile. The first query executed might require a full scan, but the next one could use an index lookup ... but it won't because you parameterized the query and basically forced SQL to use the same plan.
It's true that the article doesn't mention using recompile with dynamic SQL. That may be an oversight rather than a feature.
The OPTION ( RECOMPILE ) may be redundant with EXEC(), but it doesn't hurt anything if the plan would always be (re)compiled anyway.
The same holds true for static SQL thanks to parameter sniffing. Having individual query plans for each main set of criteria is a good thing in these "Catch-All" queries thanks to the plans that are formed. And RECOMPILE is never a good idea if the proc is going to be hammered upon. That IS a place were RECOMPILE should not be used because it does, in fact, hurt.
NOT if the queries have dramatically different row counts with supporting indexes. The recompile time is trivial compared to full table scans.
I'll agree that "It Depends". Would you add RECOMPILE to every bit of code that may have such an issue even though it's used 1,000's of time per day? One would hope not.
We once had some code that "only" took 100ms to "run". It took a while to figure out why people were complaining how long it took to run in the GUI. It turns out that the code was recompiling EVERY time it ran and it was taking 2 to 22 seconds with the norm being about 20 seconds. Talk about "scans".
Still not close to the deleterious effect of table scan(s). That causes other data buffers to get flushed out, often causing a cascading effect across many queries. Typically most systems have excess CPU but tight I/O. If you've got any recompile that takes 20 seconds you need to take a longer look at that query.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 3, 2021 at 6:33 pm
I totally agree that tables scans are bad but adding OPTION(RECOMPILE) to every CATCH-ALL query is a really bad thing. And, you state the truly obvious when you say anything that takes 20 seconds to do a recompile needs to be fixed... so does anything that causes a recompile every time it's called.
Using OPTION(RECOMPILE) prophylactically on Catch-All queries that are called even just thousands of times a day without any proof that it's actually needed is a waste of time and resources. If it were called a couple of times per day, then yes... probably ok to use especially if it guarantees something like using Minimal Logging on heavy lifting Import/Export/ETL routines, etc.
It's like telling people that they have to stop at all traffic lights... even the Green ones. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
November 3, 2021 at 8:06 pm
So how do you know this will be run "thousands of times a day"? I wouldn't expect it to called anywhere even close to that many times.
I did not add it to a CATCH-ALL query, I added it a customized, dynamic query that only specifies the columns it needs, without generic comparisons. In general, for those types of queries, it's much safer to recompile than not to.
Again, if you can provide some example of a query that actually 20 seconds to generate a query plan (!), then I'll consider that example more closely.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 3, 2021 at 8:27 pm
You added OPTION (RECOMPILE) to the code you posted for this thread. That's what I'm talking about. The OP said the code was being run thousands of times per day.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 3, 2021 at 9:49 pm
So OP does in their next post.
OK. I might still stick with recompile on this. It's returning only a single int column and the controlling columns (in the WHERE clause) could vary drastically every time. Typically a recompile takes only ms anyway.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 4, 2021 at 8:52 am
Test the given catch-all options ! YMMV ( I'm on the "option (recompile) should be a last choice" side)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 4, 2021 at 12:34 pm
Test the given catch-all options ! YMMV ( I'm on the "option (recompile) should be a last choice" side)
Yeah. Without duplicating some tests of this that I did quite a while ago, I am firmly in the catch-all camp.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 4, 2021 at 2:45 pm
{Thousands of recompiles a day} vs {thousands of extra table scans a day} is an easy choice. I'd have to see actual damage from the recompiles to recommend differently. Partly because, again, most systems have vast reserves of CPU but are relatively (or very) tight on I/O capacity.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 4, 2021 at 3:25 pm
{Thousands of recompiles a day} vs {thousands of extra table scans a day} is an easy choice. I'd have to see actual damage from the recompiles to recommend differently. Partly because, again, most systems have vast reserves of CPU but are relatively (or very) tight on I/O capacity.
Using dynamic SQL properly and assuming tuned indexes, will rarely result in a table scan.
I am not sure why you are stuck on a table scan. Nothing in this thread, with the exception of the original code posted, would indicate that table scans will occur with each execution.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 4, 2021 at 3:49 pm
ScottPletcher wrote:{Thousands of recompiles a day} vs {thousands of extra table scans a day} is an easy choice. I'd have to see actual damage from the recompiles to recommend differently. Partly because, again, most systems have vast reserves of CPU but are relatively (or very) tight on I/O capacity.
Using dynamic SQL properly and assuming tuned indexes, will rarely result in a table scan.
I am not sure why you are stuck on a table scan. Nothing in this thread, with the exception of the original code posted, would indicate that table scans will occur with each execution.
I'm stuck on table scans because they are the biggest danger to performance.
Unless an index is fully covering, SQL is extremely picky about what % of rows it will do lookups for before just reverting to a table scan. The infamous "tipping point." [Besides, the vast majority of people don't have tuned indexes: in most cases, they don't even have the best clustered index, which is a critical starting point for tuning.]
Of course, for just the customer_id, all indexes should be fully covering. So, yes, for now it could be dropped. But the OP stated that "Then I could join the other customer data once [the search results] are 'filtered'". If, in the future, that is combined in the same query that retrieves the customer_ids, then potential table scans could come back in.
In our business, we must strive very hard never to get hit with big performance gotcha's later, live, when a client is doing production work. Much of what we help clients do is extremely time sensitive. We can't have response time go to Hades and then just correct it later. Some people have more flexibility in that area.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply