Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Website Returning Slow Recordsets - ASP

Posted on 2009-05-06
13
Medium Priority
?
231 Views
Last Modified: 2012-05-06
HI Experts,

I have a query regarding a website that I am running written in classic ASP, which was attached to an MS SQL 2000 database (and worked fine) but is now attached to an MS SQL 2008 database and keeps timing out.

The thing is that I am testing it against the same wildcard searches and getting varied responces, in as much as at certain times the results are returned and browser sent to the results page in under two seconds, whereas on other occasions the same search takes more than 60 seconds and the server times the page out. (I know how to increase the script timeout value, but don't want the query to take so long)

The situation is that the website is on a dedicated server (2 x 1.8 GHZ Intel Core 2 Duo) 2.5GB RAM, with a 10240Kbps data pipe, the SQL 2008 database is on a shared SQL 2008 Server.

I know that some of the problem here could be the way the database and queries have been designed, and will look at this at a later stage (the site is live so can't take it down) but as the issue isn't constant, I was curious as to whether the issue could be affected by either -

our dedicated server size -
or
the fact that we are on a shared SQL server?

Please can I have your thoughts.

Many many thanks

Gareth
0
Comment
Question by:garethtnash
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 13

Expert Comment

by:St3veMax
ID: 24312661
Have you updated the statistics for the database or undertook a reindex since you moved it ?

That would be my first port of call.
0
 

Author Comment

by:garethtnash
ID: 24312677
Hi St3veMax,
Sorry how would I do that? - thanks
0
 
LVL 13

Expert Comment

by:St3veMax
ID: 24312697
Run the attached code to produce a SQL Script to copy/paste into a new window to update all statistics on your database tables.
SELECT 
	'UPDATE STATISTICS ' + SS.Name + '.' + SO.Name 
FROM 
	Sys.Objects SO 
INNER JOIN 
	sys.schemas SS ON SO.Schema_ID = SS.Schema_ID
WHERE
	TYPE = 'U'
ORDER BY 
	1 ASC

Open in new window

0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:garethtnash
ID: 24312739
Does that mean that I need to have access to the system database? as Its on a shared SQL server  and I dont think they will give me access to that.
I'll try it anyway - thanks
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24312747
no, its has nothing to do with system database, it will find table and schema from your database and update its statistics.
0
 
LVL 13

Expert Comment

by:St3veMax
ID: 24312757
No; You only need access to the database in question.

Are your DBA(s) not looking into this for you ?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24312759
interesting to know more about UPDATE STATISTICS, have a look at below article to know more.

http://www.sql-server-performance.com/tips/update_statistics_p1.aspx
0
 

Author Comment

by:garethtnash
ID: 24313018
Thanks Guys, Statistics updated, the shared SQL server is at a well known ISP, to be honest with you I'm contemplating buying our own SQL server licence, as they keep changing things without consulting us first, oh and I'm going to be doing a MCDBA course....
However in the mean time, with Statistics updates, I still have the same problem, I've just re run the recordset search a handfull of times and got the results in  -
 
12 seconds
4 seconds
7 seconds
2 seconds
17.69 seconds
I know some of this could be my broadband speed, dont think its my PC - as very powerful, so I'm back at the question, Server or because we are on a shared SQL licence?
 
Any thoughts would be really helpful
 
Thanks Guys
 
P.S saved Statistics Quert in MSSMS and reading up as well
0
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 664 total points
ID: 24313031
it just statistics you have updated, you have to reorganize or rebuilt your indexes also, it stats doesn't help. Moreover, the configuration you saw in not best for shared hosting, it is somehow dependent on your hosting company as well.
0
 
LVL 13

Assisted Solution

by:St3veMax
St3veMax earned 664 total points
ID: 24313033
If it's SQL 2008; you could try running the code below to see what's going on (if you have access)...

Select * from master..sysperfinfo - its Perfmon via SQL Server.

Other than that; you may need to look at re-indexing your tables.

HTH
0
 
LVL 6

Accepted Solution

by:
PIERCGG earned 672 total points
ID: 24313476
In addition to re-indexing and statistics:

If you get different results when running the query more than once with the EXACT SAME search criteria then I would have to guess it is not your code.  The next thing I would look at is finding out what else is hitting the SQL Server instance at the same time as your slow query times.  Are there, possibly, people running some data intensive reports/queries at the same time?  

Also, if the applications you are running are web-based, you can go into IIS and try assigning your app to a different application pool than the other applications running on your application server.  In addition, have you tried examining your queries in the Database Tuning Advisor in SQL 2008 and perform some of its suggestions just to be sure your dbs and queries are running at optimal condition?
0
 

Author Comment

by:garethtnash
ID: 24362315
That is fantastic thank you
0
 

Author Closing Comment

by:garethtnash
ID: 31578371
Very helpfull all thankyou
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question