SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

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

Adbot: ADBOT LOVES YOU

# ? May 12, 2024 17:27

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

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

minato: Jun 7, 2004; cutty cain't hang, say 7-up.; Taco Defender

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

Xae: Jan 19, 2005

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:

UPDATE mytbl
   SET text_column = Replace(text_column, '''', ' ');

That should replace all 's with spaces.

# ? Apr 15, 2008 12:47

Zombywuf: Mar 29, 2008

Muzjik posted:

I have a cd, SQL server 2005 and it has several versions on it ( enterprise, standard)

Im sort of new at this and iv been trying to find help on installing the CD, when i put it in and open it, it opens in internet explorer, then it has like a list of links. So i hit one of the links that look best ( standard 32 bit ) and i found the set up, then this is where i get confused

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

StonedogJones: Mar 13, 2006

Xae posted:

Alter for your flavor of SQL:
code:
UPDATE mytbl
   SET text_column = Replace(text_column, '''', ' ');
That should replace all 's with spaces.

worked like a charm, thanks folks!

# ? Apr 15, 2008 15:44

<deleted user>

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

npe: Oct 15, 2004

Oracle is hint crazy. There are 124 different documented hints, and 57 undocumented.

# ? Apr 15, 2008 16:10

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

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

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

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?

Do some sort of explain plan. If the plan is all retarded, then you may have to tell your RDMS to use your nice index.

# ? Apr 15, 2008 20:22

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

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

dagard: Mar 31, 2005

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

Xae: Jan 19, 2005

yaoi prophet posted:

Oracle is hint crazy. There are 124 different documented hints, and 57 undocumented.

The best part about Oracle hints is that they are just that, hints. If Oracle doesn't want to use an index, or do a table scan you can't force it. It will do what it drat well pleases and gently caress you for suggesting otherwise. I have seen some crazy poo poo done to systems try to force Oracle into a certain path. The worst at when people go nuts thinking Oracle hosed up the execution plan, when it was doing it correctly and people try to force Oracle into using a slower method.

Oracle always amazes me at how impressive it can be, but also how stubborn it can be.

# ? Apr 16, 2008 02:40

Barrackas: Aug 12, 2007

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:

IF EXISTS(	SELECT TOP 1 PersonID
		FROM	PersonTable
		WHERE	PersonID = v_suppliedPersonID)
...

At the minute, I've got this...

code:

DECLARE
	rowCount INTEGER;
SELECT COUNT(*) INTO rowCount FROM dual WHERE ROWNUM = 1 AND
EXISTS( 
	SELECT PersonID
	FROM PersonTable
	WHERE PersonID = v_suppliedPersonID
        );
  
  IF(rowCount > 0) THEN
    ...
  ELSE
    ...
  END IF;

Is this an appropriate approach? Are there any significant performance hits resulting from the use of SELECT COUNT(*)? And if so, does the "WHERE ROWNUM=1" clause go anyway to rectifying this?

Cheers

# ? Apr 17, 2008 15:44

npe: Oct 15, 2004

You want to just test if something exists? You can just do

code:


  SELECT 1 FROM dual WHERE EXISTS( 
    SELECT PersonID FROM PersonTable WHERE PersonID = v_suppliedPersonID
  )

That's going to be about as fast as it gets.

# ? Apr 17, 2008 16:29

Barrackas: Aug 12, 2007

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:

DECLARE
  row_count INTEGER;
BEGIN
  SELECT 1 INTO row_count FROM dual WHERE EXISTS(   SELECT PersonID 
                                                    FROM PersonTable
                                                    WHERE PersonID = v_suppliedPersonID
);
  IF(row_count > 0) THEN
    DBMS_OUTPUT.PUT_LINE('I found it');
  ELSE
    DBMS_OUTPUT.PUT_LINE('I did NOT find it');
  END IF;
END;

Did I use it correctly? Excuse the extreme newbie questions, I'm just beginning with Oracle

# ? Apr 17, 2008 16:57

npe: Oct 15, 2004

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

Barrackas: Aug 12, 2007

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

Xae: Jan 19, 2005

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.

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!

90% of the time you don't need Dynamic SQL. Oracle will pre-parse most statements in stored procedures. Since that can't be done with dynamic SQL they often times execute slower since Oracle may need to Hard Parse the statement.

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

npe: Oct 15, 2004

Barrackas posted:

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?

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

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

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:

select sum(value), $column
from table
group by $column
order by $column

I've seen some examples where you essentially construct a string and then eval that string with EXECUTE(@query) but is there something more direct? Also, is that use of EXECUTE(@query) bad/slow?

# ? Apr 18, 2008 17:01

Zombywuf: Mar 29, 2008

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

Aredna: Mar 17, 2007; Nap Ghost

SQL Server

I need to check if a string contains 3 specific characters in any order. Currently I am doing it as:

code:

st.email like '%a%'
and st.email like '%b%'
and st.email like '%c%'

Is there a more efficient way to accomplish this?

# ? Apr 18, 2008 17:32

npe: Oct 15, 2004

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

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

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.
code:
select sum(value), $column
from table
group by $column
order by $column
I've seen some examples where you essentially construct a string and then eval that string with EXECUTE(@query) but is there something more direct? Also, is that use of EXECUTE(@query) bad/slow?

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.

# ? Apr 18, 2008 17:45

Xae: Jan 19, 2005

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.

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.

# ? Apr 18, 2008 23:27

npe: Oct 15, 2004

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

chocojosh: Jun 9, 2007; D00D.

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

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

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).

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.

# ? Apr 19, 2008 08:13

chocojosh: Jun 9, 2007; D00D.

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

hey wiz: Jun 18, 2005

Barrackas posted:

code:

DECLARE

BEGIN
 SELECT 1
   FROM PersonTable
  WHERE PersonID = v_suppliedPersonID;

  IF(SQL%FOUND) THEN
    DBMS_OUTPUT.PUT_LINE('I found it');
  ELSE
    DBMS_OUTPUT.PUT_LINE('I did NOT find it');
  END IF;
END;

Would this work? The SQL%FOUND is part of Oracle not just PL/SQL right?

# ? Apr 19, 2008 20:59

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

chocojosh posted:

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).

I guess what I'm trying to say is that he could dynamically generate a query string with no parameters and then he could execute said query with either EXECUTE or sp_executesql and it wouldn't matter. He gains nothing by using sp_executesql since he does not actually have any parameters in his query and thus must re-generate it each time.

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

Xae: Jan 19, 2005

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

Coconut Pete: Jul 31, 2004; Bad Mother Fucker

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

noonches: Dec 5, 2005; It represents my lifestyle and stratus as a street savy irreverent youth, who lives large, and yet hungers for the next level in life.

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

npe: Oct 15, 2004

noonches is right, these are usually called "xref" tables. Don't do the comma delimited thing.

# ? Apr 21, 2008 11:11

w_rogers82: Apr 11, 2008

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:

SELECT id,request_submit_date,request_name,request_start_date,request_submitter_id,last_viewed
FROM REQUESTS WHERE request_submitter_id = 114 AND EXISTS
(SELECT id,request_submit_date,request_name,request_start_date,request_submitter_id,last_viewed FROM
REQUESTS WHERE mgr_status = 'DECLINED' OR worker_status = 'COMPLETED');

# ? Apr 21, 2008 20:24

noonches: Dec 5, 2005; It represents my lifestyle and stratus as a street savy irreverent youth, who lives large, and yet hungers for the next level in life.

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:

SELECT id,request_submit_date,request_name,request_start_date,request_submitter_id,last_viewed 
FROM REQUESTS 
WHERE request_submitter_id = 114 
    AND (mgr_status = 'DECLINED' 
          OR worker_status = 'COMPLETED');

# ? Apr 21, 2008 20:33

No Safe Word: Feb 26, 2005

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.
code:
SELECT id,request_submit_date,request_name,request_start_date,request_submitter_id,last_viewed
FROM REQUESTS WHERE request_submitter_id = 114 AND EXISTS
(SELECT id,request_submit_date,request_name,request_start_date,request_submitter_id,last_viewed FROM
REQUESTS WHERE mgr_status = 'DECLINED' OR worker_status = 'COMPLETED');

Why do you need a subquery?

code:

SELECT
   id
  ,request_submit_date
  ,request_name
  ,request_start_date
  ,request_submitter_id
  ,last_viewed
FROM
  requests
WHERE
  request_submitter_id = 114 AND
  (mgr_status = 'DECLINED' OR worker_status = 'COMPLETED');

# ? Apr 21, 2008 20:35

Adbot: ADBOT LOVES YOU

# ? May 12, 2024 17:27

w_rogers82: Apr 11, 2008

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

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »