vba delete query 1

[LucasH]

Hi all,

I have an application built through MS Access 2003 with linked tables to a SQL Server 2005 db. My question revolves around a delete query that is taking a very long time to process.

The table in question has about 5.6 million rows.

I use a vba docmd.runsql statement which goes something like this

"Delete from table where date = #4/13/2010# and company = '0515';"

The delete query will delete multiple rows.

The table contains indexes on date and company and a couple other fields as well but it's still taking a long time to process.

Im wondering if it's ODBC that is slowing it down or if there is something else I can check.

Im also wondering if it would be faster to use a passthrough query to perform the delete and pass the sql statement programatically.

Any thoughts?

 

[gmmastros]

Do you have an single index that includes DATE and COMPANY as part of the same index? A multi-column index may help speed things up considerably.

Also... do you have any triggers on the table that you are deleting from? It could be the triggers that are slowing you down.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[LucasH]

I have just one index on the table and it contains 4 columns

UnitID
Version
FileDate
FileCompany

Is that what you mean by a single index?

I didn't see any triggers on the table, but I was looking in the Management Console under that table and there is nothing in the Triggers folder. Could they be elsewhere?

Thanks!

 

[LucasH]

Hi again,

I changed my code to programmatically update a passthrough query's sql statement and it is night and day faster! Must have been Access getting in the way.

 

[gmmastros]

passthrough query's sql statement and it is night and day faster!

That's good. Are you satisfied with the performance now?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[LucasH]

yes very much so thank you