Speed up Delete Query on SQL Server table (1 Viewer)

[jco23]

I recently migrated network-linked tables to a SQL server, and select queries run much quicker now. however, delete/append queries take quite some time (20+ minutes to run a standard delete query to remove 6000+ records).

i've read that I may be able to increase the speed by creating a delete query via pass-through query, but I'm a little stuck on the correct logic.

I've tried creating a PTQ using, "delete * from [mytable];" but to no avail (however, when replacing delete with select, the query executes just fine).

this query executes via button press, so I'm wondering if I need to adjust some VBA logic as well.

additional info: i'm simply looking to clear out the entire contents from mytable, and re-populate it with an imported spreadsheet (the spreadsheet gets imported just fine into a temp table, and then an append query runs immediately after)

can someone please assist?

thanks!

 

[theDBguy]

Hi. A pass through query means you don't refer to linked tables. Instead, you specify the connection string in the ODBC property of the query. Is that what you did?

 

[Isaac]

I've tried creating a PTQ using, "delete * from [mytable];" but to no avail (however, when replacing delete with select, the query executes just fine).

That's because you need to use T-SQL syntax. You cannot just paste Access-syntax SQL into the PT query view.
In this case the mistake is:

Delete *

In t-sql, the correct syntax is either:

Delete From Table

, or, (what I use), just

Delete Table [Optional Where]

One more note on that. If you're sure you only want to empty the table with no Where clause, you can just use:

truncate table [tablename]

Lastly, since you are now programming in an entirely different code language, with the t-sql wrapper, there are tons of possible ways to optimize your delete query. Most of those ways are going to follow the same principles that would speed up the Select version of the query, but one other thing may be deleting in batches. Info on that: https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes
This last thing turned out to be very handy for me in my last job when performing large Delete operations. However, this is relevant when deleting with a Where clause. In your case you might as well just use Truncate.

 

[jco23]

Hi. A pass through query means you don't refer to linked tables. Instead, you specify the connection string in the ODBC property of the query. Is that what you did?

thanks for the quick reply - yes, I did. i'm somewhat familiar with writing PTQ. I was merely stating that the old delete query was tied to a linked table.

 

[jco23]

That's because you need to use T-SQL syntax. You cannot just paste Access-syntax SQL into the PT query view.
In this case the mistake is:

Delete *

In t-sql, the correct syntax is either:

Delete From Table

, or, (what I use), just

Delete Table [Optional Where]

One more note on that. If you're sure you only want to empty the table with no Where clause, you can just use:

truncate table [tablename]

Lastly, since you are now programming in an entirely different code language, with the t-sql wrapper, there are tons of possible ways to optimize your delete query. Most of those ways are going to follow the same principles that would speed up the Select version of the query, but one thing may be deleting in batches. Info on that: https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes
This last thing turned out to be very handy for me in my last job when performing large Delete operations.

i'll give that a try.... thx!

would there be a quicker way to run the append query, or am I stuck on that slowness?

 

[Isaac]

would there be a quicker way to run the append query, or am I stuck on that slowness?

Need more information on the Append query.
Are you running that as:

1. Using MS Access query syntax (meaning you are appending into a LINKED TABLE but still using MS Access as the engine-not pass through), or
2. Running this entirely as a pass through query?

 

[jco23]

Need more information on the Append query.
Are you running that as:

1. Using MS Access query syntax (meaning you are appending into a LINKED TABLE but still using MS Access as the engine-not pass through), or
2. Running this entirely as a pass through query?

I would like to append data from a local table to a SQL server table. my current append query has the standard logic, but takes several minutes to append 6000+ records. I have not set up the append query via PTQ.

side note, using delete mytable worked. not sure which is better between truncate vs. delete, but it worked instantaneously.

 

[Isaac]

not sure which is better between truncate vs. delete

Well, in cases where you are sure you want to empty the table with no turning back, Truncate Table is almost always preferred. Truncate table creates no "logging" of the operation. Logging the operation generally increases the clogging of TempDB (which is something to avoid when possible), and, logging slows things down. In cases where I wanted to empty a table completely, I'd always use Truncate Table. The rest is a different discussion. Glad it worked for you!

I would like to append data from a local table to a SQL server table. my current append query has the standard logic, but takes several minutes to append 6000+ records. I have not set up the append query via PTQ

If you were to set up this query as a pass through query, you'd have to construct SQL that relied 0% on anything in MS Access. (thus not referring to your temp table from the spreadsheet)

Having said that, there still MIGHT be ways to speed up things in SQL server. They'd be the same things you'd look at if you wanted to speed up an insert operation that was entirely on the server... and the top thing that comes to mind is potentially disable any indexes on the server side prior to doing the insert, then re-enable them afterwards. That would be a PT query (to disable the index(es)), then your Access append query, then another PT query (to re-enable or re-create the index). Another thing to look at is simply to make sure the SQL server table is not over-indexed; i..e, do you only have the indexes you need. Those are all things you'd have to analyze from a sql server mindset or from talking to your DBA.

Having said all that, it's not guaranteed that the time it takes to re-create the index is necessarily less than the "drag" caused by inserting records to an indexed table--it all depends, it may or may not be.

In theory, you have one other option. Create code that builds up a large (!) block of code, which would result to something like:

insert tablename (col1,col2)
select 'value1','value2'
union all
select 'value2','value2'
...etc

And, run as a pt query on the server, it may be faster than the MS Access Append into linked table. Never know until you try.

 

[jco23]

Well, in cases where you are sure you want to empty the table with no turning back, Truncate Table is almost always preferred. Truncate table creates no "logging" of the operation. Logging the operation generally increases the clogging of TempDB (which is something to avoid when possible), and, logging slows things down. In cases where I wanted to empty a table completely, I'd always use Truncate Table. The rest is a different discussion. Glad it worked for you!


If you were to set up this query as a pass through query, you'd have to construct SQL that relied 0% on anything in MS Access. (thus not referring to your temp table from the spreadsheet)

Having said that, there still MIGHT be ways to speed up things in SQL server. They'd be the same things you'd look at if you wanted to speed up an insert operation that was entirely on the server... and the top thing that comes to mind is potentially disable any indexes on the server side prior to doing the insert, then re-enable them afterwards. That would be a PT query (to disable the index(es)), then your Access append query, then another PT query (to re-enable or re-create the index). Another thing to look at is simply to make sure the SQL server table is not over-indexed; i..e, do you only have the indexes you need. Those are all things you'd have to analyze from a sql server mindset or from talking to your DBA.

Having said all that, it's not guaranteed that the time it takes to re-create the index is necessarily less than the "drag" caused by inserting records to an indexed table--it all depends, it may or may not be.

In theory, you have one other option. Create code that builds up a large (!) block of code, which would result to something like:

insert tablename (col1,col2)
select 'value1','value2'
union all
select 'value2','value2'
...etc

And, run as a pt query on the server, it may be faster than the MS Access Append into linked table. Never know until you try.

right.... this table only has one index, but this is to be performed by non-admin users going forward, so I need to keep permissions in mind... I'll try the insert logic.

thanks again!

 

[Isaac]

Any time

 

[Anakardian]

A thing that might be a bit of a workaround and may not work.

If you can have a staging table on your BE server, you can put all you data from the FE there.
With a stored procedure you could have the server do the entire append operation instead of using access for any heavy lifting.

I found that having access do such a move means data will flow from the server, to access and then back again with a lot of delay.

 

[Pat Hartman]

The reason that delete queries on linked tables take so long to process is because Access creates a transaction so it can give you the option of cancelling or proceeding. That means it has to do the work twice and there is a lot of traffic back and forth between your computer and the server, plus SQL Server logs each delete.

A pass-through query simply deletes the selected rows because everything is processed on the server and Access will not give you the option to change your mind but it still logs the individual deletes.

If you are deleting all the rows, a better option is to truncate the table. That is pretty much instantaneous since it doesn't log anything. Poof the data is gone

TRUNCATE TABLE (Transact-SQL) - SQL Server

TRUNCATE TABLE (Transact-SQL)

docs.microsoft.com

 

[Micron]

Isaac, you should have suggested Truncate in post 8.
Oh, wait....

 

[sonic8]

Truncate Table is almost always preferred.

Truncate Table requires Alter Table permission. An application user will/should not have that.

 

[Isaac]

Could be wrapped in a stored procedure and given access to that.