Calculated field in pass through query

[Guest]

I'm trying to write a simple pass through query to return a filtered
selection of transaction data from a SQL Server database. In one field
(works_order) I need to return a particular result if the transaction type is
"COMP" and a different result in all other cases. I have written the
following SQL query:

SELECT (IIf((transaction_type) =
'COMP',Left(lot_number,6),Left(movement_reference,6))) AS works_order, dated,
movement_reference, transaction_type, lot_number, movement_quantity,
movement_cost, total_labour_cost
FROM scheme.stkhstm
WHERE (((dated) >('1/1/2005')) AND ((transaction_type)='W/O' Or
(transaction_type)='DKIT' Or (transaction_type)='COMP'));

The dependent field is the first one: (IIf(transaction_type ... AS
works_order,

I can't get this to work though. I'm a complete novice at pass through
queries, so I'm sure I must have the syntax wrong. Can anyone see what I'm
Doing wrong here?

Thanks in advance

Vaughan

 

[Tom Ellison]

Dear Vaughan:

A pass-thru query for SQL Server must be written using the syntax and
functions that are for a SQL Server query. If this is what you have, rather
than a Jet query to a linked SQL Server table, then you'll nee to change
this.

IIf() is a Jet/Access function. It won't work in SQL Server. Possibly you
could use a CASE statement.

As a novice, rather than learning yet another new database (SQL Server) I
recommend instead you create a linked table and let Access Jet do the query
work for you.

I also recommend you write the query in a simple form, then add
complexities. That way, if it fails at the point of adding a feature,
you'll know it is that feature you've got wrong.

Start with something like this:

SELECT transaction_type, dated,
movement_reference, transaction_type, lot_number, movement_quantity,
movement_cost, total_labour_cost
FROM scheme.stkhstm

Then add your criteria, one at a time:

WHERE dated > '1/1/2005'

Here, by the way, for Jet, a date is entered as #1/1/2005# not '1/1/2005'

Build up the WHERE clause one piece at a time and test to see it works.

Please let me know if this helps you. Come back with what problems you have
along the way.

Tom Ellison

 

[Guest]

Thanks Tom. I know the Jet Query will work, because what I'm trying to do is
speed up an existing query on the linked tables.

The tip about IIF statements not working in SQL Server is what I was looking
for. Many thanks. I'm away to work out a CASE statement.

Thanks

Vaughan

 

[Tom Ellison]

Dear Vaughan:

There are many experts who do not find SQL Server to be faster than Jet
given that you have the proper indexes, and that the query is not too
complex. This does not match my experience exactly, but I believe there's a
lot to that. On the other hand, if you have a high performance server, and
especially if you have bandwidth that is quite limited or latency problems,
SQL Server can be hundreds of times faster.

Please let us know how this works out for you.

Tom Ellison

 

[Michel Walsh]

Hi,


Using the same hardware, with a single user, Jet will be faster than MS SQL
Server simply because Jet does not use log. Even within MS SQL Server,
running a query on MSSQL TempDB can be four times faster that running the
same query on the same data but on another MSSQL db, because TempDB as less
logging overhead than a fully logged database (Inside MS SQL Server 2000, at
Microsoft Press, by Delaney, page 173).

That being said, judging, or selecting, a db engine just for its speed is,
in my opinion, a very bad criteria.


Vanderghast, Access MVP

 

[Tom Ellison]

As I said, there are many experts with this opinion. In the case of Mr.
Walsh, I really do mean EXPERT.

To some extent, I do not agree with the answer he gives. If you compare SQL
Server with logging to Jet without logging, you are not making a fair
comparison. You could turn off the SQL Server logging and have a product
more like Jet. If you do not desire the advantage of logging, you may do
so. If you do desire logging, you have eliminated Jet from consideration.

If you have a limited network bandwidth or high latency, you can experiment
with Jet and you'll see some huge differences. Over a VPN with a 4-6 second
latency I have seen a SQL Server connection that opens a form in 7-10
seconds. The same form opened with Jet in an hour and a half. Latency
destroys Jet because its methods cycle between sending and receiving what
can be a very large number of times. SQL Server sends the query and then
receives the results. My test in this case indicate perhaps 600
turn-arounds. With SQL Server, there is 1 turn around. (The form involved
was an extreme case with 8-10 subforms. Other forms would open in 10-20
minutes.)

The bandwidth demand over a LAN is not as severe, but it is worth
considering. In this case, it is not just the performance of the
application, but the impact to other users that is worth considering.

This leaves a HUGE area of application where Jet is a fine choice. I do not
generally find it to be superior in many cases, and I have experience with
using advanced capabilities of SQL Server not available in Jet where I can
perform query work at speeds more than 100 times what I can do with Jet.
For example, by writing a UDF loading table variables with just the relevant
data, and then process using that, I have changed processing from 20-30
minutes (Jet) to 4 seconds (SQL Server). To me, this is shocking!

Mr Walsh's final statement is considerably true. Yet, if you have extensive
processing that is taking way too long, performance (especially including
network impact) can be the primary consideration (assuming the technology
change is not prohibitive).

My specific reply is that, with the same hardware, and turning off logging,
and given sufficient memory (as a server really should have) SQL Server will
range from about the same speed as Jet to many times the performance. As
time has passed, SQL Server developments have resulted in performance
improvements while Jet has not been greatly developed in this way. This
trend is sure to continue.

One final note. When constructing a server to use SQL Server, I specify
large amounts of memory. For a 2 GB database (the largest Jet allows) I
would specify at least 2 GB of memory. The whole database should be cached
if you're after performance. This is the best price/performance advantage I
can find. Memory just isn't that expensive. I typically use techniques to
provide performance that are based on having such large amounts of memory.
Unless you wish to exploit the advantages offered by SQL Server, the
differences may not be very great. If you do exploit the differences, SQL
Server is very superior.

Tom Ellison

 

[Michel Walsh]

Hi,


That is why we also call these newsgroups "forum of discussion", it is
because we can get different opinions


On the other hand, I am not sure to know a way to systematically turn
off the logging feature in MS SQL Server.


Vanderghast, Access MVP

 

[Tom Ellison]

That's one thing I really like about vanderghast. He's civil and will
discuss things reasonably.

I do not want to propound not keeping a log. However, the time to create
the log can be reduced to virtually nothing. This overcomes the problem.

First, I want to explain a bit about logging. The logs allow you to
recreate the whole database as of any moment in time. Let's say you are
making daily backups. You have 30 people working 3 shifts, entering data
into the database. So, on a daily basis, the employer has invested several
thousand dollars in data entry.

If something goes terribly wrong in the middle of the day, and you simply
restore the last backup, you have lost several thousands of dollars of data
entry plus the disruption to the business of not having up-to-date
information till everything is re-posted. If you take orders, confirm them
by fax, then lose them, and re-post them, it is possible some of the
re-entered orders may not match. That sort of thing.

One strong recommendation is to keep the log on a separate hard drive.
Preferably this would not be the same one with the database, but a separate
one just for logging. A good, high-performance SCSI drive does this well.
An IDE (ATA) drive on a separate controller is another option.

I have an alternative suggestion that is less expensive and performs even
better. Log to a ram-drive (make sure you have UPS for the system that
really works!) Copy the log to another drive and clear it, maybe hourly
(you must size the amount of log being created and purchase enough ram to
minimize the frequency, perhaps daily is more likely, so your log backups
can just go with the database backup). This can be automated. With today's
technology, a removable USB ram drive might be a good choice. I think a
gigabyte isn't all that much, and it's not subject to power outages losses.

Logging to a ram drive is almost as fast as not logging at all. But you
still have the logs and all their emergency restoration capacities.

This will make restoration a bit more complex, if and when it's needed.
You may go for years without needing it, but when you do, it will be worth
the time and expense to have kept it.

The whole picture is a complex mix of costs and benefits. Nothing to be
just sneezed over. You could purchase a more powerful server and not mess
with this. There are a lot of variables, and a lot of solutions.

If you really don't want to log, you could clear the ram log without backing
it up. I'm not saying I recommend that in any typical scenario, but it's
doable.

Tom Ellison