Multiple SELECTS in a SP options on splitting that out?

[davism]

Hi all,

I have a question that I hope that you may be able to assist with.

I have a stored procedure and in it over 52 SELECT statements are executed but each SELECT uses the table value in the WHERE clause.

for instance:

SELECT a.CheckID, a.test1, a.test2, a.test3 from Test1 a where a.CheckID = @PassedID

SELECT b.CheckID, b.testb1, b.testb2, b.testb3 from Test2 b where b.CheckID = @PassedID

.
.
.

over 50 times.

This is all in an SP.

Now, I know I could probably to this by a JOIN. But there would be one SELECT with the JOIN being there over 50 times.

Is there a better way of doing this?

For instance would creating a temporary table, then do a SELECT INTO that table for like the first 6, then have it call another SP that does an UPDATE into that temporary table with the next 6, then another SP that does an UPDATE into that temporary table with the next 6, and so on.

Or just leave it the way it is? (It's relatively new but I want to address any issues before anything would happen.)

Or does anybody have any other suggestions?

Any information would be greatly appreciated.

Thanks

 

[gmmastros]

It's hard to say without seeing more of the query. Specifically how the data from each query is used.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[davism]

The interesting thing is...what I provided as an example is pretty darn close to what is going on. Just the aliases area a little different (instead of "a", "b", etc it's like "Prov", "ProvAddr", etc) and the field names like "testa", "testb", etc like, "Prov_FirstName", "Prov_LastName", etc.

But the rest is pretty close to it.

The use is that the information from the query is going into a collection on the .NET side. However, I'm not even sure if that is a good idea.

Using a recordset and datatable may be better but that's on the .NET side.

 

[gmmastros]

Hmmm.... so this stored procedure is return 52 different recordsets that will be consumed in a .NET app. Is this right?

If that's the case, then you are probably best to leave it as is.

However, I often times think it is easier to work with a single recordset returned from a stored procedure. If this is your goal, then I would encourage you to use a UNION ALL statement, like this...

SELECT a.CheckID, a.test1, a.test2, a.test3 from Test1 a where a.CheckID = @PassedID

Union All

SELECT b.CheckID, b.testb1, b.testb2, b.testb3 from Test2 b where b.CheckID = @PassedID

Union All

etc.....

Either way, the code will probably execute in approximately the same amount of time. If you start messing about with temp tables or table variables, you're likely to slow things down quite a bit.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[davism]

Yep, you are correct. It's not the way, I want this. I want to get it to one recordset being returned.

How many joins are recommended in a SELECT? I have played around with taking part of the SELECT and combining them into 1. The performance is still the same. But I do not want to create this select with a large amount of joins either.

 

[gmmastros]

What does your query look like with the joins?

I have a couple queries (views, actually) with 50+ joins. It's a little slow-ish, but that's probably more a result of my tables (and their structures) than anything else. Joins can be VERY fast if your join conditions are indexed.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[davism]

It happens that the CheckID is a key so the performance is ok. However, there are still a lot of joins.

I do not want to pursue an entityframework because of potential locking and if you use a no lock on the tables as a hint then you could have dirty reads.

This is an interesting dilemma that I'm not sure how to address, especially being that it's based on real-time use.