isaackhazi
asked on
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.??
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.??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
:(... anyother suggestions??
Thanx for the help so far... appreciate it
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
what do you mean by indexes..... PK's?? well all the Pk's are intact......
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..
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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...
Thanx guy s ... You guys saved my day...
ASKER
Im not an expert on databases....