Optimize view | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Optimize view

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
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.
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?

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
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.
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
]]>