Share This!

Thursday, January 9, 2014

Dramatically Speed Up Stored Procedures using Temp Tables

If you're wondering how to create a list, or temp table in a SQL Server Stored Procedure, look here.

But what if you're joining to an in-memory table and you experience performance issues?  I had a multiple join against such a table, and found that the query was taking over 12 seconds to complete - causing a timeout ocassionally.  Here was the temp table declaration:

    declare @tempIDs TABLE
    (
        pick_list_id integer
    )
Simple enough, right? Just a long list of integers.  But like I said the entire stored procedure was taking over 12 seconds to execute.  In a database table, my first approach would be to make an index on the column.  So that's what I did in my stored procedure.  It turned out to be incredibly simple and improved performance from 12 seconds to 30 milliseconds.  Looky!

    declare @tempIDs TABLE
    (
        pick_list_id integer not null PRIMARY KEY
    )
That's it! The rest of the stored procedure is exactly the same.  That's a 40000% speed boost!  Not bad for one modified line of code!


...

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Tuesday, January 7, 2014

Column Modification Checklist

This is one of those things that I always forget part of, so because I just went through this, I thought I would document what needs to be done to sync your application whenever you modify any column in your database.

This tutorial is designed for:
  • MS SQL Server
  • Visual Studio (for web or desktop)
Quick Checklist:
  1. In SQL Server Management Studio (SSMS)
    1. Check for source and destination columns (for instance if widening "Address1" from 40 to 50, make sure all the columns in the order table, address book, Shipping and Billing etc are all the same)
    2. Update All Views that depend on this column. (SQL Server does not do this automatically)
    3. Update all stored procedures that operate on this column (for instance in and out parameters that access the changed column)
  2. In your Desktop App:
    1. Check all dataset xsd files to ensure the result column maxlengths are updated.
    2. Check all dataset xsd files to ensure the query parameter maxlengths are updated.
    3. Ensure all databound textboxes are set to the correct MaxLength.
    4. Ensure all DataGridView Columns are set to the correct MaxInputLength.
Updating Views:

There is actually a stored procedure for updating views.  Once you have found a dependent view, just run...

EXECUTE sp_refreshview 'dbo.v_myViewName';

That will take care of it.  Of course if your view is no longer valid because of the change, you'll get an appropriately misleading error message from Microsoft.

Updating Stored Procs:

Your stored procs have a header much like this:

ALTER PROCEDURE [dbo].[StoredProcName]
    -- Add the parameters for the stored procedure here
    @customer varchar(10),
    @PurchaseOrder varchar(15),
    @Address1 varchar(40)
AS

These header parameters and any internally declared variables must be changed to match any column changes.



...

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Thursday, January 2, 2014

How to access your Main Form from a Static Method

Of course the right way is to pass a handle to the form if it is needed in the static method.

public static void Import(MainForm parent)
{
    parent.whatever();
}
But in my case I just didn't want to pass that handle around - and my application only had one form, so...

Application.OpenForms[0].Refresh();


...

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Friday, December 27, 2013

Windows 8 is Costing You Money

Your Money is Being Sucked Down a Hole
I have a client that I have designed a desktop app for.  It's a Winforms app designed in Visual Studio 2010 and uses the barely functional DataGridView object to edit data.  It's been working forever in Windows XP, Vista, and 7.  Though as stated, the DataGridView gives constant headaches.  Now the boss has upgraded his tablet to Win 8 and the app has stopped functioning once again, crashing while trying to automatically calculate column widths during a paint event.

This function of the DataGridView has been spotty at best even on the other versions of Windows, and I have had to code around problems with it before.  Basically, the thing is just a nightmare to configure and use. I constantly find myself writing 200 lines of code to get the damn thing to stop crashing, when in my humble opinion, the damned thing ought to just work out of the box.  Now, I can see there might be problems if I were trying to shoehorn grids into grids, or streaming media into 20,000 rows of data... but really I am doing nothing out of the ordinary here.

Now I have to stop ongoing development, and retrofit this whole app (at the client's expense) to an operating system that - say it with me - ought to just work out of the box!

Meanwhile the client is wondering if I am really all that sharp, because Microsoft muffed up their OS with backward compatibility problems.  Thanks, Microsoft!
...

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Monday, December 16, 2013

Refresh All Views on SQL Server

Whenever you make a change to a table - say, to modify a column - you need to update all the views that depend on that column.  This script will find and update all your views. 

-- Back Up All Databases
-- by Bryan Valencia

--create temp table
declare @temp table(commands varchar(500), completed bit)

--load it with backup commands
insert into @temp (commands, completed)
(
    SELECT DISTINCT 'EXEC sp_refreshview ''' + name + '''',0
    FROM sys.objects AS so
    INNER JOIN sys.sql_expression_dependencies AS sed
        ON so.object_id = sed.referencing_id
    WHERE so.type = 'V'
    and is_schema_bound_reference = 0
)

--variable for the current command
declare @thisCommand varchar(500);

--loop through the table
while (select count(1) from @temp where completed=0)>0
begin
    --find the first row that has not already been executed
    select top 1 @thisCommand = commands from @temp where completed=0

    --show the command in the "mesage" output window.
    print @thisCommand

    --execute the command
    EXEC (@thisCommand);

    --flag this row as completed.
    update @temp set completed=1 where commands=@thisCommand
end

--show the user the rows that have been found.
select * from @temp


Of course if a view is now no longer correct, you'll see on your messages tab in SQL Server Management Studio.

...

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.