Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 22nd, 2004, 11:04 PM
Registered User
 
Join Date: Nov 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Correlated Subquery Rewrite Needed

$100 paid to anyone who can rewrite this code to run fast: you can use temp tables, whatever. If it runs significantly better I'll send you the $100, so include your address with the rewritten code. If it works, I'll probably have more work for you..
G.

select distinct t.personnum, t.applydate,
round(t.wfctimeinseconds/3600,2) as amount,
substr(c.customdata,1,1) as shift,
(select count(*) from vp_alltotals
where personnum = t.personnum and
applydate = t.applydate and
wfctimeinseconds = t.wfctimeinseconds and
paycodename = 'Shift Differential') as NumShiftDiff,
(select count(*) from vp_alltotals where personnum = t.personnum and
applydate = t.applydate and
wfctimeinseconds = t.wfctimeinseconds and
paycodename in (select name from SF_QUAL_PAYCODES)) as NumPaycodes
from vp_alltotals t,
vp_personcustdata c
where
c.personnum = t.personnum and
c.customdatadefid = 4 and substr(c.customdata,1,1) in ('E','N') and
t.applydate >= t.prevpayperiodstart and
t.applydate <= t.currpayperiodend and
t.paycodename in (select name from SF_QUAL_PAYCODES) and
t.wfctimeinseconds <> 0 and
substr(t.wfclaborlevelname4,1,2) in (select * from SF_QUAL_JOBS)
order by t.personnum,t.applydate





 
Old November 22nd, 2004, 11:40 PM
Authorized User
 
Join Date: Apr 2004
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

What database and database version are you using?

Can you give us a rough idea of the data volumes for each table, each query, and each condition?

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
 
Old November 23rd, 2004, 07:18 AM
Registered User
 
Join Date: Nov 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Justin,
When I get into the office, will send more complete info. However, the db is Oracle 9i and the vp_* are views, not tables. The underlying base tables are on the order of 12,000 - 15,000 rows, and then entire db is probably just a few 100 Mb's or so. If you want the hefty code for the views, let me know, I'll post them or forward them to you.
G.

 
Old November 23rd, 2004, 09:57 AM
Registered User
 
Join Date: Nov 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Here are the rowcounts.
SQL> select count(*) from vp_alltotals;

  COUNT(*)
----------
     33737

SQL> select count(*) from vp_personcustdata;

  COUNT(*)
----------
     95956

db version 9.2.0.4.0

 
Old November 24th, 2004, 02:50 AM
Authorized User
 
Join Date: Apr 2004
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Code for the views would be helpful...

If you could provide an export of the underlying tables and the code for the views/ indexes/ etc., that would be really cool. I hate trying to get query plans for complex SQL statements in forums like this-- it's a lot easier if I can see the data locally.

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
 
Old November 24th, 2004, 02:39 PM
Registered User
 
Join Date: Nov 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Justin,
Recoded it myself! Your interest in the problem was appreciated. If I run into more bad code and don't have the time to do it myself, I might contact you in the future.
Gil






Similar Threads
Thread Thread Starter Forum Replies Last Post
URL Rewrite kumiko Classic ASP Basics 1 April 17th, 2008 01:05 AM
rewrite problem twr195 Beginning PHP 1 March 11th, 2008 12:49 PM
Difference between Correlated Subquery and Subquer ramk_1978 SQL Language 2 June 12th, 2006 05:35 AM
Correlated Subquery part of Select List gstanden SQL Language 1 November 24th, 2004 07:25 AM
SQL Select problem (subquery needed??) deian SQL Language 7 February 3rd, 2004 02:40 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.