Looping through multiple table result set

[syukna]

Hi,

I have a stored procedure that gets data to populate a grid. Due to new business logic I need to now look at all of the data for each row in the grid. Each row item has about 20 tables associated with it. I'm wondering if there is a way to essentially loop through a dataset but using a stored procedure instead.

Something similar to this is what I would be doing.

//get the data for the grid.
Select myID, myName from Table1

//now I would exec a stored procedure to get each individual items info
exec spGetAllMyInfo myID

//I would then need to loop through every table in that result set to see if a field contains a specific word

Maybe there is a better way to do this, but any help is greatly appreciated.

 

[gmmastros]

You could do that, but it would be a bad idea.

Can you show us the code for spGetAllMyInfo. There is probably a better way to do what you want. Looping through a recordset is NOT the best way.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[syukna]

Hi thanks for the quick response.

Unfortunately the application was coded in a way where it would be extremly time consuming to change the spGetAllMyInfo procedure. I agree with you that there probably is a better way, but I can only work with what they gave me.

I can't actually post the procedure because of security, but I'll try to describe it.

The main table is general info with a few joined tables.
There are about 8 tables getting different kinds of Text file descriptions.
There are tables for hierarchy reasons, about 4, and probably 10 more random tables.

I'm having a hard time figuring out the least intensive way to tackle this problem while taking me the least amount of time. If the only TRUE answer to this issue is to rewrite part of the app then maybe I need to sit down and talk with the other developers.

Thanks again for the help.

 

[gmmastros]

Alright. Fair enough.

I'll try my best to explain.

SQL Server has been written in such a way that set based queries are very fast. When you write a loop in SQL Server, you should expect performance to suffer a lot. It does not matter whether this loop is implemented through a cursor or a while statement. It's still a loop and will be slow.

The existing stored procedure takes an ID number and performs a query, presumably with the ID as a filter condition. Suppose the 'outer' query returns 100 records. Your plan would be to loop through each record, get the ID number, and then call the stored procedure. Each stored procedure call would hit the database.

By re-writing the query to NOT use the stored procedure, you can effectively hit the database once to return the data. Each time you 'hit' the database, there is a cost (time) associated with it. That's why it is best to avoid situations where loops are involved.

Do you have access to the source of the stored procedure? Perhaps it is as simple as making another stored procedure with no inputs and no where clause.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[syukna]

I agree that making 6 or 700 calls in the stored procedure is probably not the best way to do things. I have Admin rights to the server. I'm essentially trying to deactive a hyperlink in the grid based on a certain criteria for each row.

How would I write a stored procedure to associate the inidividual data (multiple table result set) to the grid (one table result set) data without specifying some sort of criteria.

 

[syukna]

I think I may have figured it out. I'm gonna write a bunch of select's to union all the ID's together where the criteria is in the table. Then set a flag in the result set I send back to the page to populate the grid, and disable the links based on that flag.

Is there a better way than this do you think?

 

[gmmastros]

It's hard to say.

Do you know what the difference is between Union and Union All?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[syukna]

I may be wrong, but I thought union omitted non distinct records and union all puts them in regardless.

 

[gmmastros]

Yes, you are right. But there's more.

Union All is faster than union. This is because it takes time to determine a distinct list. In this case, if you are sure to have a distinct list to begin with, then I would recommend Union All to make the query a little faster.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[syukna]

Yup, makes perfect sense.

I took out the unions and just used something like this

SELECT myID FROM table1 WHERE myCriteria IN ('A', 'B')
OR myID IN (SELECT myID FROM Table2 WHERE myCriteria IN('A', 'B')) 
OR myID IN (SELECT myID FROM Table3 WHERE myCriteria IN('A', 'B'))

etc...

Would it be faster to use unions or the method above? I tend to make things more difficult then they need to be...this seems like a simpler solution, regardless of if it's performacne, then where the thread originally started.

 

[ESquared]

Are table1, table2, and table3 actually different tables? And how many rows will each of those subqueries return?

The IN() syntax actually expands to an OR clause. Really big OR clauses can perform worse than JOINs (the optimizer might be smart enough to convert such a thing to a JOIN). But it's best to get in the habit of writing JOINs. This is what the database excels at and it's the way a SQL developer ought to be thinking.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[syukna]

Yes the tables are different tables. One will only return 7 rows while the other theoretically could be endless but will usually not hold more than 100 records.

 

[gmmastros]

This is what the [!]database excel[/!]s at...

You scared me there for a minute.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[syukna]

I understand how simple joins work, but do you guys have a good article on advanced joins?

 

[syukna]

I guess a more specific question. How would I write this query w/o using the keyword IN?

*The names have been changed to protect the innocent

select OccasionID from tblOccasions
where OccasionCriteria1 in ('A', 'B') or OccasionCriteria2 in ('A', 'B')
or OccasionID in (select OccasionID from tblOccasionFiles where FileCriteria in ('A', 'B'))
or OccasionID in (select OccasionID from tblOccasionsText where OccasionTextCriteria1 in ('A', 'B') or OccasionTextCriteria2 in ('A', 'B'))

 

[ESquared]

For the ones that return a very short list, it might be better to stay with your IN() syntax. For the one that returns many rows, experiment with a JOIN. In any case, you should pay attention to the actual execution plans to see what the query optimizer is doing and make your choices based on that, not on my theoretical musings about what *might* be better.

select OccasionID
from
   tblOccasions o
   left join tblOccasionFiles f on o.OccasionID = f.OccasionID AND f.FileCriteria in ('A', 'B')
   left join tblOccasionsText t on
      o.OccasionID = t.OccasionID
      and (
         t.OccasionTextCriteria1 in ('A', 'B')
         or OccasionTextCriteria2 in ('A', 'B')
      )
where
   o.OccasionCriteria1 in ('A', 'B')
   or o.OccasionCriteria2 in ('A', 'B')
   or t.OccasionID is not null
   or o.OccasionID is not null

Because you are using OR instead of AND, I had to make left joins and put some "is not null" conditions in the where clause. If they were ANDs I could have just used an INNER JOIN (and then the criteria in the ON clauses could optionally move to the where clause).

Again, if you are looking for performance, you must test. And you must test not just from the execution plan perspective but also looking at the CPU usage and total Reads (use SQL Profiler to get this data).

Occasion is a nasty word to type over and over again!

 

[syukna]

Hey thanks a lot for the help guys. I appreciate it....I only had to type Occasion once when I did a find and replace

 

[syukna]

Hi ESquared:

After looking at your stored procedure and converting it to what I need it doesn't actually return the correct result set. It returns only the ones from tblOccasion matching the criteria. Anyways again I apprecate your help. For now I think I'll just stick to what I had above.

 

[ESquared]

Well, you didn't give me the actual query. You translated to something symbolic. I translated your symbolic query to something else. And then you translated this new symbolic query into a new, "real" query. I'm not surprised something broke along the way.

Change column names and values if you like, but if you'll provide an actual working query, then maybe we can provide you an alternate way of doing it.

 

[syukna]

I understand things can get screwed up along the way. I didn't mean to offend you if I did. I really appreciate the help.

As far as translating the query. The query I put up is the exact query with different words for "occasion" and "criteria". I got an ambiguous column error when I didn't specify which table to select OccasionID from. I then put ccasionID up there so that's how I screwed it up to retrieve only records from tblOccasions.

Like I said above I apprecate the help as always.