| Author |
Topic |
|
henrikop
Constraint Violating Yak Guru
280 Posts |
 Posted - 2004-09-16 : 05:46:46
|
| I'm programming T-SQL for some time now (and loving it).I'm trying to make a generic application and database. I used to do a lot with Dynamic SQL, but now I'm wondering how to do things without SQL string builders.If I have a query based on tables in different databases I usually do this: (I have Database1 and Database2)SELECT t1.Price, t2.Tax FROM Database1.dbo.SomeTable t1JOIN Database2.dbo.SomeTable t2 ON t1.uid = t2.uid WHERe t1.Price > 100How will I do this if I don't know the databases names.One customer will name his databases Prices and Taxes (instead of Database1 and Database2).How can I make my query's dynamic?Henri~~~~It's taken a long time, but I've just about learned how to quit flapping my arms and float. It's good to float. -Al Pacino |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-16 : 05:50:54
|
| it's the day of the dynamic sql questions...search this forum, i think i already read it somewhere here, same need... |
 |
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2004-09-16 : 05:55:50
|
| I get time outs and don't know how to use the right words to look for an answer to this question.Henri~~~~It's taken a long time, but I've just about learned how to quit flapping my arms and float. It's good to float. -Al Pacino |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2004-09-16 : 06:13:12
|
| Sorry Jen, but I *know* dynamic SQL, I'm the King of String Builders. I don't want to use Dynamic SQL because of the performance loss. Dynamic doesn't take advantage of a stored procedure with a compiled exexcution plan.The only thing I came up with is to create Stored procedures with my code. It looks up the database names and creates Stored Procedures 'on the fly'.This works fine. But it's not easy updating my software (new versions etc.) unattended. Stored procedures need to be deleted and build up again.That's why I was looking for a different way (and I can't find any).Henri~~~~It's taken a long time, but I've just about learned how to quit flapping my arms and float. It's good to float. -Al Pacino |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-16 : 06:29:51
|
| will this help? although i decided to create the function per database, but you may want to consider spirit's suggestion, can't apply this to mine though...http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=39895 |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2004-09-16 : 06:52:15
|
| Well, thx for thinking and your effort, though the idea is nice, the problem I have is that I have query's over multiple database where I don't know the database names in advance. My software has one database, but every company has it's own databases with Human resources, Orders, CRM, etc. My application is a worklow management tool, because it has to apply to many (different kind of) companies, it must be as generic as possible, but the performance penalty with dynamic SQL is too expensive.Henri~~~~It's taken a long time, but I've just about learned how to quit flapping my arms and float. It's good to float. -Al Pacino |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-18 : 06:02:00
|
| If you are happy with Dynamic SQL and are just looking for Performance improvments from SProcs (rather than, say, tighter Permission / security settings) then I sould suggest parameterising your dynamic SQL and executing if via sp_executeSQL. You can still make the "dynamic" string-concatenated bits for the "Database1.dbo.SomeTable t1" type stuff, but the "WHERe t1.Price > 100" type stuff would become "WHERe t1.Price > @MyPrice" and so would be cached.I did this (alone) for a client who had extensive dynamic SQL in their application and it reduced their execution time by around 60%Kristen |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2004-09-18 : 06:28:05
|
| Thx Kirsten.I'm learning myself to use parameters whenever possible (which is almost always, once you get the hang of it), and indeed, besides security advantages, it's usually faster. It doesn't solve the multiple database issue, but probably it isn't possible.I believe Yukon solves this problem. So I'll try to look into that. My appliction won't be finished before next summer...Henri~~~~It's taken a long time, but I've just about learned how to quit flapping my arms and float. It's good to float. -Al Pacino |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-18 : 07:20:59
|
"It doesn't solve the multiple database issue, but probably it isn't possible."Why not?MyVBString = "SELECT t1.Price, t2.Tax " _ & "FROM " & strDatabase1Name & ".dbo.SomeTable t1 " _ & "JOIN " & strDatabase1Name & ".dbo.SomeTable t2 ON t1.uid = t2.uid " _ & "WHERe t1.Price > @MyPrice" and then execute with sp_ExecuteSQLSure, the first time this runs for "DatabaseX" it won't be in the cache, but the second time it will be!So basically the cache will store a copy of this query for each database name you use.If you only ever query a database once then there is no gain!, but hopefully that's not the case.Same thing if you want to allow the user to choose the sort order:MyVBString = "SELECT t1.Price, t2.Tax " _ & "FROM dbo.SomeTable t1 " _ & "JOIN dbo.SomeTable t2 ON t1.uid = t2.uid " _ & "WHERe t1.Price > @MyPrice" _ & "ORDER BY " & strSortOrderColumnList If you let the users choose the Order By Columns (in strSortOrderColumnList) then there will be several variations of this in the cache, but the popular ones will already be there ...Kristen |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2004-09-18 : 07:45:39
|
| Ah, So what I understand is this:If you use a stored procedure with DECLARE @Some AS NVARCHAR(100)SET @Some = "SELECT * FROM SomeTable "EXEC (@Some)This has not the same performance as:DECLARE @Some AS NVARCHAR(100)SET @Some = "SELECT * FROM SomeTable "ExecuteSQL(@Some)??Henri~~~~It's taken a long time, but I've just about learned how to quit flapping my arms and float. It's good to float. -Al Pacino |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-18 : 10:23:24
|
Yes - except that you have to parameterise it, and use sp_ExecuteSQL, soDECLARE @Some AS NVARCHAR(100)SET @Some = "SELECT * FROM SomeTable WHERE SomePrice > 1000"EXEC (@Some)becomesDECLARE @Some AS NVARCHAR(100)SET @Some = "SELECT * FROM SomeTable WHERE SomePrice > @MyPrice"EXEC sp_executesql @Some, N'@MyPrice int', @MyPrice = 1000 (The whole of the "EXEC sp_executesql" can be executes as dynamic SQL, if needs be - it still gets cached)Kristen |
|
|
|
|
Tables in different databases