Thursday, July 1, 2010

Forceliterals - somewhat surprising results

A wee test of the forceLiterals keyword. I started by taking more or less the exact query from Dev III chapter 2. Then I added some more statements to the WHERE clause, in the hope of amplifying the discrepancy between using and not using this keyword. Here's how it shook down:

static void Nathan_ForceLiterals(Args _args)
{
CustTrans ct;
System.DateTime first, second;
System.TimeSpan ts;
int counter;
str message;
;

flush CustTrans;
first = System.DateTime::get_Now();
while select forceLiterals * from ct
order by AccountNum
where ct.TransDate >= mkDate(1, 1, 2010)
&& ct.AmountCur >= 10
&& ct.PostingProfile != ''
{
counter++;
}
second = System.DateTime::get_Now();
ts = second.Subtract(first);
message += ts.ToString();
message += strFmt("\nCount is: %1\n", counter);


flush CustTrans;
first = System.DateTime::get_Now();
while select * from ct
order by AccountNum
where ct.TransDate >= mkDate(1, 1, 2010)
&& ct.AmountCur >= 10
&& ct.PostingProfile != ''
{
counter++;
}
second = System.DateTime::get_Now();
ts = second.Subtract(first);
message += ts.ToString();
message += strFmt("\nCount is: %1\n", counter);

info(message);
}



The first time I ran it I got this:

00:00:02.0322032
Count is: 6091
00:00:02.1672167
Count is: 12182

I ran it a second time:

00:00:01.9231923
Count is: 6091
00:00:01.9531953
Count is: 12182

So, some DB caching probably smudging the results here. Well within the margin of error. Maybe one day I'll do some more analysis on the SQL Server side to see what queries came through. Another day...

No comments:

Post a Comment