| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12
Comments:
<0> its not a join condition per se
<0> whether it 'works' or not is irrelevant
<0> since that condition seeks to restrict values in the resultset, the proper place for it is in the WHERE clause
<1> dfworking : it's a technique -- if you wait 'til the where clause to filter the results, it takes more processing power. Using explicit values in joins is more efficient.
<0> i have never heard of such a thing
<1> it's taught at my last place of business
<0> doesn't make it untrue, i just have never heard that its an 'efficiency' technique
<0> that also doesn't make it true
<0> TorBeest, what do you think of that
<1> you're right... but it makes sense to me
<1> every time you run a join, you'll be joining a larger set
<1> it can be exponential in difference in CPU power
<0> have you actually compared the execution plans?
<1> no - I and those who taught it to me are speaking purely from logic
<0> i question whether the query optimizer just arbitrarily joins **** before including the where conditions
<0> that makes no sense
<0> well, my gut reaction is their logic is sound but its bull**** in the real world
<0> doesn't mean anything if the query is optimized according to a different logic
<1> in order to check the constraints of a join it must compare it to the set of qualifying records up to that given point
<2> sec, I'll check the query
<1> if you narrow the qualified set earlier it has less to compare in every other join after it
<0> i don't disagree with the logic
<0> i question whether thats how it actually comes down
<1> yeah - I've never tested it in practice
<1> just got in the habit of following the logic
<0> the query optimizer may take what you have and automatically translate and treat it like a where condition
<1> perhaps, yeah
<0> anyway, i don't know, but i remain unconvinced either way. so i'll just go back to porn
<1> it's a good theoretical argument :-) but what about the error re: distinct?
<0> distinct/order by doesn't make much sense to me from an error standpoint
<0> are you saying the error goes away with order by?
<0> adding it
<1> haven't tried taking the order by out - one sec
<1> hmmm - that did it :-P
<1> hehe
<1> thanks
<2> well I've always read to put the conditions that eliminate the most records, first (as a general rule)
<1> dunno why the order by would cause issues with the distinct, but oh well
<1> yay - 1 point for me
<2> if that is true, I'm with this as well
<1> hehe
<1> j/k
<0> i just never thought it good practice to 'hijack' join conditions with var values
<2> isn't there a kind of "WITH NO OPTIMIZE" or something?
<2> so you're saying "don't optimize, I know best"
<0> but as i said i agree with the logic
<2> dfw, say you're using a subselect, which has that "where", and you're joining with that
<1> yeah, I know - I'm just teasing
<2> then you're doing the same thing right?
<1> aw snap - my query works and it's hella fast... thanks for the advice on the order by
<0> TorBeest, i suppose.
<0> its not like we're talking about HAVING, which by definition is used AFTER everything else is grouped
<0> anyway
<1> to each his own... I got used to this style and I find it good programming practice. It might rub other people raw
<2> Eeker, what about putting the variable comparison first?
<2> and then the join condition?
<2> if the variable comparison eliminates more, of course
<1> yeah, that would be even better huh?
<1> :-)
<2> supposably
<2> installing is always fun init...
<0> i'll test the two ways right now on a 1gb db i have
<0> sadly, it would only work if the query is always expecting the value, whereas in the where clause you could make it optional
<0> regardless, i'll let you know in a few minutes
<2> would it be better to make sure the optimizer knows the type of the variable, by applying a temp value, in case you are accepting a value through a parameter?
<2> but do let me know...
<2> and test the sequence of clauses too if you can :)
<2> and why can't you make it optional in the join condition btw? like with "case when null" and make a 1=1 when it is...
<0> i don't think the syntax will support that last example you gave
<0> i'm currently just trying to find some queries that don't execute in under a second
<0> well, based on these simplistic tests, from what i see through client statistics suggests that the optimizer treats them almost identically
<0> there is no significant reduction in the number of rows affected by select statements between the two
<0> which you would expect with the join = @var approach
<2> maybe it changes when dealing with more data
<0> namely, that it would reduce the number of rows in consideration
<0> maybe, i'll open it up to a category level, sec
<2> and I'll see if I get the syntax working I'm talking about
<0> yeah, it still isn't significant either way, so i think its safe, based on these overly simplified tests, that Eeker can continue with his way and i'll continue with mine and TorBeest can continue with the gay porn
<0> i would have expected to see completely different numbers in client statistics
<0> i'll paste what i was working with
<2> you don't want to know what my irc nickname highlight colour is...
<0> flaming pink?
<2> and then some...
<0> i even had unnecessary joins in there to try to slow it down
<2> maybe we can try it on an online testing database
<2> I've seen websites that host those
<0> what you see here is about two million rows of data across the various levels, the ***ettrans level itself is over a million
<2> to test queries
<0> http://www.coldfugitive.com/paste/?post=1868
<0> the stats are a bit hard to read, but you'll see they're almost identical
<2> and does the clause make a big difference?
<0> how do you mean
<2> I mean, did you make sure that "13" made a big difference result-wise
<0> heh, oh YES.
<2> ok
<0> otherwise, it would have tried to return a few thousand rows
<0> AFTER the distinct
<0> without the distinct, something like 45000 rows
<2> it's weird...
<2> this ****s... or we're forgetting something
<0> anyway, its not enough either way to change ways imo
<0> as i said, it could very well be that the optimizer treats them similarly
<2> well the "old" way is more readable
<0> indeed
<2> yay my test works
<0> which means
<2> http://www.coldfugitive.com/paste/?post=1869
<0> yikes
<2> that's what I was talking about...
<0> you and Eeker used to work at that place together i take it
<2> ugly, sure, but it compiles...
<2> I'm still not convinced for some reason, about the clause sequence...
<0> i think that if i was given some indication that it was substantially different/faster, that i would pursue it
<0> but its not, in these conditions
<2> it's been something I've not been sure about for quite some time, it's about time for clearance...
<2> uhm, clarity
<0> let me know what you find
<2> I will
<2> I can post it as an article on sqlwhores.com then ;)
<0> whats that site
<2> #sql undernet website
<0> ah
<2> the whores are channel members
<0> sounds like a lovely crowd
<2> (that goes both ways, pun intended)
<0> i have a row of data that has several aggregates, is there a technique for getting the column value for the highest and lowest of all columns
<0> short of pivoting
<2> using a temp table, which is kind of a copy-pivot
<2> probably slow for large amounts of data...
<0> ok
<2> then again, so would pivoting
<2> can't think of any smooth way, no
<1> when a uniqueidentifier is written to a string will it necessarily be 36 characters?
<2> how do you gather that?
<2> I mean why do you say 36...
<1> TorBeest : I just looked at the table and noticed that all the UserId's so far have been 36 char strings
<1> as strings that is
<3> UNIQUEIDENTIFIER is always the same size
<2> oh, right, I misread, it's indeed always that long
<4> Its a GUID, isn't it?
<2> didn't see you were talking about GUIDs
<5> yes
<2> right :)
<2> 16 bytes, always
<6> I really think SQL is going to be the death of me. :P
<6> does anyone have any suggestions for replacing the subselect in this query with something else? I'm trying to POD.POSTDE if it contains a "B", otherwise I don't care what gets returned. http://sql-servers.com/nopaste/?show=322
<6> the rest of the query works just fine, but the subselect really slows it down
<2> do you have an index on POD.POSTDE?
<6> nope, and I can't add one
<2> and use * with exists
<2> how many rows are there in OPENPOD?
Return to
#sql
or
Go to some related
logs:
getting to cpanel at crikey hosting
site:www.quotesdb.info darkgirl efnet
sinnistar torrents
#gentoo
solar eneerge
#cph
#red
#linux-noob
#windows
#goal
|
|