Asp Forum - Need help optimizing a batch query - microsoft.public.sqlserver.programming

0to60

3/8/2007 7:53:00 PM

I have a sproc that will return several tables, and it looks something like
this:

SELECT * FROM TABLEA WHERE somelongasswhereclause

SELECT * FROM TABLEB WHERE TableBID IN (SELECT foreignKeyToTableB FROM
TABLEA WHERE somelongasswhereclause)

SELECT * FROM TABLEC WHERE TableBID IN (SELECT foreignKeyToTableC FROM
TABLEA WHERE somelongasswhereclause)

So what I'm trying to do here is get a buncha records from TableA, and then
get a buncha records from TableB that relate to TableA and so on. I could
just do a JOIN, but I really would like the data to be in separate tables.
My problem is my somelongasswhereclause. Its a pretty long WHERE clause
with lots of conditions, and it seems to me that having to evaluate them
over and over is a waste. What options do I have here? I was thinking of
opening a cursor on TABLEA and creating an IN clause of the TABLEA IDs, so
that in the following queries I would never have to say "...FROM TABLEA
WHERE somelongasswhereclause" again. I could just say "...FROM TABLEA WHERE
tableAID IN (...)", but I still need to return TABLEA and how would I do
that if I made a cursor out of it?

2 Answers

Roy Harvey

3/8/2007 8:12:00 PM

One question is whether somelongasswhereclause returns
somelongassresultset, or notsobigresultset. If it is the later you
could save it in a temp table and then use the temp table for the IN
clause subqueries.

However if it does return somelongassresultset the lookups into the
temp table might easily be too costly. In that case you might as well
just keep doing what you posted. I would suggest considering creating
a view from that first query and then using the view in the subsequent
subqueries if only so that there is only one place where that
complicated WHERE clause is written.

Roy Harvey
Beacon Falls, CT

On Thu, 8 Mar 2007 13:52:45 -0600, "0to60"
<holeshot60_nospam@yahoo.com> wrote:

>I have a sproc that will return several tables, and it looks something like
>this:
>
>SELECT * FROM TABLEA WHERE somelongasswhereclause
>
>SELECT * FROM TABLEB WHERE TableBID IN (SELECT foreignKeyToTableB FROM
>TABLEA WHERE somelongasswhereclause)
>
>SELECT * FROM TABLEC WHERE TableBID IN (SELECT foreignKeyToTableC FROM
>TABLEA WHERE somelongasswhereclause)
>
>
>So what I'm trying to do here is get a buncha records from TableA, and then
>get a buncha records from TableB that relate to TableA and so on. I could
>just do a JOIN, but I really would like the data to be in separate tables.
>My problem is my somelongasswhereclause. Its a pretty long WHERE clause
>with lots of conditions, and it seems to me that having to evaluate them
>over and over is a waste. What options do I have here? I was thinking of
>opening a cursor on TABLEA and creating an IN clause of the TABLEA IDs, so
>that in the following queries I would never have to say "...FROM TABLEA
>WHERE somelongasswhereclause" again. I could just say "...FROM TABLEA WHERE
>tableAID IN (...)", but I still need to return TABLEA and how would I do
>that if I made a cursor out of it?

Evolution

5/20/2009 2:18:00 PM

rawbylaw wrote:
> On May 20, 1:14 am, Evolution <myn...@rcn.com> wrote:
>> rawbylaw wrote:
>>> On May 19, 4:06 pm, Evolution <myn...@rcn.com> wrote:
>>>> rawbylaw wrote:
>>>>> On May 18, 2:02 am, Evolution <myn...@rcn.com> wrote:
>>>>>> rawbylawwrote:
>>>>>>> On May 16, 9:21 pm, Evolution <myn...@rcn.com> wrote:
>>>>>>>> rawbylawwrote:
>>>>>>>>> On May 15, 5:43 pm, Evolution <myn...@rcn.com> wrote:
>>>>>>>>>> stuthalb...@comcast.net wrote:
>>>>>>>>>>> On May 15, 10:44 am, Evolution <myn...@rcn.com> wrote:
>>>>>>>>>>>>> Virtually EVERY woman who's led a government has led it into war, I
>>>>>>>>>>>>> suspect largely because they believe they need to show bigger
>>>>>>>>>>>>> testicles than men.
>>>>>>> No, it's probably because men control the world and the only way a
>>>>>>> woman
>>>>>>> could get in that position in the past is by playing the male game.
>>>>>>> > or be born to it like a Queen who led their country's in war such
>>>>>>> as
>>>>>>> > Catherine the Great, Elizabeth I, Isabella of Spain, the Rani of
>>>>>>> > Jhansi, Queen Jinga of Angola, and Queen Boadicea. But, of
>>>>>>> course,
>>>>>>> > they were all manipulated by thier male subjects. Not.
>>>>>>> Remind me which *started* wars.
>>>>>>> -------------------------------------------------------
>>>>>>> errr, all of them. Isabella had a co-conspirator in Ferdinand but,
>>>>>>> yes, all of them *started* large scale violent conflicts resulting in
>>>>>>> mass death and destruction.
>>>>> - You're going to have to prove this. Elizabeth I didn't start any
>>>>> wars.
>>>>> - She defended Britain against Spain.
>>>>> Wrong. The Anglo-Spanish war began with Elizabeth sending her army
>>>>> into the Netherlands.
>>>>> One might say, as you did, that this was a defensive move, since
>>>>> England was only attacking the Netherlands
>>>>> before the Spanish used it as a base to attack England, which, of
>>>>> course, then puts you in agreement with Bush in using the same
>>>>> reasoning to invade Iraq.
>>>>> - Queen Nzinga fought the Portuguese from taking over her country and
>>>>> - taking slaves. Hardly an aggressive war.
>>>>> -----------------
>>>>> Yes, Queen Nzinga fought fiercely towards that effort. However, she
>>>>> it noteworthy for her willfully employing the tactics of infacticide
>>>>> and cannibalism. You know, one woman's terriorism is another woman's
>>>>> lunch.
>>>>> After dining on one her vanquished she reportedly remarked, 'He's not
>>>>> half the man I am, or half the man he used to be, either'.
>>>>> - I didn't look up the others, but again, show me a woman who started
>>>>> a
>>>>> - war other than in defense.
>>>>> ----------------------
>>>>> I'll give you one, you can look up the others. Catherine the Great
>>>>> reign was one of expansionist invasion into central europe extending
>>>>> Russia's borders to the Black Sea. Her conquests in the Russo-Turkish
>>>>> war against the Ottoman empire included some of most barbaric bloody
>>>>> battles, as well civilian slaughter, in that period of history.
>>> - Ah, so the only example you can come up with is one woman in the
>>> 1700s,
>>> - an age of worldwide empire expansion... and a royal to boot. One
>>> might
>>> - question whether royalty are raised in a normal environment with
>>> normal
>>> - values. But I'll give you the one. Out of dozens. An exception to
>>> the
>>> - rule, and one can always find that.
>>> -
>>> i see you didn't look up the others mentioned.
> - I looked up two, and neither had started any war, so it's up to you
> to
> - prove your point. You only came up with one. I'm not going to do
> your
> - research for you.
> -------------------------
> Aethelflaed, Lordess of the Mercians.She united Mercia, invades and
> conquered Wales, and subdued the Danes becoming the de facto ruler of
> the Mercians and Danes.

Okay, you're really struggling here, aren't you? ;)
>
>
>
>>
>>
>>
>>
>>> > I would never under estimate your ideological tendicies, even if
>>> > you're only half-serious but, this is a very dangerous notion, that
>>> > women are incapable of starting war, for two reasons. First, any
>>> > country/faction that would believe such nonsense, would put itself
>>> at
>>> > to fatal naive disadvantage against any potential rival led by a
>>> > woman.
>>> - Nonsense. You might have a point if I were claiming that women
>>> wouldn't
>>> - go to war if attacked, but we are talking about wars of aggression.
>>>> And, second, and more importantly, it's an exclusionary
>>> > notion, one that views women as non-human.
>>> - What the hell? Are you saying it's human nature to want to take
>>> over
>>> - other countries? Nonsense. Humans lived sustainably in their
>>> - environment for 100s of thousands of years, and have been grabbing
>>> land
>>> - and living unsustainably only for the last 10,000 years. The few
>>> humans
>>> - left living in tribal societies don't invade and occupy other
>>> people's
>>> - lands.
>>> hey, what's 10,000 years between friends. and, as far as living
>>> sustainably in our
>>> environment before that, ha! we fooked over the neaderthals to
>>> extinction 30,000 years ago
> - That's one theory. Most common are gradual climate change or
> interbreeding.
> - Given the nomadic existence of early humans, it's unlikely that
> genocide
> - occurred.
>
> Actually, after being hunted and killed elsewhere in europe by our
> ancestors, the last of the neanderthals
> were clustered on the iberian penisula, when it was that a severe
> rapid freezing, not a gradual change, leading to their extinction.

That's one theory. Not a likely one. It's very unlikely that humans
would hunt other humans to extinction; it just doesn't fit the profile
of a nomadic, tribal people. Species go extinct all the time. In the
case of Neanderthals, our ancestors weren't much different and some put
them as a sub-species, and claim that they interbred. Show me a tribe
of humans before the agricultural revolution or existing today which
wipe out other tribes, much less a whole race or sub-species.

Most likely, the Neanderthals were a race which, much like the later
Celts, gradually moved north in times of good weather, and unlike the
Celts, couldn't survive when the cycle turned colder. The race or
sub-species which remained in warmer climates did thrive, and in later
times again moved north.
>
>
> > Is it human nature to want to take over other countries? as the
> song
> > goes, poor gal wanna be rich, rich gal wanna be queen, and a queen
> > ain't satisfied........ever. But, seriously, i'd like to believe
> it
> > is not our nature but, tens of thousands of years prove it in the
> > range of our behaviour as a species, which includes females and
> > males, alike.
>
> - No, kings and hierarchical societies arose only about 10,000 years
> ago.
> - Before that, humans lived in tribal societies where there were no
> - possessions, no owning land. This coincided with the "agricultural
> - revolution" which for the first time, enabled humans to accumulate
> - surplus and gain power. We all know power corrupts. Humans then
> began
> - living unsustainably in their environment and the age of kings and
> - slaves began. Humans existed for hundreds of thousands of years in
> - tribal, sustainable societies, and the greed is a recent thing in
> the
> -last 10,000 years. It is definitely NOT in our nature, but a
> cultural
> - thing. As greed spread, more and more tribes were absorbed into
> this
> -culture, like a cancer growing. The salvationist religions also
> sprang
> - up around this time to help humans deal with the misery the
> hierarchical
> - society begat. The biblical story of Adam and Eve is based on the
> true
> - story of the tribe which left "paradise" (living sustainably) and
> tasted
> - the forbidden fruit, which gave them the "knowledge of good and
> evil"
> - which relates to how humans began to live unsustainably. Instead of
> - observing the laws of nature, we began to change our environment and
> - decide which animals and plants would live (our food) and which
> would
> - die (wiping out animals and plants which weren't considered food).
> - Previously, the laws of nature were left to "god" who decided
> whether
> - the lion would kill the deer or whether the deer would escape and
> the
> - lion go hungry... the laws of good and evil. After Adam and Eve
> gained
> - this knowledge, they began deciding that the lion should die out and
> the
> - deer become exclusively their food, that some trees should die so
> that
> - crops could be grown... thereby upsetting the balance of nature.
> -
> - And 7 billion people later, we are heading towards the fate of
> *every*
> - species which lives unsustainably. The dinosaurs lasted millions of
> - years, but humans barely made 10,000 once they stopped living
> sustainably.
> ---------------------
> Fug! one lousy ape catches on how to cultivate the stuff growing out
> of buried poop and you
> pin the whole fookin end of the world on the poor fucker. typical.

No, farming had been done for thousands of years. Subsistence farming,
slash and burn farming, such as is done in the Amazon by tribes today.

The difference came when a tribe of humans developed greed and started
full time farming, producing surpluses, which gave them power through
trade and emergency times. Land for the first time became a commodity
to own and buy and sell (or take by force). This revolution made
specialization possible, money exchange, bartering, crafts, art,
civilization, etc. And also kings and slaves and misery, theft, murder,
war (as we know it today), genocide, rape, suicide, etc. And, most
importantly, living unsustainably in our environment... which is the
eventual death of a species. We are no exception. 12,000 years ago,
the population was a million people in the world, and then 6000 more to
get to 7 million, 4000 to get to 170 million, 1000 to double that to 254
million, 500 more to double that, 300 to double that, 100 to double
that, 45 to double that to 4 billion in 1975, and in 30 more, we've
nearly doubled again. In another 30 years, in our lifetime, although
our growth rate has slowed, we will likely have 3 billion more people on
this planet. If we last that long.

And yes, the agricultural revolution made this possible.

>
> -jim
>>
>>> -jim
>> --
>> Laurie
>>
>> http://lauriehester.blog... Hide quoted text -
>>
>> - Show quoted text -- Hide quoted text -
>>
>> - Show quoted text -
>

--
Laurie

http://lauriehester.blo...

microsoft.public.sqlserver.programming

Need help optimizing a batch query

0to60

Roy Harvey

Evolution

x Login to ForumsZone