Solved

which sql would be best

Posted on 2008-10-12
5
150 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
  • 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 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