July 28, 2006 at 11:30 am
Hello,
Having the following query:
SELECT t.idTopic
FROM tblTopic t INNER JOIN @KW f ON t.Topic LIKE '%'+f.keyword+'%'
WHERE @search=1
(@kw being a table variable)
How can I find out whether SQL server first verifies the WHERE condition, or whether it first executes all the LIKE comparisons? Can I see that from the query execution plan? Or do I have to create large amounts of test data and measure?...
Thanks
July 30, 2006 at 8:38 pm
Given that @search=1 seems to have nothing to do with the query other than to determine if it should run at all, why not put it in an IF clause
IF @search=1
BEGIN
SELECT t.idTopic
FROM tblTopic t INNER JOIN @KW f ON t.Topic LIKE '%'+f.keyword+'%'
END
This guarantees that the comparisons only run if the serach clause evaluates correctly.
August 1, 2006 at 2:25 pm
Thanks, Steve, but:
Because this query is only a (simplified) part of a bigger problem used as an example to illustrate my question in this forum which is:
How can I find out how sql server treats a query like this? I'm a beginner concerning execution plans, but I'm not even sure whether an execution plan would show me that kind of information. I'm confident that sql server optimizes the given example in the best way possible, but I have no way of verifying it. If I could do that, I would be able to optimize other (more complicated) queries and in the end write better code.
August 1, 2006 at 3:33 pm
I did consider that it was a simplified query, but went ahead anyway.
Apparently you can tell which part of a query gets executed first from the query plan as I was reading a forum a couple of weeks ago where someone suggested that they had viewed a query plan and determined one event had happened prior to another.
So hoepfully you can find someone who can let you know how. Because I don't - yet.
Good luck
August 1, 2006 at 4:15 pm
The JOIN is usually done before filtering on the WHERE clause. Look at the execution plan and follow the arrows to look at the order. Move your mouse pointer over the icons until you find your WHERE clause (it will most likely be in a 'Filter' icon). If your WHERE appears after the merge/nested loop/ hash match join, there's your answer.
August 2, 2006 at 3:41 am
I figured how to do an execution plan in text:
|--Nested Loops(Inner Join, WHERE: (like([t].[Topic], '%'+[f].[keyword]+'%', NULL)))
. |--Table Scan(OBJECT: (@kw AS [f]))
. |--Filter(WHERE: (STARTUP EXPR(Convert([@search])=1)))
. . |--Clustered Index Scan(OBJECT: ([shp].[dbo].[tblTopic].[PK_tblTopic] AS [t]))
Unlike you said, John, the JOIN seems to be evaluated after the WHERE - at least if I read the plan correctly (I figured it has to be read bottom up, in the direction of the arrows as you indicated).
If I take a look at the graphical execution plan, it gives me something like this:
SELECT 0% <-- Nested Loops/Inner Join 0% <-- Table Scan (@kw) 50%
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .<-- Filter 0% <-- CLustered Index Scan 50%
(The Filter and Clustered Index Scan are on the second line and pointing to the Nested Loops/IJ icon)
Given the direction of the arrows (Table Scan and Filter point to it), I'd interpret it the following way:
The Nested Loop/Inner Join is done between two elements, the table @KW and the filtered table tblTopic, therefore the filter is applied to tbltopic before the join is being evaluated.
Is this interpretation correct?
Thanks!
August 2, 2006 at 8:48 am
Yep, I'd say you got it. In your case, the Query Optimizer Manager determined that it is more efficient to apply the filter prior to the JOIN. This may not be the case for all query execution plans similar to yours. The only way to know for sure is to check. Here's a good article on the Optimizer and JOINs:
http://www.sqlservercentral.com/columnists/RDyess/optimizerjoinmethods.asp
August 2, 2006 at 10:18 am
If there is a WHERE clause, this will be executed first. The whole raison d'etre for the query optimizer is to reduce the amount of work that the database engine has to do, and by throwing away as much irrelevent data as possible before joining two tables together it can go a long way towards achieving this. The WHERE clause is the "filter" that makes this possible.
The structure of the WHERE clause should be in the form of a Search Argument for optimum performance:
column operator expression
e.g. WHERE col1 = 'xyz'
If there is an index on the column col1, this will enable the desired rows to be located with even less effort before they are passed into the join (there's a bit more to index selection than this, but that's basically true).
When reading a graphical showplan, start from the top right, and work left. The table on the top right is the outermost table in the join. The table almost immediately below it will be the inner table.
August 3, 2006 at 2:19 am
Steve, John, Philip, thanks a lot for your help!
I'll now try to verify some of the assumptions I made about how things are to be done best with sql server...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply