newbie question on SP, Databases,instances

I am sql server newbie who is trying to migrating a DB2 system(MF) to
SQLserver.
I have a few questions, it would be great if someone can help me out :
1. When I am porting the app to sqlserver, is it advisable to make the
queries to SP or keep it as a regular SQL ? Does SP perform any better
than SQL ? I have heard that lot of places write only SP, so the
front-end programmers can just call the SP and get the data.
2. I got multiple databases in DB2 (in mainframe ,databases are just
logical), is it a good idea to create different databases in SQLserver
or have it in one big database . Pro and cons ?
Could anybody please help
TIA
RogerComments Inline
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Perl rookie" <anytasks@.gmail.com> wrote in message
news:1107809732.055331.57880@.z14g2000cwz.googlegroups.com...
> I am sql server newbie who is trying to migrating a DB2 system(MF) to
> SQLserver.
> I have a few questions, it would be great if someone can help me out :
> 1. When I am porting the app to sqlserver, is it advisable to make the
> queries to SP or keep it as a regular SQL ? Does SP perform any better
> than SQL ? I have heard that lot of places write only SP, so the
> front-end programmers can just call the SP and get the data.
Generally speaking, it is a good idea to write a stored procedure interface
to the database. Performance is usually better due to procedure plan
caching, plus it gives you a layer of abstraction so you can make changes
without haveing to make the code changes at the same time.
> 2. I got multiple databases in DB2 (in mainframe ,databases are just
> logical), is it a good idea to create different databases in SQLserver
> or have it in one big database . Pro and cons ?
>
I prefer to keep data together in a single database if it needs to be
transactionally synchronized for backup and restore, needs to have
database-level referential integrity constraints, and makes sense to do so.
There are other considerations such as whether there are data logs or work
queues involved that have specific performance and backup needs, but those
are the major reasons.
> Could anybody please help
> TIA
> Roger
>|||When a query is processed by SQL Server, it requires miliseconds to compile
the execution plan. Whether this is a performance issue depends on the case
usage of the SP. If this SP is called 10,000 times per day, or several times
per second, then yes it can reduce the compile time and is probably more a
scalability issue. However, if this is a SP used for reporting purposes,
occasional data inserts (few times per hour), etc. calling as a SP will not
impact the runtime performance. In other words, a query that takes 2 minutes
to execute from the application will not be reduced to 2 seconds simply
because it has been re-written as a SP. Your time is better spent optimizing
the structure of the query itself.
However, from an architectural perspective, I believe that queries
(especially insert / update / delete queries) should be implemented as SPs.
This shifts business logic an data modification programming to the server
side where it can be better managed by the DBA. The application tier should
be as lightweight as possible and deal strictly with presentation, user
input, workflow, etc.; espcially if we are talking about a web application.
Whether your tables should be placed in one database or multiple databases
would require knowledge about the nature and relationship of the data. That
said, depending on the volume of data (say 10 GBs or larger), it can be
generally beneficial for performance reasons to archive rarely used
historical data to a seperate database.
"Perl rookie" <anytasks@.gmail.com> wrote in message
news:1107809732.055331.57880@.z14g2000cwz.googlegroups.com...
> I am sql server newbie who is trying to migrating a DB2 system(MF) to
> SQLserver.
> I have a few questions, it would be great if someone can help me out :
> 1. When I am porting the app to sqlserver, is it advisable to make the
> queries to SP or keep it as a regular SQL ? Does SP perform any better
> than SQL ? I have heard that lot of places write only SP, so the
> front-end programmers can just call the SP and get the data.
> 2. I got multiple databases in DB2 (in mainframe ,databases are just
> logical), is it a good idea to create different databases in SQLserver
> or have it in one big database . Pro and cons ?
>
> Could anybody please help
> TIA
> Roger
>|||Thanks a lot Geoff and Johnny . Appreciate ur reply