|
Hi,
Your article is very good. Confirms some ideas that I have.. But a have a question about 5th topic.
"5. Do not use database to store your images. Good approach is store only URL in the table and store the image file in desk itself."
Why? Is it because the size of files? I think that a URL to a file has a problem that is manager file in disk with security issues. If any user delete the file? If the Infraestruture IT professional drop theses files for any reasons?
Sorry my bad english!
Cheers...
Tuca from Bahia/Brazil
|
|
|
|
|
SQL server handles the 'binary' fields in a different way. As the number of records increases the size of the database also increases. And it affects the performance of the whole system very badly. After you specify a column as IMAGE datatype, even if you dont use it for storing images, you will be wasting lot of space. Because when a table have IMAGE datatype, one single page is reserved for each ROW.
Check this link: http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-the-filesystem.html[^]
Well regarding the 'problems', you can easily handle those with other ways. Eg: In Linux, you can block the access to the images (also called hot link protection) by configuring the .htaccess file. The similar modules are available for IIS also.
PraVeeN
blog.ninethsense.com/
|
|
|
|
|
Although none of the advice is really 'wrong' (despite what one person says about stored procedures) a lot of it is kind of vague. You talk about 'breaking big tables into smaller tables Normalization but normalization is more than just breaking big tables into small tables. If you're going to bring up normalization, then you really owe it to beginner to provide a basic explanation of normalizing, at least through 3rd normal form.
A lot of the advice has nothing to do with performance (aside perhaps from saving wasted debugging trying to read poorly formatted code).
|
|
|
|
|
Dear Friend,
Please read the title of title. It says 'Beginners. Database is not just a simple thing and it is very wide. This article contains only 'guidelines' and not a detailed 500+ paged book!
Rather than reading these kinds of articles with criticizing eye. Try to put helpful points, suggestions and point out if there is some mistake.
>> You talk about 'breaking big tables into smaller tables Normalization but normalization is more than just breaking big tables into small tables.
I DID NOT say Normalization is all about breaking big tables in to smaller....
PraVeeN
blog.ninethsense.com/
|
|
|
|
|
The title is "SQL Performance: Good Practices for Beginners" so I'm expecting performance tips. Keeping that in mind, I read: "Divide your large table to small multiple tables." Not exactly "breaking big tables into smaller tables" but I think pretty darn close.
I think a beginner is likely to wonder exactly what constitutes a 'large table'. We're talking about performance, so large tables impede performance? I have 10,000 rows in mine, is that too big? Oh, it's not rows but columns. Well, not exactly, if your columns are all unique non-repeating attributes of an entity, then multiple columns are correct.
The characteristic of a table that should be divided into multiple tables isn't 'large', it's 'non-normalized'. If you think normalization is beyond the scope of the article- fine, don't bring it up. But telling beginners to 'divide [their] large table[s] into small multiple tables' without any explanation of the criteria is useless, IMHO.
>>Rather than reading these kinds of articles with criticizing eye. Try to put helpful points, suggestions and point out if there is some mistake.
I did. I think it was a mistake, and I pointed it out.
|
|
|
|
|
I completely agree. I read the first point in this article and thought the same thing. Dividing a big table into smaller ones could be interpreted different ways and if you're targeting this article at beginners, then you owe it to them to explain your points.
Please don't take this critism to heart, there's still alot of newbies out there who need these sort of basic articles.
Thanks,
Ben
|
|
|
|
|
You have given the following example for Camel Casing:
d. Use Camel notations for all user created objects. Eg: @EmployeeCode
But This is Pascal Casing. this can be misleading for beginners. Visit the following link
http://blogs.msdn.com/brada/archive/2004/02/03/67024.aspx
Ravi
|
|
|
|
|
Hi,
Actually I am not wrong. It is of course "Camel Notation". May be the best word is "UpperCamel Notation".
Eg:
camelCaseLooksLikeThis
lowerCamelCaseLooksTheSame
UpperCamelCaseLooksLikeThis
Check this http://en.wikipedia.org/wiki/CamelCase[^]
PraVeeN
blog.ninethsense.com/
|
|
|
|
|
Thanks Ravi. I added that too.
PraVeeN
blog.ninethsense.com/
|
|
|
|
|
Thanks for the useful link.
But Upper Camel Case is nothing but Pascal Casing
"Where the first letter is capitalized is commonly called UpperCamelCase or PascalCase "
Extract from the same link.
Ravi
http://ravi-achar.blogspot.com/
|
|
|
|
|
I think the article lives up to it's heading.
Every DB expert will have it's own style of doing things.
This list is not law.. It's healthy suggestions for
people that is starting out....
|
|
|
|
|
It's a good practice for performance too ![Big Grin | :-D](https://www.codeproject.com/script/Forums/Images/smiley_biggrin.gif)
|
|
|
|
|
Great! thanks
I am sorting new tips. Soon I will add those also to list.
PraVeeN
blog.ninethsense.com/
|
|
|
|
|
9. Use stored procedures and functions instead of writing all the messy code in the program itself. It not only gives you the performance but also a matter of security.
Security?! You mean insecurity. Code in stored procedures, views, and user-defined functions can be modified too easily, outside source control. I've also had stored procedures "disappear" several times, hosing the entire system. All the code should be in executables where it can't be modified, and if the executable "disappears" at least it can't do any damage.
Distributing updated executables is also easier than distributing updated stored procedures and such.
Plus, not all database systems support stored procedures, so if you're writing an application that is to support multiple databases you need to write all the statements in code anyway.
A database is for data.
|
|
|
|
|
Hi,
Actually, by "security" I meant SQL Injuction like issues. Check this http://en.wikipedia.org/wiki/SQL_injection[^]
If one have access to the system, he can easily do any kind of mis-using which you mention. Such activities does not belongs to a developer's duty but an Administrator's.
This article a geniric one for all the databases. If it doesnot supports stored procedure, of course we will have to write all the statements in code itself. But there also we can do so many things to avoid sql injuction like issues. That I will update or publish as new article in the coming days.
Thanks for the comment.
-- modified at 1:11 Monday 21st May, 2007
PraVeeN
blog.ninethsense.com/
|
|
|
|
|
This article is for devlopers who use a front-end language (C#, php etc.) to manipulate SQL.
Once I was reviewing c# code of my friend.
I asked him : "Do you change the data of this table anywhere else?".
Without thinking he answered :"Yes! with Enterprise Manager".
PraVeeN
blog.ninethsense.com/
|
|
|
|
|
Stored Procedures (and parameters) are not a magic bullet against that, the incoming data must still be validated by the interface.
NinethSense wrote: If one have access to the system, he can easily do any kind of mis-using which you mention. Such activities does not belongs to a developer's duty but an Administrator's.
What!? That's ridiculous. It's everyone's duty, and the sooner such an attack is detected and foiled the better. As a programmer should I just pass along the data and when the system crashes say, "I thought you were handling the validation"?
Plus, who wrote the Stored Procedure? A programmer!
|
|
|
|
|
Oh dear, here comes another bunch of ORM and Amber's do not use SPs disciples:
> executable "disappears" at least it can't do any damage.
It is like saying if one app disappears then go ahead foget that you can still hack the DB apart with the second one.
> Distributing updated executables is also easier than distributing updated stored procedures and such.
Learning how to use SQL transactional functionality, versioning, services and other is more fruitful than bothering with SVN and CVS (which you can also do with SPs).
> not all database systems support stored procedures
Which toy is that?
> so if you're writing an application that is to support multiple databases you need to write all the statements in code anyway.
Like simple statements and simple use of x\SQL dialect. If you are running a serious database application you are probably using features that would not allow you to use them even if you wanted to bring in another database vendor.
Do tell, you check that in code and translate as appropriate.. hmm.
If you need to update both, enlist them in a transaction via (via your app? yikes!) otherwise oh momma.
> A database is for data.
Since at least late 1980s it actually was not, that is why it has all those things built on top that push the versions from 3.0 to 9.0 and so on.
> Stored Procedures (and parameters) are not a magic bullet against that, the incoming data must still be validated by the interface.
Validate that in code and you truly do not need a database.
> It's everyone's duty
At least one Database Administrator duty is to administer and another to design access for it.
> and the sooner such an attack is detected and foiled the better
If you take that approach, you should not be even aware that a particular table exists frankly; if you are, then you should hire a database guy.
>Plus, who wrote the Stored Procedure? A programmer!
A database designer, one paid more than programmer because he does not see your random X,Y,Z language code and 'uniform data access' mix well if at all.
LINQ will not help you there either, dynamic SQL is a first sign you are taking the wrong route not just on performance you need to see for yourself on native providers and heavy load, versioning, security and more, but on general solution/app design and approach to relational databases and SQL99 at least.
-- modified at 18:44 Friday 18th May, 2007
|
|
|
|
|
You misunderstoond NinthSense. It's everyone's duty to write code that validates data. It's NOT everyone's duty to be mucking around in the PRODUCTION database.
Developers should not have access to the PRODUCTION database. That's what the DBA is for.
I'm a developer. I don't have, or want, write access to any production data. When my stored procedures have been through QA, then and only then are they submitted to the DBA to be created/modified on the production DB.
|
|
|
|
|
>> Developers should not have access to the PRODUCTION database. That's what the DBA is for
Is that your DBA writing validation scripts in your database? If yes, you are on wrong way. DBA have a big role in database and it is not simply writing validation for your scripts.
PraVeeN
blog.ninethsense.com/
|
|
|
|
|
I've read my post, and your reply, a dozen times, and we might as well be talking different languages. In no way was I attempting to enumerate the roles of a DBA.
Let me start over.
Keep in mind the context: PIEBALDconsult claimed his stored procedures were getting changed and deleted, and thus constituted a security risk.
I was merely trying to point out that the solution isn't to stop using stored procedures, but to have and enforce a protocol for changes to the production DB. That usually means that the DBA, not developers, is in charge of making changes to the production DB. It doesn't have to be that way, but you need a protocol so you know who changed what and when, and are sure it passed proper QA.
I'm not sure who you think should be writing the code to validate data, but it doesn't really matter. What matters is that it is done, and passes QA. I don't care how good anyone is, everyone makes mistakes. Something that important has to be reviewed.
|
|
|
|
|
I don't know what you are doing but your all wrong. All applikasjons should use stored procedures when accessing the database for the following reasons
1. You can update, rename, split, merge tables without changing your code in the client applikasjons
2. The code will execute much faster since stored procedures has a precompiled execution plan
3. Stored procedure can return multiple results
4. Dynamic SQL statements in your code is a maintenance hell. If you have 10-20 apps accessing the same table you have a problem
5. Dynamic SQL statements can result in injection attacks
6. Stored procedure can perform multiple updates/changes in the database with one call transactional og non transactional
7. Microsoft, Oracle etc recommends it
It sounds like you have a maitenence problem or your are not to familiar with databases. All stored procedures scripts should be stored in source control and only be installed by the database admin in the production and test environment. When it comes to stored procedures that suddenly disappears: what kind of database are you using? No real database suddenly removes any stored procedure without someone removing it (by code or manual delete).
If your using databases that does not support stored procedures -> write dynamic SQL
If your using databases that does support stored procedures -> use stored procedures
When it comes to views -> i'm not the biggest fan.
Jarle
|
|
|
|
|
I am also not a fan of Views. Thats why I didnt mention it on the above list. Well, once I find a 'useful' tip, I will add to this article.
PraVeeN
blog.ninethsense.com/
|
|
|
|
|
Views are a good way to get a consistent, um, view, of your data.
For example, I have a 'product' table that includes foreign keys to several lookup tables. I have a number of stored procedures that return 'product' for various queries. Instead of listing all the columns and performing all the lookup table joins in every stored procedure, I do it once in the view, and every stored procedure returns from the view. If I need to add a new foreign key/lookup, I do it in one place, the view.
|
|
|
|
|
Cool reply Jarle, very informative. I agree with your statements mentioned above.
And know the use of stored procedures.
But some people say stored procedrues create one more layer and that is not good. So Procedures have to be created only when very necessary. I am not very clear on this.
Ravi
|
|
|
|