is it better (faster performance) to put a conditional in the query

statement, or test using a conditional in the loop through the recordset?



I've tried both on a very small db, and the conditional inside the loop

looks a little faster, but this seems odd since more data would be in the

recordset.



thanks in advance

99.9% of the time it would be much faster to put the condition on the WHERE

clause of an SQL statement, and offload the processing to the DB, rather

than doing some conditional filtering on the recordset in a loop.



Cheers

Ken



----- Original Message -----

From: "Michael Goldman" <mg188@h...>

To: "ASP Databases" <asp_databases@p...>

Sent: Friday, November 24, 2000 7:59 AM

Subject: [asp_databases] performance question





> is it better (faster performance) to put a conditional in the query

> statement, or test using a conditional in the loop through the recordset?

>

> I've tried both on a very small db, and the conditional inside the loop

> looks a little faster, but this seems odd since more data would be in the

> recordset.

>

> thanks in advance

I think your own results says it all. It probably doesn't matter much if the

resultset is small, but does if it is large.



I think the best solution (for SQL 70) is to use a parameterized stored

procedure

to process the query. This solution works equally well for static and

dynamic queries.



The less processing that the web server has to do, the better.



I have a SQL db with over 8M records in one table. I have found that a

sproc with parameters works the best. I have a sproc that returns a

cross-tabulated

(13 fields are summed) recordset based on a value passed in from a

<SELECT></SELECT>

tag on the web page On average, the SQL returns a resultant recordset in

less than one minute.



hth,

Dallas Martin





----- Original Message -----

From: "Michael Goldman" <mg188@h...>

To: "ASP Databases" <asp_databases@p...>

Sent: Thursday, November 23, 2000 3:59 PM

Subject: [asp_databases] performance question





> is it better (faster performance) to put a conditional in the query

> statement, or test using a conditional in the loop through the recordset?

>

> I've tried both on a very small db, and the conditional inside the loop

> looks a little faster, but this seems odd since more data would be in the

> recordset.

>

> thanks in advance

>

>

 Michael,





It's always better to do such things on database level. If you can put the

test in the query, do so.

This means that your recordset will be uch smaller when it is retreived, and

you have the exact result right away.

Remember that ASP is interpreted and not compiled, so every check is a new

line to read in your code => 1000 checks are 1000 extra lines.



Koen



-----Original Message-----

From: Michael Goldman

To: ASP Databases

Sent: 23/11/00 21:59

Subject: [asp_databases] performance question



is it better (faster performance) to put a conditional in the query

statement, or test using a conditional in the loop through the

recordset?



I've tried both on a very small db, and the conditional inside the loop

looks a little faster, but this seems odd since more data would be in

the

recordset.



thanks in advance