What would be faster? 2 Separate queries or 1? 1

[kjv1611]

Related to thread705-1488171

In that thread, we ended up with the code:

Delete [TA].[*]
FROM [tblAll] AS TA
WHERE ([TA].[Case Number] IN (SELECT [HD].[Case Number]
                              FROM [tblHist_Data] AS HD
                              WHERE ([TA].[Date] = [HD].[Date])));

Well, I realized that it would be best to first get rid of the duplicates in the same table, so I setup this query to run first:

DELETE [tblAll].*
    FROM [tblAll]
    WHERE [tblAll].[Case Number] Is Null

So, I run the second query listed here first, then I run the first query. That sped up that process by HEAPS. To me, it seems that this may be the fastest method, but I wanted to ask around here.

Would it be more efficient to run both queries in one, since they are both deleting records from "tblAll"?

The reason I'm thinking that the "null" deletion query being run first is fastest is that there at LOTS of empty records in that table, each time it is filled. Sadly, it's b/c of the way the original data is formatted.

Any thoughts/suggestions on this?

--

"If to err is human, then I must be some kind of human!" -Me

 

[JoeAtWork]

The second query is much more efficient because it is simply checking if a field is Null.

The first query is much slower because for each record it must do a query against another table.

Therefore, the more records you can eliminate before running the first, slower query the better.

So keep doing what you are doing.

 

[lameid]

I agree with Joe as long as you are using that method. I was thinking of something that was recommended in the other thread...

DELETE [tblAll].* 
FROM [tblAll] INNER JOIN [tblHist_Data] 
ON [tblAll].[Case Number] = [tblHist_Data].[Case Number] 
AND [tblAll].[Date] = [tblHist_Data].[Date]

That should run. The only differences in my post and PHV's in the other thread is that I used spaces around the "=" and square brackets around the table names. Neither of which should matter unless you are substituting table names with something that requires the square brackets. Are you sure that doesn't run?

 

[kjv1611]

JoeAtWork,
Thanks for the advice.

lameid,
I don't remember, but I can try it again just to see. If it ends up working more efficiently than the other, then I'm all for it!

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

lameid,
Your SQL is returning this error:

Run-time error '3086':
Could not delete from specified tables.

--

"If to err is human, then I must be some kind of human!" -Me

 

[lameid]

I searched on that error...

Try...

DELETE DISTINCTROW [tblAll].* 
FROM [tblAll] INNER JOIN [tblHist_Data] 
ON [tblAll].[Case Number] = [tblHist_Data].[Case Number] 
AND [tblAll].[Date] = [tblHist_Data].[Date]

The other issue that I saw is that Primary Keys may not be in both tables. Now that I think about it these should solve the same problem for the query. Keep us posted.

 

[kjv1611]

I'll give it a shot.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Yes, that DID work! Thanks a ton! Works as correctly, yet works more efficiently than the other method!

How could I add the NULL check SQL into that SQL so that I can just run one query, or is it best to just keep them separate still?

What do you think?

--

"If to err is human, then I must be some kind of human!" -Me

 

[lameid]

I think you will have to run the other query separately to maintain performance.

For future people looking was it the distinctrow or primary key that fixed your problem?

 

[kjv1611]

distinctrow

--

"If to err is human, then I must be some kind of human!" -Me