What effect does Command object have on Database P

Results 1 to 2 of 2

Thread: What effect does Command object have on Database P

  1. #1
    Stephen of New York Guest

    Default What effect does Command object have on Database P

    Hello Gents and Madams;<BR>My question of the day is "What effect does using the Command object have on Database Performance?" As I study 4guysfromrolla script on using the Command Object, it appears to me as though the first thing they do after setting the ActiveConnection Property is execute and SQL script that retrieves All the data in a Table as seen in the Example Below. Correct me if it is not Retrieving all the records of the table.<BR>After they do this, then later in the script, the Record of Interest is retrieved. Please tell me IF I understand correctly and wouldn&#039t this technique significantly diminish performance if you had 1 million records ? <BR><BR>Dim DataConnection, cmdDC, RecordSet, RecordToDelete<BR><BR>&#039-- Create object and open database<BR><BR>Set DataConnection = Server.CreateObject("ADODB.Connection")<BR>DataCon nection.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Session("DatabasePath") & ";"<BR><BR>Set cmdDC = Server.CreateObject("ADODB.Command")<BR>cmdDC.Acti veConnection = DataConnection<BR><BR>&#039-- default SQL this is where they appear to pull all records<BR><BR>SQL = "SELECT * FROM myaccessTABLE"<BR><BR>cmdDC.CommandText = SQL<BR>Set RecordSet = Server.CreateObject("ADODB.Recordset")<BR><BR>Reco rdSet.Open cmdDC, , 0, 2<BR><BR>&#039and then later on in the script they will retrieve the single record of interest. Example<BR>if xyz then do this stuff<BR><BR> SQL = "SELECT myaccessTABLE.ID, myaccessTABLE.* FROM myaccessTABLE WHERE (((myaccessTABLE.ID)=" & Request.QueryString("ID") & "));"<BR><BR>End If<BR><BR>cmdDC.CommandText = SQL<BR>Set RecordSet = Server.CreateObject("ADODB.Recordset")<BR>RecordSe t.Open cmdDC, , 0, 2<BR>&#039 the it works upon the 1 record found.<BR>CAN SOMEONE PLEASE EXPLAIN TO ME what is happening here. Is this something you are forced to do in access but you would you use stored procedures in SQL to do this same thing?<BR>THANK YOU SO MUCH IN ADVANCE<BR>STEPHEN OF NEW YORK<BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: What effect does Command object have on Databa

    I tend to favor commands in a lot of situations, and let me tell you, I never query all of the records, ignore that, then requery for the specific records. I&#039m sure there was a reason why they did it in this specific case, but it&#039s not all that necessary usually. <BR><BR>The reason I use commands is because it&#039s easier to dynamically execute updates, inserts, and deletes that way. Particularly updates. If the processing script doesn&#039t know exactly which fields are to be updated, but is passed a list of fields to be updated, it&#039s a lot easier ot make a command string out of this than to filter through each one and do record("field") = newfieldVal. I don&#039t know if I&#039m making sense here.<BR><BR>That being said, let me change what I am saying to note that by commands, what *I* mean is composing a sql string and calling connection.execute. Using an actual command object is not very useful in Access most of the time, since the main difference is that with commands you can pass parameters and a few flags and junk. I use them a good amount in SQL server, particularly with, as you noted, stored procedures. Another small advantage of commands is just that you can set the command text and repeatedly execute it with command.execute. I haven&#039t ever run across that situation though :)

Posting Permissions

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