View Full Version : GetRows or SELECT?
01-24-2005, 03:11 AM
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!!! :D
01-24-2005, 05:00 PM
getRows still uses sql o.O
its just faster than the
Do While Not rsWhatever.EOF
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
Set objConn = Server.CreateObject("ADODB.Connection")
For i = 0 To 119
Set rsSomething = objConn.Execute("..")
Set rsSomething = Nothing
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 :p
01-24-2005, 10:42 PM
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.Open "Select * FROM table_with_500_records WHERE recordID=" blah blah ", ConnObj
:thumbsup: thanks for the help thus far
Really tryin to get deepen my relationship with asp... thanks for settin me up thus far man! :D :D
01-24-2005, 11:02 PM
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
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!
01-25-2005, 09:38 AM
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? :confused:
I think i'm getting it now? Am I on tha right track?
01-25-2005, 10:54 AM
didnt paste before, its from fv neway :thumbsup:
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 :p)
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 :p)
01-25-2005, 02:12 PM
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? :o
01-25-2005, 04:43 PM
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 :thumbsup:
01-25-2005, 06:47 PM
thanks alot man!
:eek: there's so much more to learn!
01-26-2005, 05:58 AM
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.
01-26-2005, 02:07 PM
I'll definitely be keeping that in mind man!