Be Discrete

Well, sometimes being discreet is being efficient :)
I recently had to optimize some reports for speed and I found one query that took around 8 seconds.
The query, a pretty simple select was doing

SELECT blah, blah
FROM theTable
WHERE Year <= @Year
AND Year > @Year - 10

What I did was using the ugly, but efficient

SELECT blah, blah
FROM theTable
WHERE Year IN (@Year-9, @Year-8, @Year-7, @Year-6, @Year-5, @Year-4, @Year-3, @Year-2, @Year-1, @Year)


The execution time dropped from 8 seconds to about 900 ms.

UNPIVOT SQL SERVER OPTION

Sometimes reading about new features of the products you are using help a lot.

Using UNPIVOT I managed to reduce the length of a query from 8 seconds to about 650 - 700 ms.
The old query was doing a UNION between data based on data from some columns. The initial Query was doing this (data changed for privacy reasons):

SELECT country
, year
, SUM(cases) AS nbCases
, isNotValidSomething
, isValidSomething
, 'some stuff' AS ResultType
, 1 AS SortOrder
, CASE Column01 WHEN 'S' THEN Sum (cases) ELSE 0 END AS Sensitive, 
CASE Column01 WHEN 'R' THEN Sum (cases) ELSE 0 END AS Resistant, 
CASE Column01 WHEN 'Unk' THEN SUM (cases) ELSE 0 END AS Unknown
FROM vAll 
WHERE 
Country  = @Country 
AND Year =@Year 
AND Column01  IS NOT NULL
GROUP BY country, year, isNotValidSomething, isValidSomething
, Column01
, rType
UNION
SELECT country
, year
, SUM(cases) AS nbCases
, isNotValidSomething
, isValidSomething
, 'some other stuff' AS ResultType
, 2 AS SortOrder
,CASE Column02 WHEN 'S' THEN Sum (cases) ELSE 0 END AS Sensitive, 
CASE Column02 WHEN 'R' THEN Sum (cases) ELSE 0 END AS Resistant, 
CASE Column02 WHEN 'Unk' THEN SUM (cases) ELSE 0 END AS Unknown
FROM vAll 
WHERE Country  = @Country 
AND Year =@Year 
AND Column01  IS NOT NULL
GROUP BY country, year, isNotValidSomething, isValidSomething
, Column02, rType

UNION
....

(there are 8 queries like this in UNION)

The new query is doing :

SELECT country, year
, SUM(cases) AS nbCases
, isNotValidSomething, isValidSomething, ID, Cols FROM vAll
UNPIVOT (
ID FOR Cols in ( Column01
,Column02
,Column03
,Column04
,Column05
,Column06
,Column07
,Column08
)
) up
WHERE country = @Country
AND year = @Year
GROUP BY country, year, isNotValidSomething, isValidSomething, up.ID, Cols
ORDER BY country, year desc, Cols


Thanks to this post.

All best,
Radu

Publishing WPF Site

OK, we (almost) finished the WPF application and wanted to install it.
Nothing simpler than this: right-click -> publish -> choose location -> run XBAP. I wish !!!
it kept telling me desperately that "System.IO.IOException: Cannot locate resource 'app.xaml'."

Some google research lead me to this post. I tried dropping out my custom control and replace it's call with the code itself and it suddenly worked.
Thank you , guys, I was really out of ideas about it !
This is only my part of problems, my budy is struggling with thinks like : missing ADODB on the remote machine, dll not in the GAC, etc

Strange strange world...:)

Embedded Report crashing

I begun feeling the smell of WPF few weeks ago. I'm developing an application where we need reports, of course. Misteriously I noticed my report crashing on the second run. It works OK when I run it for the first time, but if I navigate back to the application and try to run the report again, I get a "nice" FatalExecutionEngineError with a code = 0xc0000005
Search the 'net deperately but no success until I tested with the same report but no data within. I noticed it worked OK if no data is loaded.
Well, I decided to test more: I created a blank report and called it from my code; all worked fine i was able to go to the report and back as many times as I wanted.
Cool, a small step forward. I added some simple labels and all continues to work.
It stopped working when I passed the parameters. Strange enough but as i don't have too much times I droped the parameters and let it run. I send the data in the DataSet.


However today my report stopped working.
The cause seems to be a field with the value set to the expression =Right(TimeValue(Fields!Date.Value), 10)

what is wrong with this ? the date field is a Date...maybe I'll never know. I just passed it from outside as a string and display directly.


But the problems with the rdlc reports don't end now. I wanted a simple pageNumber , when I added this in the future the report start crashing again on the second run.

Do You Believe in Search After Search ?

Well, I'm trying to figure out some new techologies like WPF, WCF or new features of SQL Server 2005.
What I needed in my project was to do a select where to get the first n elements in a list, or the last m...where I wanted "n" or "m" to be variables. So after very little strugle I come up with the query in a stored procedure like this:

....
SELECT IDOffice, IDTicket
FROM (
  SELECT ROW_NUMBER() OVER (
    ORDER BY 
       CASE when @WhatToGet = 1 THEN IDOffice END ASC,
       CASE WHEN @WhatToGet = 2 THEN IDOffice END DESC) AS ROWID,
    IdOffice,
    IdTicket
  FROM TheTable t
  WHERE
    t.IdTicketStatus = @IdTicketStatus and
    dbo.uf_GetDay(t.Date) = dbo.uf_GetDay(getdate())
  GROUP BY IDOffice
) tmp
where ROWID <= @MaxOffices
ORDER BY IDOffice
 

Using this where rowId < @MaxOffices is an idea that come to my mind from the database engines that don's implement select top

It was working and it won't have big performance issues as the DB it'll run on will be pretty small. However, there was something I didn't like in it...so I did some google research and I come up with :

SELECT * from ( -- just to order by offices
SELECT TOP (@MaxOffices)
  IdOffice,
  dbo.uf_ConvertFromAbsolutIdTicketToRelative(Max(IdTicket)) IdTicket
FROM
  TheTable t
WHERE
  t.IdTicketStatus = @IdTicketStatus 
  AND dbo.uf_GetDay(t.Date) = dbo.uf_GetDay(getdate())
GROUP BY IDOffice
ORDER BY 
  CASE when @WhatToGet = 1 THEN IDOffice END ASC,
  CASE WHEN @WhatToGet = 2 THEN IDOffice END DESC
) tmp
ORDER BY IDTicketoffice

this looks nicer, I didnt' know SQL Server 2005 implements the SELECT TOP (@parameter). I'll take one more look at this query, when time allows (it never allows).

ASP.NET Request

So I'm progamming ASP.NET today.
There are still some things I am not aware of. as there were many was not aware of until meeting them. Take this for example:

Somewhere in the code programmer wrote:

string requestParameterValue = context.Request[requestParameter] == null ? "": context.Request[requestParameterName].ToString();

if(requestParameterValue != "")
{
// do a GUID from the requestParameterValue ...
}

this worked until the code "meet" a piece of code writen by some other programmer. This other programmer was putting on request (on QueryString) some data to pass like clientCode - clientName. No URL Encription, no nothing !

For example (names changed for privacy issues) :
0001 - A company
0002 - Company 2
0003 - AT&T

ooops AT&T put on Query String . Great !

What happens with the queryString (a NameValueCollection ) ? If we were to pass only 0003 - AT&T the query string would be composed of:

• Key1 with valye "003 - AT" and
• another entry with the key nll and the value "T"
  

Now come back to the piece of code above. What happens if requestParameterName is null ? When requestParameterName is null (for some stupid key missing in an XML file), the request would still read the value of the key null.
But now, context.Request[requestParameterName] returns, waw, "T".
Creating a GUID from the string "T" is pretty difficult isn't it ?

That's a sample of how nice programming is :)

Enjoy programming !

Intro to programming hell

Hello world,

try {
while (inspiration != null)
{
Console.Writeln("Explain why this blog, what do I want to do with it and what I'll post here");
}
}
catch {
MyLoggingClass.Log ("missing inspiration");
}
finally {
Console.Writeln("this was the first post. pretty dirty, uh ?");
}