and IMPORT_OCR_ADMIN_2000_1 is Table A

has the field called GUID?

ok , maybe I missed something.

Is IMPORT_OCR_ADMIN_2000_1 Table A or Table B?

You are missing this part which will elimintate the records that are in table B.

where cd.GUID is null and ocr.DataSource_ID = '24'

what you have should work but using a join will be faster and cleaner

SELECT ocr.*

FROM IMPORT_OCR_ADMIN_2000_1 AS ocr

left join IMPORT_CD_D as cd

on '2OCR_ADMIN_'+REVIEW_DATE+PAT_ID+Convert(varchar(9),ocr.Form_Id) = cd.GUID...

karthikeyan (4/24/2008)

what will happen internally is

Declare @Nm varchar(25)

select @Nm = 'Age'

select @NULL = count(*) from #t1 where @Nm is null

it means select @NULL =...

I am guessing that the differnce may be rounding (my columns are decimal(9,2)) or averaging multiple rows at once vs averaging 3 rows and then using the resulting average to...

I ran mine with >= and and < and ended up with 17 passes resulting in 45351 rows again I see a difference in wellnum1001 evalnum3 which...

ok, I am changing it and running it again. All of the other calculations look like they match and it is definitely much faster 1m45s to 12m44s on my...

Your new method comes up with less rows in the result than mine on t my test data. I am running a compare now to see where the differences...

I forgot to mention that I played around with a few indexing schemes and found that no indexes worked the fastest. Likely because they slow the inserts more than they...

your xml did not show up

ok here is the winner. 12 minutes for 65K rows.

here are the tables

CREATE TABLE [wells] (

[HOLEID] [varchar] (25) NOT NULL ,

[EvalNum] [int] NOT NULL ,

[Seq] [int] NULL ,

[TopDepth] [numeric](9,...

To build the sequence can we order by wellnum,eval,topdepth or is there more to it?

I am getting ready to leave for the day but I will take a look at this tomorrow. I have been playing with the indexes and can't seem to...

The whole thing took 29 passes over 55 minutes for the 65K rows. I'll start looking at the performance peice of it. The first 2 passes took a...