I'm writing an ASP.Net application which retrieves information from
an Oracle Database using ODP.Net. I normally use Microsoft SQL Server
2000, so Oracle is a little bit of a change for me J. I've been told
that using a stored procedure should always query faster than querying
from a raw sql string since a stored procedure is pre-compiled. I.E.:


MyOraCmd.commandText = "sp_myStoredProcedure"
MyOraCmd.commandType = commandType.storedProcedure

Should be faster than

MyOraCmd.commandText = "select * from table"
MyOraCmd.commandType = commandType.text

Also, since I am using stored procedures in Oracle for this
application, I have grouped all of the stored procedures into a package
specifically for this application.

Now for the reason I'm posting this question. There are a few cases
where I need to query on an undetermined number of items, which works
nicely using "IN" in the were clause of a sql statement:
Select * from table where item in ('abc','def','ghi')

The problem is that this doesn't work nicely with a stored procedure
(as far as I can figure out). Currently I am passing a string into a
stored procedure, and then in the stored procedure, I am passing that
string into a function (that I found on google) that parses the string
into a table, that I then select from. This is working for me, but it
seems to not be very efficient. Queries that use this method seem to
be slower than ones that do not.

I then tried the same query, but sent it as a raw SQL from my code. It
seemed to be more efficient. I'm thinking because it did not have to
parse trough the string and cast the information as a table. But this
way the query isn't pre-compiled.

Are there any better alternatives? Is one of these ways better than
the other? What is the "Best Practice" in this type of situation?

Thanks in advance

Andrew