February 22, 2012 at 3:33 pm
Any ideas would be appreciated on why a simple query takes 7 seconds to respond?
Upsized the Access 2010 back-end to SQL Server 2008.
Running one copy of Access 2010 Front end to a SQL Server DB and
one Copy of the same Access 2010 Front-end to the Access 2010 DB (back end).
A form uses a query to open a list box in the first main screen that allows the user to select a Well.
On Access to Access linked DB it opens really fast.
On Access linked to SQL Server - it takes a grueling 7 seconds.
Created a View in SQL Server with the exact same query - It is under a second, but still not as fast as Access
We are not talking complex queries or large tables (see attachment).
Went to SQL Server and removed all indexes for these tables. These table are small with each record well under 500 byte
Nothing changed as far as speed with indexes removed. Changed SQL to Simple Recovery.
The ODBC driver is SQL Server for the SQL version.
SQL Server Version is RTM 10.50.1600.1 - no SP 1, 2, or 3.
The SQL Management Studio appears to run the view (exact same query) very fast for what that is worth.
This is not my first time using Access (2003) linked to SQL Server 2008. With much, much larger datasets. This never happened.
At a new facility and a novice installed SQL Server 2008 RTM. But, I do have sa rights if anyone has ideas.
Regards
February 22, 2012 at 3:37 pm
Lack of indexes
Non-sargable predicates
Stale or missing statistics
Slow disk IO subsystem and/or lack of memory
that's just the first few that come to mind
If by Access linked to SQL you means linked tables and the query in Access, that's probably slow because Access pulls all the data over and joins and filters locally instead of letting SQL Server do the work.
Have you checked for blocking? Checked wait types?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 22, 2012 at 3:51 pm
Thanks for the speedy response.
The tables had multiple indexes that performed great in Access. Removed them one at a time because sometimes an index on a tiny table can get in the way.
No idea about the disk speed.
Found the Resource Waits (thanks) Would running a query from SSMS have different results than an attached table (in theory)?
Attached a screen shot - just opening a form that runs this one query.
Is there anything else to review?
Would not really have thought to check the memory. The last little company had 64 GB for a dedicated SQL 2008 server.
This is a virtual server, so I don't know if this represents actual memory. Or if there is a setting as sa that I can actually use.
(see attachment)
February 22, 2012 at 4:35 pm
I'd recommend to create the view at SQL Server level and tune that view. Once the view is tuned, link it to Access.
A few checks to consider:
Do you have a clustered index on the SQL Server tables or just the primary key constraint?
Do you have covering indexes on the tables involved?
Are the foreign key references defined for the SQL Server tables?
Looking at the requirement and the query it seems like the query will likely return a few thousand rows. Is this really used in a list box?
As a side note, you might want to change the WHERE clause to WHERE WELSS.WELL_Name>''
February 23, 2012 at 2:30 am
Mile Higher Than Sea Level (2/22/2012)
The tables had multiple indexes that performed great in Access. Removed them one at a time because sometimes an index on a tiny table can get in the way.
Err, no. Not in SQL Server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2012 at 6:57 am
This may be a stupid suggestion but here goes anyway:
If you create 2 views - one with
Select Stuff FROM WELLS INNER JOIN on everything else
WHERE (where clause stuff)
and another view
SELECT Stuff FROM WELLS INNER JOIN WELL_STATAUS table
WHERE WELLS.County_ID is null plus other WHERE clause stuff (make sure you add the missing columns from the above query, set to NULL or '')
then in Access
SELECT * FROM 1st view
UNION ALL
SELECT * FROM 2nd view
it might be faster.
February 24, 2012 at 9:33 am
I would look into the settings around the ODBC driver or play around with different SQL drivers. Of course the native sql driver is probably the best way to go
February 24, 2012 at 9:42 am
Would the attached Perf Monitor chart appear normal?
If the IT staff were asked to increase resources, what would you recommend asking for?
Both are the same query - one from Access linked to SQL tables the other run as a SQL View (same query).
The IT group had a person fairly new to setting up a virtual server and new to SQL Server setup.
After reviewing a MSDN article, the setup would appear to be a standard test SQL Server for a virtual server.
In general, the available memory and available disk space appear to be OK.
Narrowed it down to what appears to change drastically.
Do you think the % of processor time and Page Faults/sec is something to be concerned about?
Keep in mind, this is one user, one query on a fairly small data set.
Eventually, about 40 users would each be hitting on this view once ever 4 minutes.
The Access Linked table query takes about 4 to 5 seconds after repeating multiple times.
The SQL Veiw takes about one second, but appears to use much more process time and have more page faults.
Thanks very much for the suggestions. Back in a SQL Server 2000 Performance and Monitoring presentation, the execution plan using indexes was described as being an overhead. So, I am probably outdated. Added the indexes back in, but the results were the same.
Will look at writing a more efficient query as suggested.
It is somewhat interesting that Access to a linked Access DB had a couple of millisecond response using a linked table with dozens of concurrent users.
Found this at Microsoft - but it doesn't give a solution: A high rate for the Pages/sec counter could indicate excessive paging. Monitor the Memory: Page Faults/sec counter to make sure that the disk activity is not caused by paging. A low rate of paging (and hence page faults) is typical, even if the computer has plenty of available memory. The Microsoft Windows Virtual Memory Manager (VMM) takes pages from SQL Server and other processes as it trims the working-set sizes of those processes. This VMM activity tends to cause page faults. To determine whether SQL Server or another process is the cause of excessive paging, monitor the Process: Page Faults/sec counter for the SQL Server process instance.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply