Results 1 to 8 of 8

Thread: Speed issues with MS Sql

  1. #1
    Join Date
    Jul 2003
    Posts
    4

    Speed issues with MS Sql

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Did you create indexes in your tables?. This is the most important deciding factor for speed.

  3. #3
    Join Date
    Jul 2003
    Posts
    4
    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

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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.

  5. #5
    Join Date
    Jul 2003
    Posts
    4
    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

  6. #6
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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?

  7. #7
    Join Date
    Jul 2003
    Posts
    4
    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

  8. #8
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Probably IIS server is overloaded. did you look at how busy the system is?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •