• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

MSSQl attached Db too slow

I have a job board website being hosted somewhere in the US. In certain sections of the website, whne the search criteria for a cnadidate becomes too long the server times out. So i decided to host the website on the intranet. I have a server with MS Server 2003 and MSSQl 2008 installed on it. I downloaded the database files ie MDF and log file and attached it to the server. I setup the website to run locally and everything was fine till i logged in.

Now, everytime i search for something in the website the server timesout.
Beofore when the website was hosted in the US it used to timeout ocassionally.

So i started running queries and checking why the server was timing out.

I found this:

certain columns in the tables are 'computed' type. So some columns get the data from other columns.
Eg: 'age' column computes the data from the date_of_birth column etc ect... there are around 5 such columns which some times compute data from other table columsn aswell.

What i dot understand is , why is the databsae responding like this??

When ever i run a query with any of these 'computed columsn' , even to return like 100 rows the query takes around 90 secs
but when the query doesnt involve any of these 'computed columns' its done in 2 secs.  

I dont understand why it worked ok when it was hosted ?? and doesnt work ok now.

Is it because i havent configured the MSSQl server right.
IS there anyway from preventing it from taking so long.??
0
isaackhazi
Asked:
isaackhazi
  • 4
  • 2
  • 2
3 Solutions
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
As you have attached database from another, your Statistics might be outdated or not applicable.
Hence Update Statistics of all table using the code below.

Referred from http://www.sqlservercentral.com/scripts/Index+Management/31823/
and posted the code since if you dont have login you might not be able to access it.
USE pubs -- Change desired database name here
GO
SET NOCOUNT ON
GO
DECLARE updatestats CURSOR FOR
SELECT table_name FROM information_schema.tables
	where TABLE_TYPE = 'BASE TABLE'
OPEN updatestats
 
DECLARE @tablename NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)
 
FETCH NEXT FROM updatestats INTO @tablename
WHILE (@@FETCH_STATUS = 0)
BEGIN
   PRINT N'UPDATING STATISTICS ' + @tablename
   SET @Statement = 'UPDATE STATISTICS '  + @tablename + '  WITH FULLSCAN'
   EXEC sp_executesql @Statement
   FETCH NEXT FROM updatestats INTO @tablename
END
 
CLOSE updatestats
DEALLOCATE updatestats
GO
SET NOCOUNT OFF
GO

Open in new window

0
 
isaackhaziAuthor Commented:
Ok i Ran the script for my database. What do you mean by update statistics??..  
Im not an expert on databases....
0
 
isaackhaziAuthor Commented:
i check it with a query and the improvemnet in speed is from 1 min for the query to 58 seconds.
:(... anyother suggestions??

Thanx for the help so far... appreciate it
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
tigin44Commented:
did you check the indexes on the columns of tables used by your query. You should have unindexed columns which causes the table scan resulting longer execution time. Check the columns indexes especially used in join operations. If you can provide your query me may guide you more. maybe your query need to be tuned..
0
 
isaackhaziAuthor Commented:
what do you mean by indexes..... PK's??  well all the Pk's are intact......


0
 
tigin44Commented:
Indexes are guides to the location of the data. indexes can be created on every column or column pairs. PK are special type of indexes. they are clustered and you can have only one clustered index on a table while you con have many unclustered index.Using indexes speeds up the acces to the desired data..
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Indexes are objects which will keep track of the locations of records..
In simple words, you can compare it with Index pages in a book.

This will help you more on it..

http://www.odetocode.com/Articles/70.aspx
0
 
isaackhaziAuthor Commented:
This was probably the solution ....... THe problem i was facing was that some stored procedures were using other tbales to compute values for the selected table and the columns in those tbales were not indexed...

Thanx guy s ... You guys saved my day...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now