SQL Server Performance

Optimize view

Discussion in 'T-SQL Performance Tuning for Developers' started by kfinsrud, May 11, 2004.

  1. kfinsrud New Member

    I have created a view and it takes about five seconds to execute in query analyzer, but the application using the view uses about twenty seconds. So that's why I wonder if there is a possiblity to tune this view:

    create view docsadm.pdAddress as
    select ltrim(accountnumber) AddressID,ltrim(accountnumber) AddresseeID,replace(address1,char(2),'') address1,
    replace(address2,char(2),'') address2,replace(zip,char(2),'') + ' ' + replace(address3,char(2),'') address3,
    replace(country,char(2),'') address4 from xal.xal_supervisor.debtable where dataset='hvi'
    union all select ltrim(p.debtoraccount),ltrim(p.debtoraccount),replace(p.deladdr1,char(2),'') address1,
    replace(deladdr2,char(2),'') address2,replace(p.zip,char(2),'') + ' ' + replace(deladdr3,char(2),'') address3,replace(p.country,char(2),'') address4
    from xal.xal_supervisor.projtable p where p.dataset='hvi'

    The data must be trimmed and char(2) must be replaced with blank.

    Regards,

    Kjetil Finsrud



  2. gaurav_bindlish New Member

    I think this is an bad execution plan selection issue. Did you try recompiling your query in before calling it from application.

    If you posting the query code (as to how view is being used), we can have a look at the same

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  3. Chappy New Member

    When you say 'execute' the view.. how do you mean?
    im assuming your app is selecting from the view with the exact same query as query analyser?

    what connectivity lib connects the app to the database?
  4. kfinsrud New Member

    I mean running the select sentence used by the view in Query Analyzer when I say "execute". The application is selecting from the view like this:<br /><br />Code from asp file:<br /><br />Const DSN_STRING = "Provider=SQLOLEDB;Data Source=****;Initial Catalog=DOCS;User ID=****<img src='/community/emoticons/emotion-4.gif' alt=';P' />assword=***"<br />Const IDENTIFY_STRING = "Address Lookup Server 1.0.0"<br />Const adUseClient = 3<br />Const adOpenKeyset = 1<br />Const adLockReadOnly = 1<br />Const adCmdText = 1<br /><br />Dim objConnection, rsAddress, sSQL<br /><br />sSQL = "SELECT pdAddressee.AddresseeID, pdAddressee.Name, " & _<br /> "pdAddress.AddressID, pdAddress.Address1, pdAddress.Address2, " & _<br /> "pdAddress.Address3, pdAddress.Address4 FROM pdAddressee pdAddressee, pdAddress pdAddress " & _<br /> "WHERE pdAddressee.AddresseeID = pdAddress.AddresseeID"<br /><br /> If Len(Request.QueryString) &gt; 0 Then<br /> sSQL = sSQL & " AND pdAddressee.Name LIKE '" & LCase(Request.QueryString) & "%'"<br /> End If<br />'response.write ssql<br /><br />How do I recompile my query before it is called from the application?<br /><br />This is used in an address component that fetches addresses from clients/matters.<br /><br />Hope this will clear som things up...<br /><br />Regards,<br /><br />Kjetil
  5. gaurav_bindlish New Member

    If it were a SP. it could have been recompiled but....

    I would recommend looking at profiler and try capturing execution plan of both executions to see if there is some visible difference.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  6. harsh_sr New Member

    I'd like to add that Its always better to use queries from ASP using SPs. Its too tempting to just write some lines in ASP page itself & run. But thass not the best way.

    SP can save the network traffic as well as optimised from SQL Server itself rather than uploading files on IIS everytime you change/optimise any query.


    Harsh

Share This Page