Query performance of "=" vs "<" and ">"

[This is a repost from ase.general]

Suppose you have a large table (>5 million records) containing 15
columns including a numerical string column called ACTIVITY_DATE which
is indexed (nonunique, nonclustered) and a numerical string column
called TIMESTAMP which is indexed (nonunique,nonclustered).
ACTIVITY_DATE contains a date string ('20081211'); TIMESTAMP also
appends a time ('20081211 19:18:03.34923').

Suppose you want to return all events that took place on December 11th
and suppose you had to choose between the following queries:

  - select col1,col2,col3 from TABLE where ACTIVITY_DATE='20081211'
  - select col1,col2,col3 from TABLE where TIMESTAMP>'20081211' and
TIMESTAMP<'20081212'

Is either of these queries 'better' from a Sybase internals point of
view?  Are these queries absolutely equivalent and the ACTIVITY_DATE
column is superfluous as long as TIMESTAMP is there?  Or is an '='
query special, something to be fought for?

Thanks in advance,
subnet
0
dsubnet0
12/12/2008 3:18:13 PM
sybase.ase.performance+tuning 2395 articles. 0 followers. Follow

4 Replies
728 Views

Similar Articles

[PageSpeed] 18

already answered in ase.general.

Please don't post the same question to multiple newsgroups.

"dsubnet0" <dsubnet0@gmail.com> wrote in message 
news:7198e8f3-1339-4ff1-a956-27d507e2b3a6@k1g2000prb.googlegroups.com...
> [This is a repost from ase.general]
>
> Suppose you have a large table (>5 million records) containing 15
> columns including a numerical string column called ACTIVITY_DATE which
> is indexed (nonunique, nonclustered) and a numerical string column
> called TIMESTAMP which is indexed (nonunique,nonclustered).
> ACTIVITY_DATE contains a date string ('20081211'); TIMESTAMP also
> appends a time ('20081211 19:18:03.34923').
>
> Suppose you want to return all events that took place on December 11th
> and suppose you had to choose between the following queries:
>
>  - select col1,col2,col3 from TABLE where ACTIVITY_DATE='20081211'
>  - select col1,col2,col3 from TABLE where TIMESTAMP>'20081211' and
> TIMESTAMP<'20081212'
>
> Is either of these queries 'better' from a Sybase internals point of
> view?  Are these queries absolutely equivalent and the ACTIVITY_DATE
> column is superfluous as long as TIMESTAMP is there?  Or is an '='
> query special, something to be fought for?
>
> Thanks in advance,
> subnet 


0
Sherlock
12/12/2008 5:40:13 PM
On Dec 12, 9:40 am, "Sherlock, Kevin [TeamSybase]"
<kevin.sherl...@teamsybase.com> wrote:
> already answered in ase.general.
>
> Please don't post the same question to multiple newsgroups.
>

There *is* an acceptable way to post the same question to multiple
newsgroups, and that is to have *each* newsgroup of interest on the
"Newsgroup:" line of the message.  I.e., post to multiple newsgroups
with *one* single message.

If the news reader software is working correctly, a response in one
newsgroup will also be posted to each of the other newsgroups.
Everyone in each newsgroup will know if the question has been
answered, there or elsewhere.  If they want to add or correct
anything, their response will be reflected in each newsgroup.

Some questions *are* appropriate in more than one newsgroup,
especially for newbies who aren't even sure which group is most
appropriate  ("RTFM?!  WHICH ONE?!?  I GOT DOZENS HERE!!").

The problem is multiple postings, where a person in group A can not
tell the question was answered in group B.  Folks waste time answering
a question that's already answered.

Folks who read both groups just have to tolerate it!

If a person answering the question thinks it's more appropriate in a
particular newsgroup, he can direct all follow-ups to that newsgroup.
But of course he can't do that for multiple postings.

Cheers :-)
0
ThanksButNo
12/15/2008 10:42:21 PM
"ThanksButNo" <no.no.thanks@gmail.com> wrote in message 
news:6daf4c86-6c11-4193-baf6-a18b02a47347@h20g2000yqn.googlegroups.com...
> The problem is multiple postings, where a person in group A can not
> tell the question was answered in group B.  Folks waste time answering
> a question that's already answered.

right -  so I should have said, don't use multiple postings of the same 
question.  What you describe by putting multiple groups in the header is 
called cross-posting, and you are correct that most newsreaders handle that 
fine and all responses go to all of the crossposted groups.  Multiple 
separate threads in more than one newsgroup make it hard to follow a 
"threaded" discussion.

I'd never bag on a "newbie" of course, just trying to gently remind those 
who "multiple post" of the shortcomings of that.

This is a decent guide to the generally accepted "etiquette" : 
http://www.teamsybase.com/using.html 


0
Sherlock
12/17/2008 2:08:14 AM
On 2008-12-17 13:08:14 +1100, "Sherlock, Kevin [TeamSybase]" 
<ksherlock@tconl.com> said:

> This is a decent guide to the generally accepted "etiquette" :
> http://www.teamsybase.com/using.html

Yes.  I think this issue was worked out long ago.  Stick to the guidelines.

I am against posting the same question in multiple newsgroups, period.

Not bagging newbies either, but newbies need to read the guidelines and 
learn the newsgroups, not just jump in and post anything, wherever they 
think.  Other they remain newbies forever.
-- 
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright � 2008 Software Gems Pty Ltd
--
With the financial meltdown, consolidating many databases into one 
server and managing mixed load is a demand.  Ask people who have been 
doing it for years.

0
Derek
12/17/2008 11:34:26 PM
Reply:

Similar Artilces:

Regular Expression to remove "/", "\", "<", ">" and "="
Can anyone please show me the regular expression to reject a string ("<blue", "right>" etc.) which has the following expression in it: "/", "\", "<", ">" and "="  hi, It may Help u.. it is in Class file u may use this expressin in validation controls also. Regex objReg = new Regex(@"^[^,.?/\~|`;:'<>]*$", RegexOptions.Singleline); Regex objReg = new Regex(@"^[^,][^.][^?][^/][^\][^~|][^`][^;][^:][^'][[^<][^>]$", RegexOptions.IgnoreCase);Thanks &...

.ALLCOL("%COLUMN%", " ", ", ", ", ")
Do you know anyway for me to exclude a subset of columns returned by this function. We have two columns (rec_user and rec_datetime) which are in all of our tables, but when generating triggers I want automatically generate a script which does not include those two columns but does include all other columns in that table. Bruce I should add that I am using PD 9.0.0.580. Bruce "Bruce Lamb" <lamb.bruce@mayo.edu> wrote in message news:6HgI315nCHA.155@forums.sybase.com... > Do you know anyway for me to exclude a subset of columns returned by this > function. ...

Precedence of "where" ("of", "is", "will")?
Nobody on #perl6 today could answer this one. Is: Str | Int where { $_ } the same as: (Str | Int) where { $_ } or: Str | (Int where { $_ }) ? Followup questions, Mr. President: What kind of operators are "where", "of", "is", and "will"? Is there a reason that S03 doesn't list them? What are their precedence(s)? -- Chip Salzenberg - a.k.a. - <chip@pobox.com> Open Source is not an excuse to write fun code then leave the actual work to others. Chip Salzenberg writes: &...

"<->" as "->" with automatic "is rw"
I like that arguments will be readonly by default. But when I look at my current code, I see that I would be typing " is rw" quite a lot, which in my opinion is too long for a thing that occurs very often. Every such situation in my code is a foreach loop. A thing that in Perl 6 will mostly be used with the pointy sub declaration syntax. If I'm not mistaken, <-> is still available. It communicates "bidirectional" and that is more or less the same as read/write access. I'm proposing for zip(@foos, @bars, @xyzzies) <-> $foo, $bar, $xyzz...

quotes, quotes, quotes...
I am getting this error and I know what is causing it, but I have no idea how to fix it, any help would be great. The script steps through the /var/log/messages file on a linux server and puts The entries into a mysql database. However when it gets to the 'hlt' line in the messages file it just barfs. The single quotes are freaking it out. I know about quotes but not how to use in this situation. Thanks, Paul Error: May 27 17:53:00 localhost kernel: Checking 'hlt' instruction... OK. <----- doesn't like this in the messages file DBD::mysql::st exec...

What is this for? "<label style=""display:none;"" for=""" & cboFieldType.ClientID.ToString & """>Type</label>"
I'm digging through the code of DNN and I found many ocurrences of this type of code: lblFieldType.Text = "<label style=""display:none;"" for=""" & cboFieldType.ClientID.ToString & """>Type</label>" What is that code for? The label is never displayed and I could not figure out what DDN uses it for. thank you This is for ADA compliance. The guidelines require that form elements have an associated label. This helps the text readers out with identifying the different parts of the form. BruceDynamic...

what's the different from "<page src="">" and "<page codeBehind="">"
what's the different from "<page src="">" and "<page codeBehind="">" Thank YOU how to make session' life longer? Thank You This very question was asked only 5 days ago. Searching the forums would have yielded the answer you require. However, see this thread. Steven BeyRecursion: see Recursion You can set a new value for the Session Timeout in your web.config file. Or, you can do it in a page through this property: Session.Timeout Note that if you choose the second method, the timeout will only be modified on this page,...

Replacing "\\" with "\"
Hi all I'm getting this value from a CheckBoxList control - a location of file, i have to remove "\\" and replace it with "\" and pass it to Query, how to do it, i tried with Replace, but coud'nt suceed. "\\\\Blaze10xp\\BLZ_SFS_07\\Sample Excel Files\\Excel Files\\report2.xls" thank's in advance - Prakash.C you tried Replace like this? string newstring = oldstring.Replace(@"\\",@"\");Plese, do not forget to click "Mark as Answer" on the post that helped you. Thanx!My blog: Scenes From A Developer Memory yes i tr...

Using "+" or "||"
Using SQLAnywhere 5.5.04, I've gotten into the habit of using "||" in ISQL to indicate a string concatenation. I needed to paste my SQL statement into the PowerBuilder script painter for some embedded SQL, and PB didn't like the "||" very much at all. I changed it to "+" and it seems to be ok. Do these two operators indicate ~exactly~ the same thing? moin, afaik these two's are not the same! if you're using "||" and any term is NULL then in the resultstring the term will be ignored if you use "+" then the resu...

replace the "." with a ","
Oi.... I need to build a small programm in ASP.NET and chose to use C# for it.Now i got everything working but there's one little problem.the first textbox is a double. I need to make it so that when someone enters a "." then it gets replaced by a ","any ideas?Ghan  string blah = "4.2.2.2";blah = blah.Replace(".", ",");Ryan Ryan OlshanASPInsider | Microsoft MVP, ASP.NEThttp://ryanolshan.comHow to ask a question...

"Me" is better than "You"
Yes I know, strings are frozen. But let me talk about it, I really can't get through the idea of a PC talkin to me. I consider my PC as an extension of myself, not a dumb companion who addresses Me as You. Yes there are times when I get angry with Him while I work and get wrong calculations etc.., but it really is my fault, Me using wrong istructions and eventually wanting to find someone else to blame, but it's Me. And yes, I consider Thunderbird my mail program, reading my mail on my PC as Me. So I personally like to have Me in the header bar as a compact address ...

"To" and "From" missing
When I print emails, the words "To" and "From" are blank, even though the "To" name and "From name (addresser, addressee) do show up. This is not a problem for other users on my system. Suggestions In mailbox right click, view. On the message window, right click and choose print options. Make sure print header is checked. -- Barry Merchant NSC Volunteer SysOp *** no email unless requested please!! *** > In mailbox right click, view. On the message window, right click and > choose print options. Make sure prin...

"-" not "_"
I wrote a SQL statement in the data tab. I wrote a bunch of alaises as example ' word-type ' but when I hit the layout tab it converts the "-" to "_". So now my field name is ' word_type '. Is there any way to prevent this? CardGunner Don' use a hypen ( - ).  It isn't a valid character for column names.   See http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1188931,00.html   Here's an excerpt about column names: Letters as defined in the Unicode Standard 2.0 Decimal numbers from either B...

Web resources about - Query performance of "=" vs "<" and ">" - sybase.ase.performance+tuning

Resources last updated: 1/4/2016 10:04:19 PM