[wdvltalk] Re: StoredProcedures
Is there a way to pass in the entire WHERE clause as a parameter? yeah, but then there's no point in using a stored procedure, because the advantage of the stored procedure is that the database can figure it out ahead of time (compile it, as it were), and if the retrieval condition columns are known, it can't do that or how about using LIKE and sending in an array? not sure how that would work, but LIKE is notoriously inefficient if you say LIKE foo% then an index might be used but if you say LIKE %foo% then no index will be used if you have an array of primary keys, then that is efficient (and you gain little by having a stored proc) i assume you will be using something like WHERE pkey IN ( n, m, ... ) where n etc. are key values but this begs the question of where those pkeys came from in the first place if they were obtained from a previous query, the question then arises, why do you need to query again, why weren't the tables in this second query included in a JOIN in the first rudy The WDVL Discussion List from WDVL.COM To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] Send Your Posts To: [EMAIL PROTECTED] To change subscription settings to the wdvltalk digest version: http://wdvl.internet.com/WDVL/Forum/#sub http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: archive@jab.org To unsubscribe send a blank email to [EMAIL PROTECTED]
[wdvltalk] Re: StoredProcedures
Thanks rudy, you have a very clear way of explaining things. It makes sense the sp can only be compiled if it is complete. I should have said IN not LIKE but I am sure the same things apply. I will check the indexes again and also see if we can archive some stuff. Bill -Original Message- From: rudy [mailto:[EMAIL PROTECTED]] Sent: Friday, February 07, 2003 3:04 PM To: [EMAIL PROTECTED] Subject: [wdvltalk] Re: StoredProcedures Is there a way to pass in the entire WHERE clause as a parameter? yeah, but then there's no point in using a stored procedure, because the advantage of the stored procedure is that the database can figure it out ahead of time (compile it, as it were), and if the retrieval condition columns are known, it can't do that or how about using LIKE and sending in an array? not sure how that would work, but LIKE is notoriously inefficient if you say LIKE foo% then an index might be used but if you say LIKE %foo% then no index will be used if you have an array of primary keys, then that is efficient (and you gain little by having a stored proc) i assume you will be using something like WHERE pkey IN ( n, m, ... ) where n etc. are key values but this begs the question of where those pkeys came from in the first place if they were obtained from a previous query, the question then arises, why do you need to query again, why weren't the tables in this second query included in a JOIN in the first rudy The WDVL Discussion List from WDVL.COM To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] Send Your Posts To: [EMAIL PROTECTED] To change subscription settings to the wdvltalk digest version: http://wdvl.internet.com/WDVL/Forum/#sub http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] To unsubscribe send a blank email to %%email.unsub%% The WDVL Discussion List from WDVL.COM To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] Send Your Posts To: [EMAIL PROTECTED] To change subscription settings to the wdvltalk digest version: http://wdvl.internet.com/WDVL/Forum/#sub http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: archive@jab.org To unsubscribe send a blank email to [EMAIL PROTECTED]
[wdvltalk] Re: StoredProcedures
On Friday, February 7, 2003 at 23:16, Bill Mais wrote: BM It makes sense the sp can only be compiled if it is complete. the stored procedure will be compiled, but will be recompiled on each use as the sql changed. this means that the database can't use the execution plan it has calculated for that procedure. if you're using ms sql server and want to head down this route, one thing you might want to take a look at is sp_executesql which might be able to get away without recompilation. also be aware of the security implications of using dynamic sql in a stored proc, the user executing the procedure has to have permissions on the tables you are trying to select from. BM I should have said IN not LIKE but I am sure the same things apply. I BM will check the indexes again and also see if we can archive some stuff. if you are using ms sql 2k, you can pass in a string and use a user defined function to return a table that you can join to your query. might be easier. hth, darren The WDVL Discussion List from WDVL.COM To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] Send Your Posts To: [EMAIL PROTECTED] To change subscription settings to the wdvltalk digest version: http://wdvl.internet.com/WDVL/Forum/#sub http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: archive@jab.org To unsubscribe send a blank email to [EMAIL PROTECTED]
[wdvltalk] Re: StoredProcedures
It makes sense the sp can only be compiled if it is complete. actually, it can be compiled as long as it has some idea of what will happen for example, WHERE foo = @param the compiler knows that a value will be substituted at run time it knows that foo has an index, so it knows it can compile the execution plan for this query using an index search so stored procs only have to be as complete as necessary to determine what execution plan to use rudy The WDVL Discussion List from WDVL.COM To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] Send Your Posts To: [EMAIL PROTECTED] To change subscription settings to the wdvltalk digest version: http://wdvl.internet.com/WDVL/Forum/#sub http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: archive@jab.org To unsubscribe send a blank email to [EMAIL PROTECTED]
[wdvltalk] Re: StoredProcedures
Working late, rudy? Or is it early in your neighbourhood? Sherry from New Hampshire (11:30 EST) rudy wrote: Is there a way to pass in the entire WHERE clause as a parameter? yeah, but then there's no point in using a stored procedure, because the advantage of the stored procedure is that the database can figure it out ahead of time (compile it, as it were), and if the retrieval condition columns are known, it can't do that The WDVL Discussion List from WDVL.COM To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] Send Your Posts To: [EMAIL PROTECTED] To change subscription settings to the wdvltalk digest version: http://wdvl.internet.com/WDVL/Forum/#sub http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: archive@jab.org To unsubscribe send a blank email to [EMAIL PROTECTED]
