I have a list of products that i want to display in a few different tables

for example a motherboard in one table and cpus in another. Im not sure what

sql statement to use right now i'm using:



Array ( SELECT * FROM products WHERE category = 'BRD',

SELECT * FROM products WHERE category = 'CPU' )



but I find this way very slow, could someone please help me out with this,

im a beginning asp author.



Thanks in advance.



Brent Williams

Brent,



Select * from table selects every column for every row meeting your criteria.



Try selecting just the columns you require, eg



SELECT speed, voltage, part_no FROM produts WHERE categoy = 'CPU'



substitute in your column names for speed, voltage etc.



M



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

From: Brent Williams 

Sent: Monday, July 03, 2000 11:49 PM

To: ASP Databases

Subject: [asp_databases] SQL Statement





I have a list of products that i want to display in a few different tables

for example a motherboard in one table and cpus in another. Im not sure what

sql statement to use right now i'm using:



Array ( SELECT * FROM products WHERE category = 'BRD',

SELECT * FROM products WHERE category = 'CPU' )



but I find this way very slow, could someone please help me out with this,

im a beginning asp author.



Thanks in advance.



Brent Williams

If you want to display two different recordsets in two different tables then

you'll have to create two recordsets.



Perhaps you should be looking to optimise your code?

For example, you should never do SELECT *

(http://www.adOpenStatic.com/FAQ/selectstarisbad.asp)



Are you using an OLEDB Provider? or an ODBC driver?



etc



Cheers

Ken



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

From: "Brent Williams" 

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

Sent: Tuesday, July 04, 2000 8:48 AM

Subject: [asp_databases] SQL Statement





> I have a list of products that i want to display in a few different tables

> for example a motherboard in one table and cpus in another. Im not sure

what

> sql statement to use right now i'm using:

>

> Array ( SELECT * FROM products WHERE category = 'BRD',

> SELECT * FROM products WHERE category = 'CPU' )

>

> but I find this way very slow, could someone please help me out with this,

> im a beginning asp author.

>

> Thanks in advance.

>

> Brent Williams

Im use ODBC DRIVER, DSN-Less

Why is it that SELECT * is bad?



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

From: "Ken Schaefer" 

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

Sent: Tuesday, July 04, 2000 4:44 AM

Subject: [asp_databases] Re: SQL Statement





> If you want to display two different recordsets in two different tables

then

> you'll have to create two recordsets.

>

> Perhaps you should be looking to optimise your code?

> For example, you should never do SELECT *

> (http://www.adOpenStatic.com/FAQ/selectstarisbad.asp)

>

> Are you using an OLEDB Provider? or an ODBC driver?

>

> etc

>

> Cheers

> Ken

>

> ----- Original Message -----

> From: "Brent Williams"

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

> Sent: Tuesday, July 04, 2000 8:48 AM

> Subject: [asp_databases] SQL Statement

>

>

> > I have a list of products that i want to display in a few different

tables

> > for example a motherboard in one table and cpus in another. Im not sure

> what

> > sql statement to use right now i'm using:

> >

> > Array ( SELECT * FROM products WHERE category = 'BRD',

> > SELECT * FROM products WHERE category = 'CPU' )

> >

> > but I find this way very slow, could someone please help me out with

this,

> > im a beginning asp author.

> >

> > Thanks in advance.

> >

> > Brent Williams

Did you look at the link Ken offered you? Since it doesn't look like it, 

here's what it says there 

(http://www.adOpenStatic.com/FAQ/selectstarisbad.asp):



"These are my top 6 reasons why you should not use SELECT * in you SQL 

statements. Instead you should enumerate all the fields that you want to 

select. There are probably more reasons but I couldn't think of any more 

when writing this.



1. By selecting only the fields that you want you don't return any 

superfluous fields. This results in an immediate speed increase.



2. If you do SELECT * the database needs to find out what fields are 

actually in the table before it can then select them all. By specifying the 

field names the database engine can use those names straight away rather 

than having to do an extra lookup - which is a speed increase.



3. At some point in time you'll want to look at using the Recordset 

Object's .getRows() method to convert a recordset into an array and close 

your recordset objects earlier. However you will not be able to do this 

effectively unless you know which fields correspond to which array elements 

(which requires you to enumerate the fields in the SELECT statement).



4. At some point you will also look at using the Recordset Object's 

.getString() method which is even faster than the .getRows() method for 

returning recordsets to the screen. If you use SELECT * you will have no 

control over the order in which columns are displayed on the screen.



5. If you have Access Memo type fields, or SQL Server Text type fields 

these need to be selected last in your SQL statement, otherwise you will 

start to run into the problem where these records either do not appear on 

the screen, or are truncated (see Microsoft's KB article: Q200124).



6. SELECT * is lazy coding practise. It's probably best to start with good 

habits early. As well the old saying of "a stitch in time saves nine" is 

very true. Imagine having to trawl through a 1000 line ASP page that you 

coded 6 months ago trying to find all the fields you used to edit a SELECT 

statement at the top of the page. Then imagine doing this for 1000 pages! 

Better to do it right the first time."





On 07/04/00, "Brent Williams" wrote:

> Im use ODBC DRIVER, DSN-Less

Why is it that SELECT * is bad?



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

From: "Ken Schaefer" 

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

Sent: Tuesday, July 04, 2000 4:44 AM

Subject: [asp_databases] Re: SQL Statement





> If you want to display two different recordsets in two different tables

then

> you'll have to create two recordsets.

>

> Perhaps you should be looking to optimise your code?

> For example, you should never do SELECT *

> (http://www.adOpenStatic.com/FAQ/selectstarisbad.asp)

>

> Are you using an OLEDB Provider? or an ODBC driver?

>

> etc

>

> Cheers

> Ken

>

> ----- Original Message -----

> From: "Brent Williams"

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

> Sent: Tuesday, July 04, 2000 8:48 AM

> Subject: [asp_databases] SQL Statement

>

>

> > I have a list of products that i want to display in a few different

tables

> > for example a motherboard in one table and cpus in another. Im not sure

> what

> > sql statement to use right now i'm using:

> >

> > Array ( SELECT * FROM products WHERE category = 'BRD',

> > SELECT * FROM products WHERE category = 'CPU' )

> >

> > but I find this way very slow, could someone please help me out with

this,

> > im a beginning asp author.

> >

> > Thanks in advance.

> >

> > Brent Williams

> Im use ODBC DRIVER, DSN-Less



Don't use ODBC either... :-)

Use OLEDB instead.

http://www.adOpenStatic.com/FAQ/OLEDBConnection.asp 

for Access connection string.



Cheers

Ken