Solved

which sql would be best

Posted on 2008-10-12
5
152 Views
Last Modified: 2012-05-05
small question
I have 2 different pages that do 2 different things
for example page1 needs some columns from a table and page2 needs the same columns as page1 with the addition of a handfull more
Q:
should I do 2 separate stored procedures like:
page1: select a,b,c,d,e from table where this = that
page2: select a,b,c,d,e,f,g,h,i,j from table where this = that
or should I just select all I need for page2 and use that sproc for page1 also
or something else

I've always wondered about how much overhead it is selecting unneeded columns
especially in a case where some of the columns may be varchar(max) and can at times be quite large

thanks
0
Comment
Question by:dgrafx
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 1

Accepted Solution

by:
Peter_Brabrand_Rasmussen earned 250 total points
ID: 22699470
I would create one SP that handles both. Of cause it depends on the amount of data you are pulling out of the tables.

Why not create one SP that can return both recordsets with an option..?

Hope that answers your question.
0
 
LVL 25

Author Comment

by:dgrafx
ID: 22699493
could you elaborate

to answer the amount of data
sometimes a large recordset - depending on which category
and sometimes a lot of data in a varchar(max) column
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 250 total points
ID: 22699526
Hi,

I'd be inclined to do two procedures.

You've indicated that some of the additional columns might be large, so I wouldn't want two record sets returned, or one recordset that is wider than you really need.

You could in the second proc get clever and do something like this:
select a,b,c,d,e from openquery( server, 'execute proc1 @paramter1 = somevalue' )

Which would allow the base logic to be maintained in one procedure, but reducing the columns returned .

With the added layers of the opernquery, I'd imagine that performance wouldn't be as good as a second procedure with its own independant query/

HTH
  David
0
 
LVL 1

Expert Comment

by:Peter_Brabrand_Rasmussen
ID: 22699597
For the sake of easy upkeep i would create one SP that can handle both requests depending on the input to the SP. Meaning that Option= 1 would return results for page 1 and option=2 would return the results for page 2.
It is very hard to give a specific answer to you question about speed because it all comes down to normalization of the tables and data. If you keep a huge table with lots of empty space and data in some places you are slowing the server. If possible it is a good design rule to keep as little data pr. table as possible and by dooing that keeping the excess data at a minimum.
0
 
LVL 25

Author Closing Comment

by:dgrafx
ID: 31505519
thanks for the words ...
0

Featured Post

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question