Slow on SSMS and fast from application

Question

Slow on SSMS and fast from application

Nick123-481228

Mr or Mrs. 500

Points: 551
More actions
September 21, 2009 at 5:20 am

#215499

Hi,
Stored procedure takes 50 mints if we run from SSMS(SQL Server 2005 Developer edition) and same store proc takes 2 mints from Java applcation where we use ODBC connection.
Store proc does select statment on huge table(90 millions rows) with couple joins.
Any idea ?
Regards

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Preet_S SSCrazy Points: 2124 More actions · Answer 1

The only thing I can think of is that SSMS is also an app that is bound by the limitations of .NET and incurs its own overhead. I know there are limits on the number of rows returned because of the .NET components used in SSMS.

Nick123-481228 Mr or Mrs. 500 Points: 551 More actions · Answer 2

Hi,

Thanks for quick reply.

But the store proc returning total around 18,000 ods no more then that.

This is very stragne behivaour i have seen first time.

If anyone come across same situation and sharing thieir exprience will be really appreciated.

Regards

Nick

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

Check the connections settings of both to ensure they're making the same choices regarding ANSI NULL's, etc. That can make a big difference in performance. Also, if you're trying to get the execution plan through the GUI, I'v seen the occasional performance hit, depending on the complexity of the query.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Nick123-481228 Mr or Mrs. 500 Points: 551 More actions · Answer 4

Can you please elborate what do you mean.

Currently both are using TCP/IP and ANSI_NULL is on.

Thanks

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 5

There are a whole series of connection settings you can modify. Take a look at the server properties for the default set. Implicit transactions, cursor close on commit, ansi warnings, ansi nulls, arith abort, etc.

Some of these can affect the behavior of queries.

If you can, see if you're getting different execution plans. Query the cache using sys.dm_exec_query_plan for the app & generate an execution plan from SSMS. See if they're different. That'll tell you a lot if they are.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software