Solved

which sql would be best

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Parsing the XML data to SQL Server 4 59
Delete from table 6 43
Inserting a column in a table that creates an ID and row number 4 52
Analysis of table use 7 40
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

930 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

12 Experts available now in Live!

Get 1:1 Help Now