Please excuse my language. It may look a little confuse.

I know a little bit about SQL Stored Procedure and even less in MTS COM but I'm learning.

I'm implementing ASP web-based to pull data from MS SQL 2000. I have read from some website talking about ASP performance and I did found some topic that suggest try to use stored procedure as much as possible and also paging through records using a stored procedure.

I just curious that what if I create MTS COM (and clear it) 10 times in same ASP script compare with paging through records using a stored procedure (I actually didn't paging just executed stored procedure to get result and show it in ASP.) also 10 times.

- Would it make any difference in terms of server resource usage?
- How about server traffic? Would it still make a trip between IIS & SQL Server same amount of times if I still use MTS COM?
- Or should I use MTS COM instead?

Please advice,

It does have some big differences depending on how your ASP page will work. In general if you are not paging then a persistable COM object is better. That's assuming when you said you clear the COM, you are setting the reference in you ASP to NULL but not actually destroying the object completely. The next time your ASP page is served, the COM object won't have to re-query SQL for the data. But if your data is huge then paging would be faster since much much less data needs to be transferred between SQL and IIS. Of course you could create multiple COM objects to store the pages of SQL data.