Select From Select Results

[Deltaflyer]

I am trying to run a query using BETWEEN, unfortunately the query i am running is timeing out ( it is a big query on a small dev machine ). I know that the query will run on the live machine but i think i may have found an even faster way of executing the query.

The simplified version of my coding is :

Select * From Table Where Date Is Between 'startdate' and 'enddate'

where startdate and enddate are valid date fields. Through query analyzer this is taking about 2mins to complete, however, if i try :

Select * From Table Where Date >= 'startdate' it returns in 2-3secs, equally

Select * From Table Where Date <= 'enddate' this returns in 2-3secs.

I know that i can subquery using IN but cannot get it to work.

My combination query is :

Select * Where Date >= 'startdate'
IN (Select * From Table Where Date <= 'enddate')

Is this the correct way of doing this, or could anybody please show me the correct way of doing this.

Thanks, Deltaflyer

DeltaFlyer - The Only Programmer To Crash With Style.

 

[TomSark]

Do you have a clustered index on the date column? A clustered index would speed up this query...

Tom

 

[Deltaflyer]

yes Tom, originally there was not and the query was taking apr 7mins to return.

unfortunately, it is just the sheer mass of data that i am trying to search through, apr 300,000 records, and the pitiful development machine ( it's a PII 200ish Mhz 64MB RAM )that means BETWEEN is taking so long and timeing out. DeltaFlyer - The Only Programmer To Crash With Style.

 

[crystalized]

Correct me if I am wrong anyone but when you use in you have to do something like

Select * where date>='startdate'
and recordID in (Select RecordID from table where date<='enddate')
Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[TomSark]

Another thing I would check in order to reduce the amount of I/O to speed this up...

Have you run DBCC SHOWCONTIG to ensure that your leaf pages of your clustered index are as full as possible so that the amount of page reads that SQL Server has to process are minimized?

Two things I would look at from the SHOWCONTIG output are

- Scan Density [Best Count:Actual Count].......:
this ratio would be better if it were closer to 1 (20:20, etc.)

- Avg. Page density (full).....................:
this percentage would be better if it were closer to 100%

These values, of course, are preferable when you are reading data. They would adjust down if you are in an OLTP environment.

Tom

 

[Deltaflyer]

Crystal,

Cheers mate. The finished SQL :

SELECT op.id, op.orderso, op.cFunction, op.custref, op.details, cust.cname, op.aendloc,
op.bendloc,op.crd, op.apptcdd, op.prodcode, op.ccttype, op.cc, op.rent, op.chasedate,
op.remarks, op.actionreq, op.chaserid

FROM tbl_mas_pipeline as op

JOIN tbl_peo_people AS people ON op.chaserid = people.id
JOIN tbl_mas_custcleanse AS custc ON custc.spid = op.spid
JOIN tbl_par_custuserjunction AS cuj ON cuj.custid = custc.custid
JOIN tbl_mas_customers AS cust ON cust.id = custc.custid

Where (op.apptcdd <= '15-NOV-00') and cuj.userid = 1040 and op.id in

(SELECT op.id FROM tbl_mas_pipeline as op

JOIN tbl_peo_people AS people ON op.chaserid = people.id
JOIN tbl_mas_custcleanse AS custc ON custc.spid = op.spid
JOIN tbl_par_custuserjunction AS cuj ON cuj.custid = custc.custid
JOIN tbl_mas_customers AS cust ON cust.id = custc.custid

Where (op.apptcdd >= '01-JAN-90') and cuj.userid = 1040)

returns in apr 2secs. That is exactly what i was looking for, my dev machine doesn't know what hit it.

Thanks for all help, DeltaFlyer

p.s. Tom your idea on clustered indexes, when i rechecked i found one that i had missed, so thanks for that too.[/green]
DeltaFlyer - The Only Programmer To Crash With Style.