Pages

Tuesday, September 28, 2010

I do not have the frakking time for this!

Server Application Error

The server has encountered an error while loading an application during the processing of your request. Please refer to the event log for more detail information. Please contact the server administrator for assistance.
An asp.net site that has been RUNNING and STABLE for 5 years suddenly REFUSES to execute any ASP classic pages!  I have done NOT ONE GDAMNED THING to the server other than install a MICROSOFT SECURITY UPDATE, which of course happens automatically.

So far, I have lost a FULL DAY OF PRODUCTIVITY over this, and it is NOT resolved at ALL.

in my event log I get these 3 every time I try to hit any asp classic page...

Event Type:    Error
Event Source:    Userenv
Event Category:    None
Event ID:    1058
Date:        9/28/2010
Time:        2:03:46 PM
User:        NT AUTHORITY\SYSTEM
Computer:    WEB1
Description:
Windows cannot access the file gpt.ini for GPO CN={31B2F340-016D-11D2-945F-00C04FB984F9},CN=Policies,CN=System,DC=209softwarecom,DC=local. The file must be present at the location <\\209softwarecom.local\sysvol\209softwarecom.local\Policies\{31B2F340-016D-11D2-945F-00C04FB984F9}\gpt.ini>. (Windows cannot find the network path. Verify that the network path is correct and the destination computer is not busy or turned off. If Windows still cannot find the network path, contact your network administrator. ). Group Policy processing aborted.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Event Type:    Error
Event Source:    Userenv
Event Category:    None
Event ID:    1030
Date:        9/28/2010
Time:        2:03:46 PM
User:        NT AUTHORITY\SYSTEM
Computer:    WEB1
Description:
Windows cannot query for the list of Group Policy objects. Check the event log for possible messages previously logged by the policy engine that describes the reason for this.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.


Event Type:    Warning
Event Source:    W3SVC-WP
Event Category:    None
Event ID:    2236
Date:        9/28/2010
Time:        2:08:01 PM
User:        N/A
Computer:    WEB1
Description:
The server failed to load application '/LM/W3SVC/1550346855/Root/cgi-bin'.  The error was 'The server process could not be started because the configured identity is incorrect.  Check the username and password.
'.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
I am past annoyed and fast approaching POSTAL over this.

SUCCESS
Not sure how this happened, but...

I changed the setting to "low" and all is well.



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.

Sunday, September 12, 2010

VWD 2010: What's Not to Like?

The primary thing I don't like about Visual Web Developer 2010 is...










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, September 7, 2010

Easy XML Dataset Tutorial in Visual C# Express

Create the Data Definition
First, create a new application.
 Add a dataset
Right-click in the stripes and add a datatable.
Right click the datatable to add all the columns you want.
Use the Properties form to set column properties.
Repeat until your table is complete.

Continue this until your dataset is complete.  You can add as may tables as you like.  Keep in mind that you are not creating a database (as you would in SQL Server), you're creating a data definition or schema.

Ok, now to use the dataset in your program.
Set up for Editing
From the top menu, select data - show data sources.
Now, just drag a table from the list to your form.
Notice that a number of components are added to your form when you do this...

Now it's time to hook up the UI to a dataset.  First, enable the save icon in the toolbar.

Next, construct the file/save routine.



Now it's time to tell the dataset how to load the data on startup.  Select the dataset and add an Initialized handler.
The trick here is to make sure the xml file exists before loading it.








Now run the program, and enter data.
 Click the SAVE icon, and exit the program.  Then launch it again to verify that the data has been saved.  Simple, eh?
But, where is my XML file?
Since you ran the application in debug mode, your xml data file was created in that directory.  From the C# IDE menu, select File - Open File... and select bin, then debug.  Your xml file is there, and should look a lot like this:

If you want to know more, like how to read/write data to this kind of dataset in code - just leave me a comment!


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, August 23, 2010

ASP.NET User Profiles.

It totally sucks.
Membership data in the ASP.NET database stores user names, emails, and a little other information, but I need to add more... All I want is a few additional columns added to the user's data, like their real name, phone/fax/cell numbers, etc. I don't want to go to the trouble of creating a SQL Server table and managing all the relationships to do this and then creating the SQL to fish that all up in my code.
Well, as it turns out that's exactly what the ProfileCommon object does.
There are a number of uncommonly pleasant surprises when you dig into this feature, so let's get started!
This article assumes you're using an aspnet default database setup created as in this article.
Ok try this: In your code pages, type Profile.: you will see a hint showing the options available.
Profile Context Help
yes you can stretch the pop-up box!
Now, open your web.config file. Look for the end of the system.web section. Insert the following lines.
<profile defaultProvider="ProfileProvider">
            <providers>
                <clear/>
                <add connectionStringName="ConnectionString" applicationName="/" name="ProfileProvider" type="System.Web.Profile.SqlProfileProvider"/>
            </providers>
            <properties>
                <add name="email_verified" allowAnonymous="false" type="System.Boolean"/>
                <add name="subscribed" allowAnonymous="false" type="System.Boolean"/>
                <add name="MemberID" allowAnonymous="false" type="System.String"/>
                <add name="FullName" allowAnonymous="false" type="System.String"/>
            </properties>
        </profile>
OK, obviously your connection string has to be changed to match the one in your Membership and Roles provider. Look at the profile section. The editor doesn't help much with creating these types, but they are not hard to figure out.
All you need to do is create whatever columns you want to add. Then once they are added here, go back to your C# code and type Profile. again.

Notice that the added properties are now available to you to use in your programming. You can display then onscreen, let people edit them, even modify them in your code.


The Profile object is of type ProfileCommon, and is automatically instantiated when your page is loaded. Here are a few shortcuts for using it.


Use the logged-in user's Profile Profile.[fieldname]
Look up another user's Profile ProfileCommon otherProfile = Profile.GetProfile(otherUSerName);

Tuesday, August 10, 2010

Server Application Unavailable

I fought this for a week - there was so much junk info out there.  My system config is as follows:
  • Windows SBS 2003 SP2 
  • IIS 6
  • Hosting a mix of ASP.NET 2.0, 2.5, 3.5 sites
When I added my first Visual Web Developer 2010 site using the .net 4.0 framework, These errors happened.

Server Application Unavailable

The web application you are attempting to access on this web server is currently unavailable.  Please hit the "Refresh" button in your web browser to retry your request.
Administrator Note: An error message detailing the cause of this specific request failure can be found in the application event log of the web server. Please review this log entry to discover what caused this error to occur.
...and in the Application event Log...


Event Type:    Error
Event Source:    ASP.NET 4.0.30319.0
Event Category:    None
Event ID:    1007
Date:        8/4/2010
Time:        3:34:08 PM
User:        N/A
Computer:    WEB1
Description:
aspnet_wp.exe could not be launched because the username and/or password supplied in the processModel section of the config file are invalid.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

OMG this was so easy.
On the Web Server...
  1. open the C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\Config folder. 
  2. Edit Machine.config.
  3. in the  <system.web></system.web> section, change <processModel autoConfig="true"> to <processModel username="SYSTEM"></processModel>
  4. Save and exit.
  5. open a command window and enter IISRESET.
  6. Your web site should now work.

Tuesday, August 3, 2010

HTML Dropdown for all US and Canada States/Provinces

<SELECT NAME="state" SIZE=1>
                <OPTION VALUE="United States" disabled="disabled">United States</option>
                <OPTION VALUE="AL - Alabama">Alabama</option>
                <OPTION VALUE="AK - Alaska">Alaska</option>
                <OPTION VALUE="AZ - Arizona">Arizona</option>
                <OPTION VALUE="AR - Arkansas">Arkansas</option>
                <OPTION VALUE="CA - California" SELECTED>California</option>
                <OPTION VALUE="CO - Colorado">Colorado</option>
                <OPTION VALUE="CT - Connecticut">Connecticut</option>
                <OPTION VALUE="DE - Delaware">Delaware</option>
                <OPTION VALUE="DC - Dist of Columbia">District of Columbia</option>
                <OPTION VALUE="FL - Florida">Florida</option>
                <OPTION VALUE="GA - Georgia ">Georgia</option>
                <OPTION VALUE="HI - Hawaii">Hawaii</option>
                <OPTION VALUE="ID - Idaho">Idaho</option>
                <OPTION VALUE="IL - Illinois">Illinois</option>
                <OPTION VALUE="IN - Indiana">Indiana</option>
                <OPTION VALUE="IA - Iowa">Iowa</option>
                <OPTION VALUE="KS - Kansas">Kansas</option>
                <OPTION VALUE="KY - Kentucky">Kentucky</option>
                <OPTION VALUE="LA - Louisiana">Louisiana</option>
                <OPTION VALUE="ME - Maine">Maine</option>
                <OPTION VALUE="MD - Maryland">Maryland</option>
                <OPTION VALUE="MA - Massachusetts">Massachusetts</option>
                <OPTION VALUE="MI - Michigan">Michigan</option>
                <OPTION VALUE="MN - Minnesota">Minnesota</option>
                <OPTION VALUE="MS - Mississippi">Mississippi</option>
                <OPTION VALUE="MO - Missouri">Missouri</option>
                <OPTION VALUE="MT - Montana">Montana</option>
                <OPTION VALUE="NE - Nebraska">Nebraska</option>
                <OPTION VALUE="NV - Nevada">Nevada</option>
                <OPTION VALUE="NH - New Hampshire">New Hampshire</option>
                <OPTION VALUE="NJ - New Jersey">New Jersey</option>
                <OPTION VALUE="NM - New Mexico">New Mexico</option>
                <OPTION VALUE="NY - New York">New York</option>
                <OPTION VALUE="NC - North Carolina">North Carolina</option>
                <OPTION VALUE="ND - North Dakota">North Dakota</option>
                <OPTION VALUE="OH - Ohio">Ohio</option>
                <OPTION VALUE="OK - Oklahoma">Oklahoma</option>
                <OPTION VALUE="OR - Oregon">Oregon</option>
                <OPTION VALUE="PA - Pennsylvania">Pennsylvania</option>
                <OPTION VALUE="RI - Rhode Island">Rhode Island</option>
                <OPTION VALUE="SC - South Carolina">South Carolina</option>
                <OPTION VALUE="SD - South Dakota">South Dakota</option>
                <OPTION VALUE="TN - Tennessee">Tennessee</option>
                <OPTION VALUE="TX - Texas">Texas</option>
                <OPTION VALUE="UT - Utah">Utah</option>
                <OPTION VALUE="VT - Vermont">Vermont</option>
                <OPTION VALUE="VA - Virginia">Virginia</option>
                <OPTION VALUE="WA - Washington">Washington</option>
                <OPTION VALUE="WV - West Virginia">West Virginia</option>
                <OPTION VALUE="WI - Wisconsin">Wisconsin</option>
                <OPTION VALUE="WY - Wyoming">Wyoming</option>
                <OPTION VALUE="GU - Guam">GUAM</option>
                <OPTION VALUE="PR - Puerto Rico">Puerto Rico</option>
                <OPTION VALUE="VI - Virgin Islands">Virgin Islands</option>

                <OPTION VALUE="Canada" disabled="disabled">Canada</option>
                <OPTION VALUE="AB - Alberta">Alberta</option>
                <OPTION VALUE="BC - British Columbia">British Columbia</option>
                <OPTION VALUE="MB - Manitoba">Manitoba</option>
                <OPTION VALUE="NB - New Brunswick">New Brunswick</option>
                <OPTION VALUE="NL - Newfoundland and Labrador">Newfoundland and Labrador</option>
                <OPTION VALUE="NT - Northwest Territories">Northwest Territories</option>
                <OPTION VALUE="NS - Nova Scotia">Nova Scotia</option>
                <OPTION VALUE="NU - Nunavut">Nunavut</option>
                <OPTION VALUE="ON - Ontario">Ontario</option>
                <OPTION VALUE="PE - Prince Edward Island">Prince Edward Island</option>
                <OPTION VALUE="QC - Quebec">Quebec</option>
                <OPTION VALUE="SK - Saskatchewan">Saskatchewan</option>
                <OPTION VALUE="YT - Yukon">Yukon</option>
              </SELECT>

Saturday, July 17, 2010

Visual Web Developer: Productivity Killer

Oh, my god this is awful!

I noticed a couple quick changes I needed to make to my website.  So I popped open VWD.  It took 4 minutes to load.  4 MINUTES!  I was screaming!  Then I selected the website project and another 3 minutes go by!  I started this blog entry to vent so I don't get a brain aneurysm!

Guess what!

10 minutes after I noticed the error, and spent this whole time screaming at my computer... I can't remember what the hell was wrong with my site anymore! 

I think I need to drink heavily now.

Monday, June 7, 2010

ASP.NET and SQL Server: Chapter 1: Displaying A Gridview of SQL Server Data

Chapter 1.
Assuming you already have data you wish to display in your SQL Server database, create an aspx ajax web form.

Now open the Database Explorer, and open your database, then navigate to the table you want to show in the web form.  Click the table, and drag it to your form div tags.
Hit control-s (save) and your datagrid will appear in the design view window along with a SqlDatasource.

That was easy!  As a bonus, there are some handy easy-formatting options.  Click the grid in the design view, and look for the little pop-out menu at the top right of the control.  Select AutoFormat.
The Auto Format options can configure all the visual settings for you in a flash.  This menu is also used to select editing, paging, sorting options for the gridview.

Press F5 to view the grid in action.  Then stay tuned for Chapter 2: How to use a dropdown list in the grid.

Friday, June 4, 2010

How to Design a Crappy Web Site

Ok, no one really needs information on how to make the worst possible web site.  This article was written as a guide how not to do a web page for your business.  As a professional web designer I am well-versed in all the nerdy ways required to carve a web site out of solid stone.  What I offer here is a humorous attempt to get you thinking about the things a business needs to consider before creating or redesigning your web page.

First, have no idea what you want to say.
After all, you hired a professional programmer... He should just know what you need to put on the web to take your sales profile from zero to hero.  Right?!?  He can just make it all searchable, sortable, buzzwords that hypnotize viewers into cranking out that credit card.

Next, have no goals in mind for your site.
When your web designer asks questions like "What are the goals for this site, to generate phone calls, foot traffic, direct Internet sales, gathering club members?", make sure that you don't have a good answer to that.  Tell him something vague like we just want sales.

Content is overrated.
Your web designer is a trained professional.  He should have access to all the photos, text, articles, and everything that he needs to make your site.  It's far too much work to make a list of pages and mock them up in Word for him.  Besides, thats how he earns his keep, right? 

Photos
Make sure any site photos you provide are a little blurry, an unreasonable size (like 30,000x22,000 pixels), in an antiquated image format (daguerreotype if possible), badly lit, and full of really ugly people against distracting backdrops.  If possible, take these near your competitors billboards and other signs.

Speak in generalities and marketing lingo about layout.
When your web designer asks what colors, themes, and logos you want to use, give him a description of feelings, couched in marketing weasel words.  He may expect things like "We'd like a 3 column layout in mauve and yellow with this banner and our company logo.  We'd like a button menu across the top, our sales ads in the center,  and an RSS feed of our blog articles in the right column.", but do NOT tell him that.  Say things like, "We are committed to challenging unquestionable management leadership with expansion through opportunities overcoming all obstacles."  You know, try to avoid the mention of colors, layout terms, and anything that can actually be used to - I don't know - figure out where to put stuff on a screen.


Web Design is an iterative process.
So every time your web designer sends you an email asking you to approve something here's what to do.
  1. Wait 4 to 12 days before responding.
  2. When you do call, ask why it's going so slow.  Say things like "I thought you said you were good at this."
  3. About the work you're reviewing, respond with more generalities like, "I was thinking of something more youthful and clean.
  4. It's more fun to wait until the second time around to show him examples of what you're looking for, but throw in a few spoilers you hate - it makes the job of looking for a common thread more fun!
  5. Wait until the fourth go-around before you mention the name of an actual color or pattern.
  6. Try to keep the process going forever.  This is all part of the initial quote, right?
Advertising
Don't set aside any money for advertising your site. A good web site will become popular even if you never tell anyone it's there.  Sales will just flow in, even if you never really gave enough information to let people know how to buy.  Don't integrate with tools to help you track your traffic and link to your sales.   Either it will work and you'll sell like crazy, or it won't, in which case it was the web designers fault.  It's a no-lose scenario!


Going forward
Once your site goes on-line, it's important to let your web designer know that your hits are not increasing, and you're not making many sales.  Even if you're up 4% with no advertising, you were expecting 20%.  Try to use this guilt to get the designer to "fix" his "broken concept" for free as a defect, rather than pay him to "redesign" the site.


In Contusion...
Seriously, think of your web designer like you would think of a printing house you use to create a catalog or magazine.  A good one is very qualified to take your ideas and present them on the World Wide Web in a way that will help you achieve your goals.  You would never expect a printing house to take photos or write copy, or even to create a corporate image for you.  You should not expect this of your web designer either.

Tuesday, June 1, 2010

No connection could be made because the target machine actively refused it

This happened after migrating from a T1 line to a Comcast 10Mb line.   My web sites can no longer send emails because of this error. 

My setup:

I am using SBS 2003 as a web server AND as an Exchange server.  The ASP.NET code that is trying send an email looks like this:

   1:      protected void Button1_Click(object sender, EventArgs e)
   2:      {
   3:          if (IsPostBack)
   4:          {
   5:              MailMessage myMessage = new MailMessage();
   6:   
   7:              myMessage.To.Add(new MailAddress("myAddress@gmail.com", "Me"));
   8:              myMessage.Subject = Regex.Replace(Subject.Text, @"<(.|\n)*?>", string.Empty);
   9:              myMessage.ReplyTo = new MailAddress(email.Text, name.Text);
  10:              myMessage.Body = Regex.Replace(messagebody.Text, @"<(.|\n)*?>", string.Empty);
  11:   
  12:              SmtpClient MailMan = new SmtpClient("209software.com");
  13:              MailMan.EnableSsl = true;
  14:              try
  15:              {
  16:                  MailMan.Send(myMessage);
  17:              }
  18:              finally
  19:              {
  20:                  ;
  21:              }
  22:              
  23:          }



Diagnosis:
In my case, we had switched from a T1 to a 10Mb line.  everything was configured the same but we *did* get a new IP address, which we changed in the server appropriately. HOWEVER. on this new line, provided by Comcast, hitting our IP address from inside the network gets redirected to Comcast Business Gateway.  Let me demonstrate.   From the outside world, my website looks like this:


However, when I remote into my server, I see this:

Somehow the Comcast Business Gateway is intercepting hits to my IP Address from inside the network.  This causes all requests from my ASP.NET applications to email stuff using my Exchange server to go not to my server, but to this Comcast monstrosity.

The Solution:
What I did was so simple - I am embarrassed to say it took me overnight to think of it.  I logged into the webserver and added all my websites to the HOSTS file (C:\windows\system32\drivers\etc\hosts) like this:

127.0.0.1 209software.com www.209software.com mail.209software.com
Now my server can access itself when it needs to.  BTW This is a very lame approach on the part of Comcast.  I officially hate it.

Friday, March 12, 2010

General SQL Optimization Tips and Tricks

SQL and You
SQL can get complicated.  You are simple.  That's just the way it is.  You like issuing commands like...

select * from orders
...when all you need is a few columns.  But the SQL Server will dutifully fetch all the data you requested, no matter the cost to the performance of the server, the network, and your fellow database users.

The above query might not be too bad, but when you see what an order looks like...

...you figure out you need to join in some additional data to make a meaningful report.
so you join to the customer table, and your query becomes:
select o.*, b.billto_firstname+' '+b.billto_lastname as CustName from orders o
join buyerdata b on b.OrderID = o.OrderID
...giving us a concatenated name...

Ok, time goes on and you add joins to categorize the orders by state, product type, salesman, etc. etc. etc. until your SQL gets into a state we like to call gnarly.  Another thing that happens is that your SQL is written to make life easier for you, not for the database server.  Soon, your query gives you the perfect data, and your report/web site/whatever looks amazing - after minutes of crunching to gather the data.

This article is written to help you find and correct some common things that make queries easy for us, but difficult for your database server.  This process in general, is called query optimization and companies pay big bucks for people who are experts at this.  Imagine a business with 800 users entering data on their desktops.  Some are on the phone with vendors or clients, and system performance is mission critical.  Suddenly you launch off the un-optimized query for your weekly TPS report, and the entire database starts chugging like The Little Engine That Could.  This means hundreds of your business clients all start hearing words like "Geez, hang on - this screen is loading...".  You can see the business need here.

Even if your business is small, why tax your systems needlessly if you can take a few steps to write better SQL that gives you the data you need faster?  I promise - if your small business grows big, that query that generates a report out of your 1MB database in 3 seconds now will probably take 30 seconds when you reach 10MB and 5 minutes at 100MB. 

Tip 1:  Never use Select *
Select * is great for when you just want to get a feel for what data is in a table.  But it's the least efficient thing you can do in a production query.  Think about it.  First, the parser has to access the data dictionary and fetch the names and types of all the columns in the table (or multiple, joined tables).  Then your '*' is replaced with all the names of the columns - which (by the way) are not guaranteed to be in any particular order under ANSI SQL specs.  If you are writing a query for production use, one that will potentially execute thousands of times a day, take the time to tell it which columns to return.

Tip 2: Indexes
Indexes are designed to help queries. If you're summarizing sales by agent, then adding a secondary index to the agent column in your sales table will save you a lot of time.  Technically the time is being spent, but it's being used in tiny slices whenever you change data in the sales table.  That triggers the maintenance of the index, which can then be searched much faster than the whole table.

Tip 3: Case Insensitive Searches
Consider:
Select fname, lname, email from Customers where lastname='McHenry'
This query might work, unless you're looking for all people who entered any of these: McHenry, mchenry, Mchenry, MCHENRY, McHENRY.  ok - so you want to just find the customers with a case insensitive search.  So you do this:
Select fname, lname, email from Customers where lowercase(lastname)=lowercase(@searchname)
What happens now? The query is executed not against the field "lastname" (and any indexes applied to it), it is instead executed against the function lowercase(), which has no index.  There are two common workable solutions to this issue.   Neither of the solutions is to make sure all data is entered using strict capitalization standards. No one wants to be the Capitalization Nazi!
  1. Use a function index.  Oracle supports these. you can actually make an index on "lowercase(field)" or "getmonth(datefield)".  This is not a widespread feature, but is really useful in these cases.








  2. Add a lower or upper column in your data to use for joining and searching.  If you have an ASP.NET membership provider database, note that the aspnet_users table has a column named LoweredUserName.  If this approach is good enough for Microsoft, it's good enough for me.
Tip 4: Data Warehousing
Sometimes you just *must* have that report that performs a 23 table join against a 40 million record database.  There's just no way you can put out your TPS reports without it.  What many companies do is perform certain data mining and harvesting tasks once per day, rolling up and summarizing certain data into a "data warehouse" for offline reporting needs.  This data is often de-normalized to make faster queries and it's usually stored on a different database server and even a different network to prevent reporting from slowing down the workday.
The idea is that you create a query that performs certain common joins and relations... rolls up hourly/daily/monthly or whatever kind of totals, creates various types of crosstabs, and stores the rolled up data in a reporting database.
These summaries are run during off hours (or infrequently during the workday) to prevent the database servers from clogging with work while you're open for business.
Managers and their lackeys can hammer away at the reporting database all day long and your main operation could not care less.

Tip 5: Joins vs. Sub-Selects
Sometimes when you just want a single lookup from a table (say decoding a County ID to a name) , it's faster to not join in the county table.  Some SQL servers can detect this kind of lookup and perform this optimization for you.  Note...

select U.UserName, RR.RoleName from aspnet_Users U
join aspnet_UsersInRoles R on R.UserId=U.UserId
join aspnet_Roles RR on RR.RoleId=R.RoleId
We have joined the aspnet_UsersInRoles table to get the ROLEID of the user's roles, and joined the aspnet_Roles table to get the name of the roles for the user.

Note the difference here:

select U.UserName,
(select RR.RoleName from aspnet_Roles RR where RR.RoleId=R.RoleId) as Role
from aspnet_Users U
join aspnet_UsersInRoles R on R.UserId=U.UserId
See how we're using the sub-select to pick up one field?  The thing about this tip is that sometimes the former way is faster and sometimes the latter.  You have to experiment.

Tip 6: Explain Plan/Query Optimizers
Ok, most SQL Servers have a module called a Query Optimizer.  This module can take your weak, human SQL and tweak it before execution to improve performance.  But there is also usually a UI that you can use to performance tune your queries.  I know Oracle has this, and have used it many times.  It breaks your query up into chunks called a query plan, and assigns an estimated cost to each part of the plan.  You can then alter the text of your query to improve the overall performance.

Tip 7:  Query Hints
On the rare occasions when your SQL is tricking the optimizer into taking the wrong approach to your query, Oracle and SQL Server have the ability for you to add query hints to your SQL.  These hints can tell the optimizer to use a specific kind of cursor, or prefer a different index, and alter the query plan used during execution.
The syntax differs greatly between the various SQL servers (Oracle/MSSQL/etc.), and will be shown only as an example here.
  1. SQL Server
  2. More SQL Server
  3. Oracle
Tip 8: Views
Sometimes creating a view can help optimize your query.  The idea is to optimize the SQL for certain common transactions and save it as a view, making the need to reinvent the wheel significantly lower.  But there is also such a thing as a materialized view. This is a type of view that is maintained like an index, as the live data is stored in the main tables.  A normal view does not store it's own data, but a materialized view does.  The view's data can then be queried without having to do whatever complex joins were needed to generate the data.  Of course the trade-off here is storage space.

Monday, February 15, 2010

The Latest Windows Malfunction

Lately my DSL has been wonky at the office.  Seemingly, everything is fine, but now and then, I get the little networking icon in this state - - sending but not receiving.

To fix this problem, I have been right clicking the little icon, opening my network connections, disabling and  enabling my network connection, and I'm good for another 15 minutes to a day.

Sometimes however, I have some process I'd rather not disconnect, so I try the good ol' XP repair connection function. Sometimes it works.  Sometimes it doesn't.  And then, sometimes, it does this:

 

Note how Windows is supposedly "finished", yet we don't have the "close" button, we have the "cancel" button.  You can click that button 30 million times and nothing is going to happen - believe me, I have tried.  That effing window is going to be there until you reboot.  There is no process in the process explorer to cancel, there is no application to end task on, you can disconnect the network and reconnect, and the little window stays right there.  God forbid you have some process running, and need it to complete first.

I wonder if the new Google Chrome will be any better.

UPDATE
This turns out to be a conflict between Zone Alarm and AVG anti-virus!  Remove one or the other and your troubles will cease!

Share This!

Contact Us

Name

Email *

Message *