Execution plan is different for SQL 7 and 2K

I am using a very large table. If I try a SQL with 2K it
never finishes. With SQL 7 it works fine. I found that SQL
7 using Table spool/Lazy spool but SQL 2K is not. How can
I mimic SQL 7.
I have rebuilt all indexes for SQL 2K. usinf SP3A. Any
ideas?"Aziz" <anonymous@.discussions.microsoft.com> wrote in message
news:1d8f01c4a1b7$372ba1f0$a401280a@.phx.gbl...
> I am using a very large table. If I try a SQL with 2K it
> never finishes. With SQL 7 it works fine. I found that SQL
> 7 using Table spool/Lazy spool but SQL 2K is not. How can
> I mimic SQL 7.
If you try what?
> I have rebuilt all indexes for SQL 2K. usinf SP3A. Any
> ideas?|||Hi
We need to see the DDL and DML to really help you.
Is the DB in 7.0 or 8.0 compatability mode.
Regards
Mike
"Aziz" wrote:
> I am using a very large table. If I try a SQL with 2K it
> never finishes. With SQL 7 it works fine. I found that SQL
> 7 using Table spool/Lazy spool but SQL 2K is not. How can
> I mimic SQL 7.
> I have rebuilt all indexes for SQL 2K. usinf SP3A. Any
> ideas?
>|||I am using same machine. Default install for both. Here is
my SQL:
SELECT
'B', /* Deleted record*/
a.ssn_nr,
a.last_nm,
a.trf_no, --rsmith added
line for PIR sKrish/18
a.wc_rpt_id,
a.val_rptd_wages_amt,
a.val_ee_empe_cntrb_amt,
a.val_er_empe_cntrb_amt,
a.val_empr_cntrb_amt,
a.pd_excess,
a.val_fsp_wages,
a.val_fsp_cntrb,
a.sep_date,
a.sep_type,
a.svc_crdt_days,
a.val_vol_ee_empe_cntrb_amt,
a.val_vol_er_empe_cntrb_amt
FROM be_wc_mbr_rcd_h a, be_busn_dt b
/* D@.p.02 WHERE convert(CHAR(10), a.rcd_crt_ts, 111) =CONVERT(CHAR(10), b.busn_dt, 111) */
WHERE datediff(day, a.rcd_crt_ts, b.busn_dt) = 0
AND a.rcd_crt_nm not in
('VRPD', 'LDFL', 'PRPD', 'GEPR', 'PSOI')
AND not exists (SELECT 1
FROM be_wc_mbr_rcd c
WHERE c.wc_mbr_rcd_id = a.wc_mbr_rcd_id)
AND a.rcd_crt_ts = (SELECT MAX(rcd_crt_ts)
FROM be_wc_mbr_rcd_h d
WHERE d.wc_mbr_rcd_id =a.wc_mbr_rcd_id)
Big table does not have clustered or PK. It works fine
with SQL 7. SO how can I make it as it is in SQL 2K.
Aziz
>--Original Message--
>Hi
>We need to see the DDL and DML to really help you.
>Is the DB in 7.0 or 8.0 compatability mode.
>Regards
>Mike
>"Aziz" wrote:
>> I am using a very large table. If I try a SQL with 2K
it
>> never finishes. With SQL 7 it works fine. I found that
SQL
>> 7 using Table spool/Lazy spool but SQL 2K is not. How
can
>> I mimic SQL 7.
>> I have rebuilt all indexes for SQL 2K. usinf SP3A. Any
>> ideas?
>.
>