• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

which sql would be best

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
dgrafx
Asked:
dgrafx
  • 2
  • 2
2 Solutions
 
Peter_Brabrand_RasmussenCommented:
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
 
dgrafxAuthor Commented:
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
 
David ToddSenior DBACommented:
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
 
Peter_Brabrand_RasmussenCommented:
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
 
dgrafxAuthor Commented:
thanks for the words ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now