Is it my query or the hardware?

[williamkremer]

Hi all -- this query hangs forever, so I don't know if it is the hardware (512 mb RAM) or the query.
********************************
select count(*) from contact1 c1
where c1.accountno not in (select accountno from conthist)
and c1.accountno not in (select accountno from cal)
and c1.createon not in (select createon from contact1 where c1.createon not like '%2004%' or c1.createon not like %2005%')
********************************

Am I creating one of cartesian suckers?
Thanks in advance..
Bill

 

[vongrunt]

Ouch... query, very likely.

Problems are basically about a lot of negative logic in queries (NOT this, NOT that) and self-join (contact1 table used twice) on non-optimizable expression (LIKE '%blah%').

Some questions:

Is contact1.createon datetime or char/varchar?
Is there any index on conthist.accountno and/or cal.accountno? contact1.createon?
How many rows has each table involved in a query, and how many rows query typically returns.
What is the purpose of this part:

and c1.createon not in (select createon from contact1 where c1.createon not like '%2004%' or c1.createon not like %2005%')

?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[williamkremer]

Sorry -- didn't mean to cause you pain! c1.createon is datetime. accountno is indexed in c1 and conthist and cal. (the index structure of conthist and cal involves other fields as well). Createon is not indexed. I expect this query to return approx. 40,000 out of a total of 118,000 in contact1. And lastly, c1.createon not in(etc, the last part of your question) is to eliminate records that were created during 2004 and 2005 from the total count. Make sense? Thanks.

 

[gmmastros]

To eliminate records created in 2004 and 2004...

Where Year(C1.CreateOn) Not In (2004,2005)

Or..

Where Year(C1.CreateOn) < 2004

The performance here should be better than what you are currently doing. Hope it helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[williamkremer]

Thanks George. I'll try that. There must be a reason for your asking about the indexing on the fields. Is there something else that you were thinking that could improve efficiency here? My T-SQL is self-taught, an I have never had a mentor. Methinks you know something that I also should.

 

[williamkremer]

George -- I used: where year(c1.createon) < '2004' and it rips.

 

[SQLBill]

To eliminate records created in 2004 and 2004...

I think George meant "To eliminate records created in 2004 and 2005...

And it was vongrunt that asked about indexes. Datetime columns are usually good choices for indexes. Indexes make it much easier to find values. Analogy...imagine a dictionary without an index. Whenever a new word is created, they just stick it at the end. Now try to find the word you need. You would have to look through EVERY word to find the one you are looking for. Imagine trying to find several words. You would almost have to look through every word of the dictionary over and over. An index points to where the data is.

-SQLBill

Posting advice: FAQ481-4875

 

[vongrunt]

> c1.createon is datetime.

Here are some expressions doing the same thing ranked from worse to better performance-wise:

(1) ... where c1.createon not like '%2004%' or c1.createon not like %2005%'
(2) ... where year(c1.createon) not in (2004, 2005)
(3) ... where not (c1.createon >= '20040101' and c1.createon < '20060101')

Since there is no index on createon, difference doesn't mean (so) much... but (1) is plain bad even if you have one.


> accountno is indexed in c1 and conthist and cal. (the index structure of conthist and cal involves other fields as well).

Exact order of columns covered by compound index is important. In this case index on (accountno, blah) is way better than index on (blah, accountno).


> And lastly, c1.createon not in(etc, the last part of your question) is to eliminate records that were created during 2004 and 2005 from the total count.

Then second reference of table contact1 is not necessary, as George already explained. Use this instead:

select count(*) from contact1 c1 
where c1.accountno not in (select accountno from conthist) 
and c1.accountno not in (select accountno from cal) 
and <insert (2) or (3) here>

You can also replace NOT IN() stuff with LEFT OUTER JOIN checks.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[oakgrove]

Have you tried breaking it down to one part, see if it runs the adding another, etc.


Oakgrove Computer Grouper
Lansing, MI

 

[gmmastros]

SQLBill,

Thanks for setting me straight. I did have a typo with the duplicate 2004's. And it was vongrunt that asked about the indexes. Not me.

William, I'm glad the query is now 'rippin' for you. You used... "where year(c1.createon) < '2004'" If I'm not mistaken, the Year function returns an integer, and you have apostrophes around your 2004. You may be able to squeeze another millisecond or 2 if you use... "Where Year(c1.creaton) < 2004" (Without the apostrophes).


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[williamkremer]

I love tek-tips!