If vs if

[tedsmith]

I previously thought IFF was a preferred way than IF ELSE ENDIF (because it only takes one line instead of 5)
Testing by multiple calling the same sub with either method revealed IIF to be 3 times slower than IF ELSE END IF.
This highlights a possible speed problem using IIF in SQL statements to convert different row values to something else (you can't directly use IF)
Is there another way or would using an embedded Function be any better?

 

[strongm]

Firstly, given that VB6 is compiled using an optimising compiler, the number of lines taken up by source code is hardly ever an accurate reflection of the efficiency or size of the of the compiled code.

Secondly Iif is indeed inefficient compared to If, as it always evaluates both the true and false clauses.

Thirdly, using any VBA functions in a SQL statement (which Jet-SQL allows) is always relatively inefficient (and makes the code non-portable to other more standard SQL dialects)

 

[dilettante]

The [tt]IIf[/tt] function can't shortcut by its very nature: it is a function accepting ByVal arguments. So of course it evaluates both "branches."

I wouldn't leap to a conclusion that using functions from the Jet Expression Service is "relatively inefficient" because sometimes the alternative performs far worse. So I'd reject such a blanket statement, though I can understand the point.

Portability could be a stronger argument. Not much stronger since few ever face it and when it does arise there are usually easy alternatives. But it does mean more work to convert to other non-standard SQL dialects like T-SQL.

Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to SQL. SQL, the acronym for Structured Query Language, is a standardized computer language that was originally developed by IBM for querying, altering and defining relational databases, using declarative statements. T-SQL expands on the SQL standard to include procedural programming, local variables, various support functions for string processing, date processing, mathematics, etc. and changes to the DELETE and UPDATE statements.

 

[strongm]

>Jet Expression Service

Fair point. I was avoiding making the distinction between VBA and JES (mainly because the JES version of IIF does NOT evaluate both branches), and my comment was related to Ted's timings in VB6, and hence VBA, which does. ANd it is certainly the case that JES functions may well run quite efficiently. Your own VBA funtions, however, used in a JET SQL statement may not.

 

[dilettante]

Just goes to show things don't have universal cookbook answers. Even things that look alike don't always work alike.

Personally though I'd love to find a way to expose functions written in VB6 to be callable within Jet expressions. While just adding more burden to future porting, most of my uses would involve Jet as a local datastore and not a general purpose database anyway so porting wouldn't arise.

I assume MS Access does this through private entrypoints in the Jet engine, setting up some sort of callback.

 

[tedsmith]

I wonder why they called it Visual BASIC?
So how would you use (portable) SQL to convert a single row to something else depending on it's value?
(equivalent of IIF(Value=1,Red,Green))

 

[strongm]

The usual advice is to use the CASE ... WHEN expression if IIF is not available - but this does not exist in Jet SQL.

But please read this thread again: IIF is slower than IF in VB6 (for reasons outlined above), but not in Jet SQL, because a) there is no IF to compare it against and b) it is implemented differently and so not subject to the same slowdown as the VB version.

 

[Andrzejek]

why they called it Visual BASIC?" (tedsmith)
I just found out, BASIC stands for: "Beginner's All-purpose Symbolic Instruction Code"
I didn't know that...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[strongm]

Visual Basic is not really BASIC. Sure, it is ultimately derives from BASIC, but is a very different beast (and that's why it isn't uppercase)

 

[dilettante]

VB just stayed syntactically compatible with early Microsoft Basics, making it easy to port forward from version to version until VB.Net broke the mold radically.

 

[CP60]

IIF(Value=1,Red,Green)?

Why do you need to do this?

Has it to do with how the records are displayed?

 

[tedsmith]

One reason I currently have is that in one strange users system they use a 26 hour day instead of DATE AND TIME,
Historically this was because they originally has a 300baud modem telephone hookup (before the days of the internet) and they were open for business until 2am each morning.
To save time and storage space, rather than send "20/2/15 11:55 PM" before midnight and "21/2/15 1:05 AM" (16 characters) ten minutes later they sent "2355" and "2405" (4 characters). Any time over 23:59 produces an error if you try to use it as regular time.

One reason I use IIF is to convert OldTime to normal date time format for a more modern database.
I test if the hour part > 24 and subtract 24 from it then format with ":" and the minutes to get AM/PM from the result, advancing the date only if >24.

http://www.tek-tips.com/viewthread.cfm?qid=1744974

 

[CP60]

>for a more modern database.

Ok, so it's interfacing to another database.

(For display purposes in, for instance, a bound Gridcontrol, among other things, I could have offered a different, faster solution.)

Couldn't you just add and set a field to hold the 4-char time in the receiving database table, and then just run an UPDATE sql statement on the data there?
Then you would use the WHERE clause instead of the IIF() function.
That is usually just as fast, and sometimes even faster, especially with alot of data to process.

You could also send the data to a second table in a temporary local profil mdb, (to avoid bloating), to hold the data in between in order to further process with an UPDATE statement.
Your sql statements would also be more readable and portable.

 

[tedsmith]

Thanks CP60
I ended up making a new table to do all the final querying on anyway but I still had to determine whether the time was past midnight to be able to make the use of the original data table time column (that was Text format)
The format of the time column in the new table is in date format so I can easily query it.
If I used WHERE I would have to run it twice, once for under 24:00 and again for after midnight then combine them. I think this would be much slower than IIF.

 

[strongm]

>If I used WHERE I would have to run it twice, once for under 24:00 and again for after midnight then combine them

Why?

 

[SkipVought]

...and Dartmouth BASIC is the first version of BASIC.

 

[tedsmith]

Because if you try to convert 25:00 to Date/Time you get an error.
Without IIF you'd have to run at least two procedures which would make it even slower

Doing with a IIF I used in one pass
"CVDate(IIf([Mytimesecs]<86399,(Date() & ' ' & Format([MyTime],'h:nn:ss AM/PM'))," _
& "(Date()+1) & ' ' & Format((LTrim(Val(Left([MyTime],2)-24)) & Mid([MyTime],3)),"

This added 1 day to date and subtracted 24 hours from the time where it was more then 25:59. The database contained a column that was the seconds past midnight as well as a text column for the time that ended at 26:00, an illegal computer time

As it is only run when you want the result, the day is automatically always today or tomorrow only for times after midnight.

If I had used WHERE I would have had to run it once for times before midnight and again for those times after midnight with a similar calculation because you can't otherwise ignore the illegal times without error.

In MSAccess this could also have been done using a function in the Criteria but I have never been able to get functions to work in VB6 criteria (not that I have tried very hard!)

 

[fredericofonseca]

ted

I think that was being proposed as a possibly faster option than to use the IIF was something like this

create table xx
(startdate datetime2
,enddate datetime2
,mytimex varchar(4)
)

insert into xx values(convert(date,getdate()),null,'2355')
insert into xx values(convert(date,getdate()),null,'2405')
select *
from xx
update xx
set enddate = case
              when convert(int,substring(mytimex,1,2)) <= 23
                   then convert(date, convert(varchar(10),startdate,121) + ' ' + stuff(mytimex,3,0,':') + ':00',121)
              else convert(date, convert(varchar(10),dateadd(d,1, startdate),121) + ' '
               + stuff(right('0000' + convert(varchar(4), convert(int, mytimex) - 2400),4),3,0,':') + ':00',121)
              end
where enddate is null
select *
from xx

so you do inserts into your db, do update of the fields as required (and with further validation as my assumption above is that the field is always populated, that it never goes past 2 days worth of minutes, and that the value is always a valid time if we subtract 2400 - if either of these isn't always true further validation would be required), and from that point on you only use the correct datetime field on your queries with no further conversion required

and as for "If I used WHERE I would have to run it twice, once for under 24:00 and again for after midnight then combine them"

as strongm was querying, the above case statement could be used on your queries (with changes as required to your needs) to do the select in one go (although not the faster way possibly)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[strongm]

WHERE DateDiff("h",date, date + timeserial(left(MyTime,2),right(MyTime,2),0))<=26

 

[fredericofonseca]

nice one strongm

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886