open vs execute: Which is faster?

[earme]

I thought that opening a recordset using .execute was faster if you didn't need to update the recordset (of course now I can't find the web page I thought I read it on). However, my code seems to hang or timeout on the .execute's much more than the .open's. I want to look at the data being returned, but I don't need to change it.
So, which one is faster?

Thanks,
Earme

 

[link9]

Don't know which one is faster, but I can tell you that there was a post earlier today where a guy (or gal) had a problem opening a recordset from Oracle using .execute()

Although the code looked rock-solid, I suggested that they go through and explicitly create the recordset and then use .open on it to get their data, rather than using the implicit creation of a recordset with .execute

Reportedly, it worked, so I guess I question the stability of the method, whereas I've never had a problem using .open to get my data.

 

[danielhai]

on .execute, the recordset object is being created anyways, but with defaults on. It shouldn't be all that much slow to create your recordset objects beforehand ......

how many execute's do you have on your page anyways?

 

[earme]

danielhai,
In most pages I have execute's once or none at all, but in a couple pages I found it 5 times. In the case of 5 execute's am I right in thinking that I should use open's instead since I create the recordset object once using open's while with execute's I'm creating it 5 times? In the pages with just one it should be ok though?

Earme

 

[link9]

Earme, you should pick one way that works for you and be consistent throughout your code.

You said your page was hanging when you used .execute(). So use .open() and forget about it. For anyone that has to go back into your code, it will make their life alot easier, as well as your life.

When you design the page, you shouldn't sit back and think... "hmmm... how many recordsets am I going to open here? Which one should I use? .execute(), or .open()"?

Personally, I like explicit creation and declaration of everything, rather than the implicit creation of anything. Just makes for more easily readable code, and in this case, you aren't going to see any appreciable performance gain or loss either way.

 

[earme]

I changed 99% of my execute's to open's and my application is running better now. The other 1% were used for running stored procedures and didn't return any records.

Thanks!
Earme

ps. and my code IS more readable now!

 

[Guest_imported]

I read that AdExecuteNoRecords can be used to speed up performance when using .execute, when you know that there is no recordset to be returned. (e.g. when you are inserting, deleting or updating). My colleague reckons that we should use .open anyway, but I have always used .execute.

anyway, it was introduced in ADO 2, read more here.

http://www.asp101.com/tips/index.asp?id=85

also, would you all care to laugh at the recordset function I use at work (due to coding standards) that ALWAYS opens a recordset with AdOpenKeyset and AdOpenStatic, even if it's a very basic select statement? Surely that is the WRONG way to do it?

 

[link9]

Yes, someone else (jonscott8?) posted that about adExecuteNoRecords performance gains here the other day. I have yet to start using it, but I do plan to. Every little bit helps.

And although it isn't "WRONG" (i.e. it will work), it is less efficient to always open up a recordset with those options, but there's an upside to it as well. No matter what you're doing, users won't get an error message pertaining to methods you are trying to execute on those recordsets. But, on the other hand, any and all code should be thoroughly tested before it's stuck out there for users to find errors with it, anyway.

I'm looking into my crystal ball, and seeing alot of user errors (and a lack of internal Q&A) generated in your company's past that caused that coding convention to be adopted. ;-) Looking into it again, I see no such errors in the future. LOL