|
CRAP :->
CRAP
CRAP
when we have great articles like this sqldodont.asp[^]already in CP
Don't you realize that number of such articles increase exponentially. This will make CP less useful in the future.
|
|
|
|
|
>> Don't you realize that number of such articles increase exponentially
What is wrong with that? It is a good thing. Atleast it is not "only one person" responsible for the "increase exponentially". Each article is written by "each" person with his/her experience and knowledge. Why you think like one's view is not different from other's?
>> This will make CP less useful in the future
I do not think in such a way. But may be it will increase the number of users using codeproject.
Thanks for the comments.
PraVeeN
blog.ninethsense.com/
|
|
|
|
|
I agree with you.Cp was a great resource site and now a days its quality is deteriorating day by day with junk low quality reduntant articles- 'Articles written for articles sake'.I am amused ;Is some one getting paid for writing articles ,or is it just to fill disk space.
|
|
|
|
|
|
|
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
|
|
|
|
|
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/
|
|
|
|