April 2007 - Posts - Jon Galloway

April 2007 - Posts

[SQL] Using 'GO 100' to execute a batch 100 times

The GO statement is used by SQL Server as a batch terminator. It's recognized by by tools which run scripts like SSMS, SMO, and SQLCMD, but it's not technically T-SQL. SQL Server 2005 added a new little trick to the GO command which lets you specify a number of times to execute the batch: GO 10

Neat. GO 10 reminds me of GOTO 10, the funnest command of all time. What would you use GO 10 for? Here are a few ideas:

Inserting test rows in a table

There's a nice example on MSSQLTips which shows how to use it to insert rows into a table:


CREATE TABLE dbo.TEST (ID INT IDENTITY (1,1), ROWID uniqueidentifier) 
GO 
INSERT INTO dbo.TEST (ROWID) VALUES (NEWID())  
GO 1000

Use it for a quick and dirty comparison testing

Last week I was working with Rob Conery on a query that would allow paging through view or stored procedure results for the SubSonic QuickTable control. I threw some ideas at him and he made it work:


DECLARE @Page int
DECLARE @PageSize int 
SET @Page = 2
SET @PageSize = 10 

SET NOCOUNT ON 
SELECT * INTO #temp FROM Invoices 
ALTER TABLE #temp ADD _indexID int PRIMARY KEY IDENTITY(1,1) 

SELECT * FROM #temp
WHERE _indexID BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize) 

DROP TABLE #temp 

Pretty good, I thought, but my hunch was that it would better to add an identity index before adding data. First we'll select zero rows into the temp table to get the base table schema, then we add the index, and then we insert the data, like this:


SELECT * INTO #temp FROM Invoices WHERE 1 = 0 
ALTER TABLE #temp ADD _indexID int PRIMARY KEY IDENTITY(1,1) 
INSERT INTO #temp SELECT * FROM Invoices 

SELECT * FROM #temp
WHERE _indexID BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize) 

The best way to figure that out was to compare the results, but both ran in less than a second so it was tough to get a good idea on a single execution. Using GO 100 after each script, I was able to see a 40% improvement and feel like it was accurate enough show the change was worth implementing. Note that I'm clearing all buffers before running each batch to try to keep the playing field even.


-- Clear buffers 

DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE('ALL') 

CHECKPOINT
DBCC DROPCLEANBUFFERS
GO 

-- Script A 

DECLARE @Page int
DECLARE @PageSize int 

SET @Page = 2
SET @PageSize = 10 

SET NOCOUNT ON 

SELECT * INTO #temp FROM Invoices 

ALTER TABLE #temp ADD _indexID int PRIMARY KEY IDENTITY(1,1) 

SELECT * FROM #temp
WHERE _indexID BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize) 

DROP TABLE #temp 

GO 100 

We'll run the first script (against Northwind) and note the total time, then run the second script:


-- Clear buffers 

DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE('ALL') 

CHECKPOINT
DBCC DROPCLEANBUFFERS
GO 

-- Script B 

DECLARE @Page int
DECLARE @PageSize int 

SET @Page = 2
SET @PageSize = 10 

SET NOCOUNT ON 

SELECT * INTO #temp FROM Invoices WHERE 1 = 0 

ALTER TABLE #temp ADD _indexID int PRIMARY KEY IDENTITY(1,1) 

INSERT INTO #temp SELECT * FROM Invoices 

SELECT * FROM #temp
WHERE _indexID BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize) 

DROP TABLE #temp
GO 100

Remember that you can't use GO 100 in stored procedures - it's not valid T-SQL. It's just a little feature in Microsoft's ad-hoc query interfaces, like SSMS.

Posted by Jon Galloway | with no comments
Filed under:

First draft of our ASP.NET book is done. Whew!

You wouldn't know it from the recent posts on my blog, but I've been writing a lot lately. Last week I turned in the first draft for the final chapter of the book I'm co-writing with Phil Haack, Jeff Atwood, and K. Scott Allen. It's an ASP.NET "tips and tricks" book, but I think the content is a little different than most cookbook style programming books. The information is geared to strong intermediate to advanced developers, and the topics include things like binary content, search engine optimization, troubleshooting slow queries, log4net, ASP.NET AJAX, etc.

We'd all came into this expecting to work really hard and not make any money on it, the same way we approach blogging. The hard work thing's been true; it'd be nice to be surprised and actually make a little money on the book. Like most developers, I tend to use search engines a lot more than books these days, but I've actually found that my use of technical books has gone up this past year. I'm finding that web searches are better for random access problem solving, but book are more helpful when I need to see information in context or dig deep into edge cases that haven't found their way onto the web yet.

If nothing else, I've now got a book that's very helpful for me. I have a short memory for specifics and am constantly searching my blog and the blogs of the other co-authors for information I've looked up many times before. Now I'm looking that stuff up in the book draft, reading edited and polished copy instead of blog posts. Sorry - I'm not trying to make this sound like a commercial for a book that's not out yet. I'm just wondering (hoping) that this book might be useful to others who work like I do - using search a lot, but relying on a few solid books that beat the search result averages on quality.

Anyhow, now that we're done with first drafts and wrapping up the second drafts, I can direct some energy back to my blog, including some topics that didn't fit in the book.

Posted by Jon Galloway | with no comments
Filed under:

Looking forward to SilverlightGallery.com

The domain registration for SilverlightGallery.com was updated just before Microsoft announced the name change from WPF/E to Silverlight.1 Until recently, it just redirected to the asp.net domain, but now the splash page shows that it'll go live on April 30th, which means they'll be showing it off at MIX 07.

1Silverlight is a new technology from Microsoft which is similar to Flash in that it's a rich application framework which works via a cross-platform browser plugin. It has some great features that make it a very different product, though, such as an XML format which is accessible to client-side Javascript, a significant sub-set of the .NET framework, and extensive video support.


Posted by Jon Galloway | with no comments
Filed under: ,

SubSonic 2.0: Kick the tires on Beta 1!

Summary

SubSonic 2.0 (the zero code data access layer) just hit Beta 1 today. Download it, marvel at all the cool new features, and get your feedback in quickly because they're planning a really short beta phase with a final release as early as next Wednesday.

Background

I got fired up about SubSonic when I first saw it last August and wrote that Microsoft should ship it with ASP.NET AJAX (then called ATLAS). I knew it wouldn't happen, but I still think I was right. SubSonic is a zero code data access layer for ASP.NET - drop the DLL in your bin directory and add a few lines to your web.config, and you get immediate access to classes and controls that make ASP.NET work as if it was built just for you and your database.

Part of what got me excited was that my first feature request was implemented and released less than two hours after I left it in a comment on Rob's weblog. They haven't slowed down a bit.

What's New 

Here's what's new in the 2.0 release (lifted from Rob's release announcement):

  • Multiple Database support! Now you can generate a full DAL for as many databases as you like.
  • Enterprise Library 3.0 Support. Just added this in and it works nicely.
  • All-new command-line tool. You can now use our command-line tool (called SubCommander) to do all kinds of fun things, like generate your code and version your database. You can even hook into it using Visual Studio's External Tools - this will allow you to generate your project's code with the click of a button, and it will use your project settings (look for a blog post on Rob's blog).
  • Improved querying. You can now use our Query tool to run OR's, IN's, and aggregates. You can even type what you want to see:
    IDataReader rdr = new SubSonic.Query("Products").WHERE("CategoryID = 5").ExecuteReader();
    We've also renamed many of our methods (well, we've added aliases) to make the query more readable. You can now use WHERE, AND, OR, IN, BETWEEN_AND, etc. to make your calls that much more readable.
  • New Controls. You can now use our Smart Dropdown, which loads itself:
    <subsonic:DropDown id=mySmarty runat=server tablename="categories" />
    You can also use our new ManyToMany Checkbox list helper to both list and save information for many to many relationships:
    <subsonic: ManyManyList id=myList runat=server MapTableName="Product_Category_Map" PrimaryTableName="Products" PrimaryKeyValue="1" ForeignTableName="Categories" />
  • A new AutoScaffold page that you can drop right into your project to admin all your tables. This thing reads your tables and creates scaffolds for you automagically (thanks Eric!).
  • A completely reworked code-generation system that uses an ASP-style templating system. You can now code up your templates like you would an ASP page (also just like CodeSmith). Test them in your web site to make sure they work, then replace (or add to) the bits that get generated at runtime. You can override our templates by specifying a template directory in the web.config:
    <SubSonicService defaultProvider="Northwind" fixPluralClassNames="false" templateDirectory="D:\PathToMyTemplates">
  • Regular Expression Naming Engine. If you don't like what our convention is, then you can use your own with some simple regex. Pass a simple string, or a dictionary replacement and all of your tables/views will be renamed as you specify.
  • Query Inspection. Want to know what's happening to your query, and how long it's taking? You can simply use the new Inspect() method which outputs the results and statistics to HTML for you to review.
  • Improved Trace/Debug. We've added tracing to (almost) every facet of SubSonic, so if you turn tracing on you can see what SubSonic's trying to do. We're always adding to this and if you see something we've missed, let us know :).
  • Improved Scaffolding. Your foreign key values are now pulled into the GridView and displayed instead of their keys. Eric figured this one out - go dude!

My favorites are probably the command-line tool, scaffolding improvements, the auto-scaffold, and the code generation system. Here's why:

  • The command-line tool (sonic.exe) lets you work with SubSonic however you'd like. Previous releases of SubSonic required you to either use a build provider (which only works in ASP.NET 2.0 Web Site Projects, not Web Application Projects), or through a web page which would generate your DAL code. sonic.exe removes that restriction and gives you full and complete access to just about anything SubSonic can do. Since SubSonic's data engine is pretty much SQL Server Management Object (SMO) on steroids + support for other databases like MySQL and Oracle, command line access lets you do things with your data that make a lot of sense even if you never touch ASP.NET. sonic.exe can script out your database (schema and / or data) and build you a complete data access layer based on default templates or templates you provide, which is pretty nice for any .NET data application. Phil and I contributed some code to this, and we're using it in a current application for a large client. It works very well.
  • Scaffolding improvements are a big deal. The original scaffold control was nice for developer admin interfaces, but there were a few problems that kept me from using it in live applications. One problem was that the scaffold showed ID values for foreign key columns. In the new release, the scaffold follows the columns to show the values from the foreign keyed tables, which is really slick. Even nicer is that you can output the code for a scaffold using sonic.exe, which means you can do whatever you can customize it as much as you'd like.
  • The AutoScaffold is a great timesaver. It gives you a complete admin interface to your entire database - view, add, edit and delete for all tables - with zero code. Note that the editor controls are automatically generated - calendar controls for date values, dropdowns for foreign keyed values, etc.

    AutoScaffold
  • The changes to the code generation system to use ASP.NET style templates, which will make it easier to fit this in with existing projects or standards. Even better, this will open the door for community development and improvement of templates, and that can't be bad.

It's ironic that I need to wrap this up to get back to writing a tip for the ASP.NET book I'm co-authoring (with Phil Haack, Jeff Atwood, and K. Scott Allen). This tip, scheduled and assigned to me months ago, is on SubSonic. I had a chat with Rob about some upcoming features to include in the book, and I can only say that they're even better than this 2.0 stuff.

Posted by Jon Galloway | with no comments
Filed under:
More Posts « Previous page