November 6, 2009 at 3:01 pm
Hi,
This has probably been asked several times before, I have been using MS Access previously but now moving to SQL, my server has SQL 2005 workgroup edition installed.
- Is it more efficient to use 2 or more databases than or just one?
- Is it more efficient to use stored procedures rather than queries from the pages?
- Is it more efficient to use one stored proc using a case than using several proc?
Thanks for any advice.
November 6, 2009 at 3:41 pm
s_shah-977123 (11/6/2009)
Hi,This has probably been asked several times before, I have been using MS Access previously but now moving to SQL, my server has SQL 2005 workgroup edition installed.
- Is it more efficient to use 2 or more databases than or just one?
- Is it more efficient to use stored procedures rather than queries from the pages?
- Is it more efficient to use one stored proc using a case than using several proc?
Thanks for any advice.
1) It depends. What are you trying to accomplish?
2) It depends. Stored procedures will be more secure and it depends what your are trying to accomplish.
3) This one, multiple stored procedures. Using a master stored procedure to determine which stored procedure to run will be more efficient, as each of the procedures called will have their own execution plasns.
I'm sure others will add more to this discussion.
November 9, 2009 at 8:08 am
s_shah-977123 (11/6/2009)
Hi,This has probably been asked several times before, I have been using MS Access previously but now moving to SQL, my server has SQL 2005 workgroup edition installed.
- Is it more efficient to use 2 or more databases than or just one?
Just to reinforce what Lynn has already said, you can't simply say two databases or six are better than one without knowing a lot more about the business needs we're trying to define. The one thing I will say is that if you have two, or more, completely distinct applications with distinct data sets (even with say 10-15% cross-over) then I would strongly recommend splitting them into more than one database, because of maintenance, not performance.
- Is it more efficient to use stored procedures rather than queries from the pages?
Yes and no. If the queries from pages are called in such a way that they are parameterized queries, then they'll perform exactly the same way as stored procedures. If it's straight inline TSQL, building WHERE clauses, etc., then yes, it is more efficient to use stored procedures
- Is it more efficient to use one stored proc using a case than using several proc?
Thanks for any advice.
In general, no, I'd say this is less efficient. If you have multiple paths through the data these should be split into multiple queries (parameterized or procedures) in order for each to have it's own execution plan.
In short, I agree with what Lynn said, but since you hadn't responded I thought some reinforcement was needed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 10, 2009 at 7:57 am
My advice is that you get a consultant to help you transition your app to effectively use sql server and to mentor you in how to better use and monitor sql server yourself. You will also need help in setting up configurations, backups, etc, etc, etc. Please do yourself and your company a favor and get some help on board for a week or two at least.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 10, 2009 at 1:34 pm
Thanks for all your comments.
With regard to splitting the databases, the reason I ask is becasue I have 2 applications but can create more tables in one DB or 2 individual DB's with 2 tables each - not sure which is better? the max number of users is 15 per application.
The stored procedures query was a question as I have SQL from the web page which is pretty long and complicated and gets used on serveral pages, so my thought it would be easier to manage and exeute faster.
Thanks for your input!
November 10, 2009 at 2:43 pm
s_shah-977123 (11/10/2009)
...the max number of users is 15 per application.
A decision whether to go for one or more database(s) is much more related to the context to be stored than it is to the number of users accessing the data (especially if we're talking about just a dozen user or so...). Grant already mentioned it.
Unfortunately, you haven't answer the question regarding the general purpose of the two applications and the similarity of the data to be handled yet.
When dealing with a web application, the risk of SQL injection needs to be considered as well. This would favor to use well written procedures over direct SQL from the web page.
Based on the information you provided so far it's hard to tell which way to go. Depending on the complexity of the project, how much you're going to expose data on the web (e.g. internet or intranet) and your experience with the tasks to accomplish you should think about external help, as mentioned before, too.
Edit: typo fixed
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply