VBA Rubberducking

Comintern

1:00 PM

@ThunderFrame It's a VBA style comment block!

@this The future date is just a reminder that you shouldn't change published interfaces. :-)

FreeMan

@ThunderFrame That's... impressive?

this

LOL. Too late. I already changed a bunch of things, including the GUIDs.

technically that's a breaking change but we remove the old guids on uninstall for release, so it won't be noticed. (at least nobody has opened an issue...)

FreeMan

ok, code's finally starting to look weird to me with my Dim x... statements at the top of the code and not immediately above their use.

2

I've been Rubberduck'd

3

Comintern

@this Technically it's also pre-release, too.

this

1:16 PM

True for API but I also changed the stuff in the core COM, too

Comintern

That's really not that big a deal IMHO.

this

Evidently, based on lack of complaints :p. At least we have the everything following the COM guideline that you lain out months ago.

Duga

[rubberduck-vba/Rubberduck] MDoerner created comment on issue #1057: Parser bug with Rem statements immediately followed by special characters

> When using this regex, just keep in mind that it will not necessarily find all Rem comments since the pretty printing gets in the way as mentioned in the original post.

FreeMan

I always find "lack of complaints" to be the best method for determining whether people are still using reports or processes that are ancient & unknown. Stop running them and find out what happens. :)

ThunderFrame

1:34 PM

@FreeMan there was a time when I preferred Netscape over IE, because, IIRC, Netscape supported frames. But then IE took over and by IE5, Netscape was in its death spiral. That's right about the time that the company chose Netscape as the preferred browser. In my experience, the only companies that still use IE by default, are those that have some intranet site built on ActiveX controls, that doesn't work in Chrome or Firefox.

And, from what I can tell, the only people still using IE/Edge at home, are those that don't know that anything else exists, not because they tried Chrome/Firefox and just didn't like it.

> Since normal string comparison is always case-insensitive, you can then simply do
>
> InWhatsUp = (str1 = str2)

0

A: VBA for Access- Compare function reporting "false" or #error everytime

A query can never pass objects like Field to a VBA function. Use parameter data type String, or if the values can be NULL, Variant. Since normal string comparison is always case-insensitive, you can then simply do InWhatsUp = (str1 = str2) or directly in the query: Identical: IIf(server1 = ...

I mean really, do people just post rubbish on purpose?

M.Doerner

@ThunderFrame I think you are wrong. At least, I could not find a single intranet site on our intranet that does not run on Chrome. Still, the default browser throughout the company is IE and the use of Chrome is discouraged.

ThunderFrame

@M.Doerner hmm, maybe so. There has been a greater effort by some IT departments to ensure they don't lock themselves into a certain platform, like they did in the past.

FreeMan

@ThunderFrame Yeah, IE is still our default at work, though they finally updated from IE8 to IE11 about 6-9 months ago. There are some tools that are used in the hospitals that, apparently, won't work on any other browser. I guess they rely on too many broken "features" of IE.

@ThunderFrame The other group of people are those who fear change. I haven't been able to get my in-laws off of IE for love or money. Of course, my MIL is still insisting on using WinXP on one machine because she's used to it...

this

@FreeMan That's what Microsoft gets for moving the cheese.

Comintern

@FreeMan O_O

ThunderFrame

1:47 PM

Why not just use the built-in StrComp function that is designed for this type of comparison, rather than reinventing the wheel, and somewhat inefficiently too. I suspect there are also some bugs in you function with certain unicode characters. — ThunderFrame 27 secs ago

Comintern

@ThunderFrame Does IsError mean what he thinks it does in this context? If IsError(field1) Or IsError(field2) Then Exit Function. My understanding is that it checks a Variant to see if it's a VT_ERROR type. In that field1 and field2 are both String, they'll be cast to a VT_BSTR and never be a VT_ERROR.

Mathieu Guindon

@ThunderFrame hmm, wording seems off-topic for SO. I'd take out the part that asks about books and/or online resources before putting up that bounty

this

> I'm sorry - the comparison operator is always case-sensitive, so ?"foo" = "FOO" will always return False

That's incorrect.

Most likely because of Access' defaulting to Option Compare Database.

?("abc" = "ABC")
True

running the same in Excel, I get the False

ThunderFrame

@Comintern if the field is a calculated field, it may well be an error, but I forget how to check that in DAO.

this

also, again, because of the Option Compare Database (which apparently applies even to immediate), it most likely handles the unicode characters using the same collating order as the file's established collating order.

ThunderFrame

1:55 PM

@MathieuGuindon yeah, I thought that after submission - I'll edit it

@this In Access I get

?"foo" = "FOO"
False

this

?"foo" = "FOO"
True

Locale???

ThunderFrame

oh, snap - didn't have a database open in Access

this

O_O

This is insane

"This is VBA. It may compare string as it pleases, and screw you."

I kinda of understand why - you can't have the collating order without the file

but dang, that's very confusing behavior if you ask me

Comintern

I think it's that ^^^

ThunderFrame

all the more reason to avoid equality comparisons

use StrComp - that's what it's for

this

1:59 PM

I don't disagree but the locale is also poorly documented.

ThunderFrame

yeah, most people have no idea where to find the collating order

this

If you go ?StrComp("Mueller", "Müller"), you may not get the result you want if you're after the german's collating order.

Comintern

Our WordPress raccoons set the default collation on our database to Swedish.

this

need to pass in 1024 (?) as the 3rd argument. That's undocumented.

Mathieu Guindon

@Comintern MySQL?

Comintern

2:00 PM

Yep. They probably left it at the default.

Mathieu Guindon

for some reason our MySQL-backed website has Swedish collation too

ThunderFrame

@Comintern Raccoon - is that a Wordpress achievement badge?

M.Doerner

@this I hope those are not equal in the German collation order.

There are people whose surname is Müller and people whose surname is Mueller.

Comintern

It errors when I run my SQL through the translator. SELECT * FROM 'vp_uptiuns' VHERE 'cumments_nutiffy' = TROuE

@ThunderFrame Yes. Apparently it's awarded for washing your food before you eat it.

ThunderFrame

@M.Doerner They're not the same, nut they're treated the same for collation in phone directories, according to Wikipedia

> For phone directories and similar lists of names, the umlauts are to be collated like the letter combinations "ae", "oe", "ue" because a number of German surnames appear both with umlaut and in the non-umlauted form with "e" (Müller/Mueller).

this

2:09 PM

I don't know what to say. I'm only telling what I learnt - that there's german phone book collating where those are considered equal.

?Strcomp("Mueller", "Müller")
-1
?Strcomp("Mueller", "Müller",&H10407)
 0

Comintern

Now I'm curious how ß collates.

ThunderFrame

@Comintern always as "ss"

> ß is always sorted as ss

this

?Strcomp("ß", "ss")
 0
?Strcomp("ß", "ss",&H10407)
 0

so don't need locale in this case.

FreeMan

@Comintern LOL! On purpose?

this

but i bet you there's some other weird locale where it's not equal

ThunderFrame

2:12 PM

@this Bavarians!

FreeMan

@Comintern the translator is blocked by our no-funnik IT firewall. :(

ThunderFrame

ß

In German orthography, the grapheme ß, called Eszett (IPA: [ɛsˈtsɛt]) or scharfes S (IPA: [ˈʃaɐ̯fəs ˈʔɛs], [ˈʃaːfəs ˈʔɛs]), in English "sharp S", represents the [s] phoneme in Standard German, specifically when following long vowels and diphthongs, while ss is used after short vowels. The name Eszett represents the German pronunciation of the two letters S and Z. It originates as the sz digraph as used in Old High German and Middle High German orthography, represented as a ligature of long s and tailed z in blackletter typography (ſʒ), which became conflated with the ligature for long s and...

@FreeMan there you go ^

oh, woops, didn't see what you were replying too

I can't type this evening either

it might be TTGTB

FreeMan

night night!

IvenBach

4:01 PM

Time to tackle this filtering stuff. Wish me luck.

Mathieu Guindon

ok I need to share this - this is just too F'n neat to be real

,[AvlbToShip] = (select max(Value) from (values (0), ([OnHand]-[Committed])) avlb(Value))
,[AvlbToSell] = (select max(Value) from (values (0), ([OnHand]+[OnOrder]-[OnSalesOrder]-[Committed])) avlb(Value))
,[OpenToBuy] = abs((select min(Value) from (values (0), ([OnHand]+[OnOrder]-[OnSalesOrder]-[Committed])) avlb(Value)))

355

A: Is there a Max function in SQL Server that takes two values like Math.Max in .NET?

If you're using SQL Server 2008 (or above), then this is the better solution: SELECT o.OrderId, (SELECT MAX(Price) FROM (VALUES (o.NegotiatedPrice),(o.SuggestedPrice)) AS AllPrices(Price)) FROM Order o All credit and votes should go to Sven's answer to a related question, "SQL M...

it replaces all the duplication I'd otherwise have with CASE WHEN {calc} < 0 THEN 0 ELSE {calc} END

IvenBach

Duck check: Should github.com/rubberduck-vba/Rubberduck/blob/next/Rubberduck.Core/… be namespace Rubberduck.Core.UI.Inspections?

When moving all the things around does the name space not get updated with the folder it's under?

Mathieu Guindon

4:16 PM

there's no Rubberduck.Core namespace, it's just Rubberduck

IvenBach

mkay. A few other random folders I'd seen were RD.Foo.Folder1.Etc and thought I'd ask.

Mathieu Guindon

possible.. quite a few things got moved around lately

IvenBach

Alt,W,N for the win.

Hosch250

@IvenBach Nice.

Mathieu Guindon

I think this is it. down to 8 seconds, and 95% of the work going into populating a table variable from the remote source

was >45 minutes yesterday

Hosch250

4:25 PM

Both of us are making our company's software faster :)

IvenBach

@Hosch250 ^ and behold, it is glorious.

Makes coding so much easier.

@MathieuGuindon Benefits of knowing how to tinker with stuff.

Hosch250

@IvenBach How?

I'm a fan of splitting the one page so I can see two parts at once.

IvenBach

I don't have to scroll up and down as much for the vertical monitor. I can have 3 windows open and each looking at a different section.

~~You're saying just split the screen in half with 2 windows?~~ Just tried it. I'd have to get used to it.

Hosch250

@IvenBach That thing ^

Grab it and drag it down.

IvenBach

:+1: One less window. This old man will have to get used to that.

Comintern

4:38 PM

@MathieuGuindon Oh wow, that's slick. I need to bookmark that...

IvenBach

@Hosch250 I'm so used to Excel freeze panes that I'll have to work with this a lot till I get comfortable with it.

Mathieu Guindon

Hmm I might have one or two superfluous indexes... If I shave half a millisecond per record, I could bring it down to like 5-6 seconds I think. I wonder what's the insert cost of an unused index on a table variable...

@Comintern IKR!

Comintern

I've been trying to get people here to stop using OUTER APPLY in views...

Hosch250

@MathieuGuindon LOL, you didn't know that?

SQL has pretty much all the common statistical operators built in.

Max, min, avg, stdev, stdevp, var, varp...

Mathieu Guindon

@Hosch250 I knew about Max, but I needed a way to inline it

Hosch250

4:43 PM

Oh.

Yeah, create a sub-select.

Less performant, but not by too much if you use it judiciously.

this

there's also window functions, as well.

Comintern

Windowed functions don't work in some contexts though.

this

Granted, but so far, it works for what I needed to do.

Comintern

Yeah, for me too. That's why I get indignant when I run into a context they don't work in.

this

what is the one that stops you often?

Comintern

4:47 PM

HAVING clauses.

this

Hmm. I rarely use that.

I usually push all my aggregates into a CTE. I hate big GROUP BYs especially when they group on columns that has no relevance to the aggregation.

Comintern

I use them a lot to do meta analysis on databases that I'm trying to reverse engineer the schema for.

Hosch250

@Comintern You know, there's a script you can run that dumps the schema, IIRC.

It was in my book on network attacks in college, IIRC.

Comintern

The schema dump is easy, I'm mainly trying to determine key relationships when they aren't specified by constraints and the frequency of field usage.

Hosch250

Oh.

Ignore me then.

Comintern

4:53 PM

It's appalling how rarely programmers configure the referential integrity backstops in the back end.

Hosch250

:)

Comintern

Most of the databases I examine have exactly zero constraints.

IvenBach

Is there a Last Position command in VS like there is in VS?

Comintern

Back arrow on the toolbar.

Hosch250

@Comintern Ninja'ed.

Mathieu Guindon

4:54 PM

@Comintern I could never understand that

@IvenBach a command in VS like there is in VS?

IvenBach

#Words ... like there is in the VBA IDE

Comintern

@Hosch250 Wha-cha! @IvenBach - the tiny dropdown arrow gives your "browsing history too".

this

@Comintern I go back to them and say "you don't have a database. You have a flat file with garbage."

Mathieu Guindon

^

Comintern

IKR?

this

4:56 PM

the worst is when they reinvent the constraints

Comintern

"Did you develop this in Excel VBA?"

this

I'm a programmer! I can do it!

IvenBach

@Comintern The split button on the Navigate backward. At least that's what I know they are called in office.

Mathieu Guindon

@this literally, exactly that. that backend db I'm dealing with used to be a bunch of COBOL flatfiles.

Comintern

@IvenBach I can never keep the terminology straight. :-)

this

4:57 PM

No, you silly ass, use others' work, especially one that has more investment and bug fixes. </rant>

Mathieu Guindon

they made a bunch of SQL Server tables with exactly the same names and columns, and now claim their software has a SQL Server backend

this

/headdesk

You have my sympathies, sir.

IvenBach

@Comintern I can, if I understand what it means.

Comintern

@MathieuGuindon That isn't the Sage one is it?

Mathieu Guindon

yes. to be fair they did add a few indexes here and there

and stupid composite PKs

Hosch250

4:59 PM

LOL.

this

@MathieuGuindon did they actually put it to good use or did they just do it because it looks cool?

I guess the latter.

Hosch250

And now you get to re-work the DB into how it should've been the first time?

Or is it 100% hands-off, except helping with the queries?

Mathieu Guindon

seeing how my 8-second query spends 95% of its workload in the Sage DB with a missing index that has a 64.8703 impact, I wouldn't say they exactly "put it to good use"

@Hosch250 I can't modify the DB in any way

Comintern

I think part of the problem they run into in situations like that is that you can't really decide to add a constraint you didn't have like 20 years ago without accounting for all the customer data that you've allowed to become corrupt. It's like DB tech debt.

Hosch250

@MathieuGuindon :(

Mathieu Guindon

5:03 PM

GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[ICITEM] ([ITEMBRKID])
INCLUDE ([ITEMNO],[FMTITEMNO],[CATEGORY],[SEGMENT1],[SEGMENT2],[SEGMENT3])
GO

to be fair I don't think they could have realistically included that index out of the box

Comintern

Just be thankful it isn't InterSystems Caché. Every time I run across an infuriating DB setup I find myself saying, "at least it isn't Caché".

Mathieu Guindon

the needs depend on how you've setup your ICITEM structure

Caché isn't Kasher?

Comintern

It's an OODB.

Mathieu Guindon

pronounced "uh-oh DB"?

Comintern

^^ It compiles the objects from this bizarro cryptic definition language. Query performance is similar to hitting {F5} in VS.

Hosch250

5:06 PM

I've been busy :)

Mathieu Guindon

@Hosch250 someone's busy!

Hosch250

Working in a feature branch, and the ones with the red lines are just about to get squashed.

Comintern

Used widely by hospitals - I can never figure out why the healthcare industry gravitates toward shoddy tech.

2

FreeMan

@Comintern they spent all the cash on CAT & PET scanners 'cause those are kewl!!

IvenBach

In VS Ctrl+Alt+C navigates to the Call Stack. :+1: for learning VS.

FreeMan

5:12 PM

I'm SELECTing data from my DB and pasting it into a pre-formatted table (ListObject) in an Excel spreadsheet using newsheet.Cells(2,1).CopyFromRecordset Data (where Dim Data as ADODB.RecordSet), and my numeric formatting disappears.

Hosch250

@FreeMan I don't have a cat, so the CAT scanner will be useless for me.

FreeMan

I have columns formatted as 'Time' and they get reformatted to 'Date'. I have columns formatted as numeric.2 and they get reformatted to Date.

Any ideas why?

Hosch250

I do have a dog, so I guess they could maybe use the PET scanner, though.

FreeMan

@Hosch250 me either, so +1 on the dog/PET scanner

IvenBach

@Comintern Ctrl+- doesn't work with the NumPad. This makes sad, much more than it ought to.

Comintern

5:15 PM

@IvenBach Mine map to the forward and back browse buttons on my mouse.

This makes happy, much more than it ought to.

IvenBach

You just washed away all that sadness.

That's why this pond is full of awesome duck sauce.

FreeMan

mmmm.... duck sauce....

makes note to go out for Chinese tonight

@MathieuGuindon To be fair, it does. If you look closely, they probably never claim to have a good SQL Server backend.

Mathieu Guindon

@FreeMan sure it does. now if you want the sales orders you join on ITEMNO; if you want the purchase orders you join on FMTITEMNO, and if you're looking at inventory you join on ITEM. #fun

FreeMan

for some values of #fun

Mathieu Guindon

ItemID was too simple, I guess

FreeMan

5:20 PM

reminds me of my COBOL/Btrieve days...

shudder

Mathieu Guindon

best part is that the software lets you edit the item number

...which is the PK

Comintern

COBOL/Btrieve pays the big bucks now. Tons of installed code base and nobody knows how it works.

Mathieu Guindon

and guess whether it updates all the links?

of course it doesn't

FreeMan

@MathieuGuindon O_o

@Comintern Hrm... maybe I should contact some former coworkers & see if the boss/owner is gone yet. I kinda nuked that bridge from orbit when I ~~left~~ was booted for someone else's screw up, but it was really only with him...

of course, with what I've learned in the meantime would probably leave me a twitching, drooling vegetable at the end of the first day...

Comintern

lol

I think I'd only touch stuff like that as contract work.

FreeMan

5:28 PM

eh... they were all nice enough people (presuming the staff hasn't changed too much) except for the boss. If there were enough dollar signs, I might just be tempted...

Mathieu Guindon

#TIL left hash join tells the optimizer to do use a hash match instead of nested loops

doing that involves missing indexes on the Sage side though

FreeMan

OTOH, the company was bought by IBM, then sold back about 3 years later...

IvenBach

@FreeMan "Please take more than we paid you for, we don't want this." sort of return?

Comintern

@MathieuGuindon TIL too. I've set up computed fields in tables using a hash before, then put a unique constraint on it. It's great for keeping address duplicates out of tables.

FreeMan

@IvenBach Not really sure how the transaction went down, exactly, but I wouldn't be surprised

Mathieu Guindon

5:33 PM

depending on the data, the hashing might take longer than the nested loops though

IvenBach

@FreeMan The new filter by button. ~~It works!~~ It mostly works!

this

@MathieuGuindon and that's also where CE matters.

Mathieu Guindon

CE?

this

cardinality estimate

Mathieu Guindon

keep reading Code Explorer

this

5:42 PM

I only told you about it yesterday. ;)

LOL oh yeah

<sqlserver>CE</sqlserver> then

as opposed to <rubberduck>CE</rubberduck>

Mathieu Guindon

Compact Edition

#TooManyAcronyms

this

dead

IvenBach

Does VS have the same ability to show parameter info like VBA does with Ctrl+Shift+I? Or is it delete the left parenthesis ( and add it to have it show?

Mathieu Guindon

it does

but I always just re-type the parenthesis

also Ctrl+Shift+I is the RD Inspection Results toolwindow; parameter info is Ctrl+i ;-)

Comintern

^^ Yep. For me, that key combo is {backspace}.

Works if you re-type a comma too.

IvenBach

5:54 PM

I failed at using the search... stackoverflow.com/questions/3640544/… Ctrl+Space or Ctrl+J :+1: for muscle memory.

More aptly Ctrl+Shift+Space.

Comintern

I swear, I've had more kernel updates due to that stupid Intel predictive cache firmware bug in the last 3 months than I've had in the previous 2 years.

this

@Comintern the lower you go, the more widespread the ramifications are. I would hate to be the guy in charge of patching.

Comintern

IKR?

Hosch250

And I bet they just introduced a whole slough of new memory-overflow bugs and stuff too.

FreeMan

@IvenBach awesome!

Comintern

6:00 PM

Per the change logs they're mainly optimizations to make up for the performance hit.

this

> Per the change logs they're slowly re-introducing the original bugs

2

T, FTFY.

Hosch250

@this Huh?

this

@Hosch250 Considering that the bug was a result of them taking a shortcut (e.g. optimization), it's logical(!) to say that any optimizations they introduce will create the same bugs that we saw before, right? </facetious>

Hosch250

No, what was the acronym?

this

Oh There, fixed that for you

Hosch250

6:05 PM

Oh, OK.

Mathieu Guindon

oh wow look at that, someone (♪) added index EXT_ICITEM on dbo.ICITEM

6 seconds now

Hosch250

@MathieuGuindon We've got pages that load slower than that.

Mathieu Guindon

worst case, the index gets nuked when they update Sage, and the query goes back to 8 seconds

Hosch250

Nah.

Worst case, they can't update Sage because the index is there, and they run around screaming like chickens with their heads cut off.

Mathieu Guindon

I honestly can't fathom a reason why one stupid little index could screw that up

this

6:14 PM

unlikely if it's an non-unique index

the worst an non-unique index can do is making your insert/update a bit wee bit slower.

Mathieu Guindon

non unique, non clustered

this

if their software has crazy low timeout, then yeah, they'll run around like headless chickens

Mathieu Guindon

only clustered index inserts show up in execution plans though

@this LOL nope, not gonna happen then

I'm the one timing out

this

pretty sure that all indexes has to be updated. Haven't looked at a execution plan for insert/update in a while.

Mathieu Guindon

pretty sure too

esp. if the table is an in-memory table variable

this

6:17 PM

have you had a chance to put the memory table to use?

Mathieu Guindon

what?

my whole sproc is based on that memory table =)

this

the one from yesterday? hm, thought you were using table variable

Mathieu Guindon

not the same?

this

nope

it's a common misconception, even I got confused originally

but table variable isn't necessarily guaranteed to be in memory only.

Mathieu Guindon

IIUC @table == in-memory, and #table == on disk

this

6:19 PM

and since.... 2014? they introduced in-memory tables and memory-optimized sprocs.

Nope

Mathieu Guindon

I doubt I get get another second out of that query though

this

@table == maybe in memory with some disk writes and may spill to hard drive and #table == definitely on disk

Mathieu Guindon

I'm writing roughly 6K records into it

this

yeah, been wanting to find a good use case for in-memory table/memory-optimized sprocs. My main problem, though, is that performance benefit can get lost if you transition from a normal sproc to memory-optimized sproc and back.

has to be in its own space, basically.

Hosch250

@this TIL.

this

6:22 PM

sqlperformance.com/2017/04/performance-myths/…

Mathieu Guindon

Then again, I need to take that awesome query and now make it dynamic sql, because the SSRS report that uses it passes a @db parameter

and then SSRS has to get the records and render the report

so I don't think the squeeze is worth the juice

this

Incidentally the SO linked in the article above shows some dirty details about the table variable

@MathieuGuindon even if it were, I'm pretty sure the dynamic requirement precludes you from using it. A memory-optimized sproc is limited in what it can execute and IINM, dynamic SQL are not in the picture.

Mathieu Guindon

yeah

Phrancis

Ew, dynamic SQL... sorry to hear about that

this

6:39 PM

Normally I agree but dynamic SQL are useful when handled carefully for some use cases.

Mathieu Guindon

Like when you want one SP to feed one SSRS report off Sage data that can be in 5 different databases with identical schemas

Mathieu Guindon

7:12 PM

ugh. 6 seconds locally, and 3 minutes and counting through linked server

4 minutes

damn

mismatching collation isn't helping

5 minutes

fml

Mathieu Guindon

7:25 PM

WOOHOO!!

11 seconds using the correct collation

12 if I sort the output. I can live with that

#MissionAccomplished

2

Duga

[rubberduck-vba/Rubberduck] MDoerner assigned MDoerner to issue #4215: The variable was set in the wrong place and will generate error

IvenBach

Getting failing unit tests off of next. Is that expected?

Duga

7:40 PM

[rubberduck-vba/Rubberduck] IvenBach created pull request #4217: Issue4159 to merge IvenBach/Rubberduck/Issue4159 into rubberduck-vba/Rubberduck/next

> Close #4159 by allowing filtering buttons.

![untitled](https://user-images.githubusercontent.com/26076874/43021073-37983676-8c17-11e8-9fe1-f3d7d988b353.png)

During testing if you choose the Grouping option that's checked, By inspection type, you will have a momentary blank screen and then the results are populated. This doesn't occur when you select the non-chosen option. Is this worth fixing?

[rubberduck-vba/Rubberduck] IvenBach review_requested pull request #4217: Issue4159

IvenBach

@Duga Unit tests are failing. Even so, I still want to open the PR for more opportunity for reviews.

Duga

[rubberduck-vba/Rubberduck] IvenBach review_requested pull request #4217: Issue4159

this

@M.Doerner I hate to ask but I would like to close off the busy PR. I know you have your wedding coming up. What would you like to do about the PR?

M.Doerner

I'll have look later today.

this

Ok, thank you. I just want to be sure I did it right this time around and appreciate your help.

M.Doerner

7:51 PM

Does anybody know how to get the endOfStatement context preceding a blockStmt context?

this

don't those contexts normally have a endOfStmt method?

Duga

[rubberduck-vba/Rubberduck] [codecov[bot]](github.com/apps/codecov) created comment on pull request #4217: Issue4159

> # [Codecov](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/4217?src=pr&el=h1) Report
> Merging [#4217](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/4217?src=pr&el=desc) into [next](https://codecov.io/gh/rubberduck-vba/Rubberduck/commit/9d5f49a5e60e66eed77236da94402087a9dd86c0?src=pr&el=desc) will **decrease** coverage by `0.08%`.
> The diff coverage is `0%`.

```diff
@@ Coverage Diff @@
## next #4217 +/- ##
===========================

[rubberduck-vba/Rubberduck] build for commit 1b27d577 on unknown branch: 52.15% (target 0%)

[rubberduck-vba/Rubberduck] build for commit 1b27d577 on unknown branch: AppVeyor build succeeded

M.Doerner

My problem is that I do not need the endOfStmt of the blockStmt I have, but the one from the preceding context.

this

right. can't you get a token based on the index - 1 of the blockStmt's index?

@IvenBach how's the performance of the filtering/grouping?

IvenBach

8:15 PM

@this Performance? Instant when I was working with it.

Very odd. Noticing a small red line just above the headers when the filter Warning or None or Hint is chosen and then grouping By location is chosen.

this

how many inspections did u have

mainly curious if the increase in the inspections and decrease in performance are linear

Comintern

8:34 PM

@this @this IIR based on the control we're using, filtering would only effect performance if it was performed before binding. The thing is hella-inefficient in its use of the GDI buffer.

this

I was mainly curious because I noticed that Iven is holding 2 variables, one with all the results and other with the grouped/filtering applied

it really bugs the SQL boy in me.

(I know that things aren't really the same when dealing with in-memory collections vs. database table but....)

Comintern

Just looked at it, does that force a rebind? If so, I believe it would re-render the entire graphics buffer. Check out #2439.

this

Idea: use a web browser instead of WPF

Comintern

WPF is a freaking web browser. I'm a Winforms guy.

@IvenBach probably knows more WPF by now than I do.

Hosch250

You both oughta be burned as heretics :P

Comintern

8:46 PM

LOL

this

@Hosch250 I don't have a tic here.

IvenBach

@Comintern You egregiously underestimate my ignorance.

Comintern

@Hosch250 Would this force the control to re-bind and re-render?

        public ObservableCollection<IInspectionResult> Results
         {	         {
-            get => _results;	+            get => _resultsGroupedAndFiltered;
             private set	             private set
             {	             {
-                _results = value;	+                _resultsGroupedAndFiltered = value;
                 OnPropertyChanged();	                 OnPropertyChanged();
             }	             }
         }	         }

this

@Comintern If it were, then it wouldn't need so much memory... right? I betcha that if you put the same output in Edge and it'll perform better.

Hosch250

I'm not sure.

Comintern

8:48 PM

Oh hell, that was an ugly copy-paste.

Hosch250

Yeah, it will.

Comintern

@this I bet you put the same output in Edge and I'd want to see it in Chrome instead.

this

I really really wish they had better memory profiling tools for unmanaged memory. The promising candidates are long ago defunct. :\

@Comintern Rubberduck 2.3, now shipping with Chrome pre-installed!

Comintern

LOL

Hosch250

OTOH, IIRC, that performs better than clearing the items and re-adding them to the collection.

Comintern

8:50 PM

@this What about Valgrind?

Hosch250

Because that makes it remove the item and re-render at least part of it for every entry.

this

Really, the only way to improve performance is to buffer only what needs to be displayed.

(assuming the assertion about it buffering the whole shebang is true)

Hosch250

FWIW, I should take a look at that control again.

Comintern

Set it as your launch application in .NET, and then run your host application through that.

Hosch250

Probably need to re-design it from scratch.

this

8:51 PM

hmm. haven't seen that one yet....

Damn you Google, why can't you have shown that?!?

Comintern

IIR it's old as dirt.

this

oh

> 14 October 2017: We will have a Debugging Tools Devroom at FOSDEM 2018. The Call for Participation has recently been announced. The Devroom is on the first of the two FOSDEM days, on Sat 3 Feb 2018. See you all at FOSDEM in Brussels!

at least someone's updating the website.

better that than the other tool that Microsoft forgot about 10 years ago

IvenBach

@Hosch250 Let me know if it's taboo to not have commands. I couldn't get commands to work correctly.

Comintern

It's old enough that they describe prior versions (and only back to 1.9) as available for "archaeological" reasons.

Hosch250

@IvenBach So, what did you use?

Phrancis

8:53 PM

@Comintern s/tabs/spaces/

Comintern

@Phrancis I accidentally got both sides of the diff. :-(

Phrancis

Oh, lol

IvenBach

@Hosch250 github.com/IvenBach/Rubberduck/blob/Issue4159/Rubberduck.Core/… and had each one supply the correct parameter. #DidIDoItWrong

Hosch250

That's OK.

Maybe not optimal, but I did some similar stuff in the CE.

IvenBach

I was having an issue with using a command as that was done after the filtering.

ThunderFrame

9:08 PM

@MathieuGuindon using that subquery approach, it's also possible to combine Max and Count to achieve a Rank/Position calculated field. It's also good for linear interpolation and doing things like Bollinger Bands (20 day Moving Avg +/- 2 StdDev on Time Series data, not a musical group that exclusively consumes French champagne)

Phrancis

Great error message...

Comintern

The HRESULT aliased'd to 0x80004005 is about as specific - that's the eminently useful E_FAIL. It gets used alot as the "Oh my god, we're going down and I don't know why" error.

Hmmm... PIVOT 2.47 million rows over 10 columns to concat them in SQL or do them in code from the c# calling function.

ThunderFrame

9:31 PM

Gee, only 244 open issues authored by me - wins scope creep award

Phrancis

@Comintern SQL eats 2.47 million rows for breakfast in the morning

Duga

[rubberduck-vba/Rubberduck] MDoerner created pull request #4218: Fix introduce local variable to merge MDoerner/Rubberduck/FixIntroduceLocalVariable into rubberduck-vba/Rubberduck/next

> This PR closes #4215

I chose to put the variable declaration right before the enclosing block statement. If a different position is prefered, I can see what I can do about it.

this

@Comintern Generally, I'd rather do it in SQL first. It's easy to write SQL and if you're not happy with performance, to go to the trouble of setting a C# CLR.

as a rule, though, PIVOT most likely blows away the sargability, so.

Comintern

Yeah, I'm going to have a ton of memory pressure too. Better to let the server handle it.

Duga

9:56 PM

[rubberduck-vba/Rubberduck] build for commit 63ea7498 on unknown branch: AppVeyor build succeeded

[rubberduck-vba/Rubberduck] [codecov[bot]](github.com/apps/codecov) created comment on pull request #4218: Fix introduce local variable

> # [Codecov](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/4218?src=pr&el=h1) Report
> Merging [#4218](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/4218?src=pr&el=desc) into [next](https://codecov.io/gh/rubberduck-vba/Rubberduck/commit/9d5f49a5e60e66eed77236da94402087a9dd86c0?src=pr&el=desc) will **increase** coverage by `0.06%`.
> The diff coverage is `86.67%`.

```diff
@@ Coverage Diff @@
## next #4218 +/- ##
=======================

[rubberduck-vba/Rubberduck] build for commit 63ea7498 on unknown branch: 52.29% (target 0%)

Transcript for