Home » SQL & PL/SQL » SQL & PL/SQL » Make SQl purposefully RUN SLOW
Make SQl purposefully RUN SLOW [message #241966] Thu, 31 May 2007 04:11 Go to next message
aapnz
Messages: 1
Registered: May 2007
Junior Member
hi.
iam fetching data from 4 tables. i wanna make it slow by purpose without use of any functions or sleep(). please help

thanks
Re: Make SQl purposefully RUN SLOW [message #241974 is a reply to message #241966] Thu, 31 May 2007 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use several times the same query and link them with "union".
Force nested loop with full scan without any use of index.

Regards
Michel
Re: Make SQl purposefully RUN SLOW [message #241975 is a reply to message #241966] Thu, 31 May 2007 04:50 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
It's hard to answer without knowing :

What's the purpose of this?

How much slower? A few percent slower or a few times slower?

Why can't you use a function with dbms_lock.sleep() in it?

What Oracle version?

What OS?

Does the returned data has to be correct or could the query be altered to include a few million redundant lines by joining to something else?



Re: Make SQl purposefully RUN SLOW [message #242119 is a reply to message #241966] Thu, 31 May 2007 13:09 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Don't use any joins so you will get a 4 table Cartesian product.
Re: Make SQl purposefully RUN SLOW [message #242144 is a reply to message #242119] Thu, 31 May 2007 14:56 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Nice contest! Wink
Would it be a problem if other users suffer from the consequences of this process?

If no other users/processes involved than:
- add ridiculous additions everywhere in the where clause, like ||'', +0 etc. to make sure no index can be used.
- Move your database to the smallest, oldest PC you can find and run the query there.
- Join several time to the biggest table in your database without actually using the data in your select and add a distinct around the result.
- Create another database, create a databaselink and select everything through that (please note not to add a remote_site link).
- Combine all of the above.



Re: Make SQl purposefully RUN SLOW [message #242147 is a reply to message #242144] Thu, 31 May 2007 15:02 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Switch off the turbo button on your 286; take out one of your two memory chips so that you run Oracle on only 1 MB RAM. Build a dam on the power cord to make the electric current flow slowly.
Re: Make SQl purposefully RUN SLOW [message #242149 is a reply to message #242144] Thu, 31 May 2007 15:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add a function that do complex operations to always return 0 or null that you will add or concatenate to something or to nothing.
This is a good and endless one.

Regards
Michel
Re: Make SQl purposefully RUN SLOW [message #242150 is a reply to message #242149] Thu, 31 May 2007 15:23 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Michel, OP asked that no functions be used, silly Wink
Re: Make SQl purposefully RUN SLOW [message #242185 is a reply to message #242150] Thu, 31 May 2007 23:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
[start flamewar]Migrate to SQLServer or mysql and add 200 concurrent users[/start flamewar]

[Updated on: Thu, 31 May 2007 23:52]

Report message to a moderator

Re: Make SQl purposefully RUN SLOW [message #242424 is a reply to message #242185] Fri, 01 June 2007 22:49 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Isn't that [flamewar] ... [/flamewar]???

A bit provocative considering the host database of this forum.

Ross Leishman
Previous Topic: Indexes
Next Topic: SQL Performance question
Goto Forum:
  


Current Time: Sat Dec 03 03:37:51 CST 2016

Total time taken to generate the page: 0.11420 seconds