Author |
Topic |
srinath
Starting Member
16 Posts |
Posted - 2006-08-28 : 09:00:42
|
HiWe have written a stored procedure where in we have build dynamic sql as the stored procedure exists in one database and the tables we need to access in other database... So when we look at the trace in the profiler we found that the dynamic statements is taking time to prepare the query and execute.. I searched thru net and its been suggested that to avoid dynamic sql.... Can you guys please suggest me how to avoid the use of dynamic sql in this context..I don't want to hardcode the database name...I know we can use synonyms but the synonyms are not available in sqlserver 2000... ThanksSrinath |
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-08-28 : 09:11:49
|
I don't think you can have dynamic database names without using dynamic sql. Why don't you want to hardcode the names?--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
srinath
Starting Member
16 Posts |
Posted - 2006-08-28 : 09:17:24
|
As we move from development to testing and then to production ..It will be difficult to change the database names...Also please suggest us we can take any steps to avoid the preparing time |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-08-28 : 09:59:49
|
Why is it difficult to change database name?MadhivananFailing to plan is Planning to fail |
|
|
srinath
Starting Member
16 Posts |
Posted - 2006-08-29 : 00:10:30
|
As we have a lot of aql statemenst in the SP, When we move from the development to test... we need to do a lot of changes and as we don't have permissions on the test database we need to ask someone else to do that...Eventhogh Now I hardcoded all the database names and ran the stored priocedure now its taking more time than the previous one...I look at the trace and have seen for all the sql statements the dynamic sql is taking more time.. Still when it comes to overall execution the SP with out dynamic sql is taking more time... I don't know why its happenning... Can anyone have any idea..ThanksSrinath. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-08-29 : 00:15:15
|
It's a difficult to help without know what your SP is doing.Maybe you can post your Stored Procedure, the table structure and explain what are you trying to achieve. KH |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-08-29 : 02:40:17
|
The reason why this isn't a common issue at all is that most people have the exact same environment setup for their test and production servers. That means same sql server specifications, same hardware, same databases, same everything. So having different database names in test, dev and production is a bad design and should be changed as soon as possible because this problem will only get bigger as your system grows. If you are determined to use dynamic database names there is no other option than to use dynamic sql...at least not to my knowledge. One thing you can do is to hardcode the database names, then when you move from test/dev to production you script out all your procedures, open the script in some text editor and then do a search/replace with the new database name. It's a manual operation that will give you some headache but as far as I can see it's the only way...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
srinath
Starting Member
16 Posts |
Posted - 2006-08-29 : 03:18:41
|
Thanks Lumbago for your replyI have done the same way by hardcoding the database names...I was shocked to see that SP is taking more time than before i.e when I executed the SP with Dynamic sql in it.. I got the result in 19 secs where as when I run it by harcoding the database names ..I got the result in 30 secs.. I tried to compare the traces and I have observed that all the Sql statements that I used were executing faster with Hardcoded database names rather than Dynamics Sql staements but when it comes to overall execution time its totally different...Can anyone tell me why its happenning...Thank You,Srinath |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-08-29 : 03:30:31
|
I'm confused...you are saying that the dynamic sql is slower but the overall execution time using dynamic sql is faster? What does this mean exactly?--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
srinath
Starting Member
16 Posts |
Posted - 2006-08-29 : 06:23:48
|
Sorry.. I think I misinterpreted the generated trace...The dynamic sql is executing faster than the SQL where the database names are hardcoded.... The Stored Procedure which is having dynamics SQL in it is taking 19 secs to executed when I modified the stored porcedure by removing the dynamics sql i.e hardcoding the database names then The stored procedure is taking 29 secs to execute..Still I am lloking into the reason for this.. Can anyone know why its happening plz let me know..ThanksSrinath |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-08-29 : 06:30:13
|
I've only used dynamic sql a handful of times in my career and I really don't know too much about it's performance. I guess it could be a query plan caching / recompiling issue but I suggest you make a new thread for this question.--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
KenW
Constraint Violating Yak Guru
391 Posts |
Posted - 2006-08-29 : 13:38:02
|
srinath,quote: Can anyone know why its happening plz let me know..
As was mentioned several posts ago by Lumbago, without knowing what your SQL and data look like, the most help anyone can give you is "you need to optimize your query".Post some data and SQL, and you'll have a chance of getting some help.Ken |
|
|
|