Parallel Query Execution

Does anybody have any practical experience with Parallel Query
Execution and partitioning tables just wanted to know if we are aware
of any practical issues /drawbacks

wrote in message
news:578de3e9-de80-45eb-99c3-cf1550ea0446-at-27g2000hsf.googlegroups.com...
> Does anybody have any practical experience with Parallel Query
> Execution and partitioning tables just wanted to know if we are aware
> of any practical issues /drawbacks

I've used it at several sites. There are no particular drawbacks that I've
encountered. One does need to take care to choose appropriate partitioning
rules to get full benefit otherwise you will experience performance about
the same as without partitioning.

My favorite anecdote is from a job I did last summer. I decomissioned a
system for a customer who had seven years worth of data on a high-end
Alphaserver with a SAN. They wanted to keep the data available so I
suggested porting it to a virtual PC on one of their innumerable
server-class PCs. The machine they chose had a single very large capacity
disk drive which was just big enough for the database.

About a month later I got a call complaining that it worked but the reports
were dog-slow. (Really? Whoda thunk?) Anyway, I consented to see what I
could do and a couple of hours of poking about showed it would be possible
to partition the tables to speed up the reports. A couple of hours later
the big tables were suitably partitioned and I turned on parallel query
execution.

Presto! The virtual PC with one disk now out-performed the original
Alphaserver running the same report by 40-50%. (If I'd had four or five
disks to play with it would probably have been 200-300% faster.)

Having said that, the next job I went to had such unbelievably foul code it
was incapable of exploiting any server-side enhancements without doing a
complete re-write. So be careful what you claim is possible. You might
find you can't exploit partitioning at all.

Roy
--
UK Ingres User Association Conference 2009 will be held on Tuesday June 9,
2009
Go to http://www.iua.org.uk/join to get on the mailing list.