HI,
I copied SQL Server db to AppHarbor's server. Schema is the same (tables are indexed). Locally query is executed in 2 sec, on AppHarbor it is running and does not end...
What should I do? Db is not fragmented.
SQL server is slow
Discussions are closed to public comments.
If you need help with AppHarbor please
start a new discussion.
Keyboard shortcuts
Generic
| ? | Show this help |
|---|---|
| ESC | Blurs the current field |
Comment Form
| r | Focus the comment reply box |
|---|---|
| ^ + ↩ | Submit the comment |
You can use Command ⌘ instead of Control ^ on Mac
#1 Posted by Bohdan on 10 Apr, 2011 07:37 PM
Query was executed in 11 minutes :(
#2 Posted by friism Support staff on 10 Apr, 2011 07:40 PM
Which server are you running on?
#3 Posted by Bohdan on 10 Apr, 2011 07:41 PM
db002.appharbour.net
db1698
query:
declare @p1 xml
set @p1=convert(xml,N'113100pa desc100true')
exec [dbo].[GetShapeStats_v2] @ShapeCriteriaXML=@p1
#4 Posted by Bohdan on 10 Apr, 2011 07:43 PM
query was cut, I am sending attached file
#5 Posted by friism Support staff on 10 Apr, 2011 08:43 PM
We're in the process of implementing additional monitoring and management of our SQL Server instances. The instance your database is located on is heavily loaded right now. One possible resolution would be for you to delete your current database and create a new one. The new database would be assigned to a more lightly loaded instance. I realise this is not very satisfactory, and I would like to stress that we are working on ways to ensure more consistent SQL Server performance.
#6 Posted by tom.philip on 11 Apr, 2011 11:54 AM
This comment was split into a new private discussion: SQL server is slow
Hi,
I'm having the same problem. Getting sql timeouts from a simple query to select a user by email address (only one record in table). I'm on db003. Re-creating the db didn't create it on another server either.
Hope you can help
#7 Posted by friism Support staff on 11 Apr, 2011 04:43 PM
@Bohdan the server your database is on is no longer heavily loaded, are you still experiencing problems?
#8 Posted by Bohdan on 11 Apr, 2011 05:18 PM
I will check tonight (currently website is pointed to SQL Azure)
Sending you some numbers from yesterday
Locally - 2 sec
SQL Azure - 14 sec
appharbor - 26 sec
#9 Posted by Bohdan on 12 Apr, 2011 12:40 AM
appharbor is 24 sec today it is still not acceptable :(
What should I do to improve performance?
#10 Posted by friism Support staff on 12 Apr, 2011 04:04 AM
Would you mind if I poked around your database to see what the stored
process does?
On 11/04/2011, at 17.40, Bohdan
<[email blocked]>
wrote:
#11 Posted by kg4lod on 12 Apr, 2011 05:48 AM
Same problem: db1179 on db000.appharbor.net
Select * From [parts-Common] returns in 30 seconds for 1501 records. Usually this returns in less than 1 second (from my past use on AppHarbor). Queries are executing so slowly now that my site is breaking with SQL Server timeouts...
Please feel free to poke around in my database, repo, etc... (whatever you need)
Cheers,
-Jonathan
#12 Posted by Bohdan on 12 Apr, 2011 05:03 PM
Michael Friis, you can play with db
#13 Posted by friism Support staff on 19 Apr, 2011 01:40 AM
@Bohdan I've just tried running the query on my local machine (fast Intel SSD, Core2 Duo) and it completed in around 12s. I've also taken a glimpse at the stored procedure, and it looks rather complex and has many joins. Is the 2 second number you are quoting using the same ammount of data as on AppHarbor/Azure?
At any rate, I don't think the performance you are seeing on AppHarbor is out of line within our expectations. If you need higher performance, I recommend optimizing the stored procedure. We can also discuss setting up a dedicated AppHarbor database server on a faster instance for you.
friism closed this discussion on 19 Apr, 2011 01:40 AM.
friism re-opened this discussion on 19 Apr, 2011 01:55 AM
#14 Posted by friism Support staff on 19 Apr, 2011 01:55 AM
@jf I've just tried running that query, and it returns resonably fast. The slow query time may have been an anomaly, please let us know if it happens again.
It seems the induvidual rows in the [part-common] table are very large. Unless you need all the columns, you can probably optimize the query by only selecting the columns you need.
friism closed this discussion on 19 Apr, 2011 01:56 AM.