November 26, 2002 at 10:06 pm
HI All,
I am trying to developing a database in which i have felxible and generic design e.g. i can add 2 more columns but i will not have to change my views and all other major queries except very few, but the problem is that the queries (SQL) will have to be dynamic. I see this a prformance hit because there will be no existing query plan for these dynamic queries, is there any formula through whcih i can calculate the possible performance hit. I need some advice that how should i procede. Secondly if some one has already worked on this kindly guide me in designing this kind of flexible structure.
Thanks in advance
Kind Regards,
Affan
November 27, 2002 at 1:51 am
Hard to give any advice without knowing more about what you need to accomplish, but one advice I can give you is that if you really need to use dynamic queries, you can use sp_executesql to execute them instead of EXEC. That way, if you do it right, you will have a good chance of reusing execution plans for the queries.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
November 27, 2002 at 2:09 am
Thanks Chris,
Since i will generate all the queries by looking at the metadata so i will have to generate those queries at run time so thats why i need to run those queies dynamically.
please explain that what do you mean by "That way if you do it right". If i use sp_executesql shouldn't it save the execution plan or i will have to do something else as well.
Kind Regards,
Affan
November 27, 2002 at 5:56 am
Well, there are a couple of prerequisites:
* All object names should be fully qualified.
* Use paramater substitution so that the actual statement text remains the same.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
November 27, 2002 at 8:23 am
The big key being
Use paramater substitution so that the actual statement text remains the same.
If you use the metadata and change the column names or tables involved every run then the likelyhood of a reused plan goes down. However, it still does overall give better performance than EXEC.
Is there a lot of possibilitites in choices or is it limited. If limited then you may be able to build generic SPs with a central logic SP so the execution plan for all can be saved. But I am guessing it is numerous.
1 suggestions I will make is log ther queries and see if any are run more often than others. If so the make those SPs and build logic to choose the SP over the dynamically built. This will give you an increase in execution on those most often used. This however can get complicated but does pay off in the end.
November 27, 2002 at 9:57 am
A word of warning trying to maintain a generic database of this nature will cause a serious maintenance and support overhead. In addition to increasing development time considerably. I have found that you have to go through lots of iterations before you get it working.
What usually happens as well is that you get everything done and then realise you have to do something extra which doesn't fit, you either hack it in of redesign. This is what becomes costly.
Sorry to be the voice of doom. I would just make 100% sure this is what you have to do.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 27, 2002 at 10:22 pm
Please explain
"Use paramater substitution so that the actual statement text remains the same."
Since i will be pulling all the text from a table and it will remain the same.
Secondly i agree that maintenace will be a key issue then what other options or techniques are left. Since lot of product vendors e.g. SAP, Epiphany etc. do this and i want to know to that how do they do that. Is there any help available on this topic?
Kind REgards,
Affan
December 3, 2002 at 10:15 pm
dynamically generating queries leaves you open to an SQL Injection attack. If you allow users to provide input that you use in your queries, limit the input length to something appropriate (ie. not varchar(8000)) and exclude any text that follows or includes a "--" string.
I would avoid what you are proposing.
December 4, 2002 at 4:23 am
quote:
Please explain"Use paramater substitution so that the actual statement text remains the same."
Since i will be pulling all the text from a table and it will remain the same.
Secondly i agree that maintenace will be a key issue then what other options or techniques are left. Since lot of product vendors e.g. SAP, Epiphany etc. do this and i want to know to that how do they do that. Is there any help available on this topic?
Kind REgards,
Affan
FOr the first part I mean if you build the string dynamically like this
DECLARE @SQLStr NVARCHAR(8000)
SET @SQLStr = N'SELECT * FROM tblX WHERE COlname = @val'
EXEC sp_executesql(@SQLStr.....)
Then you are changing only the searched for value and in this scenario a straight SP is far superior.
But if you instead are changing the colname in the string during build then the in memroy execution plan may or may not be used. ANd if it changes everytime it becomes less and less likely it will hurting overall performance.
For the second, I am not sure how they do it but you have to consider they have years of programming experience behind their applications and many, many man hours to boot.
December 4, 2002 at 4:35 am
I was thinking of building the whole string using cursor as i'll be building the SQL string which require , as well.
so string will be dynamic toatlly.
Secondly i am just curious that how do product vendors implement metadta concept.
Regards,
Affan
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply