Home » SQL & PL/SQL » SQL & PL/SQL » Queries slow because table is empty? is this true?
| Queries slow because table is empty? is this true? [message #2261] |
Tue, 02 July 2002 09:28  |
Travis
Messages: 15
Registered: March 2001
|
Junior Member |
|
|
I am building dynamic queries in my JAVA code and have run across a speed issue. We have equal database structures across numerous servers with unequal data. All the tables are the same, but some of the tables may not be relevant to that person so the tables may be emtpy. Since the tables that may be empty are joined using outer joins, there has never been an issue until now. The query at question is as follows...
-----
SELECT DISTINCT lsmls.mlsid,lsmls.mlsbrd,lsmst.bsmnt,lsmls.offmlsid,
lsmst.list_recno,lsmst.lst_co,lsmst.co,lsmst.lst_price,lsmst.city,
initcap(lsmst.county),style,lsmst.in_out,lsmst.fireplace,lsmst.sf,
lsmst.garg,lsmst.yrblt,lsmst.status,lsmst.stno,lsmst.stdir,lsmst.stnam,
lsmst.beds,(lsmst.pbaths + lsmst.fbaths) as baths,lsmst.st,lsmst.listing_no,
initcap(lsmst.twnshp),initcap(lsmst.sch_dist),lsmst.zip,lsmst.vtour,
lsmst.remarks,lsmst.vtour_file,lsmst.pbaths,statuses.status_seq,
to_char(oh_date,'Day, Month DD, YYYY') as to_oh_date,
openhouse.from_time,openhouse.to_time,openhouse.notes,openhouse.oh_recno,loc_desc,
gnams.fnam,gnams.nicknam,gnams.lnam,phones.cmvalue,phones.ext,
gnams.namid,to_char(reduced_dt,'YYYY-MM-DD') as reduced_dt,
to_char(just_listed_enddt,'YYYY-MM-DD') as just_listed_enddt,
to_char(price_reduced_enddt,'YYYY-MM-DD') as price_reduced_enddt,
to_char(pending_enddt,'YYYY-MM-DD') as pending_enddt,
to_char(oh_date,'YYYY-MM-DD') as openhouse_date,
to_char(lst_dt,'YYYY-MM-DD') as lst_dt,
offices.offnam as officeName,offices.addr1 as officeAddress1,
offices.addr2 as officeAddress2,offices.city as officeCity,
offices.st as officeState,offices.zip as officeZip,
offices.namid as officeID
FROM lsmst, offices, lsmls, mlsbrdord, statuses,
openhouse, lsloc, lstwnshp, gnams, phones, lsagents,banners
WHERE (SYSDATE - LST_DT) <= 10 AND
lsmst.status IN ('ACTIVE','PENDING')
AND lsmst.in_out IN ('I')
AND lsmst.sub_ptyp IN ('RESIDENTIAL','CONDOMINIUM','MULTI-FAMILY')
AND (lsmst.in_out='I') AND lsmst.co=1
AND lsmst.list_recno=lsagents.list_recno(+)
AND lsagents.namid=gnams.namid(+) AND gnams.namid=phones.namid(+)
AND phones.gctyp(+)='PH' AND phones.gccode(+)='201'
AND (offices.show_on_web is null) AND lsagents.agside='L'
AND lsagents.prmflg='Y' AND lsmst.list_recno=lsmls.list_recno
AND lsmst.lst_co=offices.offco AND lsmst.lst_offno=offices.offno
AND lsmst.status = statuses.status
AND openhouse.list_recno(+)=lsmst.list_recno
AND lsmst.list_recno=banners.list_recno(+)
AND lsmls.priority_board is null
AND lsloc.loc_id(+) = lstwnshp.loc_id
AND lstwnshp.twnshp_desc = lsmst.twnshp
ORDER BY loc_desc,lsmst.lst_price desc
-------
Without the table BANNERS, its join and the stuff selected from banners (to_char(reduced_dt,'YYYY-MM-DD') as reduced_dt,
to_char(just_listed_enddt,'YYYY-MM-DD') as just_listed_enddt,
to_char(price_reduced_enddt,'YYYY-MM-DD') as price_reduced_enddt,
to_char(pending_enddt,'YYYY-MM-DD') as pending_enddt,
to_char(oh_date,'YYYY-MM-DD') as openhouse_date)
the query returns in 3-4 seconds. with the banners in place, the query takes 15-20 seconds. the question is why... when BANNERS is empty???? shouldnt oracle just skip the join if the table is empty?
No, I can not pull out banners, it is needed for other clients and we are building a 'one size fits all' type of program. There are other places that this works fine... just not with this banners issue.... please help if you can.. thanks
|
|
|
|
| Re: Queries slow because table is empty? is this true? [message #2309 is a reply to message #2261] |
Thu, 04 July 2002 17:10  |
seng
Messages: 191
Registered: February 2002
|
Senior Member |
|
|
The empty table is not ready empty because it still keep the space, i mean "High-Watermark" of table. Sometime, query an empty table is returned slow because of this "High-Watermark". This happens when the user delete record from table with DML(DELETE).
Or use TRUNCATE to delete all data from table, and this will release 'High-Watermark" back to zero data level. Hope this is helping
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 20:43:34 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
