September 1, 2009 at 12:02 pm
I have a query where I am trying to exclude a hundred range of numbers
after my where clause:
AND
property.number < 400 AND property.number > 500
This seems to take a long time to run, is there a more efficient way to write this?
September 1, 2009 at 12:10 pm
Performance on that kind of thing will depend largely on your table and index structure, and on how the rest of the query is written. Can you provide the table structure (create statements), including indexes, and the rest of the query?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 1, 2009 at 12:33 pm
AND property.number 500
First off, you shouldn't be getting ANY rows back based on that criteria. Yet another reason we ask to see actual code.
You can rewrite it (properly) a couple of different ways, but Gus is right. If you don't have proper indexes, you wind up doing a table scan. Even if you have an index on property number, if the optimizer thinks a different index is a more efficient based on your other JOIN or WHERE criteria, the property number comparison becomes simply a filter. I don't think either of these will make your job run faster.
AND (propertyNumber 500)
AND propertyNumber NOT BETWEEN 400 and 500
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply