| Author |
Topic |
|
Nalvest
Starting Member
4 Posts |
 Posted - 2007-09-27 : 14:56:18
|
| I have a weird problem with a query we are running.I have a website and a web service - both are calling the exact same query with the exact same parameters.I find that this query through the web service is significantly slower than when being run through the web site. I've used SQL Profiler to get an idea of what's happening on the SQL server side and in the web site it hits SQL server runs in about 1 second and returns. On the web service side it hits SQL Server and times out (set to 60 seconds). I can't figure out why SQL Server would timeout with the same query (and result set) on the web service but work quickly on the web site.If I run the same query (with the same parameters) multiple times on the web service, the second time (and subsequent) it will return but takes significantly longer (20+ seconds). I can't figure out why SQL Server would respond differently. It's a select query, no updates, no locking occurring. I can consistently get it to perform quickly on the web site and consistently get it to perform badly on the web service.Anyone have any ideas of what it might be or have any ideas of how I might be able to dig deeper into troubleshooting what the problem might be?note: both the web site and the web service are running from the same server with the same credentials and connection strings. |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-09-27 : 15:02:39
|
| can you put the query into a proc and see if you still see the same behaviour?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-27 : 15:04:31
|
| "both are calling the exact same query with the exact same parameters"Are you 110% sure of that? Coz that's the most likely reason for there to be a variance.Kristen |
|
|
|
Nalvest
Starting Member
4 Posts |
Posted - 2007-09-28 : 09:22:31
|
| Not easy to put the query in a proc as it's on a live production system - it's also a fairly dynamic query so hard to do as a SP but i'll see what I can do.On the other side, I've relooked at it again and there is a difference, but I can't imagine that it has an impact (heck maybe it does, but it would amaze me) - I'll explain:In the web service we are doingselect field1,2,3,etc from tables where <x>In the web site we are doingselect * from (select top 10000000 rownumber() (Order by field1, field2) as row, field1,2,3,etc from tables where <x>) inner_table where inner_table.row between 1 and 10000000 order by inner_table.rowI can easily change it so that the web service is doing the same pseudo paging mechanism (something I can try) - but I would have thought that that would decrease the performance of the query since SQL has to do extra manipulation on the query - the web service is only getting the result set while the web site is getting it, ordering it, putting it in a table and ordering it again. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2007-09-28 : 14:06:33
|
| I'm a little confused about your pseudo-queries so maybe some sample data and a sample query would help us out here. Also, are you 100% sure that the db-server is actually getting the query and that it's not some kind of connectivity issue instead? What happens when you run a simple "select getdate() as myDate" in the webservice? Are you able to get a result?--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-09-28 : 14:22:06
|
| >>>Not easy to put the query in a proc as it's on a live production system - it's also a fairly dynamic query so hard to do as a SP but i'll see what I can do.Did you say dynamic sql?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
Nalvest
Starting Member
4 Posts |
Posted - 2007-10-01 : 13:41:19
|
| yes - dynamic sql. There are many reasons why we are using dynamic sql including the dynamic nature of these queries.----We have other things running in the web service prior to this query (including logging in) which uses the same database. Those queries work fine. I've got lots of logging and have a profile trace of the database request - it's not a connectivity issue.----I'm trying not to make it too complex, but here's a simple example of what I'm talking about from the query differences (please note that the actual select is much longer and more complex):Web Service:SELECT u.name, a.address, tl.durationFROM users u JOIN address a ON a.addressid = u.addressid JOIN timelog tl ON tl.userid = u.useridWHERE tl.time BETWEEN '1/1/2007' AND '1/2/2007'Web Site:SELECT *FROM ( SELECT TOP 10000000 rownumber() (ORDER BY u.name) AS row, u.name, a.address, tl.duration FROM users u JOIN address a ON a.addressid = u.addressid JOIN timelog tl ON tl.userid = u.userid WHERE tl.time BETWEEN '1/1/2007' AND '1/2/2007' ) inner_tableWHERE inner_table.row BETWEEN 1 AND 10000000ORDER BY inner_table.row |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-01 : 14:32:55
|
| What do you get if you compare the Query Plan for those two "variants" ? |
|
|
|
Nalvest
Starting Member
4 Posts |
Posted - 2007-10-02 : 11:17:45
|
| The query plans are identical except for the last part at the end of the query which is doing top, filter and compute scalar - all of which are 0% cost are working over a small number of rows (300ish). |
|
|
|
|
performance problem with query