Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102

    Unanswered: "order by" is slowing page load

    hi guys,
    the following test script works fine and displays a list of cars from the fairly small database, but if I specify the sort order in the querystring, the page takes ages to display and usually times out. Can someone look over it please and tell me where I can fine-tune it for performance or redundant code?
    thanks
    M


    Code:
    <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
    <%
      Dim oRS,oConn,myOrder,strSQL
    
     Set oRS = Server.CreateObject("ADODB.Recordset")
     Set oConn = Server.CreateObject("ADODB.Connection")
     'next, a couple of test lines to prevent timeout (seems to have no effect)
     oConn.CommandTimeout = 0
     Server.ScriptTimeout = 0
     
     Set strOrder = Request.QueryString("Order")
    
     oConn.ConnectionString = "Provider=MSDASQL;DRIVER=SQL Server;SERVER=address;UID=userID;PWD=password;DATABASE=name" 
     oConn.Open
    %>
    
    </head>
    
    <body>
    <%		
     strSQL = "Select make,model,price from vehicles where cat = 'car' AND active = 'yes'"
     if strOrder <> "" then
      strSQL = strSQL & " ORDER BY " & strOrder
    end if
    
     oRS.Open strSQL, oConn, 2, 3
    	
    oRS.moveFirst
          Do while not oRS.eof
    	 
    	make = oRS("make")
                 model = oRS("model")
    	price = oRS("price")
    		 
    		   
      %>
      <%=make%> <%=model%> <%=price%><BR>
      <%
    	oRS.MoveNext
      	   loop
     
    	   oRS.close
    	   set oRS= nothing
    	   oConn.close
    	   set oConn=nothing
    	   %>
    </body>
    </html>
    Last edited by darkmunk; 03-14-07 at 09:56.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    well you could use a bubble sort to handle the sort in the app code (where presentation issues should be handled) or you could index your order by column.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    thanks, urm...what's a bubble sort and how do I do it?
    and how do I create an index?
    the columns to sort by are text and updated regularly by the CMS.

    I have Googled indexing SQL Server and cursor types, but all the stuff I find assumes an already high level of understanding which is really annoying (the opposite of a Microsoft help file which states the obvious - "this a whim whom slurping valve, use it to slurp whim whoms...") sorry, I digress.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah? Go read the IBM or Oracle Manuals.....

    In any case, are we talking about SQL Server in the first place?

    Do you have a dba or are you "it"

    And why aren't you using stored procedures?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    This nifty asp you've written is wide open to sql injection. Do you know what that is?

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oh God, Oracle manuals are the worst...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I think the bubble sort thing was a little joke by Sean that went over your head.

    bubble sort is a sorting algorithm, and one of the least efficient ones. it's a CS 101 kind of algorithm. search for it on google, but don't use it. In fact most pages you find that describe it should tell you not to use it.

  8. #8
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    Can you elaborate on the sql injection problem please?
    I have had some scum bags trying to put their smutt into my database before.

    Brett, what do you mean by "Do you have a dba or are you "it""
    Feel free to direct me in the right direction

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Who administers your database?

    Like, who creates tables for eaxmple?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by darkmunk
    Can you elaborate on the sql injection problem please?
    I have had some scum bags trying to put their smutt into my database before.
    http://www.rockyh.net/AssemblyHijack...Hijacking.html

    Gist is - if you concatenate strings from user input and submit to the database for execution and you are wide open to SQL Injection. Parameterise your data accces (don't try cleaning your strings).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by pootle flump
    Parameterise your data accces (don't try cleaning your strings).
    Why can't we clean up strings?
    I've always used simple replace commands to stop SQL injection in my Access projects...
    George
    Home | Blog

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by georgev
    Why can't we clean up strings?
    I've always used simple replace commands to stop SQL injection in my Access projects...
    Oh yeah...like what?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    replacing apostrophes, equals signs, asterix' and percentages...

    Eg. replace(<fieldname>,"'","")
    George
    Home | Blog

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Great..parse this

    DECLARE @sql varchar(8000)
    SELECT @sql = 'SELECT * FROM Orders GO DROP DATABASE dbname'
    EXEC(@sql)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Sounds like a problem around tempdb. Query the waittype value in the sysprocesses table, to see what this is hanging up on. Your tempdb is not set to autoshrink, is it?

Posting Permissions

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