August 19, 2016 at 5:40 pm
I have a sproc that needs to return user data as well as perms data for that user. Returning only perms requires a 4-table join and a user may have 1500+ perms. Returning only user info requires a 2-table join.
I could write this sproc as a single query which joins everything together. However, this would return 5 columns of redundant user info 1500 times. Alternatively, I could write this sproc as 2 separate queries where Q1 simply returns a single row of 5 columns of user data and Q2 returns 1500 rows of permission data.
Hardcore SQL people I've worked with in the past tend to want to put everything into 1 big query but considering the additional size of redundant data that this would return over the network I'm thinking that the 2 separate queries I described would be more ideal.
If user perms were limited to 10-20 I might consider putting everything in a single query but since the Q2 rowset is large I'm pretty sure I'm going to use 2 queries. Is this the approach you would lean towards as well?
August 21, 2016 at 12:49 pm
If your application can handle getting two result sets, I'd go that way.
You do have a column that can associate the perms with their users?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 21, 2016 at 12:59 pm
The Dixie Flatline (8/21/2016)
If your application can handle getting two result sets, I'd go that way.You do have a column that can associate the perms with their users?
yep fk on userid
August 21, 2016 at 11:45 pm
What about getting the app to make 2 separate calls, to 2 separate procs.
August 22, 2016 at 8:21 am
Why be that chatty? Paying network overhead twice?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 22, 2016 at 1:16 pm
The Dixie Flatline (8/22/2016)
Why be that chatty? Paying network overhead twice?
It's an option.
I have had cases where this has resulted in improved performance from the UI perspective.
Done with async calls, so the results are streamed back in parallel. Not sure there will be any benefit for small result sets.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply