I have been dealing with MS SQL Server 2005 and Microsoft SQL Server 2005 Analysis Services as well as Reporting services to display results to end users.
It was amazing how many issues are in the fields of databases. I developed projects on my dev box with snapshot of database (biggest table contains aprox 200,000 rows) and I though it was done. Unfortunately I was not able to deploy in production because of following reasons:
- cube and dimensions processing was extremely slow - this was solved with playing with indecies
- Memory error: The operation cannot be completed because the memory quota estimate (1678MB) exceeds the available system memory - this was on 4xCPU and 4GB RAM on 32bit Windows 2003 std. This was solved with limiting SQL Server serive memory to 2GB and set /3GB startup switch in windows.
- then came: Arithmetic overflow error converting expression to data type int. This was tough also because I had to wait 2:30 hours before I get the error. then I make changes and try again... this was very, very long loop. For fully understand it check SQL Server Books and as resolution check Barnabas Kendall's blog entry.
there are some good points in Barnabas Kendall's blog entry like:
I don’t enjoy reading documentation (lack of plot and character development), :):)
- Create the view with the SCHEMABINDING option.
- Define the view to pre-aggregate the data (this is where the performance enhancement comes from). For example, I have a view that splits up the year, month, day, and hour of a click, groups by client, and also returns a total. I can also reuse this view to get totals by hour, day, month, year, or all time.
- Don’t use COUNT, use COUNT_BIG.
- Don’t use AVG; use SUM and COUNT_BIG. Beware of integer overflows in your final query; SUM always wants to make an INT. Why isn’t there a SUM_BIG? You can get around this by using CAST or CONVERT in your final query.
- Make a clustered unique index on the view. This forces the database engine to persist the values to disk rather than calculating them on the fly, which is what leads to five-minute queries. I am surprised to learn that you don’t need to put all the values of the table in the index, just the ones necessary to make a clustered unique index.
- When querying the view, use the NOEXPAND hint.
I would like to add: All aggregate functions return result depending on input parameter's type- if input column is of type int and result would be if type int:
select sum(numbers) from dbo.TestTable
And if you use SUM of multiple rows all that contains values like 2,000,000,000 you will end up with Arithmetic overflow error
In order to change result type this:
select sum(cast(numbers as bigint)) from dbo.TestTable
I employed some of these techniques and started the process again. I am keeping my fingers crossed...
I am reading about new features in Visual Studio 9.0 (Orcas) (while I am playing with it :) ) and I found there is a new feature in Visual Basic called Relaxed Delegates.
What this means!? For instance you can convert methods to delegate even when methods doesn;t have all parameters (if not used)
This is the sample from Amada Silver:
Another great feature that you’ll
notice in Beta1 is Relaxed Delegates. In short, relaxed delegates
are a way to extend VB’s implicit conversions to delegate types. With relaxed
delegates, you can write the following code:
Private Sub Button1_Click(ByVal
sender As Object,
ByVal e As
You can even omit *all* of the event
arguments if your method body doesn’t need them. This improves readability
without compromising type safety:
Option Strict On
Public Class Form1
Private Sub Button1_Click() Handles
A bit unfair to C# but still - this is Visual Basic :)
Today MS signed off on the Beta 1 release for Visual Studio “Orcas” and .NET FX 3.5.
Go and check here.
Visual Studio Code Name “Orcas” Beta 1
Visual Studio code name "Orcas" is the next generation development tool for Windows Vista, the 2007 Office system, and the Web. Beta 1 consists of multiple releases including, Visual Studio Professional Edition, Visual Studio Team Suite and Visual Studio Team Foundation Server, which are available as installation media ISO images you can use to install the products. Alternatively, you can download VPC images with the software pre-installed. In addition, you can download prerelease versions of Visual Basic Express, Visual C++ Express, Visual C# Express, and Visual Web Developer.
For a better download experience, MSDN Subscribers should use MSDN Subscriber Downloads for both installation media ISO images and VPC images.
This news came from Soma
Assemblies of next version of .NET Framework are divided in two groups - "green bits" and "red bits" as they are called inside Microsoft.
The red bits include all the libraries that shipped before as part of the .NET Framework 2.0 and 3.0 (such as mscorlib.dll and system.dll). To maintain a high assurance of backward compatibility for Visual Studio "Orcas," changes in the red bits have been greatly limited.
The green bits assemblies are the brand new libraries with additional classes that work on top of the red bits assemblies. Most of the classes listed in this column are in the green bits assemblies (such as system.core.dll), with a few involving limited changes in the red bits assemblies.
The CLR's contributions to the new libraries include:
- A new add-in hosting model, which was discussed in the last two editions of CLR Inside Out
- Support for the Suite B set of cryptographic algorithms, as specified by the National Security Agency (NSA)
- Support for big integers
- A high-performance set collection
- Support for anonymous and named pipes
- Improved time zone support
- Lightweight reader/writer lock classes
- Better integration with Event Tracing for Windows® (ETW), including ETW provider and ETW trace listener APIs
There are also new crypto classes that covers Suite B set of cryptographic algorithms.
for more info read New Library Classes in "Orcas"