Modern Signal Blog

Viewing posts for topic: "SQL". View all posts.

Reporting with SQL PIVOT

I wanted to write about another bit of code that I created for the What Color Is...? website I talked about in my previous post. 

Displaying the Results

Probably the most interesting and challenging part of this project was to come up with a way to generate and display the results in an efficient way.  Colors were all saved in the database as 3 separate fields (Red, Green, and Blue).  This made it easy to select a color average for all responses.  Here is the SQL:

SELECT  t.name ,
        COUNT(*) AS Count,
        AVG(IsNull(red,255)) as Red ,
        AVG(IsNull(green,255)) as Green ,
        AVG(IsNull(blue,255)) as Blue
FROM    WCIResponse r
        INNER JOIN WCIThing t ON r.ThingID = t.ThingID
GROUP BY t.name

One of the options in the survey is to select "No Color", which is saved in the database as null values.  These are converted to 255 so that it is the same as if they selected white.

That was pretty easy, but I also collect some basic, anonymous demographic information to enable breaking down the results a bit.  I ask for age, for instance.  What I wanted to do was to break down the results by age group, i.e. 20-29, 30-39, etc.  This is more complicated, of course, and I didn't want to get into doing a massive number of queries and loops, etc, to build up the results.  Fortunately I have recently gotten to know more about the SQL Server statement PIVOT.  PIVOT is made for creating just such reports as this.  This is somewhat more complicated than a simple PIVOT, because I don't want a column for every age, but I want to group the ages by decade.

I'll run through the ColdFusion/SQL code I used to make this work. 

First, I set a variable with an SQL expression to create the name of the age group, e.g. "40-49":

<cfset var AgeRange = "CONVERT(VARCHAR,Age/10*10) + '-' + CONVERT(VARCHAR,Age/10*10+9)">

I only want to include the age ranges for which I actually have data, so I do an initial query to get the age ranges in the data.  Note that the AgeRange is wrapped in brackets so that it can be used as the column name in the next query, e.g. "[40-49]".

<cfquery name="GetAges" datasource="#application.dsn#">
    SELECT  DISTINCT
            '[' + #preserveSingleQuotes(AgeRange)# + ']' AS AgeRange
    FROM    WCIPerson
    WHERE   Age IS NOT NULL
    ORDER BY AgeRange
</cfquery>

I can then build the PIVOT query including all of the age ranges.  I'm still not comfortable with the PIVOT syntax.  I've only used it a few times, and it's still hard for me to wrap my head around without looking at documentation and examples.  If you're new to PIVOT too, this site has a few good examples.  As you can see below, I reuse the AgeRange variable I set above twice.

<cfquery name="GetResults" datasource="#application.dsn#">
    SELECT  t2.name
            <cfloop query="GetAges">
                ,ISNULL(t2.#GetAges.AgeRange#,'0,255,255,255') AS #GetAges.AgeRange#
            </cfloop>
    FROM    ( SELECT    t.ThingID ,
                        t.name ,
                        #preserveSingleQuotes(AgeRange)# AS AgeRange,
                        CONVERT(VARCHAR, COUNT(*)) + ','
                        + CONVERT(VARCHAR, AVG(ISNULL(red, 255))) + ','
                        + CONVERT(VARCHAR, AVG(ISNULL(green, 255))) + ','
                        + CONVERT(VARCHAR, AVG(ISNULL(blue, 255))) AS Color
              FROM      WCIResponse r
                        INNER JOIN WCIThing t ON r.ThingID = t.ThingID
                        INNER JOIN WCIPerson p ON r.PersonID = p.PersonID
              WHERE     p.Age IS NOT NULL
              GROUP BY  t.ThingID ,
                        t.name ,
                        #preserveSingleQuotes(AgeRange)#
            ) AS t PIVOT ( MAX(color) FOR AgeRange IN ( #ValueList(GetAges.AgeRange)# ) ) AS t2
</cfquery>

The value returned here for each age range is actually a comma-delimited list of values, where the first value is the number of responses, and the next three are the RGB values.  When the data is pulled into the application (as JSON), I am able to parse these values and fill a table to colored blocks to show the selected colors for each age range.

Although the What Color Is...? site doesn't have a lot of data at this point, I believe this code should scale pretty well.  Of course in the unlikely event that the site becomes an internet phenomenon and gets millions of responses I may have to revisit this code.

Comments (0)   Posted on January 31, 2011 5:09:23 PM EST by Catherine Field

SQL Server Query Performance

This page houses tips and tricks for improving query performance in SQL Server.

POSTED BY: Adam Polon

After running into some very quirky results from seemingly simple queries, I have found the following suggestions to make an enormous difference in query performance. 

Of course, you should always start by making sure that your query is optimized using standard practices, such as the following:

• Make sure fields that are searched over frequently have indexes assigned.  This only makes sense if the field will return less than 10% of the table, so don't index status fields if there are only 5 of them, etc.
  
• Make sure the data type for a field is appropriate.  For instance, if your field contains numeric data, avoid using a varchar field and CASTING AS INTEGER.
  

In some cases, switching around the order of fields in WHERE clauses makes a difference, but I usually do not find this to solve my issues.

Here are some things to try next.

---------------

Debug sluggish queries in SQL Server Management Studio by toggling the “Include Actual Execution Plan” button.  This will allow you to see the execution plan – including any table scans, index seeks, etc, to identify slow points in the query.

---------------

Review the nightly database maintenance plan to ensure that indexes are rebuilt on a nightly basis. 

-----------------

I believe that the rebuilding of indexes on a nightly basis makes the following item unnecessary, but it is listed here just in case in helps where other avenues do not. 

Defragment indexes on large tables. To do this, use the following command:

DBCC INDEXDEFRAG (database_Name, 'table_name', index_name)

Calling this command on key data tables made a huge difference in performance for a few queries that were performing slower than expected.  Queries literally went from 45 seconds to 2 seconds.

It would be a good idea to schedule these commands to run on a nightly or weekly basis.

----------------

Run Update Statistics on key data tables.  In conjunction with DBCC INDEXDEFRAG, I have seen huge performance enhancements as a result of running this.  To do this, use the following command:

update statistics table_name;

It would be a good idea to schedule these commands to run on a nightly or weekly basis.

---------------

Please let me know if you wish to add any additional tips and tricks to this page.

Comments (0)   Posted on May 30, 2008 8:15:02 AM EDT by Catherine Field

Search and Replace in a Text Column

SQL Server has a replace function, but it doesn't work on "text" columns.  SQL Server seems to address this with a new varchar(max) datatype, but those of us still using SQL Server 2000 still need a solution.  The following stored procedure should do the trick.  The seed of the idea for this came from the following page:
http://www.sqlteam.com/article/search-and-replace-in-a-text-column

My version puts the code in a stored procedure, replaces all instances of the string, escapes any wildcard characters that might be in the string, and also prints out the number of replacements that are done.  It is limited in that it won't work if the new text contains the old text, because that would cause an infinite loop, but maybe we'll be using varchar(max) before we need to do that!

- David (dave@modernsignal.com)

Usage:
EXEC usp_utility_replacetext 'Content','htmlContent','oldtext','newtext'

CREATE PROCEDURE dbo.usp_utility_ReplaceText
    @TableName VARCHAR(32),
    @ColumnName VARCHAR(32),
    @otxt VARCHAR(100),
    @ntxt VARCHAR(100)
AS
DECLARE @num INT
DECLARE @sql NVARCHAR(4000)
DECLARE @parms NVARCHAR(4000)
DECLARE @invalidChars VARCHAR(20)

SET @invalidChars = '%[^a-z1-9_@#$]%'

IF PATINDEX(@invalidChars, @TableName) > 0
BEGIN
    PRINT 'Invalid character in table name.'
    RETURN
END

IF PATINDEX(@invalidChars, @ColumnName) > 0
BEGIN
    PRINT 'Invalid character in column name.'
    RETURN
END

IF CHARINDEX(@ntxt, @otxt) > 0
BEGIN
    PRINT 'Sorry, the new text cannot contain the old text.'
    RETURN
END

SET @sql = N'
    DECLARE @txtlen INT
    DECLARE @ptr BINARY(16)
    DECLARE @pos INT
    DECLARE @otxtPat VARCHAR(100)
    DECLARE @more bit

    SET @txtlen = LEN(@otxtIN)
    SET @otxtPat = @otxtIN
    SET @otxtPat = REPLACE(@otxtPat,''%'',''[%]'')
    SET @otxtPat = REPLACE(@otxtPat,''_'',''[_]'')
    SET @otxtPat = ''%'' + @otxtPat + ''%''
    SET @numOUT = 0

    WHILE EXISTS (
        SELECT * FROM ' + @TableName + '
        WHERE ' + @ColumnName + ' LIKE @otxtPat
    )
    BEGIN
        DECLARE curs CURSOR local fast_forward
        FOR SELECT
            TEXTPTR(' + @ColumnName + '),
            PATINDEX(@otxtPat,
            ' + @ColumnName + ') - 1
        FROM ' + @TableName + '
        WHERE ' + @ColumnName + ' LIKE @otxtPat
    OPEN curs
   
    FETCH NEXT FROM curs INTO @ptr, @pos
    WHILE @@fetch_status = 0
    BEGIN
        UPDATETEXT
        ' + @TableName + '.' + @ColumnName + '
        @ptr @pos @txtlen @ntxtIN
        SET @numOUT = @numOUT + 1
        FETCH NEXT FROM curs INTO @ptr, @pos
    END
    CLOSE curs
    DEALLOCATE curs
    END'

SET @parms = N'@otxtIN VARCHAR(100),@ntxtIN VARCHAR(100),@numOUT INT OUTPUT'
EXEC sp_executesql @sql, @parms,
    @otxtIN = @otxt,
    @ntxtIN = @ntxt,
    @numOUT = @num OUTPUT
PRINT CONVERT(VARCHAR(32), @num) + ' occurrences replaced.'
GO

Comments (0)   Posted on November 13, 2007 9:20:35 PM EST by Catherine Field