Printable View
I have migrated a database from ms access to ms ms sql. But I am encountering a problem with the website loading speed. It was running much faster with ms access then it is running with ms sql.
Any inputs or thoughts are appreciated.
Treat it as urgent.
Did you create indexes in your tables?. This is the most important deciding factor for speed.
No i did not but the thing is that the querries run at a proper speed but when it comes to the html formating the website slows down.
any ideas
How you are connecting to SQL server through ODBC?
If it is asp.net use datagrid control which will take care of it.
BTW. You need to create index (as skhanal said) and table hints on your queries.
Thanks for the suggestions guys
I am connecting in the following manner
<!-- #include file="adovbs.inc" -->
<%
dim db
dim strConn
strConn = "Provider=SQLOLEDB.1;persist Security Info=True;User ID=myid; Password=mypswd; Initial Catalog=servername; Data Source=localhost"
Set db = Server.CreateObject("ADODB.Connection")
db.open strConn
%>
this is a seperate file and is included in all other files
and it is simple asp and not asp.net
Suggestion:
Give ip address or the real server name instead of "localhost"
Initial Catalog=databasename instead of servername
If you had installed SQL Client on that box add the alias name of the server in the client network utility.
Create non clustered index on those table where you are retrieving and in the select query use table hints like
select col1,col2 from tabl1 (index=myindex) or use stored procedure with table hints
BTW. you have MSDE or SQL Server standard edition?
Thanks Mak for your suggestions
But i have a doubt. If i querry the database using the querry analyser it gives me the result in 0.00 secs. But if i happen to through the same results out by a asp page it takes around 10 seconds and some times more. I dont understand what is causing such a problem
thanks
shrenik
Probably IIS server is overloaded. did you look at how busy the system is?