Ravi Pachai — Sat, 06 May 2000 01:06:00 +0000

Thanks,

It's not just about making the code, it's about making the code the most effiecent eh.

Ravi

------------------
Lyricsh.com
Find the lyrics to over 7,000 Songs.
Lyricsfile.com
Over 20,000 indexed lyric files.

Peter J. Boettcher — Sat, 06 May 2000 00:31:00 +0000

Ravi,

I don't think we need a dynamic cursor for this. Change these lines:

RSArtist.Open SQL,Conn,1,3 to RSArtist.Open SQL,Conn,2,2

and

RSSongs.Open SQL3,Conn,1,3 to RSArtist.Open SQL3,Conn,2,2

And maybe since you're not really retrieving any data from these SQL statements you don't really need to say "SELECT *", change it to just select the primary key like "SELECT ArtistID" & "SELECT SongID".

In the future it might be a good idea to just setup a table that holds this information. So instead of querying the whole database every time, you would just query this table that has the latest totals. You could update this table every day, week or whatever.

Regards,
Peter J. Boettcher

[This message has been edited by Peter J. Boettcher (edited 05 May 2000).]

Ravi Pachai — Fri, 05 May 2000 21:02:00 +0000

It's exactly the one you made for counting the records, its a recordset, then it counts, I'll post it.
<%
Dim SQL
Dim RSArtist
Dim ArtistTotal
Dim RSSongs
Dim SongTotal

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DSN=;UID=;PWD="

Set RSArtist = Server.CreateObject("ADODB.Recordset")

SQL = "SELECT * FROM Artist"

RSArtist.Open SQL,Conn,1,3

ArtistTotal = RSArtist.RecordCount

Set RSSongs = Server.CreateObject("ADODB.Recordset")

SQL3 = "SELECT * FROM Songs"

RSSongs.Open SQL3,Conn,1,3

SongTotal = RSSongs.RecordCount

Ravi

------------------
Lyricsh.com
Find the lyrics to over 7,000 Songs.
Lyricsfile.com
Over 20,000 indexed lyric files.

[This message has been edited by Ravi Pachai (edited 05 May 2000).]

Peter J. Boettcher — Fri, 05 May 2000 18:52:00 +0000

Ravi,

So you're the one slowing down the SQL server!!

Why don't you post the SQL/ASP script you're using and I'll see why it's eating up so much CPU. Just make sure to take out the dsn and password stuff, I don't want anyone hacking your db.

Regards,
Peter J. Boettcher

Ravi Pachai — Fri, 05 May 2000 18:28:00 +0000

Peter,

Jus wondering if you have anything to say about this email I got from powersurge. Sorry, I know this is going a little off topic, but I chose not to start a whole new thread. Hope you dont mind,

<EMAIL>
The reason for this is that the SQL query on the opening page of
"lyricsfile.com" is huge. When we hit up that page, the SQL server consumes
about 60% of the entire time for both CPUs on the server in question here.
That's why it takes so long to come back to the browser.
</EMAIL>

hmm, could be it be lyricsfile.com.

Thanks,
Ravi

------------------
Lyricsh.com
Find the lyrics to over 7,000 Songs.
Lyricsfile.com
Over 20,000 indexed lyric files.

Peter J. Boettcher — Sun, 30 Apr 2000 20:54:00 +0000

Ravi,

A COM object is a piece of code (written in a language like VB, C++, JAVA) that you register on the server to preform tasks that you do frequently or tasks that aren't possible by scripting (system level stuff, impersonating logons, etc).

Then instead of your ASP page handling the scripting or whatever, it passes the values to the COM object for processing. You would only notice the benefits of this if your site was VERY busy.

As to your query lag I'm not sure what the problem is, 20,000 records should not be a problem. I think it has more to do with the fact you're sharing that SQL server with so many people. I'm starting to wonder about how many accounts Powersurge is cramming onto their SQL servers, not to mention their NT servers. I've been finding the DB and web access very slow.

Regards,
Peter J. Boettcher

Ravi Pachai — Sun, 30 Apr 2000 19:45:00 +0000

Thanks Peter and Adam!

What exactly is a COM object? Also I can access lyricsfile.com ok and lyricsh.com, however lyricsfile.com is experiencing some lag. Is it because of the counting in the database? There are almost 20,000 songs, could that be causing the lag?

Thanks,
Ravi

------------------
Lyricsh.com
Find the lyrics to over 7,000 Songs.
Lyricsfile.com
Over 20,000 indexed lyric files.

Peter J. Boettcher — Sun, 30 Apr 2000 19:35:00 +0000

Ravi,

Sorry about that, forgot the speed question. Adam's right, you can feel confident using ASP/SQL 7, it was designed for large applications like you describe.

Just remember when you get into that type of application (thousands of users at once) it sometimes pays to use a COM object to do some common work. So instead of writing a lot of script inside an ASP page, you would just call your COM object, it's a bit more efficient that way. It's just more of a pain to develop since every time you want to make a change you have to recompile and register it on the server again.

I just tried to view www.lyricsfile.com and the site wouldn't come up. If the site is still on Powersurge, then that might explain it, they've been having a lot of problems today.

Regards,
Peter J. Boettcher

Sun, 30 Apr 2000 19:24:00 +0000

Ravi,

If the site is going to get a far amount of use then go for SQL.

------------------
Adam
AIS Internet Solutions
adam@aisinternet.com
www.aisinternet.com

Ravi Pachai — Sun, 30 Apr 2000 19:20:00 +0000

what about speed wise? whats the best database solution when you have thousands of users at once?

Rav
PS - did u see lyricsfile.com yet Peter?
------------------
Lyricsh.com
Find the lyrics to over 7,000 Songs.
Lyricsfile.com
Over 20,000 indexed lyric files.

[This message has been edited by Ravi Pachai (edited 30 April 2000).]