Troubleshooting Slow Pass Through Query From Access
Jun 29, 2007
One of our developers has a Microsoft Access 2000 database that runs queries that compare the Access db data to a SQL Server database. He uses pass through queries to get the data from SQL Server.
We're finding that the Access query runs quickly against our test server, even with copies of production data, but when we try the same query against our production server, the CPU on the local computer running Access is pegged and the query takes up to 10 minutes to run.
First I verified that the SQL Server structures between test and production were identical, including indexes. I checked index fragmentation, and productions indexes are less fragmented than tests. Again, test and production currently have the identical data.
I've run a profiler trace on our production SQL Server 2000 server, and I see the RPC for the query from Access running almost instantaneously.
Any ideas on what might be the cause of the difference in speed between test and production SQL Server servers, or any suggestions on other things I could look at/tools I could use to troubleshoot this issue further?
I would like to call this stored procedure, but I am unable to passparameters to the @Start and @End.Is thier a way to pass parameters to a pass through query from MSAccess?SELECT COUNT(dbo.tblPersActionHistory.PersActionID) AS [Total Ct],[dbo].fn_FindStartPayPeriod(dbo.tblPersActionHistory.Pe rsActionID, 2)AS [Signed PP]FROM dbo.tblPersActionLog INNER JOINdbo.tblPersActionHistory ONdbo.tblPersActionLog.PersActionID =dbo.tblPersActionHistory.PersActionIDWHERE (dbo.tblPersActionLog.StatusID BETWEEN 4 AND 7) AND(dbo.tblPersActionLog.Rejected = 0) AND(dbo.tblPersActionLog.IsPayAction = 0) AND(dbo.tblPersActionHistory.ActionTypeID = 5) AND(dbo.fn_IsParACorrection(dbo.tblPersActionHistory. PersActionID) = 0)AND([dbo].fn_ParNotException(dbo.tblPersActionHistory.PersA ctionID) = 1)AND (dbo.tblPersActionHistory.ItemDTG >= @StartDate) AND(dbo.tblPersActionHistory.ItemDTG <= @EndDate)GROUP BY[dbo].fn_FindStartPayPeriod(dbo.tblPersActionHistory.Pe rsActionID, 2)
Recently we started using Pass throughs to perofmr large inserts, however we have started to notice that some of these pass throughs are executing twice, and therefore duplicating data.
Is this a known bug, and if not has anyone got any advice on what could be causing it?
We're connecting from Access 2002 (SP6) to SQL Server 2000 Enterprise.
The conenction string / code is as follows :-
Dim cmd As ADODB.Command Set cmd = New ADODB.Command Set cmd.ActiveConnection = CurrentProject.Connection cmd.Properties("Jet OLEDBDBC Pass-Through Statement") = True cmd.Properties _ ("Jet OLEDB*** Through Query Connect String") = _ "ODBC;DSN=" & myDatabaseShort & ";DATABASE=" & myDatabase & ";UID=sa;PWD=" & Left(myDatabaseShort, 4) & ";"
It connects fine. My sql string is a straightforward Insert statement that only executes once via SQL Query Analyzer.
I'm calling the pass through using the following lines of code:-
cmd.CommandText = mysql cmd.Execute
Can anyone see anything obvious that I'm doing wrong, or is this a known issue?
Hi all. I'm new to this forum and looking for some assistance.
I've run into a unique (for me) performance problem.
I have a select statement that performs fine ( < 1 second ) using one set of values in the criteria but very poorly ( > 3 minutes )using different values. In both circumstances the query returns zero rows. The query involves a parent-child join with the criteria spread across both tables.
The execution plan looks similar between the two; the difference being a few percentage points difference on some of the operations. The tuning advisor has no recommendation in case 1 but suggests a couple of additional indexes and 4 statistics in case 2.
My gut tells me that the solution is *not* applying the additional indexes/statistics but some other issue. Or it could be the sushi I just ate.
Anyway, I'm hoping someone can point me in the right direction as what to analyze to determine why simply changing a single supplied criteria value would have such a dramatic effect on performance.
create a new Connection Manager by right-clicking in the Connection Managers section of the design area of the screen. Select New OLE DB Connection to bring up the Configure OLE DB Connection Manager dialog box. Click New to open the Connection Manager. In the Provider drop-down list, choose the Microsoft Jet 4.0 OLE DB Provider and click OK. Browse to the Access database file and connection set up---all good!!!
Dataflow task Add an OLE DB Source component Double-click the icon to open the OLE DB Source Editor. Set the OLE DB Connection Manager property to the Connection Manager that I created . Select Table from the Data Access Mode drop-down list. I cannot see the tables set up as set up as pass-through table types to a Oracle 9i db
I am experiencing VERY slow connectivity between client and server SQL 2000. I have checked the network activity and it is low. It also takes Enterprise manager ages to load, and then browsing tables and trees is impossible!
I have a access database, the data store in another server. This noon, one of our user is runing the access database too slow. Open the database and search the data, etc. It took a long time to come out, Any body has experience on it, why, we had etrust install on each user machine, is that cause this too slow? Thanks in advance.
Has anyone else exported data to Access? If so, is it a slow process in SQL Server 2005?
I am exporting 3,000 records to an Access database using a view and it has taken 20 minutes. I am using the Export Data wizard. Any tips on how to speed it up would be appreciated.
I have 25+ WinXP SP2 workstations on a Novell file server (Novell login) and also have a W2K SQL 2000 Server for our database. We are usint Win NT authentication to the SQL Server.
One AND ONLY ONE user is having trouble with this setup. Her workstation is PAINFULLY slow accessing the SQL server through my Delphi programs, through our third party programs, and through ODBC connections.
I thought it was her workstation until I configured a clean workstation for her, and it too had the same problem. I then took a brand new Dell 9200 workstation with 2gb memory, gigabit NIC, etc. and configured it to use our servers. It too had the same slowness connecting to the SQL server. Everything else is fast.
I deleted her entry in the NT domain users table, and re-entered her. No help.
A bit new to SQL 2k5 but here goes, I recently installed SQL 2k5 on a preety decent box with about 4GB Ram and created a database called PointOfSale. As probably obvious by now, the application that accesses the Database is a PointOfSale application. The store sells ladies clothing items, of which each is barcoded. When an item is scanned via the barcode reader into the application, it takes like about 10-15 seconds for the item information to appear. Of course the item information resides on the database. There are three registers and the same thing happens on all. I am running sp 2 for sql2k5 already. Any thing I can do that would reduce the 15 sec delay significantly? Any help at all would be appreciated immensley.
Environment - Windows 2003 standard server SP1, SQL 2000 SP4 (installed mixed mode)
Goal: To have Crystal reports run via odbc dsn using windows authentication.
Big Honkin' Problem: I should say that if the users are local admins on this server then the windows authentication works just great, if not a local admin then it doesn't work. SQL gives an error access denied. (this shows in the logs btw.. to the end user crystal displays a generic database error) Since windows authentication failed, I then decided to use the dbo and switch to SQL auth. This dbo did not have a password and everything ran great. Then the IT dept. asked me to give the user a password, so I did. Then everytime Crystal opened, a window would appear asking the user to type the database , user and password. In Windows everytime this was done the crystal control files would change ownership to whomever entered the information making the report useless to any other user until I manually went into the file properties and changed it back. I develop these reports thru an VPN connection and on my desktop I said phooey on re-entering the password 50 zillion times while developing so I opened the dsn and put the PWD line in and it ran great. So we figured why not put this on the server - the password does not pass thru to SQL, even as admin user. The final problem that came up in relation to this is that I then created another user in SQL with no password so the reports would run. I developed a report that takes the current user credentials and passes it to the report via dde link. On my machine running over the VPN and launching the database program (GoldMine) over the WAN the report did everything it was suppose to. I high-fived myself and installed it on the server where the users would launch it and the dang thing wont pass the credentials over to the report.
I know there is not much to SQL security - it's either SQL or mixed mode. I have it on mixed mode and I know for fact it runs because Goldmine runs. I have to have some sort of security 'thang' in Windows 2003 somewhere that doesn't allow the passord to move and SQL only sees a blank password.
If any body can give me any help - I would so greatly appreciate it.
We have several people accessing the SQL server 7.0 data thru' MS Access Server has been slow on queries etc.Everything looks good,other than this one finding of MS Access being used for update/select. Is MS Access a potential problem? How to solve the problem of slow response time?
Note: MS Access is running on the client desktops retrieving data from SQL Server and joining data from MS Access to SQL Server.
I have a stored procedure which creates 3 temporary tables. Every table is about ten rows and 25 columns. The inserts in the tables goes fast (< 30 ms). The selects from them is also that fast. BUT the first select takes about 3200 ms one each of the temptables. (I first do insert, then select from them.) So the SP executes at about 13 seconds instead of 3.
We have an issue with accessing SQL Server 2000 where the access of data from the database is slow unless the user is logged in as an administrator to their computer.
The system is as follows: SQL Server 2000 on a W2K server. Users logging into a Win 2003 domain server. Users using W2K on their workstations. Application is VB.NET using the Enterprise Library Data Block, connection pooling ON, and windows authentication.
We are assuming that the issue is down to one of authentication and that when a user is set as an administrator then they have instant access. We have been able to replicate the issue using just SQL server on a W2K workstation and accessing from another W2K workstation. Again data access is way slow unless the account is an administrator.
I have a table that has appx 3.2 million rows. see sp_help
Name Owner Type Created_datetime ------------------------------------------------------------------- TB_SAAI014_BPD dbo user table 2005-08-10 11:33:23.893
Column_name Type Comp Lngth Prec Scale Nullable ------------------------------------------------------------------------ RowID int no 4 10 0 no SPHInstID int no 4 10 0 no BPDInstID int no 4 10 0 no BMUID varchar no 11 no InfoImblCfw numeric no 9 12 2 no BMUPrdNonDel numeric no 9 12 2 no PrdFPN numeric no 9 13 3 no PrdBMUBalSrvVol numeric no 9 13 3 no PrdInfoImblVol numeric no 9 13 3 no PrdExpdMtrVol numeric no 9 13 3 no BMUMtrVol numeric no 9 13 3 no PrdBMUNonDelBidVol numeric no 9 13 3 no PrdBMUNonDelOfrVol numeric no 9 13 3 no TranLossFctr numeric no 9 15 7 no TranLossMtpl numeric no 9 15 7 no TradUnitName varchar no 30 no TotTrdUnitMtrVol numeric no 9 13 3 no BMUAppBalSrvVol numeric no 9 13 3 no DTCreated datetime no 8 yes DTUpdated datetime no 8 yes
Identity Seed Inc Not Repl ----------------------------------------- RowID 0 1 0
RowGUIDcol ----------------------------- No rowguidcol column defined.
Data Located on File Group ========================== PRIMARY
Index Name Decsription Keys ---------------------------------------------------------------------------- idx_SPH_BPD clustered, unique located on PRIMARY SPHInstID, BPDInstID
This table has 1 clustered index based on its own unique record ID and that of its parent table record
I have an import process that adds appx 980 rows of data to this table and numerous rows to several other tables as part of a transaction and it ran in about 15 seconds.
However we suffered a server failure and it had to be rebuilt (Svr2k3), SQL 2000 re-installed (with default options) and the data base restored.
The same transaction is now taking 8 to 9 minutes. I tracked it down to this particular table. Just doing a count(*) takes over 5 minutes. Select * where ID = 1 takes over 5 mins. Also, whenever the table is accessed you can hear the server thrashing the disks. Other tables, although smaller do not seem to be suffering from this masive performance drop.. I've tried droping and recreating the index. I have even created a copy of the table, with index, and still get the same issue with speed. DBCC CHECKTABLE returns the following but takes 6 and a half minutes DBCC results for 'TB_SAAI014_BPD'. There are 3168460 rows in 72011 pages for object 'TB_SAAI014_BPD'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
No errors are shown
A DBCC CHECKTABLE on another table with 230 thousand rows, run at the same time only took 10 seconds
Can anyone please point me in the direction of things to check, try or repair.
Here is the brief to my problemWe had our database on SQL Server 2000 and Windows 2000.This machine had 2gb of RAM and dual Penitum 3 processors and about 25-30 users were connected all the time. The size of database is around 2 gb. Even on this setup rate of data retrival was good, never had any issues. We moved to SQL Server 2005 and Windows 2003. This machines has 2 Pentium Xeon 3.4 processors and 2 stick of KINGSTON 1024 MB 333 MHZ DDR DIMM ECC CL2.5 DUAL RANK X4 INTEL. The rate of data retrival is awful and its very slow. It using about 1.7 to 1.9 gb of RAM all the time. Page File usage is about 2.07 gb and Virtual Usage is about 1.7gb.I dont quiet understand why is it so slow to get data. We use bespoke software, so nothing has changed there. Hardware specification of our server is far more better then the recommended system requirement for SQL Server 2005.Am i missing something out or i havent set up the SQL Server properly? Any help would really be appreciated.Mits
I've noticed that after the database have been idle for some time, it takes up to 10 seconds to get it started when something needs to access it. In the event viewer it says that the database <name> have been started.
Obviously, there is some idle timeout setting.
I saw an option in the database properties that is called "Auto Close" which is set to true. I assume this is what i'm looking for. Can someone confirm that? (it could take some time to test myself...)
But what i'm actually wondering is:
1. Is it possible to adjust how long it would wait before timing out? 2. What advantages does closing the database bring? Does it free up (a noticeable amount of) ressources? Or is it only that it's unlocking the files, so that it's possible to copy the database source files?
I have an Access2000 ADP that I want to run under Access2007. The problem I have is that some forms take up to 45 seconds to open in Access2007! These are not complicated forms--just simple navigable reference forms like setting up transaction types etc. that are based on basic select statements like:
SELECT * FROM ArReceivableType
Where ArReceivableType is a reference table (less than 10 columns, all int or nvarchar(100) max) containing about 15 or 20 rows. They open instantly in Access2000.
I put a trace on to see what is happening on the SQL Server, and I noticed heaps of nasty code like this that generates tens of thousands of reads:
select object_name(sotblfk.id), user_name(sotblfk.uid), object_name(sotblrk.id), user_name(sotblrk.uid) from sysreferences srfk, sysobjects sofk, sysobjects sotblfk, sysobjects sotblrk where srfk.constid = sofk.id and srfk.fkeyid = sotblfk.id and srfk.rkeyid = sotblrk.id and user_name(sofk.uid) = N'dbo' and object_name(sofk.id) = N'FK_FaAssetTransactionWork_ArReceivableType_ArReceivableTypeId'
It looks like Access2007 is reading all of the constraints for the underlying table, including all foreign keys. My SQL database contains 1400+ tables all with properly constructed foreign keys and other constraints.
Any suggestion on how to NOT have Access2007 do this? Right now, Access2000 works great for this enterprise app, but I really like the new Access2007 features (and I don't want to still be developing Access2000 apps in 2010).
I've got a football (soccer for the yanks!) predictions league website that is driven by and Access database. It basically calculates points scored for a user getting certain predictions correct. This is the URL:
http://www.pool-predictions.co.uk/home/index.asp
There are two sections of the site however that have almost ground to halt now that more users have registered throught the season. The players section and league table section have gone progressively slower to load throughout the year and almost taking 2 minutes to load.
All the calculations are performed in the Access database Ive written and there are Access SQL queries to get the data out.
My question is, is how can I speed the bloody thing up! ! Somone has alos suggested to me that I use stored procedures and SQL Server to speed things up? Ive never used SQL Server before so I am bit scared about using it (Im only a hobbyist), and I dont even know what a SP is or does. How easy will it be upgrading the whole thing to SQL Server and will it be worth the hassle, bearing in mind I expect my userbase to keep growing? Do SP help speed things up significantly? Would appreciate some advice!
I am able to run a query which runs FAst in QA but slow in theapplication.It takes about 16 m in QA but 1000 ms on theApplication.What I wanted to know is why would the query take a longtime in the application when it runs fast on SQL server?How should we try debugging it?Ajay
I have an application built with Access 2003 (MDB). It is running under Windows XP without any problems. If I run it under Vista, it works technically well but I get the data very slow from the server.
Server: Windows Server 2003 R2, SP2 SQL Server 2005, version 9.00.3054.00 Firewall: off
Client: Access 2003, SP3
Connection strings: ODBC;DRIVER={SQL Native Client};UID=SD_Admin;DATABASE=SDX;SERVER=MARS;PWD=xxx; or
I am using two almost idential laptops, one with XP and one with Vista, the only differences is that the XP laptop has 1G of RAM and running Office XP and the Vista has 2G RAM and is running Office 2007.
I have a MS Access database that has linked tables to a SQL Server 2000 database. The performance of the Access database on Vista is 5-10 times slower on the Vista machine. Just flipping through records or opening forms can take 5 - 15 seconds on the Vista machine while the XP machine takes 1 sec or less.
What gives? I looked at the CPU performance and the network performance while the Access database was busy flipping through records, the network traffic was < 2% and the CPU would spike to 40% on one of the CPUs (dual core) but would remain under 5% most of the time.
I also previously had Office XP installed on the Vista machine and it had the same performance issue so bought and install Office 2007 on the Vista machine and it did not solve the problem.
It seems that Vista is doing something that is slowing down Access with linked tables. Is this a issue between Vista and using an ODBC connection to SQL Server?
In a Data Flow Task, I have an insert that occurs into a SQL Server 2000 table from a fixed width flat file. The SQL Server table that the data goes into is accessed through an OLE DB connection manager that uses the Native OLE DBMicrosoft OLE DB Provider for SQL Server.
In the OLE DB Destination, I changed the access mode from Table or View - fast load to Table or View because I needed to implement OLE DB Destination Error Output. The Error output goes to a SQL Server 2000 table that uses the same connection manager.
The OLE DB Destination Editor Error Output 'Error' option is configured to 'Redirect' the row. 'Set this value to selected cells' is set to 'Fail component'.
Was changing the access mode the simple reason why the insert from the flat file takes so much longer, or could there be other problems?
I have an update query running which to just now has been running for 22 hours running on two tables 1 a lookuptable that has just been created within the batch the other a denormalised table for doing data analysis on
the query thats causing teh problem is
--//////////////////////////////////// this is the one thats running
Print 'Update Provider 04-05 EmAdmsCount12mths : ' + CAST(GETDATE() AS varchar) GO Update Provider_APC_2004_05 set EmAdmsCount12mths = (Select COUNT(*)-1 from Combined_Admissions where ((Combined_Admissions.NHSNumber = Provider_APC_2004_05.NHSNumber) or (Combined_Admissions.PASNUMBER = Provider_APC_2004_05.PDDISTNO)) and (Combined_Admissions.AdmDate BETWEEN DateAdd(yyyy,-1,Provider_APC_2004_05.AdmDate) AND Provider_APC_2004_05.AdmDate) AND Combined_Admissions.AdmMethod like 'Emergency%')-- and -- CA.NHSorPrivate = 'NHS')) FROM Provider_APC_2004_05, Combined_Admissions
any help in improving speed would be most welcome as there are 3 more of these updates to run right after this one and the analysis tables are almost double the size of this one
I am trying to do the following. I am capturing the logged in username from Windows Authentication then stripping the domain name using the following codeProtected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting Dim Bareusername e.Command.Parameters("@strusername").Value = My.User.Name Bareusername = My.User.Name.Replace("MyDomain", "") e.Command.Parameters("@LoginName").Value = Bareusername End Sub I use the derived value as the where clause in a select query Select * from Master Where AccountName = @LoginName. This allows a user to only see their own information in DetailsView1. This is working as desired. I have another column in the Master table named ID. This ID is the key that I need to reference to allow users to see and edit data in other tables. So I would like to be able to do something like Select ID from Master where AccountName = @LoginName and be able to pass this value to a Select statement against another table(Awards) for a DetailsView2 on the same page. Alternatively the value that I am trying to query is in the first row of DetailsView1. Is it possible to access the value in that field and pass it in the query for DetailsView2? This would be simpler if possible.I am not sure how to go about this and would appreciate any guidance that you could provide. I have found a lot on the web regarding this based on a selected value but not through the method that I am trying to use. Thanks in advance,Ken
I have the following function that is run when a person enters a value, "Cell", in an access form. I am trying to execute the pass through query in SQL Server but i keep getting the following message:
"The microsoft jet database engine cannot find the input table or query 'CellDefs'. Make sure it exists and its name is spelt correctly."
There is nothing wrong with the table name. I cant understand what the problem is ?????
Can anyone tell me how to execute a query that passes a parameter from access to Sql server.
Thanks, Ruaidhri
Public Function GetData(Cell As String) Dim dbsCurrent As Database Dim qd1 As QueryDef Dim SQL As String Dim rs As Recordset Dim qdf As QueryDef Set dbsCurrent = CurrentDb
Set qd1 = dbsCurrent.CreateQueryDef("") With qd1 .Connect = "ODBC;DSN=MISChief;SERVER=Janeway;UID=sa;PWD=;DATA BASE=MIS" SQL = "" SQL = SQL + "INSERT INTO TableX SELECT A.Name AS CellCode, C.FinderNumber" SQL = SQL + "FROM (CellDefs AS A INNER JOIN Calls AS B ON " SQL = SQL + "A.CellDef_id = B.CellDef_id) INNER JOIN Finders AS C ON " SQL = SQL + "B.Finders_id = C.Finders_id " SQL = SQL + "WHERE A.Name LIKE '" & Cell & "' " SQL = SQL + "ORDER BY C.FinderNumber" .ReturnsRecords = False .ODBCTimeout = 2400
dbsCurrent.Execute (SQL) End With dbsCurrent.Close End Function
suppose i get date from MSsql how do i pass that to dos command is there a way so i can maipulate date and pass on to dos command to run at certain time
just getting started with my first db driven web project... I am using a MySql database with membership and roles and got that working fine. I have tables containing details of courses that users are enrolled on etc. and want to display a list of courses for the user that is signed in, so he can continue lessons on the one of his choice. How do I pass the users name to the database query for a DataList control. So far I have tried lots of variations of the following: <asp:SqlDataSource ID="dsCourses" runat="server" ConnectionString="<%$ ConnectionStrings:xxx %>" ProviderName="<%$ ConnectionStrings:xxx.ProviderName %>" SelectCommand="SELECT c.CourseName FROM courses c, enrolments e WHERE c.CourseID=e.CourseID AND e.Username='<% =User.Identity.Name %>'"></asp:SqlDataSource> <asp:DataList ID="DataList1" runat="server" DataSourceID="dsCourses"> <ItemTemplate> <asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="Lesson.aspx">'<%# Eval("CourseName") %>'</asp:HyperLink><br /> </ItemTemplate></asp:DataList> </p> However, the <% =User.Identity.Name %> doesn't work here (but it does elsewhere on the page) - not sure why?? The code works fine if I hard code a user name into the select statement. Suggestions pleeeeeese!!
