Why is this faster

[Filips Benoit]

Dear all,

Access adp on SQL-server 2005. The adp run successfully 8 year but the
company (orders, database, specialy some tables) grows very fast and some
code runs to slow.

2 questions about this problem.

1. Copying a template-order with all its dependences became to slow!
After 6 year some tables became very large and simply adding a new record
to these tables became very slow.
I did not find a solution meself so the company asked some help from another
developer.
He changed some code like here below.
So what's the theory behind this change ( WHERE 1=2)?
AND, do you have other tips to speed up the process?

The code runs in a transaction and adds many records to diferent tables in 1
tranaction using SP's and VBA-code.


OLD CODE
objrsOrdersProps.Open "ORDER_PROPERTY", InterfaceConn,
adOpenDynamic, adLockOptimistic, adCmdTableDirect

NEW CODE
objrsOrdersProps.Open "SELECT * FROM ORDER_PROPERTY WHERE 1=2",
InterfaceConn, adOpenDynamic, adLockOptimistic, adCmdText

2. While copying a template-order(s) sometimes the user should give some
extra information for the new order by adding data in an inputbox.
The whole action is in a transaction and locks other users out. If the
user waits to fill in the inputbox, other user can't copy an order !
It's dificult to give all information for each order before the action
( transaction) starts.
How can I solve this problem? Since mostly many orders are copyed in 1
action (transaction) the new data (inputbox) is different for each order.


Thanks,

Filip

 

[Robert Morley]

(Inline)

Dear all,

Access adp on SQL-server 2005. The adp run successfully 8 year but the
company (orders, database, specialy some tables) grows very fast and some
code runs to slow.

2 questions about this problem.

1. Copying a template-order with all its dependences became to slow!
After 6 year some tables became very large and simply adding a new record
to these tables became very slow.
I did not find a solution meself so the company asked some help from another
developer.
He changed some code like here below.
So what's the theory behind this change ( WHERE 1=2)?
AND, do you have other tips to speed up the process?
The code runs in a transaction and adds many records to diferent tables in 1
tranaction using SP's and VBA-code.


OLD CODE
objrsOrdersProps.Open "ORDER_PROPERTY", InterfaceConn,
adOpenDynamic, adLockOptimistic, adCmdTableDirect

NEW CODE
objrsOrdersProps.Open "SELECT * FROM ORDER_PROPERTY WHERE 1=2",
InterfaceConn, adOpenDynamic, adLockOptimistic, adCmdText

The concept behind this code is that instead of opening the entire table,
which can take a very long time to transfer over a network (though I believe
ADODB only sends a small portion until the rest is requested), you're
actually transferring nothing at all beyond the table's structure.

The "WHERE 1 = 2" is simply to create a case that is ALWAYS False. In other
databases/languages, you would use "WHERE False" or "WHERE 0", but SQL
Server doesn't support that, AFAIK. So in essence, the new code is
guaranteed to return a table structure, but with no records. This is very
useful if the only thing you're doing is appending data.

An even faster (but sometimes less useful) method is to ignore the recordset
altogether and simply issue a .Execute on the connection, and update the
data that way. For example:

CurrentProject.Execute "INSERT INTO MyTable (MyField1, MyField2)
SELECT 'MyData1', 2")

2. While copying a template-order(s) sometimes the user should give some
extra information for the new order by adding data in an inputbox.
The whole action is in a transaction and locks other users out. If the
user waits to fill in the inputbox, other user can't copy an order !
It's dificult to give all information for each order before the action
( transaction) starts.
How can I solve this problem? Since mostly many orders are copyed in 1
action (transaction) the new data (inputbox) is different for each order.

I think the only way to do this is to add code that will verify the data
beforehand. The point of a transaction is to lock out any other changes to
affected records while the transaction is in effect, so it's pretty much
always "a bad thing" to be waiting on user input during a transaction.

If for some reason you MUST do this in a transaction, then the other
possibility is to break up the transaction so that only the smallest
possible number of records are locked at any given time.



Rob

 

[Filips Benoit]

Thanks, for your clear answers!

Filip

(Inline)



The concept behind this code is that instead of opening the entire table,
which can take a very long time to transfer over a network (though I
believe ADODB only sends a small portion until the rest is requested),
you're actually transferring nothing at all beyond the table's structure.

The "WHERE 1 = 2" is simply to create a case that is ALWAYS False. In
other databases/languages, you would use "WHERE False" or "WHERE 0", but
SQL Server doesn't support that, AFAIK. So in essence, the new code is
guaranteed to return a table structure, but with no records. This is very
useful if the only thing you're doing is appending data.

An even faster (but sometimes less useful) method is to ignore the
recordset altogether and simply issue a .Execute on the connection, and
update the data that way. For example:

CurrentProject.Execute "INSERT INTO MyTable (MyField1, MyField2)
SELECT 'MyData1', 2")


I think the only way to do this is to add code that will verify the data
beforehand. The point of a transaction is to lock out any other changes
to affected records while the transaction is in effect, so it's pretty
much always "a bad thing" to be waiting on user input during a
transaction.

If for some reason you MUST do this in a transaction, then the other
possibility is to break up the transaction so that only the smallest
possible number of records are locked at any given time.



Rob