slow performance
-
Like many people we also converted an Access database into an SQL database and an Access client.
When we moved to SQL we noticed that the performance of the frontend was decreased.
With a lot of views on the SQL database we managed to get the same performance, but I still didn't solved the actual problem.
The actual problem is why is retrieving data through ODBC slower that retrieving data from a file server using the same client.
When I just get a table in three different cases
1 from my hard disk with an access database,
2 from a file server with an access database and
3 with ODBC from from the SQL server
the last one is the slowest.
Can someone tell me why this is so, and how I can solve the performance I've lost.
thanx
Marcel
-
Take a look at this lively thread
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=165207
--------------------
Colt 45 - the original point and click interface
-
If your Access query contains many joins or multiple tables, it's slow using ODBC conections. Either you have to use those queries in the SQL Server view or you have to create a stored procedure and have the required data inserted to a table which you can join in Access. Also if all your tables are in SQL Server, then don't do any inserts, updates or deletes in Access. Do it using a sp in SQL Server. You can call the sp from Access.
-
When you use SQL server, instead off using ODBC connection you should use OLEDB connection.
Use ODBC only with WEB based application.
See more doc in MSDN.microsoft.com website.
Kindest Regards,Ramazan KOYUNCU
-
Many of the performance problems that arise after migrating Access to SQL Server are a result of the application not performing as a proper client/server system. Instead data is dragged over the network from the server and processed/filtered on the client.
Have a look at a page on our web site (http://www.aldex.co.uk/sqlupsizing.htmm) which discusses some of these issues.
Regards
David Saville
Aldex Software Ltd.
-
I've found that the use of Access functions will dramatically decrease performance of upsized apps. If you use things like IIF (a favorite, apparently) in your queries, all the data is returned to Access and evaluated locally. To fix the issue, rewrite the query to use a stored procedure and, if necessary, convert some of the Access functions to User-defined functions in sql.
I took an access app that was running 40 sec queries. After upsizing some of the queries went to > 2 minutes. Rewriting as stored procs and user defined functions got them down to sub-second.
-
I highly recommend using stored procedures that are called from Access. A great book on the subject is Microsoft Access Developer's Guide to SQL Server by Sams. Lot's of great code and suggestions. Your ap should start to fly after modification.
Access is a great FE for SQL Server but it has to be coded correctly.
Bill
-
We are working with an SQL server with an access frontend. In this we experiences some performance problems with drop down boxes.
To solve this I want to go into extreem and want to load the data of the dropdown boxes into the memory of the local PC in a way that the the application doesn't have to reload the data from any database what so ever.
I'm looking at a record set wich I can define on a global bases, but this record set can't be used as recordsource for a listbox. To clarify my excersice this is the thing I'm trying:
--Defining the variable
Public testrecordset As RecordSet
--Setting the recodset
Public Sub testre()
Set testrecordset = DBEngine.Workspaces(0).Databases(0).OpenRecordset _
("SELECT * " + _
"FROM [Q_lookup_Financial instrument]" + ";" _
, dbReadOnly)
End Sub
The idee behind this is that the recordset is now in the memory of the local PC, and the data in this recordset should be fast. It also could be that the data is not in the memory, but in a temp file of the access database.
Anyway my question: Did anyone try to do a simular thing, and did he/she had success?
-
Do the values you need from the SQL table change very often? If not, I'd spin through them creating a value list that I assign to the combobox instead of maintaining a connection or a global variable.
Bill
-
De values doesn't change a lot, but every day there are new records. The problem is how fast a form opens. If it's possible, I declare a global variable for useing as a dropdowlist. Now I call the dropdown list from memory. May be this is faster. Does anybody know how to do?
When I start the application, I get the up to date dropdownlist
thank you
Marcel
-
You ought to be using ADO instead of DAO. This example has not been tested. In the Code window of your form, put some code like this at the bottom:
Private Function getValuesForComboBox() As String
Dim rst as ADODB.Recordset
Set rst = New ADODB.Recordset
Dim qry As String
Dim List As String
qry = "Select [MyField] From [MyTable]"
rst.Open qry, CurrentProject.Connection, adOpenStatic, adLockReadOnly
With rst
List = .Fields("MyField")
Do Until .EOF
.MoveNext
List = List & ";" & .Fields("MyField")
Loop
End With
rst.Close
Set rst = Nothing
getValuesForComboBox = List
End Function
In Properties set your combobox properties like this:
Row Source Type "Value List"
Row Source ""
In the Open Event of the form, put in some code like this:
Me.MyComboBox.RowSource = getValuesForComboBox()
Anytime you want to refresh the list just make the same call.
Hope this helps.
Bill
-
Marcel,
Are you using pass through queries to get your data? Pass through queries are normally what is used to call stored procedures. You can insert SQL in them as well. Creating one looks something like this:
Public Function CreateStoredProcedure(strProcName As String, strSQLString)
' Procedure builds a query definition to call a stored procedure
Dim DB As Database
Dim QD As QueryDef
' Open a database from which QueryDef objects can be
' created.
Set DB = DBEngine.Workspaces(0).Databases(0)
On Error Resume Next
DB.QueryDefs.Delete strProcName
' Create a QueryDef object to retrieve
' data from a Microsoft SQL Server database.
Set QD = DB.CreateQueryDef(strProcName)
QD.Connect = Forms!frmhiddenparameters!txtConnect ' Connection String to SQL Database
QD.SQL = strSQLString
QD.ODBCTimeout = 0
QD.Close
'DB.Close
End Function
so I can say:
createstoredProcedure ("sptSelectCust" , "select * from cust order by lname")
and then open the query sptSelectCust to get the results.
These are much faster than access qdfs using linked tables.
-
Marcel,
Using local copies of static or semi-static tables can be a help if you have a lot of drop down lists on a form. However make sure that you do not mix the use of local tables and tables on the SQL Server back end in any queries. Ie make sure that you use the original master table on SQL Server rather than the local Access table in any SQL queries.
If you do not do this then the query will be resolved on the client machine and large volumes of unecessary data may be dragged over the network.
BTW the link I provided for our Access to SQL upsizing page earlier in this thread had a typo in it - it should have read http://www.aldex.co.uk/sqlupsizing.html
Regards
David Saville
Aldex Software Ltd.
-
We never had any performance problems with drop down lists with ODBC connections. We use the DSN-less connection to SQL Server.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply