So you at least find when you're making mistakes. You make rules that when you break you get stopped. We do that a lot when you're writing trying to write really reliable software. Because it was my rule that I put in there to catch myself and keep myself from making mistakes. So if that's kind of a rule that must be enforced and as we as a develop software developer, I would much rather have been able to enforce that rule in case I as a software developer made a mistake, And when I am told by my database that I write some SQL and I've violated a rule, I'm not like mad at the database. Two columns, two nulls, I wasn't allowed to do. The way I ultimately did it was I didn't use a stored procedure, I just made sure I never put records in that violated my rule of two nulls, basically. And the only way to do that is to do a stored procedure. And there's no way to express that in a CREATE statement. I wanted to say one of these two fields must not be null but it's okay for either field to be null as long as the other field is not null. The one time I wanted to do this, well, the Postgres did it, was a key where this instead of me saying this field has to not be null and this field has to not be null. A strong reason to use a stored procedure is to solve a major performance problem, because they're harder to test and they're not portable and, you know, maybe there's some rule that you can enforce on a CREATE statement like a constraint or a unique key. I just tend to use them kind of in I like to isolate them and have a real strong reason why I'm going to do it, like I said. And so I just I don't say I never use them. So the fewer SQL trans statements and fewer round trips between your application and the SQL database. So from a performance thing, they're pretty awesome because they take multi-step processes that are full round trips between an application like pgsql and your database and then do them all in one database transaction. It would be so horribly slow that you'd be like, can we please make that main screen be a stored procedure and someone like me be like would be like oh, man, I really don't like stored procedures, but our company's going to go out of business if we don't take these nine SQL statements with some if statements in the middle and just turn them into a stored procedure. But if you're a company that is a Postgres company and you're building an online system and you have some query that if you did it all kind of run a query, run a query, read the results, run seven more queries, then do this other thing, and you had to do that for like your main screen, that would be so dog slow. Even what's possible to be done in stored procedures has slight variations from one database to another. But if you're moving from Oracle to Postgres to MySQL to SQLite and and you start using stored procedures too much, they're generally not portable at all. Now, you know, if you're working for a company and that company is a Postgres company, then, you know, they're not going to be nearly as opposed to stored procedures. And the reason that I don't like stored procedures is I tend to move from one database to another. I tend to avoid stored procedures at all costs. And I'll share my strong opinion with the understanding that it's just an opinion. Stored procedures is another one of those topics that you can start a conversation in a coffee shop about stored procedures and people will often have strong opinions. So now we're going to talk about stored procedures.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |