Slow Scripts!

[bomayed]

Does anyone know why some ASP scripts run slow?

What causes that?

What should we do to make the script run fast?

Thank you

 

[Veep]

The speed of a script is going to be directly related to the amount of processing that the script has to do. Do you have an example of "slow" script that you'd like to speed up? The possibility exists though that there's no way that it's going to get any faster as an uncomplied script.

 

[clarkin]

or, more likely, if your script is getting database info it can be sped up

Show us - or check out:

http://www.aspfaq.com/show.asp?id=2424

(excellent page + further links)
and if you are using SQL server:

http://www.sql-server-performance.com/asp_sql_server.asp

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[bomayed]

Thanks Veep and thanks clarkin . . .

Honestly I have created a script which is suppose to loop and insert 90,000 records in Access file. Each record has 10 fields!

The script seams to take hours to finish . . This is the script:
-----------------------------------------------------------

do while not(record.EOF)
on error resume next
set car_place=cn.Execute ("select * from detail_codes where master_code=7" & " and Detail_Code=" & record("REGISTRATION_SOURCE&quot)


set plate_color=cn.Execute ("select * from detail_codes where master_code=27" & " and Detail_Code=" & record("PLATE_COLOR&quot)


set ticket_place=cn.Execute ("select * from detail_codes where master_code=7" & " and Detail_Code=" & record("TICKET_SOURCE&quot)


set car_type=cn.Execute ("select * from detail_codes where master_code=20" & " and Detail_Code=" & record("VEHICLE_CLASS&quot)

set ticket_amount=cn.Execute ("select * from ticket_fines where ticket_number=" & record("ticket_number&quot)

set ticket_type=cn.Execute ("select * from ticket_fines where ticket_number=" & record("ticket_number&quot)


set ticket_type2=cn.Execute ("select * from ticket_detail_codes where master_code=1 and detail_code=" & ticket_type("fine_type&quot)


set ticket_responsible=cn.Execute ("select * from ticket_detail_codes where master_code=3" & " and Detail_Code=" & record("TICKET_RESPONSIBILITY&quot)


set ticket_status=cn.Execute ("SELECT * FROM ticket_detail_codes where master_code=4 and detail_code =" & record("TICKET_STATUS&quot)


set rss=cnn.Execute("INSERT INTO the_database (plate_number,car_place,plate_color,tek_place,car_type,tek_type,tek_amount,tek_responsible,tek_status,tek_number,tek_date) values ("&record("plate_number&quot&",'"&car_place("DESCRIPTION&quot&"','"&plate_color("DESCRIPTION&quot&"','"&ticket_place("DESCRIPTION&quot&"','"&car_type("DESCRIPTION&quot&"','"&ticket_type2("DESCRIPTION&quot&"',"&ticket_amount("FINE_AMOUNT&quot&",'"&ticket_responsible("DESCRIPTION&quot&"','"&ticket_status("DESCRIPTION&quot&"',"&record("ticket_number&quot&",#"&record("TICKET_DATE&quot&"#)&quot

record.MoveNext
loop

 

[intranetgeeky]

In this case I don't think it's the script that is running slow, it's the write time to the database. Especially Access, which is really not a robust piece of software. Your solution might be found in trying to write to a SQL Server database instead, it is more robust and can handle more (much, much more!) information at one time. Of course, this would mean creating a new database in SQL Server and going through all of the setup stuff for that.

If you want to keep writing 90,000 records to an Access database, you might just have to wait.

 

[Veep]

That's pretty ugly. Unless this is something interactive (which will put users to sleep) I would set up this job in task scheduler or an Access Timer. Also, if any of the columns in that table have indexes on them you should get rid of them (at least for the duration of the insert) as that will slow things down too.

 

[bomayed]

Thanks intranetgeeky and thanks Veep ,

I would use SQL Server Database instead of MS ACCESS but the problem is that I want to upload the database to my website so that visitors can use it through my ASP script!

So what I'm doing is that I'm Selecting records from Oracle Server , Inserting records to MS ACCESS file and upload it to the website. That's my goal!

Is there anything else you guys can advice me to spead up my script? what is better than MC ACCESS and is commonly used in websites?

Thank you . .

 

[intranetgeeky]

I take it you don't have control over the hosting computer (it's outsourced.) So your plan is to actually FTP the Access database file up to your web host after you have updated it with information from the Oracle database. Is this correct?

 

[Bastien]

are your 90k records a subset or the entire db?

Bastien

cat, the other other white meat

 

[bomayed]

Thanks intranetgeeky , The answer is (YES)



Thanks Bastien , the 90,000 records are only 10 fields from the original database not the entire database.

I was thinking , why don't I have 2 or 3 scripts which insert the records at the same time , will that make the inserting proccess faster?

for example I'll let the first script insert records for last year and the second script will handle records for this years and so on . Both scripts will insert the data at the same time . . is that stupid?

Thank you all

 

[bomayed]

Can someone help me plz

 

[jemminger]

rather than using ASP and SQL to perform the insert, such as:

rs = cn.execute("select the 90000 records")
while not rs.eof
  cn.execute("insert one record")
  wend
wend

which results in 90000 insert calls to the database (verrrrrry slow)

you can typically do it all in one SQL statement - only one call to the db

cn.execute("insert into myTable (field1, field2)" &_
  " select foo, bar from otherTable" &_
  " where conditions result in desired 90000 rows")

note that this is all pseudo-code!


=========================================================
-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); }