SQL Server Performance

Performance with Access 2000 as FrontEnd

Discussion in 'Performance Tuning for DBAs' started by mima, Mar 3, 2004.

  1. mima New Member

    Hello!<br /><br />The Background:<br />SQL Server 2000 whit SP3a<br />Access 2000 (all SP installed)<br />Win 2000 SP4<br /><br /><br />We have upgreat the SQL Server from 7.0 to 8.0 (2000) and now I have Problems with<br />the Performance on Stored Procedures.<br /><br />If I call a SP from Access I have to wait more then 4 Minutes, but if i call the<br />same Procedure whit the Query Analyzer its run in less then 8 seconds!!!<br /><br />Side Information I have read the kompl. Topic:<br /<a target="_blank" href=http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1356>http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1356</a><br /><br />But the diffrent is whit the Query Analyzer it go whitout problems.<br /><br />I have take a look to the execution Plan from the access SP-Call vs. the Query Analyzer<br />SP-Call, the issue is that the execution Plan is diffrent!<br /><br />If I´m call the SP from Access with a other SP and use Like sp_executeSQL p_xyz it<br />go fast!<br /><br />And I´m really don´t like to learn all about hint´s, but it´s to late <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />How I can bring the SQL-Server to create the same execution Plan`s like by call´s<br />from the Query Analyzer on Access?<br /><br />I work on this Problem now 1 week...<br /><br />Sorry for me English...
  2. satya Moderator

    I appreciate your information and insight about the issue and the major advantage of SQL is cross-platform compatibility. As you'd seen using SP_EXECUTESQL is to increase possibility of query is being cached, the issue where query is getting complicated.

    So it would be better if you choose to recompile the involved stored procedures & triggers by using SP_RECOMPILE. For this refer to books online for more information.

    I would suggest few KBAs to deal the issue:
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q243586 - troubleshoot slow running queries
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q243589 - troubleshoot adhoc queries


    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. FrankKalis Moderator

    Hi, no need to excuse for bad english [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />I'd say without providing more information as for DDL, your statement, maybe the textual presentation of the query plan any advise will be like a shot in the dark. <br /><br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>

Share This Page