Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts

    GetRows or SELECT?

    Now here's the deal.... in a desperate attempt to understand the usefulness of the "SELECT .... WHERE..." against the speed of the getRows method.

    I have 50 records.... wanna find a record with a particular id, somewhere in the database. Do I "SELECT * FROM table" then use getRows OR do I just "SELECT * FROM table WHERE...."

    Then now I've got 500 records..... "SELECT FROM table.... WHERE...." or SELECT ALL and use getRows

    I figure that even though the SQL will pinpoint the record.... it must take some server strain to get it done... so which is better!!!

  • #2
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    getRows still uses sql o.O

    its just faster than the
    Code:
    Do While Not rsWhatever.EOF
    	...
    rsWhatever.MoveNext
    Loop
    although there isnt much of a difference unless you are reading a LOT of records, on MSSQL

    as MSSQL goes through a network layer not just though the hard drive, the longer the connection is open, the slower it is to load

    getRows() puts it in a 2d array, heres what i got when i asked this question, its very useful i'll prob put it in its own function so i can use it as closely as possible to the other method (heres a useful hint, if a function's returntype is an array you can use like myFunction("something")(0) .. but beware that this will run the function every time so you should put arrMyFunction = myFunction("something") ... then.. arrMyFunction(0)

    also, make sure you close recordsets as soon as possible, getRows is faster only if you open the recordset, run getrows and close it again before running the loop, a LOT of people dont close connections at all, but a lot of people that do, dont close recordsets, i have a loop that itterates 120 times, each time opening a recordset and closing it again, like

    Code:
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open ...
    For i = 0 To 119
    	Set rsSomething = objConn.Execute("..")
    	....
    	Set rsSomething = Nothing
    Next
    objConn.Close
    Set objConn = Nothing
    so the recordset opens and closes each time, but the connection doesnt, i dont know if it would be more efficient to open and close the connection each time, but i doubt it

  • #3
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I'm thinking about what you said..... and another question pops into my head:
    Which is more tasking for the server to perform?

    RS.Open "Select * FROM table_with_500_records", ConnObj
    use get rows
    RS.Close

    OR

    RS.Open "Select * FROM table_with_500_records WHERE recordID=" blah blah ", ConnObj




    thanks for the help thus far
    Really tryin to get deepen my relationship with asp... thanks for settin me up thus far man!

  • #4
    Regular Coder
    Join Date
    Aug 2002
    Location
    Texas
    Posts
    287
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Always best to let the database handle such things. If you know the id of the record you are after, by all means select it in you sql:

    Select * FROM table_with_500_records WHERE recordID=" blah blah

    better yet, if you know what columns you want
    Select column1
    , column2
    ......................
    , columnN
    FROM table_with_500_records WHERE recordID=" blah blah

    Your first statement:
    Select * FROM table_with_500_records",

    is wastfull of bandwidth and database resources, plus tosses you 499 rows you don't even need!

    fv

  • #5
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    thanks for tha insight man! So what i'm getting so far from what you guys are saying is that the data that is returned is the "real strain" on the server and not the actual query?

    I think i'm getting it now? Am I on tha right track?

  • #6
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    didnt paste before, its from fv neway

    efficient mssql get data

    you are sorta right, its the process of getting the data that makes it slow, and if you have more data than you need it hogs memory (which is why you should always use as small a datatype as possible, dont use a long if you know the number -255 to 255 (i think thats the range, it might be -256 )

    depending on the querymethod, techinically sometimes it may take longer to find a record than just display all, but forget about that as i think it isnt the case in anything to do with asp or databases it can use (as they can use indexed sequential and stop after they find it, as well as other methods that i dont understand )
    Last edited by ghell; 01-25-2005 at 12:08 PM.

  • #7
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Alright thanks alot man! I was doing some thinking though and I just really wanna push this question a little further... thing is... I understand that MS Access tends to use indexed sequential methods and hashing algorithms and other algorithms to make the records traverses and seraches quite efficient.... So I figure that in the long run it goes something like this:

    When I use the sql to pinpoint the record ("WHERE recordID=" blah) its better coz there are more prominent & efficient algorithms being used than i would create to traverse the database and find the records

    And even if I could come up with an algorithm that I would say matches the competence of the ones used in Access... chances are I would be clogging up in order to retrieve all the records... correct?

  • #8
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    well they are just ontop of the database algorithms anyway, so you would be doing it twice when you could be doing it once, and you would have to load the whole database into the memory etc so its better to just select what you need

  • #9
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    thanks alot man!
    there's so much more to learn!

  • #10
    Regular Coder
    Join Date
    Aug 2002
    Location
    Texas
    Posts
    287
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yep - you are on the right track. The database engines that drive these various databases are built to be very efficient at retrieving data according to the SQL they are executing. Learn your SQL very well and let the database handle as much as possible.

    fv

  • #11
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I'll definitely be keeping that in mind man!


  •  

    Posting Permissions

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