May 9, 2017 at 12:45 pm
Hi,
I know this is a crazy question, and I haven't been able to find the answer yet, but is it possible to capture and translate a query to SQL server? Here's the problem--we have a third-party application that effectively builds its own queries based on some user input. The problem is that it builds them in an incredibly inefficient way. For example,
I see a lot of this:
SELECT *
FROM some_table
WHERE some_id IN (SELECT DISTINCT some_other_id FROM some_other_table)
I mean, not only is this horrible because the DISTINCT clause is completely unnecessary (and, in fact, adds overhead), but I believe many queries like this one can be easily translated to not use a nested SELECT (and therefore run for every single row, also causing a lot of overhead).
So, I'm just wondering if there's a way to intercept the query and have my translation logic rewrite the query before executing it.
Again, I'm pretty sure this isn't possible, but I thought I'd ask anyway.
Thanks in advance for your help.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
May 9, 2017 at 1:15 pm
I'm not aware of such a thing, but that sounds like basically writing your own optimizer, so I'm not too surprised 🙂
On that note, with this particular query form, the optimizer will be using a semi-join, and is intelligent enough to remove the DISTINCT in that case (for example, see http://sqlinthewild.co.za/index.php/2011/01/18/distincting-an-in-subquery/).
It also won't actually execute this subquery for every row. It'll just be a join between the two tables.
Of course, that's just this particular query form. For other awfully written, automatically generated code, you won't necessarily be so lucky.
Cheers!
May 10, 2017 at 2:53 am
Application generated code can be horrible, unfortunately I can't think of any easy way of doing what you want to do. Even if you did manage it, I'd imagine that you're going to have your work cutout to write something that could spot inefficiencies and optimise them.
I'd love to hear if anyone has manged it though...
https://sqlundercover.wordpress.com/
May 10, 2017 at 6:17 am
I'm pretty sure this is utterly impossible. About the only tool you have at your disposal around stuff like this are plan guides. You can force behaviors through hints or through USE PLAN statements. However, it won't fundamentally rewrite the code in any way, and that's what's needed.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 10, 2017 at 7:11 am
As the DBA, about the only influence you have this situation (poorly constructed SQL coming from an application) is to insure that both [some_table].[some_id] and [some_other_table].[some_other_id] are indexed. If investigation suggests that [some_other_id] is unique, then create a unique index on it, which may shortcut the DISTINCT clause.(Disclaimer: The following feature doesn't actually exist, I'm just fantasizing.)It would be great if SQL Server had something like a resultset cache. Yes, we already have a page buffer cache and a query plan cache, but what I'm talking about is a cache for persisting resultsets that would link to the query plan that initially returned it. So, if an application re-executes and identical query using the same parameters, and the underlying tables havn't changed, then it will retreive the resultset from cache rather than actually executing the query.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 10, 2017 at 9:17 am
Thanks, everyone, for the replies. This wouldn't be a headache for us database developers and admins if apps would just construct their queries to leverage the query engine instead of just barnacling things together in some seemingly logical fashion.....
Mike Scalise, PMP
https://www.michaelscalise.com
May 11, 2017 at 6:11 am
Mike Scalise - Wednesday, May 10, 2017 9:17 AMThanks, everyone, for the replies. This wouldn't be a headache for us database developers and admins if apps would just construct their queries to leverage the query engine instead of just barnacling things together in some seemingly logical fashion.....
But that ORM data access layer cut their delivery time by at least 25%. Doesn't that mean anything to you?
:ermm:
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 12, 2017 at 7:04 am
Select DeveloperName, HitCount
from AppDevelopers
cross apply ufn_BigStick(AppDeveloperID)
GO 20
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply