SQL

View Performance / SQL Server Profiler

Leave a comment

A couple of days ago I pushed some changes to our custom ePrescription application into production.  The changes had been made a couple of weeks ago and tested fine.  However, after deploying we began to receive calls from angry doctors about how slow the system was, and how long it was taking to complete tasks.  During this same period, we had a major network issue with out anti-virus software (name withheld as we are awaiting an official response from the company).  A routine upgrade of the anti-virus software brought our entire network to a halt.  The upgrade closed all network connections on every server and client machine until a reboot was initiated.  We wound up having to manually connect to every server in our agency (30+ at last count) and click a confirmation on a dialog box to finish the installation and reboot the machines.  A mass email was also sent out for users to perform the same steps.  Eventually the problem was taken care of and network traffic resumed.  Due to this failure, I wasn’t sure if the complaints about the ePrescription software was related to the anti-virus debacle, or the code changes I made.  The answer became clear when after the network cleared up, the application’s performance was still suffering.  My job then became to analyze the queries running from the application.  For this I turned to SQL Server Profiler.  If you haven’t used this awesome tool, I recommend you begin immediately.  With profiler, you can set up a trace that will listen to and display information about all kinds of events and activities being executed on a SQL Server.  For my purposes, I selected a built in Tuning Trace and filtered it to listen to TSQL statements from the database used by the software.  I ran the trace for approximately 10 minutes and was able to immediately see that one query was taking anywhere from 8 to 15 seconds to execute, and it was a query that I had put in as part of the upgrades.  I don’t know why I didn’t catch this in testing.  Our test server is extremely slow by nature.  It has been beat up to no ends and I may have subconciously considered the slow execution just a result of running on the test system.  I won’t make that mistake again.  The query that was causing all the commotion was relatively simple, selecting four columns from a table joined with a view.  I remembered in the past a project that I had taken over for a departed employee.  There was a function that was used to delete records from a table.  When run, it would often cause the ColdFusion server to time out.  Upon analysis, I saw that this function was calling a view that called another function.  I changed the procedure to call one function only, and the process completed in no more than 2 seconds after that.  I figured the view in my new query was to blame.  I did some research and came across this article that explains performance hits encountered when using views, especially when joining.  I restructred the query to omit the use of the view and it is now running on average of 20 miliseconds.  That’s a far better figure than 8 to 15 seconds!  I’ve just deployed the new query into production, and am confident that our Dr.’s will have a much better experience tomorrow. 

Hope this helps.  Happy profiling / analyzing / viewing!

 
SQL

Testing ODBC Data Sources

Leave a comment
We do a ton of document imaging where I work.  As a healthcare organization, we have made it a priority to work towards our ultimate goal of being a paperless entity.  For now, most printed documents we use are scanned in and saved to a dedicated SQL Server.  The software that handles this is 3rd party and the connections to handle the data exchange are built in and permanently set.  The software does allow us the ability to alter export settings for individualized documents, as was the case for a client satisfaction survey we are required to implement.  These documents were set up to have their data stored in our primary clinical SQL Server, with an SSIS package that is run monthly to collect the reportable data to send to the responsible insurance agency.  As usual, specifications for the surveys were changed quite dramatically, which resulted in a need to redo the surveys, as well as the database tables that hold the data and the package that exports it.  As the DBA, it was my job to set up the tables, the SSIS package, and the ODBC System DSN entries that would allow for the exporting of the data to our primary SQL Server.  Our report writer and document master created the new surveys, and we sat down to test them out.  All documents scanned in to the system are subject to verification of data, a semi-automated process that requires user interventions whenver the software is unable to validate entries on the scanned documents itself.  It was upon verification of our new forms that we ran into some errors related to the back-end VBA script that handles external calls for verification of client data.  The scripts were set up by someone no longer working here, and at a time before I began my career here.  While not terribly complicated, I am not familiar with the script and do not have the time to diagnose the problem.  As a programmer, I understand that one simple misplaced character can wreak catastrophic havoc on code, and the offending part may not be easily visible, especially when dealing with a language that I have not looked at in over 6 years.  While I was confident that a problem existed with the script, my report writer seemed to lean towards an issue with the new DB tables and the ODBC data source.  Up until now, I had never known how to test the connection to an existing data source.  After creation of a new data source, there is a button that performs a connection test, and that is all.  I needed a way to test an existing connection, and an easy way to see if I could retrieve and push data through it.  After a quick search, I came across this site which has all kinds of goodies.  From there I downloaded a small and powerful tool called OdbcTest.  This tool allows you to connect to any defined ODBC connection, and perform queries against it, including inserts and updates.  This was exactly what I was looking for.  Using this tool I was able to confirm that the data source I set up was functioning as designed and data could flow in and out.  Armed with this info, I asked the report writer to generate a new survey using an existing template that verified without problem.  He set the new form up and it both verified correctly and the data was successfully sent through the ODBC connection to the tables.  So if you are looking for a great utility to help analyze your connections, make sure to check out OdbcTest.
 
Hope this helps.   Happy connecting!
ColdFusion

Resending undelivered email in ColdFusion

Leave a comment
We got a new exchange server configured and brought online yesterday.  As a result, I had several emails from various ColdFusion apps and scheduled tasks that were unable to be sent.  When a message is unable to be sent, ColdFusion will move it into the Mail\Undelivr directory under your main ColdFusion installation directory.  After some searching, I came across this little snippet on how to resend those messages.  Turns out it’s really easy. All you need to is copy the unsent mail, stored as a .cfmail file back into the Spool folder in the Mail directory.  Depending on your spool settings, ColdFusion will eventually attempt to resend these messages.  With this knowledge, it would be a great idea to set a scheduled task to periodically go through the Undelivr directory and try to resend the mail.  I’ll post back if I decide to get that working. 
 
Hope this helps.  Happy Mailing!
ColdFusion

Making a single cfselect required

Leave a comment
I’ve experienced the same frustration as others when it came to requring a value for a cfselect.  The documentation states that you just have to set the required attribute of the cfselect to true and it should validate.  This unfortunately only works for cfselects that allow multiple values (size >= 2).  For a single cfselect, a value is always selected and the requirement will never be properly triggered.  I’ve tried numerous workarounds that always failed to produce the appropriate results.  I finally came across the solutions today, posted from a user on the Adobe Coldfusion MX7 LiveDocs for cfselect.  The solution was so ridiculously simple that I couldn’t help but laugh at myself.  The user pointed out to use the following javascript:

 

<script language="JavaScript"><!–

document.formName.selectName.selectedIndex=-1;

//–>

</script>

Place this script after your cfselect with the required attribute set to true, and it will validate upon form submission.  I have tested this on HTML forms only.  I’ll test later on Flash forms and post my results.

Hope this helps.  Happy Coding!

SSRS

Hiding fields using Multi-Value parameters

Leave a comment
I have a report where I switched all the single valued parameters to multi-value.  The report had 8 sections whose visibility was controlled through the selection of a parameter.  The user could choose one of the sections or all.  I had set the sections up as rows of a table and used the following expression on the hidden attribute for that table row: IIF(Parameters!<parameterName>.Value = <thisSectionName> OR Parameters!<parameterName>.Value = "ALL", false, true).  This expression would cause this section to only be displayed if that section’s name or All was picked from the parameter ("ALL" was a static value I added to the parameter value when it was created).  However this no longer worked when switching to multi-value parameters.  The solution was to combine the JOIN() function, which creates a delimited string out of its argument, with the InStr() function, which returns the starting position of a string within another string.  Combining the two resulted in the following expression: IIF(InStr(JOIN(Parameters!<parameterName>.Value), "<thisSectionName>") > 0 OR Parameters!<parameterName>.Count = <totalNumberOfParameterValues>, false, true).  This expression will create a string containing all of the selected values of the multi-value parameter and search it for an occurence of the section name. If found, the integer position will be returned. If that value is greater than 0, then we want the section displayed (hidden = false).  Also, the expression checks to see if all of the values are selected, which is controlled by the count property of the parameter compared against the total number of possible values, which again will signal that we want the section displayed.  If the string is not found or all of the values are not selected, then the section will not be displayed (hidden = true).
 
Hope this helps.  Happy Coding!
SSRS

Displaying the value of a Multi-Value parameter

Leave a comment
I was working on re-designing a report today where I was changing certain single-valued parameters to multi-value.  I love this ability in that it allows the user to create much more dynamic reports.  The previous version of the report was displaying the value of the selected parameters in text boxes in the header of the report using the expression: ="<parameterName>: " & Parameter!<parameterName>.Label.  Unfortunatley, this caused an error when switching to multi-value parameters.  I found a couple of great articles and comments that showed how to display the value / label of a multi value parameter (links below).  You can use the JOIN(expression, delimiter) function to create a delmited string of either the parameter’s values or labels. To make a comma delimited list of the parameter’s labels, you would use the expression: JOIN(Parameters!<parameterName>.Label, ",").  This solution works perfectly. I still had one other problem.  One of my parameters contained a large list of staff, and I didn’t want every  staff member to be shown if the user selected all of the values for the parameter.  Fortunately, SSRS report parameters have an attribute called Count that contains the number of selected values. With this, you can construct a conditional expression to control what is displayed depending on how many values are selected.  For example, This expression will only display the word "All" if all of the values have been selected, or else it will display the comma delimited list of values: IIF(Parameters!<parameterName>.Count = <totalNumberOfValues>, "All", JOIN(Parameters!<parameterName>.Label, ",")).  So how do you determine the total number of values to compare against? If you have a non-queried dataset for a parameter, then the solution is easy; simply count up the values you have and that’s your number.  For my purposes, I was gathering a list of staff which can change over time.  The solution was to create a new dataset that returned the count of staff, using the same query that retrieved the staff names for the parameter.  Once I had the count, I created a new internal parameter and assigned it’s default value to the value of that count.  Now in my expression, I can compare the count of parameter values to the count of staff retrieved and contained in my internal parameter.  This way the results will always be the same and dynamic. 
 
Hope this helps. Happy coding!
 
SQL

TSQL PATINDEX()

Leave a comment
If you are not aware, there is a string manipulation function available called PATINDEX().  The function is similar to CHARINDEX() in that it will allow you to find the index of a particular character in a string, however PATINDEX() takes it one step further and allows you to search by a regular expression pattern.  I have found this ability extremely useful several times where I had to pare strings that were similar in construction yet contained different prefix and suffixes.  Case in point, I had a list of 500 medications. Each record held the name of the med along with the dosage information attached to the end.  Some of the meds also had anidentifier appended to the front, either @-, X-, or *-, to mark them as belonging to a special category.  My goal was to strip the records of these identifiers, if present, as well as the dosage information on the end, and return a distinct list.  My process was not pretty, and probably not the most efficient solution, but making good use of PATINDEX() and CASE statements, I was able to come up with the following solution:
 

SELECT DISTINCT SUBSTRING(somestuff,

                CASE

                                WHEN PATINDEX(‘%X-%’, somestuff) > 0

                                THEN 3

                                ELSE PATINDEX(‘%[A-Z]%’, somestuff)

                END,

                CASE

                                WHEN PATINDEX(‘%[0-9]%’, somestuff) = 0

                                THEN LEN(somestuff)

                                ELSE PATINDEX(‘%[0-9]%’, somestuff) –

                                                CASE

                                                                WHEN PATINDEX(‘%X-%’, somestuff) > 0

                                                                THEN 3

                                                                ELSE PATINDEX(‘%[A-Z]%’, somestuff)

                                                END

                END)

 

 

Note the extra use of PATINDEX(‘%X-%’, somestuff).  This was necessary to remove the prefix indentifier "X-" that was not removed by the use of PATINDEX(‘%[A-Z]%’, somestuff).  The other identifiers were (@ and *).  There is probably a way to do this easily with a better RegEx, but I am definitely an amateur in that arena. 

Hope this info helps.  Happy Coding!

 

SSRS

Parameter Ordering – Forward dependencies are not valid.

Leave a comment
We have a report at work that displays staff payroll information.  The report is viewed by managers and executives.  Each person viewing the report only has access to certain departments (one or more) that we control through a table we built.  The viewer is presented with a list of departments to choose from, and their choice of department is validated in our table using the Global report value UserID.  They will see the information if authorized, or otherwise a message displays stating as such. As this report was written by someone else no longer working at the company, my question was why are we even offering a list of all departments if the viewer can only select specific ones?  The solution was to list only those departments for which the viewer could access in the report parameter.  To do this, I created an internal report parameter that got the network id of the current viewer using the Global UserId value, then used that parameter to query the authorization table for those departments that the viewer was assigned.  Sounded fine, except when running the report I received an error stating that "Forward dependencies are not valid."  A quick Google later (can I use that as a unit of time?)  and I found a forum entry on one of my favorite SQL sites, SQL Server Central (registration required).  The entry mentioned that the ordering of report parameters in the paramter dialog matters.  It makes total sense, but not something you would commonly think about.  I moved the user id parameter above the parameter that uses it to get the list of departments, and the report displayed as expected.     
 
Hope this info helps.  Happy Reporting!