Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Slow performance after Migration from Access to SQL back-end Expand / Collapse
Author
Message
Posted Saturday, June 11, 2011 12:07 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:03 AM
Points: 4,052, Visits: 4,192
naphan.710 (6/7/2011)
thanks


What does your ad have to do with this thread?


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1123822
Posted Saturday, June 11, 2011 5:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
naphan.710 (6/7/2011)
thanks

Spam reported.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1123851
Posted Tuesday, June 14, 2011 1:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 02, 2013 4:44 AM
Points: 237, Visits: 54
when using SQL views, joining take place on the server which generally gives a better performance than locally by MSAccess queries. So you can replace your queries by SQLServer views and attach these as linked tables. Though there are some restrictions when you want to update.
Post #1124779
Posted Wednesday, June 15, 2011 5:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 10, 2012 3:53 PM
Points: 9, Visits: 24
Hi
The MS White Paper Optimizing Microsoft Office Access Applications Linked to SQL Server
http://msdn.microsoft.com/en-us/library/bb188204(v=sql.90).aspx
contains a useful and fairly exhaustive discussion of the issues and the means to address them.
Cheers
Post #1125596
Posted Sunday, June 19, 2011 10:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 17, 2013 12:17 PM
Points: 3, Visits: 22
Microsoft Access can be a marvelous Front End application to a SQL Server database, IF you plan well. Here are some quick tips

USE TIMESTAMPS ON EVERY TABLE! Without a timestamp, Access makes a column-by-column comparison on every column in your table for DML queries. So, a simple update like:
UPDATE t1
SET C1 = XX
WHERE ID = N,
is converted to,
UPDATE t1
SET C1 = XX
WHERE t1.c1 = t1.c1, t1.c2 = t1.c2, t1.c3= t1.c3….,

This can cause full-table-scans and locks. Just watch the sql profiler and you will be amazed at the differenece a timestamp makes! This is the biggest mistake that all my students and customers make when using Access against SQL server and it causes everyone to poo poo Access.

USE FILTERS. Depending on your access version and connection, filters are processed server side, so you don’t need to dump the entire table to the Access client in order to filter your SQL server data. see http://support.microsoft.com/kb/304259.

USE PASS THROUGH QUERIES. Depending on your access version and connection, Access queries use the local Jet engine to perform query work. So, selecting one record from a table of 1 million records may require all 1 million records to be dumped to the client in order for Access to process the query and return one record. See http://support.microsoft.com/kb/303968.

QRYTMP
I use one Access query (qrytmp) to run all my procs. It returns and DAO recordset that can be bound to forms and reports, or just browsed in the Access query analyzer. So, I use ONE Access query for EVERYTHING!

Remember, however, passthrough queries return non-updateable recordsets. you still need linked tables and/or access queries and filters for that.)

Public Function ram_fnCreateTmpQuery(strSQL As String, intTimeOut As Integer, blnReturnsRecords) As Integer
Dim qdf As DAO.QueryDef
On Error GoTo Proc_Err
ram_fnCreateTmpQuery = 1

' Creates a temporary access query to call stored procedures
' where strSQL is any T-SQL statement, such as
' EXEC sp_WHO2
' UPDATE t1 SET C1 = X WHERE t1.ID = nn

'Delete the Query if it exists
On Error Resume Next 'in case qrytmp does not exist.
DoCmd.DeleteObject acQuery, "qryTmp"
On Error GoTo Proc_Err

Set qdf = CurrentDb.CreateQueryDef("qryTmp")
With qdf
.Connect = gostrODBCConnect 'Use this if u you have a global ODBC or DSN Connection string
'CurrentProject.Connection 'http://support.microsoft.com/kb/281784
'CurrentProject.AccessConnection 'http://support.microsoft.com/kb/281784
.SQL = strSQL
.ReturnsRecords = blnReturnsRecords
.ODBCTimeout = intTimeOut
End With

ram_fnCreateTmpQuery = 0 ' No Error occured
Exit Function

Proc_Err:
ram_fnCreateTmpQuery = 1 ' An error occured
Exit Function
qdf.Close

End Function
Post #1127859
Posted Sunday, June 19, 2011 11:20 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:03 AM
Points: 4,052, Visits: 4,192
Robert Magrogan (6/19/2011)
Microsoft Access can be a marvelous Front End application to a SQL Server database, IF you plan well.


In my opinion even if you do plan well Microsoft Access can cause a lot of problems.


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1127866
Posted Monday, June 20, 2011 1:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:48 PM
Points: 100, Visits: 780
What's a timestamp and how do you add it to every table?
Post #1128009
Posted Monday, June 20, 2011 6:08 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:03 AM
Points: 4,052, Visits: 4,192
grovelli-262555 (6/20/2011)
What's a timestamp and how do you add it to every table?


Please refer to the following article:

http://www.sqlteam.com/article/timestamps-vs-datetime-data-types


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1128151
Posted Monday, June 20, 2011 7:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 17, 2013 12:17 PM
Points: 3, Visits: 22
The timestamp is a binary number used to determine whether or not someone has changed the data between the time you fetched the record and the time you updated the record. Its purpose is to prevent us from over writing someone else’s changes.

The following script can be used to add the TIME STAMP column to one table at a time.
ALTER TABLE yourtablename ADD TIMESTAMP

The below script will add the TIMESTAMP column to all tables in your database. WARNING! THIS WILL ALTER ALL YOUR TABLES! This script assumes you do not already have any TIMESTAMP columns in your tables.



DECLARE @TableName TABLE ( query VARCHAR(1000) )
INSERT INTO @TableName
SELECT 'Alter table ' + Name + ' ADD TimeStamp'
FROM sys.objects
WHERE Name <> 'dtProperties'
AND type = 'u'

--The below line is used to look at the data prior to executing it.
SELECT *
FROM @TableName

--WARNING - this script will ADD the TIMESTAMP COLUMNS TO ALL your tables!
--Comment out the RETURN line to auto run this script.
RETURN

DECLARE @sql VARCHAR(1000)
SET @sql = ''
WHILE EXISTS ( SELECT *
FROM @TableName
WHERE @TableName.query > @sql )
BEGIN
SELECT @sql = MIN(@TableName.query)
FROM @TableName
WHERE @TableName.query > @sql
EXEC(@sql)
END
Post #1128261
Posted Monday, June 20, 2011 8:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:48 PM
Points: 100, Visits: 780
Thanks!
Post #1128271
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse