Problem with the query timeing out

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with the query timeing out

Author

Topic

pbourgui
Starting Member

3 Posts

Posted - 2007-07-09 : 16:43:45

I have several tables with over 300,000 records in each table from which I have to retrieve the values from two fields. These tables once created do not grow but the number of these tables grows as time go by, soon we will have well over 100 tables. The information in it is called to be modified until each records have been reviewed manually. Once all records have been verified / modified these two fields are the only one that are important so I want to copy them into one table which will become my master table therefore this table is called to grow quite a bit. The information in one of these two fields is unique throughout this master table and the second one is a number between 0 to 9. I am trying to add the distinct fields from the second table 674000 records to the result from the first one making sure that the distinct value is not repeated from the first table. I have attempted with this query but keep on receiving a timeout error.

insert into [table_a]
(value1,value2)
select distinct [table_b].value1, [table_b].value2
from [table_b] left outer join
[table_a] as [table_1] on [table_b].value1 <> [table_1].value1

What am I doing wrong ?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-09 : 16:50:37

I didnt understand your join condition. Can you provide some sample data from each table and the result you are expecting to insert into the other table?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

pbourgui
Starting Member

3 Posts

Posted - 2007-07-10 : 05:50:35

The program is used to categorise pictures using their MD5 value (a 32 digit hex number representing the digital file). Each client get their own table of all their pictures and each pictures is then analysed individually and categorised using a number of 0 to 9. We currently have over 40 promoters who will supply us with pictures but we want to make sure that 1) the picture was not already supplied to us and secondly if we already have categorised the picture we want to keep the picture in the same category. So by using the MD5 of the picture we can make sure the same picture is not categorised twice.

In short, what we then have is a table supplied by the promoter containing 1000s of pictures we categorise them using the MD5 value as unique identifier for each picture. Once categorised we don’t need any further information other then the MD5 and the category. When a new shipment comes in we obtain the MD5 for each pictures and we compare them with what we have already in the master table to see if the pictures were viewed by us in the past, if they were each picture is then being categorised automatically by what we did in the past using the second field in the master table.

My problem is a time out error when I try to add the new (not previously categorised) pictures to the master table because we want to have only unique MD5 in our master table. I must point out that the MD5 is my primary key in my master table.

The join condition is to make sure that I verify every MD5 in the new table against every MD5 in the master table to ensure that it does not already exist. If it does not exist then its added.

pbourgui
Starting Member

3 Posts

Posted - 2007-07-11 : 16:05:02

Thanks

I have decided to create a series of 5 queries to replace this one - which in the long run will ensure a better data quality control.

I also found out this afternoon that I could use the exist command in my query to accomplish the same thing.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources