I need a better comparison query 1

[SQLBill]

Using MS SQL Advanced Server 2000

I currently have a query where I use "NOT IN" as part of the WHERE clause. This is probably inefficient since I have hundreds of items that are in the "NOT IN" statement. My query checks, on a daily basis, whether any new "items" have been added to the database and returns those items. Using a phone book as an example here's what I am doing:

I need to know any new phone numbers that are added to the phone book database. That way I can add the name and address. So I have a script:

select distinct phonenumber
from directory
where phonenumber not in ('5550001', '55500002', '5550003', '5550004', ..........)

When I get a new phone number, I add it to the NOT IN statement. I also then add it to the phonebook with the users name and address (remember, this is just a example - not what I am really doing).

Is there a more efficient way to compare what was in the database yesterday with what is in it today and tell me what is new today?

One option I thought of was make two temporary tables, one with the distinct phonenumbers from every day but today and one with the numbers from today and then compare the two. But I haven't quite figured that out yet (I'm new to SQL Server programming) and I'm not sure that's the most efficient way to do it.

By the way, I'm using T-SQL and this will end up as a job in Enterprise Manager.

-SQLBill

 

[bperry]

Sometimes in this situation you want a DateAdded column in your table. Then you could say:

Select * from Directory where DateAdded > @parmDate

(or whatever)

If this query gets run a lot, perhaps consider putting an index on DateAdded.

Would an appraoch something like this work for you?

 

[tlbroadbent]

You could also create a table with the values in it and use the table instead of the list to exclude phone numbers. Make sure both tables are indexed on phonenumber.

Select Distinct d.phonenumber
From directory d
Where Not Exists
(Select * From tblList l
Where l.phonenumber=d.phonenumber) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[SQLBill]

bperry,
Thanks for your suggestion, however do to the nature of what we do and what the database is used for, it can't be changed. We store information that can be used in legal investigations. Since the information you suggest would be an addition to the database and not the original information, the data would legally be corrupt and unusable.

terry,
again same problem unless I use temporary tables or create a whole new database.

I currently have 340 different 'items' in my NOT IN statement and it doesn't seem to be impacting my database yet. I would just keep doing it my current way, except that I expect the number of 'items' to reach 2000. That still may not create any impact, but I didn't want to wait until it did and then try to solve the problem.

SO, right now it looks like I'm either going to have to keep doing it my way or create a new database instance on my cluster to do this job.

-SQLBill

 

[tlbroadbent]

SQLBill,

You can easily create the table in another database and use it in the query. I would do that rather than maintain a huge list. Using another table ceratainly makes sense in a relational database.

Select Distinct d.phonenumber
From directory d
Where Not Exists
(Select * From MyOtherDatabase.dbo.tblList l
Where l.phonenumber=d.phonenumber) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[NickISD]

Sqlbill,
Having done something similair here in the uk with post codes I agree with Terrys suggestion. It is far more manageable and your NOT IN list could be open to a mis types number that could be missed.

Nick

 

[williamu]

If i've got it right then

SELECT dir.*
FROM directory dir
WHERE NOT EXISTS
(SELECT dir2.* FROM directory dir2
WHERE dir.telno = dir2.telno)
UNION
SELECT dir.*
FROM directory dir
WHERE NOT EXISTS (SELECT dir2.*
FROM directory dir2 WHERE dir.telno = dir2.telno)

so if nothing is returned then there are no new rows.

HTH

William
Software Engineer
ICQ No. 56047340

 

[tlbroadbent]

William,

That query will never return any rows. It selects from the same table in each query and sub-query. There will never be an occurence of a teleno in directory that doen't exist in directory. I don't understand the purpose of the query. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[williamu]

Ooops I got the impression it was lookin up it's own a&^%.

But having said that with additional where conditioning the theory of it is 100% sound.



William
Software Engineer
ICQ No. 56047340

 

[SQLBill]

Terry,

Thanks for the suggestion of an additional database and table....that does seem to be the best way to go. Now if only the legal issues I have to deal with will allow me to add a database and table.

But that's my issue to deal with.

-SQLBill

 

[LarryWFox]

Assuming each new phone number is entered minus its address, write your SELECT as "WHERE Address is NULL". If the address is unknown can you enter "N/A" or anything else, e.g. a space, to make the Address no longer NULL. At the worst make your "NOT EXISTS IN (...)" the list of phone numbers where the Address is unknown. This hopefully will make the exclusion list much smaller.

The above is a kluge. The best solution has already been given -- request the "legal" owner/originator of the data to insert a "DateAdded" field into the table.

 

[SQLBill]

Larry,

As I said in my initial post...the use of a phone book was just an example. My actual information is proprietary and is used for legal investigations. So, I can't state what type of data is in my database. I am also the 'legal' owner of the data base...the legal issue is that the data in the database can be used for criminal investigations and MUST not be changed from the way it is received (UPDATE, DELETE are big NO-NOs in the database.) So, adding a new field 'corrupts' the data for legal usage. Consider this: (example only) if my database was for a police department and the data was used in court, if any data was changed or added to the original entry, the data would not be admissable in court.

About the only suggestion that would work for me is creating a new database and table and 'join'ing the two in my query. But I have to find out if that is allowed under the rules I have to follow.

-SQLBill

 

[LarryWFox]

I'm a little confused. You original post stated, "I need to know any new phone numbers that are added to the phone book database. That way I can add the name and address." If you are going to "add the name and address", you are changing something.

It appears you can not change the original table data, so you must be changing (name and address) of some secondary table.

Either you need well behaved data, i.e. all new records have a NULL name and/or address (then when you "add" in your data its not NULL any more); or, you will have to build your own table to track the new additions.

Create your table with the same key columns as the original. Select all of the original records that do not exist in your table. This is your list of "new" records. Once you have your list of new entries in hand, insert their associated key values into your table. This prevents them from being selected again in the future.

Using your phone number analogy:

1. Create your table, PhoneDirectory.

2. SELECT DISTINCT phonenumber
FROM directory d -- I assume "directory" is the Original table
WHERE NOT EXISTS
( SELECT phonenumber FROM PhoneDirectory pd
WHERE pd.phonenumber = d.phonenumber )

3. As you add the name and address, insert the corresponding phone number into your table, PhoneDirectory.


PS: Call me at 803-929-0000 ext 369, if your need real-time help. I'm here from 8am - 6pm, est.

 

[SQLBill]

Larry,

Sorry to be confusing....sometimes it's tough to come up with an example that explains what I'm trying to do without giving away what I do for a living or how our database/tables are set up. You and Terry gave me great ideas to try out....mainly I'm going to have to try a second database instance. Probably a more valid example would have been to compare it to a home security company. Let's say I have a database that keeps track of clients. I need to 1) make sure that I am aware of when a client is added 2) make sure that client is added to correct 'monitoring' office. I take care of issue 1 by using a NOT IN list and comparing the distinct information in the database to those in the list. Then when there is a new item in the database that is not in the NOT IN list, I add it to my NOT IN query and make sure that the item is being monitored properly/added to the proper places outside the database.

I guess that's closer to what I do and I also need to report the new items to higher ups.

Sometime proprietary information s**ks.

-SQLBill