How to write a decent script to generate a cross reference?

Question

How to write a decent script to generate a cross reference?

MissyDaisy

Ten Centuries

Points: 1164
More actions
April 5, 2011 at 2:46 pm

#235082

I have 2 input sources:
1. a list of stored proc in a specific database
2. Many output tables captured from sql server profiler that help indicate which stored procs were executed. I start and stop a trace based on a particular online web function.
Objective: Create a table that maps stored proc names to web function names.
I can't provide specifics about my inputs but their sample data look like below.
1. usp_GetCustomersTotalCount
2. exec usp_GetCustomersTotalCount @Location='Seattle'
The sproc name in input1 may or may not be found in input2 depending on the online web function utilizing that sproc or not.
Needless to say the sql profiler output is huge though I set a filter to set the boundaries to one database only and to sprocs and t-sql statements ( profiler standard template with filter)
I am thinking of writing nested while loops or nested cursors to generate the sproc to online function mapping but it is running so slow.
I am looking for suggestions and ideas.
Thank you much in advance.

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

can you change things like the connection string of the web application?

if you could get the web application to change the Application Name attribute on a per-page basis, you'd have the ability to capture the page in your trace.

for example, a connection string might be like this:

SqlConnectionFormat As String =

"data source={0};

initial catalog={1};

user id={2};

password={3};

Trusted_Connection=False;

Application Name=[/Products/default.aspx]"

another possibility is to have the page set the CONTEXTINFO, but that's a little harder to get in a trace.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

MissyDaisy Ten Centuries Points: 1164 More actions · Answer 2

Thank you very much for the useful pointer. I never looked up nor knew that the connection string could have a keyword "Application Name". I will certainly code that in the connection string to narrow the number of sprocs used per page.

Many thanks!

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

Lowell offers a good tip but one I would reserve for one-off troubleshooting. If you're using connection pooling (which you should be) you may want to consider how a change like this will affect application performance and the number of connections you app has open to your SQL Server. I have not tested it to see if this is true specifically when only the Application Name attribute is different, but MSDN (see article reference below) states that a difference between two connection strings causes them not to be pooled. If every page in your app has a different Application Name attribute then your pool will have one connection for each different connection string, i.e. at least one per page.

From article http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx :

Under Pool Creation and Assignment:

Connection strings must also be an exact match; keywords supplied in a different order for the same connection will be pooled separately.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

MissyDaisy Ten Centuries Points: 1164 More actions · Answer 4

Your point is well taken. I happen to have a sandbox to change the web.config on the fly to generate the mapping. I would not consider multiple connection strings in a public dev, qa or prod environment. Thanks for raising the flag!