Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Avoiding dynamic sql

Author  Topic 

srinath
Starting Member

16 Posts

Posted - 2006-08-28 : 09:00:42
Hi

We 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...

Thanks
Srinath

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"
Go to Top of Page

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
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-28 : 09:59:49
Why is it difficult to change database name?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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..

Thanks
Srinath.
Go to Top of Page

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

Go to Top of Page

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"
Go to Top of Page

srinath
Starting Member

16 Posts

Posted - 2006-08-29 : 03:18:41
Thanks Lumbago for your reply


I 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
Go to Top of Page

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"
Go to Top of Page

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..

Thanks
Srinath
Go to Top of Page

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"
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -