ASP Classic - Alphabetical lists

B

bcap

Hi,

I have a table of contacts and have the SQL listed as ORDER BY
LastName so my contacts list is alphabetical order.

However, I am looking to learn how to disply the list broken up by
letter for easier reading and finding. For example I would like my
page to look like this:

A

Adams, John
Appleseed, Johnny

B

Boggs, Wade
Bugs, Bunny
Bush, George


C

Columbus, Christoper


And so on ...


Does anyone have suggestions on the best way to do this or could
provide any samples?

Thank you kindly, in advance!
 
N

news

Adrienne Boswell at Home
Select * is bad practice. Always explicitly state column names in
queries.

I thought that I was the only one that stated this fact.

OK.
For those that are new to working with databases rather
Access, SQL Server, MySQL, Oracle or others.

When you do this.

sql = "Select * FROM MyTable"

And you have this going on.

<%=rs("Field1")%><%=rs("Field15")%><%=rs("Field19")%>

OK.
When the page renders the information, the Query run against the databse,
and has the search all columns to find the
Once that match the Criteria.
So, this puts a lot of strain on the database, as well as spiking the CPU
during the process. (Tested on a large SQL Server database
And it spiked for 45-seconds at 65% on a 2.66gb Process 2Gb Memory, not
good)

So.
Supply your column names as suggest by myself as well as: Adrienne.

So it should look like this. (Example of a lot of fields)

sql = "Select Field1, Field2, Field3, Field4, Field5,
Field............,Field19 FROM MyTable"

This way, when you run your query against your table, it has the Fieldnames
readily available and there is not extra strain on the Server
To search and find the Columns, as they are their ready to service the
request.

Have a good one.
Wayne
 
E

Evertjan.

news wrote on 05 jul 2009 in microsoft.public.inetserver.asp.general:
I thought that I was the only one that stated this fact.

OK.
For those that are new to working with databases rather
Access, SQL Server, MySQL, Oracle or others.

When you do this.

sql = "Select * FROM MyTable"

And you have this going on.

<%=rs("Field1")%><%=rs("Field15")%><%=rs("Field19")%>

OK.
When the page renders the information, the Query run against the
databse, and has the search all columns to find the
Once that match the Criteria.
So, this puts a lot of strain on the database, as well as spiking the
CPU during the process. (Tested on a large SQL Server database
And it spiked for 45-seconds at 65% on a 2.66gb Process 2Gb Memory,
not good)

So.
Supply your column names as suggest by myself as well as: Adrienne.

So it should look like this. (Example of a lot of fields)

sql = "Select Field1, Field2, Field3, Field4, Field5,
Field............,Field19 FROM MyTable"

This way, when you run your query against your table, it has the
Fieldnames readily available and there is not extra strain on the
Server To search and find the Columns, as they are their ready to
service the request.

You are right in the sense that it is [at least somewhat] cpu intensive.

1
However many pages are low in uses access/time,
sometimes the wole site is not heavily accessed,
and often a page is just for database management
by the webmaster only.

In these circumstances the use of "SELECT *" is not bad practice,
provided that the programmer knows his stuff, and that is what
programming is all aboud, ain't it?

===========

2
However the searching of the fieldnames should only be needed once every
sql execution, and could be only a fraction of the total procedure time
if more than a few records are retrieved.

I cannot believe the time for getting the database names exceeds the
fetching of one record. If not better try better programmed db-engine.

So the extra strain in these circumstances will be minimal.

==========

3
However:

"SELECT *"

plus

<%=rs(0)%><%=rs(1)%>

will not suffer the "extra strain" above, methinks.

[not that I recomment this last practice for multi field dbs]
 
N

news

Evertjan
I cannot believe the time for getting the database names exceeds the
fetching of one record. If not better try better programmed db-engine.

So the extra strain in these circumstances will be minimal.
Did not mention a single record did I?
I mentioned a large Database. That is pulling a LOT of records back when I
first started
With SQL Server.

I took the same script about a year or so later and re-wrote it using the
column names in my SQL Statement.
And the time was a HUGE difference in returning the records.
I learn more as time goes on, and you spread the information that you learn
to others in hopes that they
Will not make the same mistakes as you once did.

Enjoy
 
E

Evertjan.

news wrote on 05 jul 2009 in microsoft.public.inetserver.asp.general:
Evertjan wrote:

[quoteing corrected]
Did not mention a single record did I?

No, but I did, and with reason, see below, where I repeat my reasoning.
I mentioned a large Database. That is pulling a LOT of records back
when I first started
With SQL Server.

Why should a single sql execution, returning say 1000 records,
need to resolve the database names more than once?

Resolving the database names that were not resolved before
because of using "SELECT *" will reasonably not take more than the cpu-
time to resolve one record.

If it does, the engine must be badly written.

So the larger the number of returned records, the smaller the relative
impact of "SELECT *" must be.
I took the same script about a year or so later and re-wrote it using
the column names in my SQL Statement.
And the time was a HUGE difference in returning the records.

Seems improbable to me, seen the above.

Perhaps you also improved your script in more ways,
used different indexing,
or used a newer SQL or ASP version?

The real test should be to test the same side by side,
using both sql strings, and do numerical comparisons, wouldn't it?
I learn more as time goes on, and you spread the information that you
learn to others in hopes that they

Good for you, and goor of you.
Will not make the same mistakes as you once did.

Me? Ah, general you!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top