Re: Calculated field in pass through query

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" <tellison@xxxxxxxxxxx> wrote in message
news:uKfwTuhFGHA.1260@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>
>
> "Vaughan" <Vaughan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:76715E53-6801-4659-BA06-26695BF01D29@xxxxxxxxxxxxxxxx
>> 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" wrote:
>>
>>> 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
>>>
>>>
>>> "Vaughan" <Vaughan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>>> news:0B15B20A-080F-4708-9AE0-E4F1A3BF529A@xxxxxxxxxxxxxxxx
>>> > 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
>>>
>>>
>>>
>
>


.

Relevant Pages

  • Re: Official Status of SQLServer 2005 ADP
    ... As far back as Access 97, Jet has made every attempt ... to "pass through" every Access query against a linked ODBC datasource. ... ends - DB2, Oracle, and SQL Server among others. ... > solution might be to use ADP. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Discussing 3 different strategies for deleting from multiple tables
    ... I will be using SQL Server but I am riding on top of a third party ... FYI, Account contains around 20K ... >>> This results in one parameterized query followed by two more trips to ...
    (microsoft.public.data.ado)
  • Re: Efficient coordinated queries??
    ... :-) We are distributing processing over a large server farm -- bringing it back onto the SQL server would probably kill our performance. ... Hitchhiker's Guide to Visual Studio and SQL Server ... A large chunk of it can be brought in with a single query and each row represents an item to process. ... For a hypothetical example consider a customer order database where we need to process through all customers and can bring in a row of customer data as our main query but there are 0..n1 rows of customer order data, 0..n2 rows of customer feedback information, 0..n3 rows of customer discounts available, etc. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Need help with recursive SQL
    ... MS SQL Server 2005 has proprietary syntax to deal with that kind of problem. ... But you said you use Jet. ... a query implying the table a finite number of time. ... michael, william and alfred should be returned. ...
    (microsoft.public.access.queries)
  • Re: Calculated field in pass through query
    ... Server with logging to Jet without logging, you are not making a fair ... You could turn off the SQL Server logging and have a product ... perform query work at speeds more than 100 times what I can do with Jet. ...
    (microsoft.public.access.queries)
Quantcast