Stored Procedure Executes Very Slowly When Run From VB App But Quickly From QA

[RupeshMX]

M Using SQL Server 2000 And VB6 Application.
When The Procedure Is Executed From Query Analyser It Executes in around 30 Minutes. But When Exeuted From The Application Does Not Come Out Even After 12 Hrs.
I Have Found That The Procedure During Execution Comes at Some Query Which Uses Hash Tables And Stops at That Query.

However The Query Executes Quickly When The Same Procedure Is Executed From The Query Analyser.

Can Anyone Suggest.
Thanks.

[vb5prgrmr]

There should not be that much of a difference between QA and VB6.0. Are you sure you are supplying all the required inputs from VB6.0? Does the SP return records? What else is going on during this processing? How much memory does the system have and are you sure you are not running out? What is it that could be different between QA and VB when it comes to those hash tables? There is so much information missing and I'm not sure that you are in the right forum as you may need to be in a SQL forum to optimize your SP so it takes less time and to perhaps find out the difference between using QA and a program...

Questions, we have questions... :)



Good Luck

[RupeshMX]

There should not be that much of a difference between QA and VB6.0. Are you sure you are supplying all the required inputs from VB6.0? Does the SP return records? What else is going on during this processing? How much memory does the system have and are you sure you are not running out? What is it that could be different between QA and VB when it comes to those hash tables? There is so much information missing and I'm not sure that you are in the right forum as you may need to be in a SQL forum to optimize your SP so it takes less time and to perhaps find out the difference between using QA and a program...

Questions, we have questions... :)



Good Luck

Yes I agree, I Have Never Faced Such a Problem In My Experience Till Now. The Execution Stmt. I M Running IN QA Is Directly Taking From The The Profiler Stmt. Thats Excuted When Called From Front End App.

My Procedure Is Very Long, I Have Found THat When Called From VB Application, Initially It Executes As Fast as From QA. But After It Reaches Some Particlar Query It Slows Down. THe DiskIO at That Time Stops Changing Quickly. Previously The Procedure was Called From DLL, Now For Test Purpose I Called The Procedure Directly From VB6 Application Form. I Also Tried Replacing The #tbls with actual Physical tables.

[vb5prgrmr]

Hmmm... I wonder... What is your growth amount? I'm wondering if you are filling up your db and it needs to grow... thus the slow down as it tries to figure out what to do next... Another thought, how many threads do you allow SQL server to have and how many of your statements execute before it starts to slow down? How much memory do you allow it to have? How much hard drive space do you have on the data drive? How much free space on the OS drive where the swap file is prior to running this and when the slow down happens? Is OS and data drive the same? (What I was kind of eluding to at the beginning of this post.) Other things that could be contributing to this... hard drive speed, amount of buffer the drive has, seek/read time (doubt it as these are in the ms range)... Have you run any type of disk check on the drive to see if it has any bad clusters?

Sorry, but as I should have said, it is kind of hard to tell what is going on without seeing the code...



Good Luck