November 4, 2003 at 8:27 am
Sorry the following query i put in MS ACCESS Section in this forum, but i didn't get any proper reply please see this.....
I am executing the following query for deleting duplicate records from a table
Delete from TAB_Ex where not ID=(Select Max(ID) from TAB_Ex T where T.Name=TAB_Ex.Name)
In the above query TAB_Ex is the table name, ID is Autonumber (Unique id) and Name represents Chat field which is having duplicate entries
My problem is when i am executing this query, it is taking around 10 minutes to complete the query execution in MS Access
But when i run the same qury in SQL Server2000 there is no problem.
The table contains around 12000 records and out of 4000 records are duplicate
what could be the reason?
Is there any other alternatives for deleting duplicate records in ACESS/SQL SERVER ?
November 4, 2003 at 8:31 am
I hate access and think it is a form of a virus, however....
You might try forming a temporary table of of the list of duplicates then deleting them all at once. A lot of things in Access seem to get slower as the queries get complex, whereas SQL has a much better optimizer.
November 5, 2003 at 12:30 am
Sqlserver is an advanced database engine !
Access is one of the ancestors. If you want to optimize it you can try to apply the same guidelines you would use with sqlserver, but you will encounter issues where you'll have to use an other approach to solve them.
In this case, maybe a materialised "temp"-table with the max for each id can solve the problem.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 5, 2003 at 6:15 am
Access is a wonderfull client application! (far superior to VB6). BUT there are loads of problems with queries on tables linked to SQL Server - some bugs in the drivers I guess. But the easy way out of your problem is to run the query as a pass thru!
P
November 5, 2003 at 6:28 am
Now there a litle bell ringing somewhere...
I didn't realize you were using linked tables from access to sqlserver. In that case, access usualy will try to solve the query by itself (parsing and execution).
Like alphaindex mentioned, run it as pass thru.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply