- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
You made me laugh, thank you.
Somehow I suspect that a "select * from... " and a C++ scan of a linked list would be faster than this query though.
Admin
"Oracle supports this feature, alter session/system set cursor _sharing = force
Its disabled by default, as its a crutch for shit software"
Yeah, I thought I smelled something. ;-)
Thanks for the info on the config.
-- Joel.
Admin
I have been facing a situation where I had to deal with such a situation:
Each client where given an ID, each client could connect to a any server of a farm and poll for incoming message.
Because the number of client is potentially huge, each server issue an sql request on behalf of all the clients he manages.
The max number of client / server is 60K+, you want to group your sql request, since the pooling needs to happen every second or so.
So how did I solved this:
-A store procedure on MSSQL:
CREATE PROCEDURE GetMessage @list varchar(8000) AS
exec ( 'SELECT * FROM StoreMessage where SessionID in (' +@list+ ' ) ORDER BY insertdate ASC
delete StoreMessage from
(select * from StoreMessage where SessionID in (' + @list+ ') ) as newtable
where ( ImMessage.sessionid = newtable.sessionid and ImMessage.Owner = newtable.Owner and ImMessage.insertdate = newtable.insertdate)')
GO
Note that what is passed to the store procedure is a coma seprated value of the sessionID's .
(the servers issue multiple request if the list param > 8000 char)
This works very nicely indeed!
Sev
Admin
There is a similar query in a PL/SQL package I'm maintaining, and I've had some trouble getting a clean solution to it. Maybe you can help? And please, no flaming. I'm a newbie programmer, and started using PL/SQL last year.
The situation: I have to select almost all values from a single row of a remote table, and loop over them in a very specific way (see point 2 below). The old solution was to specify each of the column names, and dynamically selecting into l_list(1), l_list(2), etc. (a table indexed by binary integer). Running Oracle9i.
When I had to extend the code for a different table, I hit the brick wall of getting a too long SQL statement (194 columns), so I tried something new. I couldn't find any way to select * into an indexed table without specifying index numbers, so I ended up with the following sequence:
- Insert * from the remote table into a local table with the same
columns (doing point 3 from the remote table is prohibitively slow)
- Use a triple loop to build the column names I need (necessary because of the display in a semi-3D HTML table)
- Dynamically select the current column
- Output the result
This hack gave a much smaller code than the earlier hack, but I'd still like to just select directly into a variable, and then looping over elements X through Y. It doesn't seem to be possible to loop over the columns in a record, or to easily convert a record to a list. Even better would be to be able to select all columns except a given set. Is any of this possible?Admin
I have few points to say regarding the posible use of those kind of queries.
1. Real life situation:
In our statistics web pages, we have form for filtering statistics results.
Form is made of several linked selectboxes (changing the content of one, changes
the content of all linked ones) so that customer with few clicks can actualy
make thousands of selections.
So, on the result page (where query is executed) we realy have
thousands of values to be compared against certain fields.
2. Mysql FIND_IN_SET
Ok, so we made that famous SELECT WHERE field IN (valuesList). Soon we
noticed that this way has limitations in quantity of arguments, and that
was not too quick. In mysql there is function FIND_IN_SET ( field, 'valuesList')
which is much faster and has no limitation how many items valuesList contains.
3. Almost Off topic
On one another application, we had slow executing query (20-30 seconds). It was
query which was joining few tables, and tables had aprox few houndrer thousands
records each. Indexes were ok and they were used in query (tested with mysql
EXPLAIN). Well, we split the query into 2 querys, result of first was id list,
that id list was passed as argument to 2nd query, and the execution was done in
1 second.
Admin
Try this:
declare
cur integer;
val varchar2(2000);
desc_tab DBMS_SQL.DESC_TAB2;
desc_rec DBMS_SQL.DESC_REC2;
colcount number;
begin
cur := DBMS_SQL.open_cursor;
DBMS_SQL.parse(cur, 'select * from yourTable where yourKey=whatever', DBMS_SQL.V7);
DBMS_SQL.describe_columns2(cur, colcount, desc_tab);
for i in 1..colcount loop
DBMS_SQL.define_column(cur, i, val, 2000);
end loop;
DBMS_SQL.execute(cur);
if DBMS_SQL.fetch_rows(cur)>0 then
for i in 1..colcount loop
DBMS_SQL.column_value(cur, i, val);
process_the_column(desc_tab(i).col_name, val); -- 1st parameter is the name of the column, 2nd the value
end loop;
end if;
DBMS_SQL.close_cursor(cur);
end;
Admin
>on older versions of Oracle temporary tables logged...
>and that means your log files really do start eating disk.
>In effect, temp tables weren't as temporary and discardable as
>you would desire. So the use of them was avoided quite heavily.
I second this. Before they nuked rollback segments, etc, the systems at once company I worked at could run out of space at any time. Apparently- oracle's clears its log non-deterministically and doesn't like to rollover, or that's what the DBA's would tell us.
Their solution was frequently to reboot the database so I didn't really trust their technical conclusions...
http://www.lamecode.com/
Admin
AFAIK Oracle never clears the archive log files. After all, after a "big bang", you need them to roll forward transactions from a full backup, so it's up to the admin to backup and delete them. Rollback segments contain all changes made in uncommited transactions; their behaviour is just as indeterministic as your application is. I cannot see any gain from rebooting the database.
Admin
I once worked on a product which did a similary thing. It was a web-based app that kept a comma-separated list of the IDs of all the objects a user had permission to view, and passed this list in to stored procedures, which built up a SQL statement using IN. Execution time for a single query on my development box was around the 2 second mark.
There was no reason why the object ID list couldn't be generated on the fly, so I changed things around a bit. The "exec(...)" was changed into plain SQL, and the IN clause was modified to use a subquery based on the user ID. Execution time now? About 150ms.
Just because you can build and execure SQL queries on the fly inside a stored procedure doesn't mean it's a good thing. SQL has to compile the query and work out an execution plan each time you execute a dynamic statement. Don't even think of going down this route unless your subquery takes a stupidly long time to execute.
Admin
Whenever you build a SQL string you have to worry about SQL injection. This is true whether you are building the SQL string in ASP.NET or PHP or even in SQL.
In cases like this, I put in a sanity check to make sure @list only contains commas, spaces, and digits:
CREATE PROCEDURE GetMessage @list varchar(8000) AS
-- SANITY CHECK
IF @List LIKE [^ ,0-9]
BEGIN
-- raise an informative error
RAISERROR('@List contains unexpected characters!', 11, 1)
RETURN (1) -- bail
END
-- END SANITY CHECK
exec ( 'SELECT * FROM StoreMessage where SessionID in (' +@list+ ' ) ORDER BY insertdate ASC
delete StoreMessage from
(select * from StoreMessage where SessionID in (' + @list+ ') ) as newtable
where ( ImMessage.sessionid = newtable.sessionid and ImMessage.Owner = newtable.Owner and ImMessage.insertdate = newtable.insertdate)')
GO
If your calling code is correctly tightened down, the error should never be tripped.
If someone finds a hole in your calling code, this might save you.
The LIKE check is fairly cheap.
Admin
Q: I mean, didn't it occur to the geniuses that invented SQL that sometimes you want to just pull all records from the table without any criteria?
A: SELECT * FROM customer
Q: And now everytime they add a customer, I have to change this code to add a new Cust_Num.
A: Why not just have a function that takes in the new customer details and does an INSERT statement? You don't need to change code.
Q: If SQL were a real language, it would have a Sum function.
A: SELECT SUM(transactionCount) FROM customer
Admin
Q: What does the word "sarcasm" mean?
A: Please follow this link.
Admin
You cannot get the list of ID via a query , this is not possible. sorry.
So how do you go about that now?
Admin
Another possible reason (one I have encountered) for this problem: retrieving non-relational data from an RDBMS. When your hosting provider [this is a cheap, experimental site] only provides you with a MySQL backend, it's a bit tough to work on syllogistic deep-search metadata analysis [if anyone knows enough of what I'm talking about to call me on that, think "an implementation of LISP in RDF and Prolog"] without directly referencing the node data.
Admin
My guess is that the author was hoping that the database engine does a faster job by utilizing threads when it's given conditions that can be tested in paraller.
Admin
I 100% agree with what you said.
An appendum, because I have seen this rule misunderstood too many times:
"Your data should be driving your logic {snip]"
That DOES NOT mean you should base your code design around how your data storage is designed! Design your business objects independently from your persistance layer. If both look similiar, that's a coincidence that will vanish soon during the software's lifetime.
Decoupling ffs, don't fall into the Domain Driven Design Storage Layer trap!
Best regards, TheseSoftwareArchitectsAreMorons