January 8, 2009 at 8:09 am
I have a simple table in a database (87 columns, 2226 records).
Primary key created, indexes on organisation name, Org ID etc.
It returns all data to Management Studio sub 1s but via an Access 2007 application is taking over 15s (according to SQL Response) to run
"Select OrgID, OrgName from Organisation order by OrgName". (I guess its to list all organisations to the application for user selection)
Does anyone have any ideas why such a simple query would take so long?
- The Access application is compiled so there isnt any option to move to a stored proc.
- Both columns are in indexes
There are 3 triggers on the table: 2 for UPDATE,INSERT and 1 for UPDATE but we are dealing with a simple SELECT that is taking a long time.
All indexes are sub 30% fragmented. I have yet to analyse any profiler trace as this ought to be something dumb that I am missing :ermm: ...
Thanks
Jonathan
January 8, 2009 at 8:15 am
is a query in access? or is it a dynamic string run via ADO?
Also what are the connection speeds between your access app and the SQL server?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 8, 2009 at 8:16 am
Can you post the schema, indexes, and execution plan attached in a zip file?
That will help seeing the problem, and we might be able to help better.
How many rows is there in the table?
Is the order by column indexed?
Thanks,
J-F
Cheers,
J-F
January 8, 2009 at 8:28 am
The execution plan is your friend.
That will tell you what's occurring within the query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 8, 2009 at 8:32 am
Christopher;
1 - Its SQL made on the fly in the VBA I would expect. (I didnt write the App!)
**** The 15s is being reported as time to run the SQL at the server, would the line speed make any difference to that?
2 - 2Mbps leased lines
Jean-Francois;
1 - attached
2 - 2226 and yes
January 8, 2009 at 8:35 am
I guess we gonna have to ask for the execution plan.
I did notice that you don't have a covering index for OrgId, OrgName
but lets see what the plan says...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 8, 2009 at 8:37 am
SELECT OrgID ,
OrgName
FROM Organisation
ORDER BY OrgName
Clustered Index Scan on Primary Key - 95%
Sort (on Indexed OrgName column) - 5%
this is whats driving me mad - its such a simple query, even with no indexes and double the data the server should return it faster than this ...
January 8, 2009 at 8:40 am
jonathan allen (1/8/2009)
SELECT OrgID ,OrgName
FROM Organisation
ORDER BY OrgName
Clustered Index Scan on Primary Key - 95%
Sort (on Indexed OrgName column) - 5%
this is whats driving me mad - its such a simple query, even with no indexes and double the data the server should return it faster than this ...
A scan is going to cost more, but since you don't have a WHERE cluase, that's about all you can expect. You should run a trace to see what's happening on the server. It could be this is all an artifact of the network or Access.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 8, 2009 at 8:47 am
Guys, posted the execution plan now.
Thanks very much for your rapid replies - hopefully I'll learn a bit about reading these plans from your comments.
I agree that a scan is not as good as a seek but the app needs a full list of organisations for some reason so it wont ever be a seek...
cheers
Jonathan
January 8, 2009 at 8:50 am
I don't see the execution plan...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 8, 2009 at 8:52 am
Could this be an access issue? Are you connected remotely to the server, and if yes, by what means.
I had that kind of situation when connecting to the sql server through a poor VPN connection, and it gave me those times, because the data retrieval was so long and costy.
Can you connect to the server, and run the same query locally, so we make sure we can break this possibility of poor data retrieving?
J-F
Cheers,
J-F
January 8, 2009 at 9:04 am
Just to clarify:
SQL Response is showing a query taking in excess of 15s to complete. The same offending query is coming from a number of our offices where the application is installed. All our offices connect via a dedicated VPN to the office (where I and the SQL server ) are based over 2Mbps links. I connect to the SQL Server via SSMS on my PC - I dont have the Access application installed. When I run the same SQL as the application I get the whole table in the results grid in < 1s. I am on a 100MB connection to the same LAN as the server. The server is 64bit SQL 2005 Standard edition on windows server 2003 r2.
January 8, 2009 at 9:13 am
Ah, then it's not going to be solved by tweaking indexes and messing with the execution plan. Something is going on in the network or with the Access app.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 8, 2009 at 9:18 am
Agreed, nothing to tweak there, if you can get the query to work perfectly on your workstation, then the query runs fine. Maybe the bandwidth connection between the app and the database is too poor, or the Access Application is poorly designed.
J-F
Cheers,
J-F
January 8, 2009 at 9:23 am
I agree with the others.
Have some more questions.
What are the actual speeds that are showing across the line? perhaps there is conjetion.
Also is this the only query where you have noticed a time difference?
Are you using ADO or ODBC is the access app an ADP or MDB?
Thanks
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply