Solved

which sql would be best

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Where not exists in same table 3 65
insert query with value having 's 2 51
Help with SQL joins 9 48
Restrict result set 1 37
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
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.
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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now