...

View Full Version : GetRows or SELECT?



jaywhy13
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

ghell
01-24-2005, 05:00 PM
getRows still uses sql o.O

its just faster than the

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


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 :p

jaywhy13
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.Close

OR

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

fractalvibes
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
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

jaywhy13
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?

ghell
01-25-2005, 10:54 AM
didnt paste before, its from fv neway :thumbsup:

http://www.codingforums.com/showthread.php?t=48537

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)

jaywhy13
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

ghell
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:

jaywhy13
01-25-2005, 06:47 PM
thanks alot man!
:eek: there's so much more to learn!

fractalvibes
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.

fv

jaywhy13
01-26-2005, 02:07 PM
I'll definitely be keeping that in mind man!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum