SQL Server Performance Forum – Threads Archive

Dynamic SQL performance question.

I have a community site that right now are in swedish. Im currently working on a english version on the same site with diffrent forums and private message systems and so on. I took the decison to split my tables and have for example one private message table for each country to make the site faster and get more "clean" code without to much if statements and stuff. I have most of my quries in Stored Procedures and it can look something like this. SELECT u.Nick, u.UserID, pm.subject, pm.datum, pm.message
FROM swe_tPrivMessage pm INNER JOIN tblUser u ON u.userID = pm.FromUserID
WHERE privmessageID = @privMessageID AND toUserID = @userID As you see i have "swe_tPrivMessage". On the english version of the stored procedure i have the exact same code but i use "eng_tPrivMessage". My question is if there is any good way of doing this without haveing to use diffrent stored procedures. I know i can use sp_ExecuteSql or EXEC() but do i gain any performance if i use them? I might as well execute the SQL from ASP then? /Ola

---

Using sp_ExecuteSQL has the performance gain over calling from ASP or using Execute. The execution plan for the statement passed in sp_ExecuteSQL is cached and so the next time the statement is run, execution will be faster. HTH. Gaurav

---

Honestly, I would have kept all messages in the same table with different code for each language, and maybe put a cluster index on lang_code + MessageID + toUserID. Performance should be as good as with 2 tables, with the benefit of having 1 stored procedure for each task. It would also be easier to maintain. You would obviously have to pass the lang_code to the stored procedure.
If it is still possible for you to change table structure? If not let us know and we will come up with another solution. Bambola.

---

If you compare execute from ASP, Execute via sp_ExecuteSQL and using a standard stored procedure how big difference is there. Does it depend much on whats in the querie?<br /><br />Bambola:<br />Its not just that table i have. Its lots of tables and other issues then just performance that is behind my decison to put it in diffrent tables. So i dont want to merge the tables <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br /><br />But i really want to hear some more advice and tips on how to do this with both maintenence and performance in mind with my current database design <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />

---

Standard Stored procedure will be fastest and most difficult to code & maintain(Considering large no. of procs reqd.)
sp_executesql will be less fast than Stored procedure but easy to maintain. (No change at SQL end any time)
Executing from ASP will be slowest and cheapest to maintain. (Saves little bit code in ASP). Take your pick. Gaurav

---

I understand.
Something to take into consideration when using sp_executesql is security issues. You will have to give permissions (select update etc) on all tables within your queries (I am assuming you are NOT using the sa account to access the database via ASP). Personally, I would try to avoid this approach when possible. If you do decide to go with sp_executesql, make sure you are using it with parameters and controling quotes. Search for SQL injection if you need more information. Bambola.

---

Thanx! I think i do something in beetween. The stored-procedure that i know will be used alot i do standard stored procedure. The ones i know will be used less freqvently i use sp_executesql for. One more question. Can i send in a SQL statement and just run like sp_executesql (@sqlQuerieFromASP). Or do i have to build the SQL string in the stored procedure?

---

You can, but I’d still recommend you call it from a stored procedure and use it with parameters <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br />You can check BOL for examples.<br /><br />Bambola.

---

Hi Stuck, as Bambola has mentioned there is a potential security risk with dynamic SQL. Apart from granting an overhead in permission. For the pitfalls of dynamic SQL I found this to be a good source http://www.algonet.se/~sommar/dynamic_sql.html Cheers, Frank

---

Security is not so important in my project .. <br /><br />Thanx everyone for the replies <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br /><br />/Ola

---

Hi Ola, I would be very careful with such statement. You’ve said, you’re running a public community site. I guess none of your users would be happy, to know his private data he not securely kept. SQL Server Security is one of the most important topic around. Of course, there has to be a trade-off between security and your needs. But in your case SQL Server is exposed to the web and potentially exposed to injection. BTW, I’ve made the experience that in almost any cases, there is a workaround for dynamic SQL, be it if-clauses taht evaluate input parameter, be it case structures and so on… Good luck! Cheers,
Frank

---

Security is always relative. Exposure/loss of data on a messageboard (community forum) where members use only nicknames without really storing their personal data may not be financially/legally alarming. In this case even performance may also just be a DBA instinctual worry -and not an actual user demand. I’ve always found that requirements (performance, scalability,security etc) will, more often than not, go hand in hand. Nathan H.O.

---

Hi vbKenya, agreed with that. As I’ve wrote there is always a trade-off. And one must determine oneself what is wanted. Cheers,
Frank

---

]]>