VB.Net - Looking for Speed Issue Running S/P

Good Day Experts!

I have been tasked with trying to find the performance issue with an existing S/P that is called from a VB.Net application.  I have setup a testing app to execute the S/P from.  In the S/P 6 db calls are made and cursors are used.  The S/P is called numerous times in loop by AccountNumber anywhere from 1-6 times. Each AccountNumber has it's own database.  I tested it 50 times and have a sample range of 1.99 to 111 seconds.  

1) It is understandable that the queried table may have a large number of records that may account for a performance issue
2)Network traffic? perhaps could be a contributor

But within the S/P how can I test for performance issues based on possible poor construction when calling from my VB.Net app?

Thanks,
jimbo99999
Jimbo99999Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

käµfm³d 👽Commented:
Does your query make use of indexes?
Jimbo99999Author Commented:
The S/P that I am trying to improve upon has the queries in it.  I started looking at the tables the queries use and there are indexes on them.  I am unfamiliar with your question about the query using indexes.  

Can you please explain so I can understand as I am not familiar too much with S/P's of this complexity?

Thanks for helping,
jimbo99999
käµfm³d 👽Commented:
If you're WHERE clause isn't making use of the columns which are indexed--provided we are still talking about the "large" table--then the query optimizer probably can't do the best job in picking the most efficient execution plan. This will most likely result in a "full table scan," where every single row of the table is inspected for matches to your WHERE. This can be very slow. When dealing with larger tables--in terms of rows--you want to try to take advantage of indexed columns. Reading an index is much quicker than reading the entire table.

As a real world analogy, picture a book which has an index. If you were looking for a particular topic within the book, which would take longer:  reading the entire book for that topic, or looking the topic up in the index, then going to the page that is referenced within the index? The database does something similar to this.
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Jimbo99999Author Commented:
Okay...gotcha.  Let me check on the indexes they have on the table and see if it matches the query.

Thanks,
jimbo99999
Jimbo99999Author Commented:
The table has 4 fields in the PK_Index and I verified that the table has those 4 fields set as the PK.  The query has those 4 fields plus and additional one. Could that be a problem?

Thanks,
jimbo99999
käµfm³d 👽Commented:
The query has those 4 fields plus and additional one.
The WHERE clause has them or the SELECT clause has them?
Jimbo99999Author Commented:
I apologize, the WHERE clause.
käµfm³d 👽Commented:
You may want to have your database team profile the query to see how long it is taking to execute. I'm not trying to say that the issue is definitely the query, but if you can eliminate that, then you can begin to look at other things like network traffic or your code.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John Gates, CISSPSecurity ProfessionalCommented:
Can you post the SP?  I am not totally against cursors but at times people can be cursor "crazy" there needs to be a balance.  My guess is a over zealous cursor is your performance issue...

-D-
Jimbo99999Author Commented:
Yes...heavy cursor.
SP.txt
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.