asked on

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

kaufmed

Does your query make use of indexes?

Jimbo99999

ASKER

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

kaufmed

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.

Jimbo99999

ASKER

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

Thanks,
jimbo99999

Jimbo99999

ASKER

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

kaufmed

The query has those 4 fields plus and additional one.

The WHERE clause has them or the SELECT clause has them?

Jimbo99999

ASKER

I apologize, the WHERE clause.

ASKER CERTIFIED SOLUTION

kaufmed

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Gates, CISSP, CDPSE

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-

Jimbo99999

ASKER

Yes...heavy cursor.
SP.txt