|
StonedogJones posted:no i dont think replace will do it. the column has different text in each row, so i cant search for a string, and then replace something in that string, because the string is not constant for each row. Replace(column,',','')
|
# ? Apr 15, 2008 06:40 |
|
|
# ? May 15, 2024 04:09 |
Why does adding an index on one columnn speed up one SELECT query a lot while it slows down another SELECT query? Or should that not happen and something else is causing it?
fletcher fucked around with this message at 07:28 on Apr 15, 2008 |
|
# ? Apr 15, 2008 07:25 |
|
fletcher posted:Why does adding an index on one columnn speed up one SELECT query a lot while it slows down another SELECT query? Or should that not happen and something else is causing it? Indexes should only improve the speed of SELECTs (though they may have adverse effects on the speed of UPDATEs and DELETEs). If adding an index is slowing a SELECT down, then I would guess that the query planner is stupidly using the index when a sequential search would be better (which is the case when the number of rows in the table is low). Not much you can do about that.
|
# ? Apr 15, 2008 08:00 |
|
StonedogJones posted:no i dont think replace will do it. the column has different text in each row, so i cant search for a string, and then replace something in that string, because the string is not constant for each row. Alter for your flavor of SQL: code:
|
# ? Apr 15, 2008 12:47 |
|
Muzjik posted:I have a cd, SQL server 2005 and it has several versions on it ( enterprise, standard) Sorry I can't be of more help, but my best advice is persevere. We lump newbies in this office with the task of installing SQL Server as a kind of practical joke. It often takes a day, and there's no clear pattern.
|
# ? Apr 15, 2008 14:56 |
|
Xae posted:Alter for your flavor of SQL: worked like a charm, thanks folks!
|
# ? Apr 15, 2008 15:44 |
|
minato posted:If adding an index is slowing a SELECT down, then I would guess that the query planner is stupidly using the index when a sequential search would be better (which is the case when the number of rows in the table is low). Not much you can do about that. Many databases allow for index hints of some kind. MySQL has "USE/IGNORE/FORCE INDEX". Oracle has "SELECT /*+ ... */". I think MSSQL has WITH(INDEX()) or something like that. Last I checked, Postgres does not have hints, because the developers don't like the concept (which I agree with mostly, but I've had lots of instances with MySQL where hints were a must).
|
# ? Apr 15, 2008 16:04 |
|
Oracle is hint crazy. There are 124 different documented hints, and 57 undocumented.
|
# ? Apr 15, 2008 16:10 |
genericadmin posted:Many databases allow for index hints of some kind. MySQL has "USE/IGNORE/FORCE INDEX"....I've had lots of instances with MySQL where hints were a must. I was just about to ask about this. It seems like a strange thing to need to specify. How do I know if it is a situation where I have to specify the index to use?
|
|
# ? Apr 15, 2008 20:10 |
|
fletcher posted:I was just about to ask about this. It seems like a strange thing to need to specify. How do I know if it is a situation where I have to specify the index to use?
|
# ? Apr 15, 2008 20:22 |
I'd like to log the slow queries in separate files for each website on the server. I see that you can specify a user config file in ~/.my.cnf. Is that supposed to be the username of the user you are logging into the DB with? I have no clue how to set this up.
|
|
# ? Apr 15, 2008 21:32 |
|
fletcher posted:I'd like to log the slow queries in separate files for each website on the server. I see that you can specify a user config file in ~/.my.cnf. Is that supposed to be the username of the user you are logging into the DB with? I have no clue how to set this up. No, the .my.cnf is for the system user connecting. So your 'apache' user, for example (the perl module DBD::mysql can set *some* client specific options, am unwaware of similar ways in php however). If I remember correctly, the slow query log variables only are paid attention to in the mysqld section, anyways, so you'd have to have a unique mysql instance for each website. Probably be better off parsing it out via logrotate (or similar)
|
# ? Apr 16, 2008 01:50 |
|
yaoi prophet posted:Oracle is hint crazy. There are 124 different documented hints, and 57 undocumented. Oracle always amazes me at how impressive it can be, but also how stubborn it can be.
|
# ? Apr 16, 2008 02:40 |
|
Hi everyone, I'm doing some MS-SQL to Oracle conversion and was wondering if I could get some tips on best practices etc. when it comes to translating certain logic. In particular, this example... code:
code:
Cheers
|
# ? Apr 17, 2008 15:44 |
|
You want to just test if something exists? You can just docode:
|
# ? Apr 17, 2008 16:29 |
|
The problem with that approach, as far as I can tell, is that it throws an exception if the inner statement returns no results. This isn't ideal, as I don't want exception handling to be used as flow control. The way that I tested your suggestion was as follows;code:
|
# ? Apr 17, 2008 16:57 |
|
Well, ok I was just trying to point out that you don't need it in a procedure, as it seems like it's going to be easier for other queries to simply check using EXISTS when they go to do their queries. But if you're refactoring, for a drop-in replacement to a procedure use the count(*) method you had but drop the rownum=1, it's not doing anything. A relevant Tom Kyte answer on this topic that may be useful: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3069487275935 Edited to add: I didn't mean to sound so much like a jerk, but here in Oracle-land there is a frequent clash of idioms from other databases. A procedure to test if a value exists in a table explicitly and then do some action strikes me as a smell of something designed inappropriately for a PL/SQL application. Normally you would either use EXISTS() in whatever query you were doing inline, or if you wanted to select the data out just handle the exception if it doesn't exist if you're not expecting that. It's similar to using explicit cursors in PL/SQL, you can do it, but it's discouraged, and frequently a sign of some poor design decisions. Some other things that drive me crazy, that I see from people who "grew up" in other databases or who just never learned the correct way somehow: * explicit cursors when a simple join, inline view, or FOR loop would have sufficed * wrapping up individual "simple" queries in functions/procedures, only to call them in a procedural way using a cursor in a loop * WHEN OTHERS THEN [whatever]; * COMMIT or ROLLBACK in pl/sql * application logic in triggers * dynamic sql in procedures using EXECUTE IMMEDIATE npe fucked around with this message at 17:43 on Apr 17, 2008 |
# ? Apr 17, 2008 17:22 |
|
Well, as you say, this smacks of something that wasn't designed with PL/SQL in mind because it's true It's a massive MSSQL database (over a thousand stored procedures, hundreds of tables etc.) that is being ported to Oracle. It would be desirable to keep a similar flow to the procedures between the T-SQL and PL/SQL versions, epecially for maintenance, so I think it's acceptable for this project to use conventions that may be otherwise discouraged (unless there's some significant performance hit of course). While we're talking about Oracle conventions, would you explain to me what's wrong with the things you've listed? I'm actually pretty much new to database development full stop, not just Oracle, so I'm still trying to get my mind out of OO programming mode. In particular, what's wrong with explicit cursors and dynamic SQL using EXECUTE IMMEDIATE in procedures? Cheers!
|
# ? Apr 18, 2008 10:49 |
|
Barrackas posted:Well, as you say, this smacks of something that wasn't designed with PL/SQL in mind because it's true It's a massive MSSQL database (over a thousand stored procedures, hundreds of tables etc.) that is being ported to Oracle. Hard parsing is when Oracle creates an execution plan for the statement. For many SQL statements the act of parsing consumes more resources than executing the statement. Once Oracle has determined the execution plan for the statement it stores it for a while so that if similar statements come through it can use that plan instead of generating a new one. Generally speaking you want the commit or rollback to be handled by a smarter language than PL/SQL. There may be some batch processes that you want PL/SQL to do the transaction handling for, but most of the time your Stored Procedures shouldn't be handling the transaction.
|
# ? Apr 18, 2008 12:42 |
|
Barrackas posted:
Cursors are not evil, they are just vastly overused by people who aren't comfortable with implicit cursors or complex queries. Implicit cursors perform better and result in much cleaner code, and it's pretty rare to actually NEED an explicit cursor. EXECUTE IMMEDIATE is pretty heinous. That's not to say it's never appropriate, but on top of the substantial performance problems Xae covered, it also makes your code unsafe. When PL/SQL is compiled, dependencies on objects (tables, functions, procedures, etc) are taken into account. If you refer to a table and column in a procedure, and that table is changed, it will invalidate the state of the procedure and force a recompile, which will fail if the column you're referring to has changed type or name. Same for calls to other procedures and functions, changing an argument in a procedure spec will cause anything that depends on it to be invalidated. All of this type safety and dependency checking goes away with dynamic SQL via EXECUTE IMMEDIATE. You're winging code on the fly and hoping it doesn't break, and it's incredibly more risky. Use only when necessary, when nothing else will do. Tom Kyte's mantra is handy when getting started doing PL/SQL: o if you can do it in a single sql statement - do so. o if you cannot, do it in as little PLSQL as you can. o if you cannot, because plsql cannot do it, try some java (eg: sending an email with an attachment) o if you cannot do it in java (too slow, or you have some existing 3gl code already) do it in C as an extproc.
|
# ? Apr 18, 2008 15:29 |
|
Every day is like a rapid fire of dumb SQL questions to Victor and #cobol... Here's my current one for SQL Server. Is there a way to store the value of a column into a variable? In Perl-speak, I would like this.code:
|
# ? Apr 18, 2008 17:01 |
|
You can't do it in SQL Server, unless you do EXEC('...'). There are problems with this approach, and generally it's better to rethink your design than attempt it. One problem is speed, every time you call your query the bit in the EXEC will need to be re-compiled, the DB can't use a cached plan for this. Another is safety, SQL Server's static checking is pretty crap, but it's better than nothing. By using dynamic SQL like this, you get nothing.
|
# ? Apr 18, 2008 17:20 |
|
SQL Server I need to check if a string contains 3 specific characters in any order. Currently I am doing it as: code:
|
# ? Apr 18, 2008 17:32 |
|
Zombywuf posted:You can't do it in SQL Server, unless you do EXEC('...'). There are problems with this approach, and generally it's better to rethink your design than attempt it. One problem is speed, every time you call your query the bit in the EXEC will need to be re-compiled, the DB can't use a cached plan for this. Another is safety, SQL Server's static checking is pretty crap, but it's better than nothing. By using dynamic SQL like this, you get nothing. It's funny, because while my earlier post about the problems with Oracle's EXECUTE IMMEDIATE was Oracle specific, the argument is basically identical for the MS SQL equivalent. Bad ideas seem to be consistent across platform...
|
# ? Apr 18, 2008 17:36 |
|
Triple Tech posted:Every day is like a rapid fire of dumb SQL questions to Victor and #cobol... Here's my current one for SQL Server. Is there a way to store the value of a column into a variable? In Perl-speak, I would like this.
|
# ? Apr 18, 2008 17:45 |
|
yaoi prophet posted:Cursors are not evil, they are just vastly overused by people who aren't comfortable with implicit cursors or complex queries. Implicit cursors perform better and result in much cleaner code, and it's pretty rare to actually NEED an explicit cursor.
|
# ? Apr 18, 2008 23:27 |
|
Xae posted:I can't say I have ever seen much of a difference between explicit and implicit cursors. I don't doubt that they are slightly faster, but I think any gains are pretty small when you get over a couple rows. It's certainly not a massive difference, however it completes the trifecta: more cumbersome to write, more prone to errors (forgetting to close, for example), and slower. We do use ref cursors sometimes when we need to pass them to/from procedures, but otherwise implicit cursors are the way to go. npe fucked around with this message at 01:36 on Apr 19, 2008 |
# ? Apr 19, 2008 01:23 |
|
Jethro posted:You could use sp_executesql instead of EXECUTE, but I don't think it really gains you anything in this case. Either way, this is definitely a case of needing dynamic SQL, assuming there isn't a totally different way of doing what you want to do. I may be mistaken, but I believe that sp_executesql only allows for parameterization on the WHERE clause. That is, you cannot use sp_executesql to dynamically select different fields/tables (SELECT and FROM clauses), but it can be used for a dynamic where clause. This would be because sp_executesql does make an execution plan that it will re-use assuming you pass the exact same string to sp_executesql (note that you can use different parameter values as the string passed to sp_executesql takes a list of parameters). Talking about dynamic sql, I found this paper was really helpful when I first had to work with it: http://www.sommarskog.se/dynamic_sql.html
|
# ? Apr 19, 2008 07:03 |
|
chocojosh posted:I may be mistaken, but I believe that sp_executesql only allows for parameterization on the WHERE clause. That is, you cannot use sp_executesql to dynamically select different fields/tables (SELECT and FROM clauses), but it can be used for a dynamic where clause. This would be because sp_executesql does make an execution plan that it will re-use assuming you pass the exact same string to sp_executesql (note that you can use different parameter values as the string passed to sp_executesql takes a list of parameters).
|
# ? Apr 19, 2008 08:13 |
|
Jethro posted:That was my impression as well, which is why I didn't think that TT would get much out of using sp_executesql instead of EXECUTE. Except that I don't think TT CAN use sp_executesql -- it should be an error. Note that sp_executesql is drat awesome for dynamic where conditions (think many different search criteria that don't need to apply).
|
# ? Apr 19, 2008 09:58 |
|
Barrackas posted:
|
# ? Apr 19, 2008 20:59 |
|
chocojosh posted:Except that I don't think TT CAN use sp_executesql -- it should be an error. EDIT: So when I said "you could use sp_executesql" I mostly meant it as an alternate syntax, as opposed to an actual functional alternative. Jethro fucked around with this message at 21:57 on Apr 19, 2008 |
# ? Apr 19, 2008 21:54 |
|
hey wiz posted:Would this work? The SQL%FOUND is part of Oracle not just PL/SQL right? I don't think it would. If there is no row Oracle should raise a NO_DATA_FOUND exception. The most common way to check for data before you select it is to do a SELECT COUNT(*) FROM <<stuff>>, as COUNT(*) will always return data, even if it is 0.
|
# ? Apr 19, 2008 22:52 |
|
I have what is probably a simple question but I know little about SQL: I have a table called "users" and a table called "courses" Assuming that each user can be in any number of courses, what would be the ideal way to represent this? Should I have a field in users that lists all the course id's they are in, separated by commas? Is there an easy way to do something like that? Or should I make another table that has an entry for each attendee for each course, and just do something like "... IN ATTENDEES WHERE COURSE = blah AND USER = bob" to get the courses that a user is attending? I'm not really sure how I should go about this.
|
# ? Apr 21, 2008 04:54 |
|
The best way, I think, to go about that is make a third table called users_courses or something like that and just have it map keys from the users table to keys from the courses table.
|
# ? Apr 21, 2008 05:05 |
|
noonches is right, these are usually called "xref" tables. Don't do the comma delimited thing.
|
# ? Apr 21, 2008 11:11 |
|
Ok. Maybe it's because it's Monday or because I'm stupid... possibly both. I'm trying to do a select statement with a subquery to pull all rows where the user_id = 114 and either the mgr_status is 'DECLINED' or worker_status is 'COMPLETED'. Here is what I have.code:
|
# ? Apr 21, 2008 20:24 |
|
w_rogers82 posted:Ok. Maybe it's because it's Monday or because I'm stupid... possibly both. I'm trying to do a select statement with a subquery to pull all rows where the user_id = 114 and either the mgr_status is 'DECLINED' or worker_status is 'COMPLETED'. Here is what I have. Wait, there seems to be many problems with that query. First off, even if the exists statement was necessary, I don't think you'd need to pull all those fields specifically, a SELECT * should do. I could be missing something, but what do you need the subquery for, it's pulling the exact same table you're comparing already, it seems like it could be accomplished with: code:
|
# ? Apr 21, 2008 20:33 |
|
w_rogers82 posted:Ok. Maybe it's because it's Monday or because I'm stupid... possibly both. I'm trying to do a select statement with a subquery to pull all rows where the user_id = 114 and either the mgr_status is 'DECLINED' or worker_status is 'COMPLETED'. Here is what I have. Why do you need a subquery? code:
|
# ? Apr 21, 2008 20:35 |
|
|
# ? May 15, 2024 04:09 |
|
Thanks for the help. I knew it had to be a whole lot easier than I was making it out to be.
|
# ? Apr 21, 2008 20:48 |