|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Performance - Joins vs FiltersI reckon this is a "how long's a piece of string"-type of question but I'll try it anyway. If you could provide any pointers, even if it is not a direct answer then I'd be really grateful. I've written an app generates SQL. I'm joining many tables and it's stable. However, I now need to enhance it some more and link in another table. I have the option of extending the WHERE clause instead of modifying the joining mechanism in the FROM clause. Extending the WHERE clause means adding a subselect and the way to do this is *far* easier to implement than to rework the joining mechanisms to include an extra table - most especially for Left Joins. So my preference would be just extend the filter but I'm not sure about the impact on performance. Will left joining from the additional table (and extending the filter) be significantly faster than SubSelecting from it and using an IN? Thanks Simon Recently, I developed a data warehouse and in terms of SQL performance tips
etc. I was a complete novice. I did alot of investigation and research into the fastest way to query and I found that subselects in general were a big performance hit. I found by creating intermediate tables that I could join into other queries that performance was greatly enhanced. Of course this is subjective to the scenario and I imagine there are plenty of exceptions, why dont you try both and find out? Show quote "Simon Woods" wrote: > Hi > > I reckon this is a "how long's a piece of string"-type of question but I'll > try it anyway. If you could provide any pointers, even if it is not a direct > answer then I'd be really grateful. > > I've written an app generates SQL. I'm joining many tables and it's stable. > However, I now need to enhance it some more and link in another table. I > have the option of extending the WHERE clause instead of modifying the > joining mechanism in the FROM clause. Extending the WHERE clause means > adding a subselect and the way to do this is *far* easier to implement than > to rework the joining mechanisms to include an extra table - most especially > for Left Joins. > > So my preference would be just extend the filter but I'm not sure about the > impact on performance. Will left joining from the additional table (and > extending the filter) be significantly faster than SubSelecting from it and > using an IN? > > Thanks > > Simon > > > Simon,
Why don't you find out instead of guess? Type the two possible queries you're considering into query analyzer and either compare their execution plans or test them on sample data. If for some reason you can't test the queries you're considering, and want more advice here, you'll have better luck if you post specific queries along with the relevant CREATE TABLE statements and some sample data. Steve Kass Drew University Simon Woods wrote: Show quote >Hi > >I reckon this is a "how long's a piece of string"-type of question but I'll >try it anyway. If you could provide any pointers, even if it is not a direct >answer then I'd be really grateful. > >I've written an app generates SQL. I'm joining many tables and it's stable. >However, I now need to enhance it some more and link in another table. I >have the option of extending the WHERE clause instead of modifying the >joining mechanism in the FROM clause. Extending the WHERE clause means >adding a subselect and the way to do this is *far* easier to implement than >to rework the joining mechanisms to include an extra table - most especially >for Left Joins. > >So my preference would be just extend the filter but I'm not sure about the >impact on performance. Will left joining from the additional table (and >extending the filter) be significantly faster than SubSelecting from it and >using an IN? > >Thanks > >Simon > > > > Hi Simon
can't argue with Alexander and Steve's recommendation of "try both and find out which is better"! However, my experience (10 years) is that subselects are almost always less efficient than joins. I never use them now, and I'm pleasantly surprised again and again at how SQL Server can gobble up the most evil-looking multiple join operations and flip the results back in seconds. The disadvantage of joins as you say is that they can take quite a bit of work to get right. try out the subselect with some (sufficiently large set of) sample data, I reckon. cheers Seb Well, first question in my mind is "do you need any of this data for
output?" If yes, then join, if no, then where clause. If it is too slow, then optimize. If you are just filtering data, then an exists in the where should be faster, it will certainly express what you are trying to do in a more correct manner. And as everyone else has stated, test it out :) -- Show quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arguments are to be avoided: they are always vulgar and often convincing." (Oscar Wilde) "Simon Woods" <simonSPAMMENOT.woods@virginNOTMESPAM.net> wrote in message news:eQ94X$tEGHA.4036@TK2MSFTNGP09.phx.gbl... > Hi > > I reckon this is a "how long's a piece of string"-type of question but > I'll try it anyway. If you could provide any pointers, even if it is not a > direct answer then I'd be really grateful. > > I've written an app generates SQL. I'm joining many tables and it's > stable. However, I now need to enhance it some more and link in another > table. I have the option of extending the WHERE clause instead of > modifying the joining mechanism in the FROM clause. Extending the WHERE > clause means adding a subselect and the way to do this is *far* easier to > implement than to rework the joining mechanisms to include an extra > table - most especially for Left Joins. > > So my preference would be just extend the filter but I'm not sure about > the impact on performance. Will left joining from the additional table > (and extending the filter) be significantly faster than SubSelecting from > it and using an IN? > > Thanks > > Simon > On Fri, 6 Jan 2006 16:40:02 -0000, "Simon Woods"
<simonSPAMMENOT.woods@virginNOTMESPAM.net> wrote: >So my preference would be just extend the filter but I'm not sure about the If you're very lucky, the optimizer will turn out exactly the same>impact on performance. Will left joining from the additional table (and >extending the filter) be significantly faster than SubSelecting from it and >using an IN? code for any of the top three or four ways to code it. Actually, it's pretty common to see that. J.  |
|||||||||||||||||||||||
Other interesting topics