| Author |
Topic |
|
jameswoodmancy
Starting Member
13 Posts |
 Posted - 2008-01-11 : 10:44:42
|
| Hello,I have a stored procedure with a simple select statement that queries a complex view. It returns about 10000 rows. This takes about 2 seconds in query analyzer but when I call this in my application using ADO.NET it takes around 3 minutes. Does anyone know why?Thanks for your time |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-01-11 : 10:50:24
|
| are you loading your page with with the data like in a datagrid or something?Be One with the OptimizerTG |
 |
|
|
jhermiz
3564 Posts |
Posted - 2008-01-11 : 11:31:36
|
| show us the codeWeblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
|
|
|
jameswoodmancy
Starting Member
13 Posts |
Posted - 2008-01-14 : 04:40:38
|
| I've used profiler to determine that the SP takes 3 minutes..I have also put insert statements to insert a timestamp before and after the select statement so I know the SP is taking 3 minutes when using ADO...so i dont think the time delay is due to the fact that the data is rendering in the datagrid. The select statement is SELECT DmBuyingAgencyName AS BuyingAgency, ClientName AS Client, ClientShortname, ProductName AS Product, AudienceName AS Audience, StationName AS Station, SalesHouseName AS SalesHouse, ChannelName AS Channel, MonthShortName AS [Month], QuarterNumber AS [Quarter], SpotYear AS [Year], TypeName AS SpecialType, TotalEquivImpressions AS TotalImpressions, CentreImps, EndImps, BlankPiBImps, FirstPiBImps, LastPiBImps, SecondPiBImps, ThirdPiBImps, PenPiBImps, DayPtOneImps, DayPtTwoImps, DayPtThreeImps, DayPtFourImps, DayPtFiveImps, DayPtSixImps, DayPtSevenImps, DayPtEightImps FROM vwQualityAllDetails WHERE DmBuyingAgencyID Not In (Select TheString From fnCharListToTable(@BuyingAgencyIDExclude,',')) AND SpotYear = @SpendYearthanks for your help |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2008-01-14 : 05:40:46
|
| have a read of the topic 'parameter sniffing'. You may be affected. It is also worthwhile posting the executino plan so that we can guess at the core problem. What's in that "function"? |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-01-14 : 10:40:15
|
I'm still not 100% convinced that the issue doesn't have something to do with factors outside sql server. Especially since you are just calling an SP. Forgive me if you've already gone through this but:First, just to confirm we're comparing apples to apples, capture the SP call in profiler ie: the call ADO is actually making to the server rather than the underlying statements within the SP and View. Confirm that the exact SP call (plus parameters) when called from a QA window only takes the 2 seconds where the same call from asp code resulted in a 3 minute wait.Then try disabling all the asp code that reads the results, just do the bare minumum to execute the SP and allow the results to be discarded. If that speeds things up then try adding your asp funcationality back bit by bit to see where the delay is happening.I would also encourage you to follow jhermiz advise to post the asp code including the provider type, connection string (minus the servername and password  ). Some ASP guy may be able to spot something (that would not be me  )Be One with the OptimizerTG |
|
|
|
jameswoodmancy
Starting Member
13 Posts |
Posted - 2008-01-15 : 05:09:24
|
| I've read up on parameter sniffing but I dont think this applies here. Im also pretty sure this isn't to do with the ADO.NET. I am using a SQLWrapper provided my Microsoft to call my SP. I think this is to do with how SQLOLEDB executes its plan different from when its executed in QA. I can get the execution plan for the query when ran in QA. Its difficult to post here but if anyone is geninuely interested in helping me you can send me your email address and I can send it to you. I cant however get the execution plan when the SP is executed from my application so I cant compare. My SP contains a very complex view pulling in data from many tables from different databases. Could this be the case of slow execution time over ADO.NET? |
|
|
|
kiruthika
Yak Posting Veteran
67 Posts |
Posted - 2008-01-21 : 07:36:04
|
| ve read up on parameter sniffing but I dont think this applies here. Im also pretty sure this isn't to do with the ADO.NET. I am using a SQLWrapper provided my Microsoft to call my SP. I think this is to do with how SQLOLEDB executes its plan different from when its executed in QA. I can get the execution plan for the query when ran in QA. Its difficult to post here but if anyone is geninuely interested in helping me you can send me your email address and I can send it to you. I cant however get the execution plan when the SP is executed from my application so I cant compare. My SP contains a very complex view pulling in data from many tables from different databases. Could this be the case of slow execution time over ADO.NET? |
|
|
|
kiruthika
Yak Posting Veteran
67 Posts |
Posted - 2008-01-21 : 07:59:17
|
quote:
Originally posted by kiruthika
ve read up on parameter sniffing but I dont think this applies here. Im also pretty sure this isn't to do with the ADO.NET. I am using a SQLWrapper provided my Microsoft to call my SP. I think this is to do with how SQLOLEDB executes its plan different from when its executed in QA. I can get the execution plan for the query when ran in QA. Its difficult to post here but if anyone is geninuely interested in helping me you can send me your email address and I can send it to you. I cant however get the execution plan when the SP is executed from my application so I cant compare. My SP contains a very complex view pulling in data from many tables from different databases. Could this be the case of slow execution time over ADO.NET?
sorry for the problem |
|
|
|
|
Stored procedure taking longer in ADO.NET