Pass Through to SQL Server Optimization Questions

On an autoexec function several recordsets are opened.  Some are
queries to local tables, some are to sql server tables.

I have been trying to optimize performance but I haven't seen much
difference in speed between queries run directly against linked SQL
Server tables vs pass through queries.  Here is my code for creating
the pass throughs (I have another function OpenRecordSetLocal that
handles queries to tables within the frontend.  The ODBC connection
string resides in the query Q_PassThrough.

Am I missing something key?  Is there a better way to do this?
Thanks.

Function OpenRecordset(sql As String)

    On Error Resume Next

    Dim DB As Database, rs As DAO.Recordset
    Set DB = CurrentDb()

    CurrentDb.QueryDefs("Q_PassThrough").sql = sql

        Set rs = DB.OpenRecordset("Q_PassThrough")

        Set OpenRecordset = rs

    Exit Function

Err_OpenRecordset:
    MsgBox Error(Err), vbInformation, "DATA ERROR"
    Debug.Print "Data Error"
    Exit Function

End Function
0
tcb
11/30/2007 5:45:06 PM
access 16762 articles. 3 followers. Follow

2 Replies
791 Views

Similar Articles

[PageSpeed] 59

Firstly, there is no general reason to expect SQL Server to be faster.
On the same hardware, you still have to lookup the data, and load
if from disk - you can't get away from that.

Secondly, SQL Server is designed to handle heavy loads from
multiple connections in a resource constricted environment better
than Access/Jet -- you haven't tested that yet :~)

Thirdly, ODBC defines a restricted set of standard SQL. Any
application that restricts itself to that restricted open standard
should be able to talk to any ODBC driver. If you use Pass-Through
queries instead of ODBC linked tables, you can bypass that restriction.

In particular, ODBC had a restriction of only one Left or Right join
per query. You can have multiple symmetrical inner joins, but only one
Left or Right join. This means that for a complex query, Access/Jet
has to implement most of your Left/Right joins locally when using
linked tables.

There are several important restrictions like that. So the performance
difference you see may depend on the SQL of  the query you are
comparing.

Finally, you haven't shown how you are using the recordset. It
may be that all of your performance is swamped by some other
slow process, for example, connecting and disconnecting.

(david)

PS, I see that you have both an Error Handler, and ON ERROR
RESUME NEXT. No point in having both. Also, Error(Err) is
not a good way to get the error string - better to use Err.description.
Also, Err only gets the top error  -- better to loop the dbEngine.errors
collection when using linked tables.


"tcb" <tbenson@mn.rr.com> wrote in message
news:c19382d3-2406-45ca-bee2-d346d232122c@r60g2000hsc.googlegroups.com...
> On an autoexec function several recordsets are opened.  Some are
> queries to local tables, some are to sql server tables.
>
> I have been trying to optimize performance but I haven't seen much
> difference in speed between queries run directly against linked SQL
> Server tables vs pass through queries.  Here is my code for creating
> the pass throughs (I have another function OpenRecordSetLocal that
> handles queries to tables within the frontend.  The ODBC connection
> string resides in the query Q_PassThrough.
>
> Am I missing something key?  Is there a better way to do this?
> Thanks.
>
> Function OpenRecordset(sql As String)
>
>     On Error Resume Next
>
>     Dim DB As Database, rs As DAO.Recordset
>     Set DB = CurrentDb()
>
>     CurrentDb.QueryDefs("Q_PassThrough").sql = sql
>
>         Set rs = DB.OpenRecordset("Q_PassThrough")
>
>         Set OpenRecordset = rs
>
>     Exit Function
>
> Err_OpenRecordset:
>     MsgBox Error(Err), vbInformation, "DATA ERROR"
>     Debug.Print "Data Error"
>     Exit Function
>
> End Function


0
david
12/1/2007 1:40:06 AM
On Nov 30, 7:40 pm, <david@epsomdotcomdotau> wrote:

Thanks for your thoughtful response, things for me to consider this
morning.
0
tcb
12/1/2007 2:14:02 PM
Reply:

Similar Artilces:

Demand Planner Questions...
Hi, Has anyone out there worked with Demand Planner (ideally with Great Plains)? If so, I have some basic questions: 1) Do user or administration manuals exist? I can't find them on PartnerSource. 2) What is the data flow from Great Plains to Demand Planner and back again. Alan ...

attachment question #4
I have a friend with whom I have been exchanging emails for several years. I have hundreds of her emails which contain pictures of her home and pets and other artifacts of her life. Last spring her house exploded due to a propane leak destroying everything including the pets and her hands. She is ready to see her former life again. I am willing to extract the photos one by one for her but if there is a way to do a batch extraction of the jpeg files it would save a great deal of time. I am willing to purchase software to facilitate this if necessary. I currently use Outlook 2003 sp2 ...

How Can I switch CRM Outlook between two different servers
I have a development CRM database and a demo database. I want to know if there is any way that I could switch back and forth between the two so that I can have my outlook online show the CRM development database when I am coding and switch it to the demo database when I am talking to customers. I only need this in On Line mode of CRM Outlook intergration. Is this something other people have run into? Is it possible to do this? Is there some setting I could change? Mike Take a look at the CRM Client registry setting on the workstation. The usual disclaimer applies. "Hotwheels...

Encryption Question
I've manages to create a number of encryption algorithms that work pretty good. I've also managed to find dozens more written by others. However, all the algorithms I've found can end up with problem characters such as '\0'. I know I can encode the result using hex characters but then storage will be at least twice as long. Has anyone run across a decent encryption algorithm that, when encrypted, produces standard ASCII characters? (Note: This is not for national security or anything. Anything better than a simple XOR should be fine for my purposes.) Thanks! -- Jonath...

Xcopy
This is probably considered OT for this group, but I didn't see where else to ask it, and I know there are some people here that use batch files a lot. I have this command: xcopy c:\MyFiles\*.* F:\Backup\*.* /S /Y /M (there are a lot of sub-folders) But it backs up the entire contents of the folder, everytime. The files are photos. Nothing has changed. I ran it the first time without the "M" switch. But after adding that switch it still backs up every single file. Any ideas? Thanks. Probably because archive (A) attribute is set on every single f...

Money 05 Cash Flow Question
I have recently started using Ms Money 05 and I have a problem with the cash flow forecast. I have setup my budget as well as the bills and deposits. We use our credit card to purchase all of our day to day items due to the fact we get cash back from the credit card. We also pay the balance off every month from our checking account. All of the items in the budget reflect the correct account they come out of including the day to day items coming out of the credit card account. However in the cash flow forecast the average of the accounts is correct, but the accounts themselves are no...

Access 2000 DB import tables in SQL Server 2000 DB
I inherited an Access 2000 database that needs to have tables imported into a SQL Server 2000 new database. The Access database has approximately 30 tables. The Access database has approximately 10 forms, 10 reports, and 10 modules. I would like to import Access database tables into the new SQL Server database. After the tables has been imported I will create the relationship between the tables. Next I will create the indexes and link the SQL Server tables to Access database. I know Access 2000 upsizing will complete this procedure but it must have SQL Server v7.0 or v6.X database....

Deadlock on SQL SELECT statement
I have inherited the maintenance of a product which includes the snipet of code below. Every 10 seconds the code is executed. It is causing a deadlock in some instances, but I am undable to reproduce the problem on my machine. The "PC" table contains a list of PCs seen on a network, so isn't very large. Since I dont have much background in database programming, I was wondering if there is some simple answer to the deadlock issue...but from reading on deadlocks, there rarely seems to be a simple solution. //**************************************** // Find PCs to restart CStri...

Windows Mail- Delete it from Server
I have been using "Delete it from Server" option in Windows Mail Rules successfully for a number of years now until recently. The I noticed i began receiving mails that I have defined to be deleted from server. To further this, I used another email address defining the emails from this address to be deleted from server but the emails still come through. Can anyone enlighten and help me on this? I am using Vista Home Basic and updated to the most current. The mail program is the Windows Mail. "rbernal43" <rbernal43@discussions.microsoft.com> wrote in m...

Some questions regarding EPM
All, I have setup Enterprise Project Environment with Microsft Project Server 2007, There are multiple projects running in my company and all projects plan published in MS Project Server. Question 1: I want to view the resources assignments / usage involved in all projects, i tried built in reports through web interface but that are not clear when you select many resouces the graphs gets confusing. OR please recommend any report that gives us the bird eye of all resources and their assignment, so that I can see which resource are over allocated and busy in another project. ...

Can�t receive msg from win2003 server
Hi all! I�ve configured a Win2003 Server (not Domain Controler) with SMTP/POP3 services enabled. In Outlook Express, I can send e receive messages properly, but in Outlook (MS Office), I just have e-mails sent, but not received. Althought I�ve turned SPA on, it doesn�t work. What is wrong? Someone can help me, please? thx, Adrian Do you receive any errors when trying to receive? "Adrian" <adrian_trabalho@hotmail.com> wrote in message news:ewYB9E1dFHA.3712@TK2MSFTNGP12.phx.gbl... > Hi all! > > I�ve configured a Win2003 Server (not Domain Controler) with SM...

Outlook 2003 contacts
When you open an email and want to save the email address on the "From" section: 1. right click the address and save it to your contacts - Outlook will then save it to your c:\drive profile How does one go about having it saved to the network \\network\publicfolders\contacts instead of the c:\drive Setup: Windows 2003\Exchange 2003 - individual profiles and not saving pst's on the c:\ We're already sharing a contacts folder on the public folders section. MS forgot about allowing users the ability to save to it directly. I've already checked Outlook 2007 and ...

SQL Server 2000+2008 on Win2008x64 question
Hi, I have SQL Server 2000 on my Win2008x64 server, the db file is on the D drive, while the program file is on C drive. Today I installed the SQL Server 2008 on the same machine. After the installation, the SQL Server Service Manager is still the same as just only SQL Server 2000 installed, I'm thinking isn't there some newer version of the SQL Server Service Manager for the SQL Server 2008? And in the SQL Server Management Studio, I can only find those db in the SQL Server 2000 db folder, I can't find those SQL Server 2008 system database like Master and Model. H...

Group by query question
I have a query which is supposed to (and does) return the earliest record from a table that might have multiple records per "person" (famno). "dis is a calculated field in a pervious query that calculate how many days until a certain event (so a person could have 2 or more records, each with a different value for dis). It works properly: SELECT yahr_dist_from_today_greg.FAMNO, Min(yahr_dist_from_today_greg.dis) AS MinOfdis, Min(Date()+[dis]) AS NextYZ FROM yahr_dist_from_today_greg GROUP BY yahr_dist_from_today_greg.FAMNO; I have another field in the query, Rel...

Storing email on the server and not on the workstation
Just recently set up SBS 2003 w/exchange. When adding the computers to the domain, chose the option to keep email stored on the workstation. Now would like to store the email on the exchange server instead of workstation so that it can be viewed remotely as well as backed up. How can this be changed? -- Thanks, Jabooty Change the Delivery Location in the MAPI profile. -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.com/blog ---------------------------------------------- "Jabooty" <Jabooty@myemail.wow> wrote in message news:97C7...

pfmigrate, removing from old server
In using pfmigrate I used /s:server1 /t:server2 /a to add replicas to sever2. To remove them from server1 do I need to switch them around? ie.. pfmigrate.wsf /s:server2 /t:server1 /d Simple I know, just want to make sure so I don't delete them from my swing server before my upgrade and set me back a little. Thanks. Jason True... D: Deletes the SOURCE server from the replica list of folders where the TARGET server is also a replica.http://support.microsoft.com/default.aspx?scid=kb;en-us;822895 -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exc...

Outlook 2002 Will Not Connect to Server after WinXP SP2 Install
Anyone aware of why Outlook 2002 would not connect to a POP3 mail server after installing Windows XP Service Pack 2? I wanted to get the enhanced firewall and security improvements from the SP, but after installation NOTHING would get Outlook 2002 (or Outlook Express) to "connect" to my POP3 E-mail server. Went thru all possible "fixes" including blowing away the account info and creating a brand new one - nothing worked until I used System Restore to rollback to pre-SP2 - then Outlook works again. ...

SQL Error Message- Row Cannot Be Located...
Hi, we're getting the following error message on a customers machine when she tries to open up Store POS for the first time- "Row cannot be located for updating- some values may have changed since last read". This is a new RMS Store Operations 2.0 installation and it's connecting to HQ. After this particular store was first created, there was an error synchronizing the databases, a collation problem which was immediately fixed and everything synched up fine. Disconnected and reconnected the database, Store Manager opens fine, deleted and recreated the register and ...

JDBC Driver issue with SQL Server 2008
Hi, I just migrated to SQLServer 2008 Express from the 2005 version. I have a Java application that uses SQL JDBC Driver (sqljdbc4.jar - latest version) to connect to DB. I had to upgrade to the latest driver as the previous version sqljdbc.jar did not work with SQL 2008. Now my application works, but in one instance it fails. Debugging i found the issue to be when accessing this particular stored procedure that has both Update and Select queries. In this case the error says 'statement didnt return a resultset' when executing this code 'preStmt.executeQuery();' ...

complicated drop down menu question
Hi there, Perhaps someone can help me with this issue, it's a bit long so bear with me. I have 2 spreadsheets, the first one is called data, the second one is called summary On the data sheet, I have it lay out as a sample like below, in different columns, all in text format week 1 Jan 1 Jan 2 Jan 3 Jan 4 week 2 Jan 8 Jan 9 Jan 10 Jan 11 week 3 Jan 17 Jan 18 Jan 19 On the summary sheet, I'd like to have a drop down menu on cell A1 that should have the week number as my choices, and then as I select the week number, the date will show up on a set ...

RegisterBluetoothComPort function question in bluetooth test
Hi: we have a MIPSII BSP and board with UART 0,1,2,3 BSP uart driver define(nxc26uart.reg) and suport physical com port 1,2,3,4 i take reference from : http://vinoth-vinothblog.blogspot.com/2008/10/enabling-bluetooth-serial-port-profile.html and AP sample c:\Wince600\PUBLIC\COMMON\SDK\SAMPLES\BLUETOOTH\COMTEST\comtest.cxx then write an AP to simulate blooth manager. at the call h=RegisterBluetoothComPort(L"COM",index,&pp));or h=RegisterDevice(L"COM",index,L"btd.dll",(DWORD)&pp); h return NULL ,index=1,2,3,4 h return handle, if index...

MS CRM 4.0 to .pdf questions
Hi, Does anybody have a clue on how to solve following requirements: 1) 'MailMerge' to PDF: - Is there any way to perform a MailMerge behind the scene for the user, but with a PDF as a result document? Following is needed: when a sales person generates a letter document, the process of MailMerge should happen behind the scene, but as a result of the mailmerge, the salesperson should have the pdf emailed to him (or attached to the MS CRM record). The reason for all this, is because it is not allowed for the sales person to change anything on the generated document...All he can...

Project Portfolio Server
Is it possible to customize the "Status" tab in PPS? We wish to include a number of custom attributes as text boxes that PM's would fill out to update their status. We have successfully updated the "Project Info" tab and the Additional Info Tab, but are not able to update the status tab. project.aspx default settings: <tabitem name="Status"> <WP:MonthlyStatus runat="server" > <Config> <content columns="2"> <sectionbreak name="Attributes" width="910" /...

Exporting OLE links from SQL tables
I'm not familiar enough with GP table structure to know if I'm even asking the right question here, but here goes: I have a client that manually creates an Excel spreadsheet that contains exported general ledger transactions. They then create a hyperlink for each transaction in the spreadsheet, linking the line to a document on their document server. What I'm wondering is if it is possible to export the OLE links from GP transactions directly into Excel (or some other format) at the same time as exporting the transaction detail. Any help would be greatly appreciated. Thx, Fra...

some Users can not connect to Server to fetch Emails !!! (URGENT)
hi dear friends we have Windows 2000 SP4 + Exchange 2000 SP3 installed and Post SP3 for exchange is installed too.users have Office 2003 SP2 installed ,we have defined S/R restriction for users for upto 5MB .now i have two questions first : users receive emails in their mailboxes that exceed 5MB limit .i want to know that how this occurs ? i think that they must not receive these emails . second : users use POP3 to fetch emails on their own computers ( .pst file )randomly we get these error that some users can not Send/Receive emails from server & Client Computer Hangs (Clients use Wi...