Date extraction
in [Visual Basic]
|
Prev: Debug VB OCX and DLL
Next: COM Error 80110428 occurred
From: Tony Proctor on 30 May 2007 15:05 I would hope that SQL query compilation would generate a more optimal execution plan than one involving two independent comparisons Rob. Unless the query is extremely convoluted, it's no longer rocket science to perform this type of optimisation when compiling languages. Tony Proctor "Robert Morley" <rmorley(a)magma.ca.N0.Freak1n.sparn> wrote in message news:OLntcvuoHHA.4512(a)TK2MSFTNGP03.phx.gbl... > You'd want to check with someone who's more "up" on the internals of modern > database optimization, but I suspect that a database might well be able to > better optimize a BETWEEN clause than it would using the "normal" relational > operators. > > Personally, I've always felt that BETWEEN was missing as a relational > operator in most languages, simply because you're specifying the same > variable twice for what is conceptually one comparison ("is x in this > range?"), even if it might well get boiled down to two distinct comparisons > at the low level. It's kind of like using an IN() or the simple Select Case > operator...the variable gets specified once ("is x in this set?"), even > though the number of comparisons may be greater. > > > Rob > > "Tony Proctor" <tony_proctor(a)aimtechnology_NoMoreSPAM_.com> wrote in message > news:Of2OpkuoHHA.208(a)TK2MSFTNGP05.phx.gbl... > > So what's wrong with using normal relational operators instead of BETWEEN, > > e.g. > > > > SELECT * FROM MyTable WHERE MyDate >= '2007-05-28' AND MyDate < > > '2007-05-31' > > > > Note the use of '<' against the end date > > > > Tony Proctor > > > > "Jeff Johnson" <i.get(a)enough.spam> wrote in message > > news:uNi8oEuoHHA.2596(a)TK2MSFTNGP06.phx.gbl... > >> "Bob Butler" <noway(a)nospam.ever> wrote in message > >> news:OkVKy7soHHA.3944(a)TK2MSFTNGP02.phx.gbl... > >> > >> >> assuming that no entries for '2007-05-31 00:00:00' exist? > >> > > >> > Well, there's one reason > >> > >> Exactly. And I KNEW I was going to get that reply, too. I knew it! > >> > >> 99.999999999999999999999999999999999999999% <> 100% > >> > >> Ever. Sometimes things really are black and white. > >> > >> > > > > > >
From: Robert Morley on 30 May 2007 15:24 I was actually thinking of things like VB, where at the machine language level, I believe this would compile as two separate comparisons, with JGE and JL or whatever the proper instructions are (my 80x86 assembly language is more than a little rusty). In terms of databases, they're using entirely different approaches based on indexing and so forth, which I don't pretend to be up-to-date on, which is why I mentioned that I *thought* the BETWEEN operator might be more optimized in a database than the two individual comparisons would be using normal relational operators. Rob "Tony Proctor" <tony_proctor(a)aimtechnology_NoMoreSPAM_.com> wrote in message news:%23iDqD2uoHHA.716(a)TK2MSFTNGP05.phx.gbl... >I would hope that SQL query compilation would generate a more optimal > execution plan than one involving two independent comparisons Rob. Unless > the query is extremely convoluted, it's no longer rocket science to > perform > this type of optimisation when compiling languages. > > Tony Proctor > > "Robert Morley" <rmorley(a)magma.ca.N0.Freak1n.sparn> wrote in message > news:OLntcvuoHHA.4512(a)TK2MSFTNGP03.phx.gbl... >> You'd want to check with someone who's more "up" on the internals of > modern >> database optimization, but I suspect that a database might well be able >> to >> better optimize a BETWEEN clause than it would using the "normal" > relational >> operators. >> >> Personally, I've always felt that BETWEEN was missing as a relational >> operator in most languages, simply because you're specifying the same >> variable twice for what is conceptually one comparison ("is x in this >> range?"), even if it might well get boiled down to two distinct > comparisons >> at the low level. It's kind of like using an IN() or the simple Select > Case >> operator...the variable gets specified once ("is x in this set?"), even >> though the number of comparisons may be greater. >> >> >> Rob >> >> "Tony Proctor" <tony_proctor(a)aimtechnology_NoMoreSPAM_.com> wrote in > message >> news:Of2OpkuoHHA.208(a)TK2MSFTNGP05.phx.gbl... >> > So what's wrong with using normal relational operators instead of > BETWEEN, >> > e.g. >> > >> > SELECT * FROM MyTable WHERE MyDate >= '2007-05-28' AND MyDate < >> > '2007-05-31' >> > >> > Note the use of '<' against the end date >> > >> > Tony Proctor >> > >> > "Jeff Johnson" <i.get(a)enough.spam> wrote in message >> > news:uNi8oEuoHHA.2596(a)TK2MSFTNGP06.phx.gbl... >> >> "Bob Butler" <noway(a)nospam.ever> wrote in message >> >> news:OkVKy7soHHA.3944(a)TK2MSFTNGP02.phx.gbl... >> >> >> >> >> assuming that no entries for '2007-05-31 00:00:00' exist? >> >> > >> >> > Well, there's one reason >> >> >> >> Exactly. And I KNEW I was going to get that reply, too. I knew it! >> >> >> >> 99.999999999999999999999999999999999999999% <> 100% >> >> >> >> Ever. Sometimes things really are black and white. >> >> >> >> >> > >> > >> >> > >
From: Jeff Johnson on 30 May 2007 15:33 "Tony Proctor" <tony_proctor(a)aimtechnology_NoMoreSPAM_.com> wrote in message news:Of2OpkuoHHA.208(a)TK2MSFTNGP05.phx.gbl... > So what's wrong with using normal relational operators instead of BETWEEN, > e.g. > > SELECT * FROM MyTable WHERE MyDate >= '2007-05-28' AND MyDate < > '2007-05-31' > > Note the use of '<' against the end date Nothing at all. I was simply remarking that to get an accurate answer you're forced to use this type of syntax instead of BETWEEN. I happen to think BETWEEN looks a lot nicer.
From: David Kerber on 30 May 2007 15:37 In article <uUrHfBvoHHA.4692(a)TK2MSFTNGP05.phx.gbl>, rmorley(a)magma.ca.N0.Freak1n.sparn says... > I was actually thinking of things like VB, where at the machine language > level, I believe this would compile as two separate comparisons, with JGE > and JL or whatever the proper instructions are (my 80x86 assembly language > is more than a little rusty). > > In terms of databases, they're using entirely different approaches based on > indexing and so forth, which I don't pretend to be up-to-date on, which is > why I mentioned that I *thought* the BETWEEN operator might be more > optimized in a database than the two individual comparisons would be using > normal relational operators. Most likely, they would optimize to very similar code, the only difference being that BETWEEN includes both endpoints. From what I've read about database query optimization (not a lot, but some), AND operators optimize fairly well with indexed searches, but OR operators do not, and often cause table scans, which really hurt query performance. d -- Remove the ns_ from if replying by e-mail (but keep posts in the newsgroups if possible).
From: Robert Morley on 30 May 2007 18:44
Ah, I see your point now...I haven't been following the whole thread. :) Rob "Jeff Johnson" <i.get(a)enough.spam> wrote in message news:uySdVEvoHHA.3880(a)TK2MSFTNGP04.phx.gbl... > "Tony Proctor" <tony_proctor(a)aimtechnology_NoMoreSPAM_.com> wrote in > message news:Of2OpkuoHHA.208(a)TK2MSFTNGP05.phx.gbl... > >> So what's wrong with using normal relational operators instead of >> BETWEEN, >> e.g. >> >> SELECT * FROM MyTable WHERE MyDate >= '2007-05-28' AND MyDate < >> '2007-05-31' >> >> Note the use of '<' against the end date > > Nothing at all. I was simply remarking that to get an accurate answer > you're forced to use this type of syntax instead of BETWEEN. I happen to > think BETWEEN looks a lot nicer. > |