Best Practice, Stored Procedures & Datasets

Hi,
Would be interested to hear your thoughts on whether is best to minimise or
maximise the use of stored procedures in SQL Reporting. Is it best to have
as much as possible coming from Stored Procedures or is it better to have as
little as possible. At this stage I am more concerned about management
rather than performance. However I'd love to hear arguments from all sides !
Cheers,
JayI prefer to use stored procedures as much as possible. There are two causes
for this: I think it's more efficient to let SQL Server handle the
processing needed to return the correct dataset and just use RS to do the
formatting, and it's usually easier to change the stored procedures later
than your report. But if you know you won't be able to access the SQL server
later, you should put the queries and logic in your RS report.
Kaisa M. Lindahl
"Jay Sanderson" <jay@.REMOVEMEacttiv.com> wrote in message
news:evEMmciJGHA.604@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Would be interested to hear your thoughts on whether is best to minimise
> or maximise the use of stored procedures in SQL Reporting. Is it best to
> have as much as possible coming from Stored Procedures or is it better to
> have as little as possible. At this stage I am more concerned about
> management rather than performance. However I'd love to hear arguments
> from all sides !
> Cheers,
> Jay
>|||Yes, I would recommend using Stored Procedures as much as possible as you can
usually reuse them in other reports. I find it particualarly useful to use
SPs for returning default paramaters as they are usually the same across most
reports I build and if you need to change any logic you just do it once and
you dont even need to republish the report or anything.
If you store the sql query in the report itself this will become
unmanageable as the amount of reports increase. Any small business logic
change will mean you have to trawl through all your reports to edit your
queries and then republsh the reports. SPs will save you all this hassle.
"Kaisa M. Lindahl" wrote:
> I prefer to use stored procedures as much as possible. There are two causes
> for this: I think it's more efficient to let SQL Server handle the
> processing needed to return the correct dataset and just use RS to do the
> formatting, and it's usually easier to change the stored procedures later
> than your report. But if you know you won't be able to access the SQL server
> later, you should put the queries and logic in your RS report.
> Kaisa M. Lindahl
> "Jay Sanderson" <jay@.REMOVEMEacttiv.com> wrote in message
> news:evEMmciJGHA.604@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > Would be interested to hear your thoughts on whether is best to minimise
> > or maximise the use of stored procedures in SQL Reporting. Is it best to
> > have as much as possible coming from Stored Procedures or is it better to
> > have as little as possible. At this stage I am more concerned about
> > management rather than performance. However I'd love to hear arguments
> > from all sides !
> >
> > Cheers,
> >
> > Jay
> >
>
>

Benefit of moving Master & TempDB to diff HD

I am interested to hear if people think it would be a good idea to move
the Master & TempDB to a different HD.

Here is my DB Server's set up:
1. Processor: (1) AMD XP 2800
2. 1st HD (IDE 0) is the system & boot drive
3. (3) SCSI HD make up a hardware RAID level 0 (striped without
parity)solution - these striped drives are just for my working DBs
4. (1) SCSI HD that's not doing anything.

I want to put the Master & TempDB on the SCSI HD that's not doing
anything. Would that be the best place for it for maximum performance or
should I put in the striped array. I am leaning more towards putting on
the SCSI HD that's not doing anything. What do you all think?

Ed

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!adude (nospam@.devdex.com) writes:
> I am interested to hear if people think it would be a good idea to move
> the Master & TempDB to a different HD.
> Here is my DB Server's set up:
> 1. Processor: (1) AMD XP 2800
> 2. 1st HD (IDE 0) is the system & boot drive
> 3. (3) SCSI HD make up a hardware RAID level 0 (striped without
> parity)solution - these striped drives are just for my working DBs
> 4. (1) SCSI HD that's not doing anything.
> I want to put the Master & TempDB on the SCSI HD that's not doing
> anything. Would that be the best place for it for maximum performance or
> should I put in the striped array. I am leaning more towards putting on
> the SCSI HD that's not doing anything. What do you all think?

I would not move master.

If that idle HD is on a different controller, moving something could be
good for performance. If you have lots of action in tempdb, this could
be a candidate. You could also consider moving transaction logs to the
idle disc.

If the disk in the same controller as the rest, I think it would be better
to move disk into the stripe. If the IDE disk is slow, you couls still
move tempdn into the RAID.

I need to add the disclaimer that hardware configuration is not my best
game.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp